events

Uses month and date of cycling races to show how to get ranges that span clustering columns. Example of copying data from a CSV into the table.

events table

SOURCE '0_create_keyspace.cql';

DROP TABLE IF EXISTS cycling.events;

// START-events_table
CREATE TABLE IF NOT EXISTS cycling.events (
  year int,
  start_month int,
  start_day int,
  end_month int,
  end_day int,
  race text,
  discipline text,
  location text,
  uci_code text,
  PRIMARY KEY (
    (year, discipline), start_month, start_day, race
  )
);
// END-events_table

COPY cycling.events
FROM 'events-data.csv'
WITH HEADER = true
  AND DELIMITER = '|';

// START-select_with_in_and_equals_allow_filtering
SELECT *
FROM cycling.events
WHERE race = 'Superprestige - Hoogstraten -2017'
  AND start_month IN (1, 2)
ALLOW FILTERING;
// END-select_with_in_and_equals_allow_filtering

// START-select_count
SELECT start_month, MAX(start_day), COUNT(*)
FROM cycling.events
WHERE year = 2017
  AND discipline = 'Cyclo-cross';
// END-select_count

CAPTURE 'select_all_from_events_with_order_by.results';
// START-select_with_order_by
SELECT *
FROM cycling.events
WHERE year = 2017
  AND discipline = 'Cyclo-cross'
  AND start_month = 1
  AND start_day = 1
ORDER BY race;
// END-select_with_order_by
CAPTURE OFF;

// START-select_with_order_multiple_columns
SELECT *
FROM cycling.events
WHERE year = 2017
  AND discipline = 'Cyclo-cross'
  AND start_month = 1
ORDER BY start_day, race;
// END-select_with_order_multiple_columns

// START-select_with_where
SELECT *
FROM cycling.events
WHERE year = 2017
  AND discipline = 'Cyclo-cross'
  AND start_month = 1
  AND start_day = 1;
// END-select_with_where

CAPTURE 'select_from_events_non_existing_year.results';
// START-select_with_non_existing_year
SELECT start_month, MAX(start_day)
FROM cycling.events
WHERE year = 2022
ALLOW FILTERING;
// END-select_with_non_existing_year
CAPTURE OFF;

Data from CSV

Insert the following comma separated data:

Year|Discipline|Start_Month|Start_Day|Race|End_Month|End_Day|Location|UCI_code
2017|Cyclo-cross|1|1|DVV verzekeringen trofee - GP Sven Nys|||Baal|C1 
2017|Road|1|1|Mitchelton Bay Cycling Classic|1|3|Geelong Victoria|CRT 
2017|Road|1|4|Cycling Australia Road National Championships|1|8| Ballarat| CN 
2017|Cyclo-cross|1|8|Belgian Cyclo-cross National Championships|||Oostende|CN 
2017|Cyclo-cross|1|8|British Cyclo-cross National Championships|||Bradford|CN 
2017|Cyclo-cross|1|8|Dutch Cyclo-cross National Championships|||Holland|CN 
2017|Cyclo-cross|1|8|USA Cycling Cyclo-Cross National Championships|||Hartford CT|CN 
2017|Road|1|14|Santos Womens Tour|1|17|South Australia|WE 
2017|Road|1|15|Tour Down Under|1|22|South Australia|WT 
2017|Cyclo-cross|1|15|UCI Cyclo-cross World Cup Fiuggi Regione Lazio|||Fiuggi|CDM 
2017|Cyclo-cross|1|22|UCI Cyclo-cross World Cup Hoogerheide|||Hoogerheide|CDM 
2017|Road|1|23|Vuelta Ciclista a la Provincia de San Juan|1|29|San Juan| 
2017|Road|1|26|Cadel Evans Great Ocean Road Race - Towards Zero Race Melbourne|||Melbourne|CRT 
2017|Road|1|26|Challenge Mallorca: Trofeo Porreres-Felanitx-Ses Salines-Campos|||Mallorca| 
2017|Road|1|28|Challenge Mallorca: Trofeo Serra de Tramuntana -2017|||Mallorca| 
2017|Road|1|28|Cadel Evans Great Ocean Road Race|||Geelong| WE 
2017|Road|1|28|Challenge Mallorca: Trofeo Andratx-Mirador des Colomer|||Mallorca| 
2017|Cyclo-cross|1|28|UCI Cyclo-cross World Championships|1|29|Bieles|CM 
2017|Road|1|29|Cadel Evans Great Ocean Road Race|||Geelong|WT 
2017|Road|1|29|Grand Prix Cycliste la Marseillaise|||France| 
2017|Road|1|29|Mallorca Challenge: Trofeo Palma|||Mallorca| 
2017|Road|1|29|Dubai Tour|2|4|Dubai| 2.1 HC
2017|Road|1|31|Ladies Tour of Qatar|2|3|Qatar|WE 
2017|Road|2|1|Jayco Herald Sun Tour|2|5|Victoria| 
2017|Road|2|1|Volta a la Comunitat Valenciana|2|5|Valencia| 
2017|Road|2|1|Etoile de Besseges|2|5|Languedoc-Roussillon| 
2017|Cyclo-cross|2|4|DVV verzekeringen trofee - Krawatencross|||Lille|C1 
2017|Cyclo-cross|2|4|DVV verzekeringen trofee - Krawatencross|||Lille|C1 
2017|Road|2|5|G.P. Costa degli Etruschi|||Italy| 
2017|Cyclo-cross|2|4|Superprestige - Hoogstraten -2017|||Belgium|C1 
2017|Road|2|6|Tour of Qatar|2|10|Qatar|WT 
2017|Road|2|9|South African Road Championships|2|12|Western Cape|CN 
2017|Cyclo-cross|2|11|Superprestige - Middelkerke -2017|||Middelkerke|C1 
2017|Road|2|11|Vuelta Ciclista a la Region de Murcia|||Murcia |1.1 
2017|Road|2|12|Clasica de Almeria|||Almeria|1.1 
2017|Road|2|11|Trofeo Laigueglia|||Liguria|HC 
2017|Road|2|14|Tour of Oman|2|19|Oman|  HC 
2017|Road|2|15|Volta ao Algarve em Bicicleta|2|19|Algarve|HC 
2017|Road|2|15|Vuelta a Andalucia Ruta Ciclista Del Sol|2|19|Andalucia|HC
2017|Track|2|17|UCI Track World Cup III|2|19|  Cali|CDM 
2017|Road|2|18|Tour Cycliste International du Haut Var-matin|2|19|Provence  |2.1 
2017|Road|2|21|Tour Cycliste International La Provence|2|13|Provence|2.1 
2017|Road|2|22|Le Tour de Langkawi|3|1|Langkawi|HC 
2017|Road|2|23|Abu Dhabi Tour|2|26|Abu Dhabi|WT 
2017|Road|2|25|Faun Environnement - Classic de lArdeche Rhone Crussol|||Ardeche   | 1.1 
2017|Road|2|25|Omloop Het Nieuwsblad Elite|||Belgium|WT 
2017|Road|2|25|Omloop Het Nieuwsblad Elite Women|||Gent|WWT 
2017|Track|2|25|UCI Track World Cup IV    Track|2|26|Los Angeles|CDM 
2017|Road|2|26|Kuurne-Brussel-Kuurne|||Kuurne|HC 
2017|Road|2|26|Omloop van het Hageland - Tielt-Winge|||Belgium|WE 
2017|Road|3|1|Le Samyn|||Hainaut|1.1 
2017|Road|3|1|Le Samyn des Dames|||Hainaut|WE 
2017|Road|3|4|Strade Bianche|||Tuscany|WT 
2017|Road|3|4|Strade Bianche Women|||Tuscany|WWT 
2017|Road|3|5|Dwars door West-Vlaanderen Johan Museeuw Classic|||Flanders| 1.1 
2017|Road|3|5|GP Industria and Artigianato|||Larciano|HC 
2017|Road|3|5|Paris - Nice|3|12|France|WT 
2017|Road|3|8|Semana Ciclista Valenciana|3|11|Valencia|WE 
2017|Road|3|8|Tirreno-Adriatico|3|14|Italy|WT 
2017|Road|3|8|Oceania Championships|3|14|Canberra|CC 
2017|Road|3|11|Ronde van Drenthe|||Netherlands|WWT 
2017|Road|3|12|Drentse Acht van Westerveld|||Dwingeloo|WE 
2017|Road|3|15|Nokere Koerse - Danilith Classic|||Flanders|HC 
2017|Road|3|17|Handzame Classic|||Flanders|1.1 
2017|Road|3|18|Classic Loire Atlantique|||Loire|1.1 
2017|Road|3|18|Milan-San Remo|||Italy|WT 
2017|Road|3|18|Tucson Bicycle Classic|||Tucson Arizona|NE 
2017|Mountain Bike|3|19|Cape Epic|3|26|Cape Town|SHC 
2017|Road|3|19|Trofeo Alfredo Binda Comune di Cittiglio|||Italy|WWT 
2017|Road|3|20|Volta Ciclista a Catalunya|3|26|Catalunya|WT 
2017|Road|3|22|Dwars Door Vlaanderen|||Flanders|WT 
2017|Road|3|22|Dwars door Vlaanderen women|||Waregem|WE 
2017|Road|3|23|Settimana Internazionale Coppi e Bartali|3|26|Emilia-Romagna|2.1 
2017|Road|3|24|E3 Harelbeke|||Flanders|WT 
2017|Road|3|26|Gent Wevelgem|||Flanders|WT 
2017|Road|3|26|Gent Wevelgem Women|||Belgium|WWT 
2017|Road|3|28|Driedaagse De Panne-Koksijde|3|30|West Flanders|HC 
2017|Road|3|29|Pajot Hills Classic|||Flemish Brabant|WE