Tutoriel avancé

​

Créer une nouvelle table

1. Connectez-vous à la console SQL :
   • Pour ClickHouse Cloud, sélectionnez un service dans le menu déroulant, puis SQL Console dans le menu de navigation de gauche.
   • Pour un ClickHouse autogéré, connectez-vous à la console SQL à l’adresse https://_hostname_:8443/play. Renseignez-vous auprès de votre administrateur ClickHouse pour obtenir les détails.
2. Créez la table trips suivante dans la base de données default :
   CREATE TABLE trips
   (
       `trip_id` UInt32,
       `vendor_id` Enum8('1' = 1, '2' = 2, '3' = 3, '4' = 4, 'CMT' = 5, 'VTS' = 6, 'DDS' = 7, 'B02512' = 10, 'B02598' = 11, 'B02617' = 12, 'B02682' = 13, 'B02764' = 14, '' = 15),
       `pickup_date` Date,
       `pickup_datetime` DateTime,
       `dropoff_date` Date,
       `dropoff_datetime` DateTime,
       `store_and_fwd_flag` UInt8,
       `rate_code_id` UInt8,
       `pickup_longitude` Float64,
       `pickup_latitude` Float64,
       `dropoff_longitude` Float64,
       `dropoff_latitude` Float64,
       `passenger_count` UInt8,
       `trip_distance` Float64,
       `fare_amount` Float32,
       `extra` Float32,
       `mta_tax` Float32,
       `tip_amount` Float32,
       `tolls_amount` Float32,
       `ehail_fee` Float32,
       `improvement_surcharge` Float32,
       `total_amount` Float32,
       `payment_type` Enum8('UNK' = 0, 'CSH' = 1, 'CRE' = 2, 'NOC' = 3, 'DIS' = 4),
       `trip_type` UInt8,
       `pickup` FixedString(25),
       `dropoff` FixedString(25),
       `cab_type` Enum8('yellow' = 1, 'green' = 2, 'uber' = 3),
       `pickup_nyct2010_gid` Int8,
       `pickup_ctlabel` Float32,
       `pickup_borocode` Int8,
       `pickup_ct2010` String,
       `pickup_boroct2010` String,
       `pickup_cdeligibil` String,
       `pickup_ntacode` FixedString(4),
       `pickup_ntaname` String,
       `pickup_puma` UInt16,
       `dropoff_nyct2010_gid` UInt8,
       `dropoff_ctlabel` Float32,
       `dropoff_borocode` UInt8,
       `dropoff_ct2010` String,
       `dropoff_boroct2010` String,
       `dropoff_cdeligibil` String,
       `dropoff_ntacode` FixedString(4),
       `dropoff_ntaname` String,
       `dropoff_puma` UInt16
   )
   ENGINE = MergeTree
   PARTITION BY toYYYYMM(pickup_date)
   ORDER BY pickup_datetime;
   

​

Ajouter le jeu de données

1. La commande suivante insère environ 2 000 000 lignes dans votre table trips à partir de deux fichiers distincts dans S3 : trips_1.tsv.gz et trips_2.tsv.gz :
   INSERT INTO trips
   SELECT * FROM s3(
       'https://datasets-documentation.s3.eu-west-3.amazonaws.com/nyc-taxi/trips_{1..2}.gz',
       'TabSeparatedWithNames', "
       `trip_id` UInt32,
       `vendor_id` Enum8('1' = 1, '2' = 2, '3' = 3, '4' = 4, 'CMT' = 5, 'VTS' = 6, 'DDS' = 7, 'B02512' = 10, 'B02598' = 11, 'B02617' = 12, 'B02682' = 13, 'B02764' = 14, '' = 15),
       `pickup_date` Date,
       `pickup_datetime` DateTime,
       `dropoff_date` Date,
       `dropoff_datetime` DateTime,
       `store_and_fwd_flag` UInt8,
       `rate_code_id` UInt8,
       `pickup_longitude` Float64,
       `pickup_latitude` Float64,
       `dropoff_longitude` Float64,
       `dropoff_latitude` Float64,
       `passenger_count` UInt8,
       `trip_distance` Float64,
       `fare_amount` Float32,
       `extra` Float32,
       `mta_tax` Float32,
       `tip_amount` Float32,
       `tolls_amount` Float32,
       `ehail_fee` Float32,
       `improvement_surcharge` Float32,
       `total_amount` Float32,
       `payment_type` Enum8('UNK' = 0, 'CSH' = 1, 'CRE' = 2, 'NOC' = 3, 'DIS' = 4),
       `trip_type` UInt8,
       `pickup` FixedString(25),
       `dropoff` FixedString(25),
       `cab_type` Enum8('yellow' = 1, 'green' = 2, 'uber' = 3),
       `pickup_nyct2010_gid` Int8,
       `pickup_ctlabel` Float32,
       `pickup_borocode` Int8,
       `pickup_ct2010` String,
       `pickup_boroct2010` String,
       `pickup_cdeligibil` String,
       `pickup_ntacode` FixedString(4),
       `pickup_ntaname` String,
       `pickup_puma` UInt16,
       `dropoff_nyct2010_gid` UInt8,
       `dropoff_ctlabel` Float32,
       `dropoff_borocode` UInt8,
       `dropoff_ct2010` String,
       `dropoff_boroct2010` String,
       `dropoff_cdeligibil` String,
       `dropoff_ntacode` FixedString(4),
       `dropoff_ntaname` String,
       `dropoff_puma` UInt16
   ") SETTINGS input_format_try_infer_datetimes = 0
   
2. Attendez la fin de l’INSERT. Le téléchargement des 150 MB de données peut prendre un certain temps.
3. Une fois l’insert terminé, vérifiez qu’il a bien fonctionné :
   SELECT count() FROM trips
   
   Cette requête doit renvoyer 1,999,657 lignes.

​

Analyser les données

• Calculez le montant moyen des pourboires :
  SELECT round(avg(tip_amount), 2) FROM trips
  
  Résultat attendu

  ┌─round(avg(tip_amount), 2)─┐
  │                      1.68 │
  └───────────────────────────┘
  

• Calculez le coût moyen selon le nombre de passagers :
  SELECT
      passenger_count,
      ceil(avg(total_amount),2) AS average_total_amount
  FROM trips
  GROUP BY passenger_count
  
  Résultat attendu

  Le passenger_count est compris entre 0 et 9 :

  ┌─passenger_count─┬─average_total_amount─┐
  │               0 │                22.69 │
  │               1 │                15.97 │
  │               2 │                17.15 │
  │               3 │                16.76 │
  │               4 │                17.33 │
  │               5 │                16.35 │
  │               6 │                16.04 │
  │               7 │                 59.8 │
  │               8 │                36.41 │
  │               9 │                 9.81 │
  └─────────────────┴──────────────────────┘
  

• Calculez le nombre de prises en charge quotidiennes par quartier :
  SELECT
      pickup_date,
      pickup_ntaname,
      SUM(1) AS number_of_trips
  FROM trips
  GROUP BY pickup_date, pickup_ntaname
  ORDER BY pickup_date ASC
  
  Résultat attendu

  ┌─pickup_date─┬─pickup_ntaname───────────────────────────────────────────┬─number_of_trips─┐
  │  2015-07-01 │ Brooklyn Heights-Cobble Hill                             │              13 │
  │  2015-07-01 │ Old Astoria                                              │               5 │
  │  2015-07-01 │ Flushing                                                 │               1 │
  │  2015-07-01 │ Yorkville                                                │             378 │
  │  2015-07-01 │ Gramercy                                                 │             344 │
  │  2015-07-01 │ Fordham South                                            │               2 │
  │  2015-07-01 │ SoHo-TriBeCa-Civic Center-Little Italy                   │             621 │
  │  2015-07-01 │ Park Slope-Gowanus                                       │              29 │
  │  2015-07-01 │ Bushwick South                                           │               5 │
  

• Calculez la durée de chaque trajet en minutes, puis regroupez les résultats en fonction de la durée du trajet :
  SELECT
      avg(tip_amount) AS avg_tip,
      avg(fare_amount) AS avg_fare,
      avg(passenger_count) AS avg_passenger,
      count() AS count,
      truncate(date_diff('second', pickup_datetime, dropoff_datetime)/60) as trip_minutes
  FROM trips
  WHERE trip_minutes > 0
  GROUP BY trip_minutes
  ORDER BY trip_minutes DESC
  
  Résultat attendu

  ┌──────────────avg_tip─┬───────────avg_fare─┬──────avg_passenger─┬──count─┬─trip_minutes─┐
  │   1.9600000381469727 │                  8 │                  1 │      1 │        27511 │
  │                    0 │                 12 │                  2 │      1 │        27500 │
  │    0.542166673981895 │ 19.716666666666665 │ 1.9166666666666667 │     60 │         1439 │
  │    0.902499997522682 │ 11.270625001192093 │            1.95625 │    160 │         1438 │
  │   0.9715789457909146 │ 13.646616541353383 │ 2.0526315789473686 │    133 │         1437 │
  │   0.9682692398245518 │ 14.134615384615385 │  2.076923076923077 │    104 │         1436 │
  │   1.1022105210705808 │ 13.778947368421052 │  2.042105263157895 │     95 │         1435 │
  

• Affichez le nombre de prises en charge dans chaque quartier, réparti par heure de la journée :
  SELECT
      pickup_ntaname,
      toHour(pickup_datetime) as pickup_hour,
      SUM(1) AS pickups
  FROM trips
  WHERE pickup_ntaname != ''
  GROUP BY pickup_ntaname, pickup_hour
  ORDER BY pickup_ntaname, pickup_hour
  
  Résultat attendu

  ┌─pickup_ntaname───────────────────────────────────────────┬─pickup_hour─┬─pickups─┐
  │ Airport                                                  │           0 │    3509 │
  │ Airport                                                  │           1 │    1184 │
  │ Airport                                                  │           2 │     401 │
  │ Airport                                                  │           3 │     152 │
  │ Airport                                                  │           4 │     213 │
  │ Airport                                                  │           5 │     955 │
  │ Airport                                                  │           6 │    2161 │
  │ Airport                                                  │           7 │    3013 │
  │ Airport                                                  │           8 │    3601 │
  │ Airport                                                  │           9 │    3792 │
  │ Airport                                                  │          10 │    4546 │
  │ Airport                                                  │          11 │    4659 │
  │ Airport                                                  │          12 │    4621 │
  │ Airport                                                  │          13 │    5348 │
  │ Airport                                                  │          14 │    5889 │
  │ Airport                                                  │          15 │    6505 │
  │ Airport                                                  │          16 │    6119 │
  │ Airport                                                  │          17 │    6341 │
  │ Airport                                                  │          18 │    6173 │
  │ Airport                                                  │          19 │    6329 │
  │ Airport                                                  │          20 │    6271 │
  │ Airport                                                  │          21 │    6649 │
  │ Airport                                                  │          22 │    6356 │
  │ Airport                                                  │          23 │    6016 │
  │ Allerton-Pelham Gardens                                  │           4 │       1 │
  │ Allerton-Pelham Gardens                                  │           6 │       1 │
  │ Allerton-Pelham Gardens                                  │           7 │       1 │
  │ Allerton-Pelham Gardens                                  │           9 │       5 │
  │ Allerton-Pelham Gardens                                  │          10 │       3 │
  │ Allerton-Pelham Gardens                                  │          15 │       1 │
  │ Allerton-Pelham Gardens                                  │          20 │       2 │
  │ Allerton-Pelham Gardens                                  │          23 │       1 │
  │ Annadale-Huguenot-Prince's Bay-Eltingville               │          23 │       1 │
  │ Arden Heights                                            │          11 │       1 │
  

7. Récupérez les trajets à destination des aéroports de LaGuardia ou de JFK :
   SELECT
       pickup_datetime,
       dropoff_datetime,
       total_amount,
       pickup_nyct2010_gid,
       dropoff_nyct2010_gid,
       CASE
           WHEN dropoff_nyct2010_gid = 138 THEN 'LGA'
           WHEN dropoff_nyct2010_gid = 132 THEN 'JFK'
       END AS airport_code,
       EXTRACT(YEAR FROM pickup_datetime) AS year,
       EXTRACT(DAY FROM pickup_datetime) AS day,
       EXTRACT(HOUR FROM pickup_datetime) AS hour
   FROM trips
   WHERE dropoff_nyct2010_gid IN (132, 138)
   ORDER BY pickup_datetime
   
   Résultat attendu

   ┌─────pickup_datetime─┬────dropoff_datetime─┬─total_amount─┬─pickup_nyct2010_gid─┬─dropoff_nyct2010_gid─┬─airport_code─┬─year─┬─day─┬─hour─┐
   │ 2015-07-01 00:04:14 │ 2015-07-01 00:15:29 │         13.3 │                 -34 │                  132 │ JFK          │ 2015 │   1 │    0 │
   │ 2015-07-01 00:09:42 │ 2015-07-01 00:12:55 │          6.8 │                  50 │                  138 │ LGA          │ 2015 │   1 │    0 │
   │ 2015-07-01 00:23:04 │ 2015-07-01 00:24:39 │          4.8 │                -125 │                  132 │ JFK          │ 2015 │   1 │    0 │
   │ 2015-07-01 00:27:51 │ 2015-07-01 00:39:02 │        14.72 │                -101 │                  138 │ LGA          │ 2015 │   1 │    0 │
   │ 2015-07-01 00:32:03 │ 2015-07-01 00:55:39 │        39.34 │                  48 │                  138 │ LGA          │ 2015 │   1 │    0 │
   │ 2015-07-01 00:34:12 │ 2015-07-01 00:40:48 │         9.95 │                 -93 │                  132 │ JFK          │ 2015 │   1 │    0 │
   │ 2015-07-01 00:38:26 │ 2015-07-01 00:49:00 │         13.3 │                 -11 │                  138 │ LGA          │ 2015 │   1 │    0 │
   │ 2015-07-01 00:41:48 │ 2015-07-01 00:44:45 │          6.3 │                 -94 │                  132 │ JFK          │ 2015 │   1 │    0 │
   │ 2015-07-01 01:06:18 │ 2015-07-01 01:14:43 │        11.76 │                  37 │                  132 │ JFK          │ 2015 │   1 │    1 │
   

​

Créer un Dictionary

1. Exécutez la commande SQL suivante, qui crée un dictionnaire nommé taxi_zone_dictionary et alimente le dictionnaire à partir du fichier CSV stocké dans S3. L’URL du fichier est https://datasets-documentation.s3.eu-west-3.amazonaws.com/nyc-taxi/taxi_zone_lookup.csv.

CREATE DICTIONARY taxi_zone_dictionary
(
  `LocationID` UInt16 DEFAULT 0,
  `Borough` String,
  `Zone` String,
  `service_zone` String
)
PRIMARY KEY LocationID
SOURCE(HTTP(URL 'https://datasets-documentation.s3.eu-west-3.amazonaws.com/nyc-taxi/taxi_zone_lookup.csv' FORMAT 'CSVWithNames'))
LIFETIME(MIN 0 MAX 0)
LAYOUT(HASHED_ARRAY())

3. Vérifiez que tout a bien fonctionné. La requête suivante doit renvoyer 265 lignes, soit une ligne pour chaque quartier :
   SELECT * FROM taxi_zone_dictionary
   
4. Utilisez la fonction dictGet (ou ses variantes) pour récupérer une valeur dans un dictionnaire. Indiquez le nom du dictionnaire, la valeur souhaitée et la clé (qui, dans notre exemple, est la colonne LocationID de taxi_zone_dictionary). Par exemple, la requête suivante renvoie le Borough dont le LocationID est 132, ce qui correspond à l’aéroport JFK) :
   SELECT dictGet('taxi_zone_dictionary', 'Borough', 132)
   
   JFK se trouve dans le Queens. Notez que le temps nécessaire pour récupérer la valeur est quasiment nul :
   ┌─dictGet('taxi_zone_dictionary', 'Borough', 132)─┐
   │ Queens                                          │
   └─────────────────────────────────────────────────┘
   
   1 rows in set. Elapsed: 0.004 sec.
   
5. Utilisez la fonction dictHas pour vérifier si une clé est présente dans le dictionnaire. Par exemple, la requête suivante renvoie 1 (ce qui correspond à “true” dans ClickHouse) :
   SELECT dictHas('taxi_zone_dictionary', 132)
   
6. La requête suivante renvoie 0, car 4567 n’est pas une valeur de LocationID dans le dictionnaire :
   SELECT dictHas('taxi_zone_dictionary', 4567)
   
7. Utilisez la fonction dictGet pour obtenir le nom d’un borough dans une requête. Par exemple :
   SELECT
       count(1) AS total,
       dictGetOrDefault('taxi_zone_dictionary','Borough', toUInt64(pickup_nyct2010_gid), 'Unknown') AS borough_name
   FROM trips
   WHERE dropoff_nyct2010_gid = 132 OR dropoff_nyct2010_gid = 138
   GROUP BY borough_name
   ORDER BY total DESC
   
   Cette requête calcule le nombre de courses de taxi par arrondissement qui se terminent à l’aéroport de LaGuardia ou à celui de JFK. Le résultat se présente comme suit, et notez qu’il y a pas mal de trajets dont le quartier de prise en charge est inconnu :
   ┌─total─┬─borough_name──┐
   │ 23683 │ Unknown       │
   │  7053 │ Manhattan     │
   │  6828 │ Brooklyn      │
   │  4458 │ Queens        │
   │  2670 │ Bronx         │
   │   554 │ Staten Island │
   │    53 │ EWR           │
   └───────┴───────────────┘
   
   7 rows in set. Elapsed: 0.019 sec. Processed 2.00 million rows, 4.00 MB (105.70 million rows/s., 211.40 MB/s.)
   

​

Effectuer une jointure

1. Commencez par un JOIN simple, similaire à la requête précédente sur l’aéroport :
   SELECT
       count(1) AS total,
       Borough
   FROM trips
   JOIN taxi_zone_dictionary ON toUInt64(trips.pickup_nyct2010_gid) = taxi_zone_dictionary.LocationID
   WHERE dropoff_nyct2010_gid = 132 OR dropoff_nyct2010_gid = 138
   GROUP BY Borough
   ORDER BY total DESC
   
   La réponse est identique à celle de la requête dictGet :
   ┌─total─┬─Borough───────┐
   │  7053 │ Manhattan     │
   │  6828 │ Brooklyn      │
   │  4458 │ Queens        │
   │  2670 │ Bronx         │
   │   554 │ Staten Island │
   │    53 │ EWR           │
   └───────┴───────────────┘
   
   6 rows in set. Elapsed: 0.034 sec. Processed 2.00 million rows, 4.00 MB (59.14 million rows/s., 118.29 MB/s.)
   

2. Cette requête renvoie les lignes correspondant aux 1000 trajets avec le montant de pourboire le plus élevé, puis effectue une INNER JOIN entre chaque ligne et le dictionnaire :
   SELECT *
   FROM trips
   JOIN taxi_zone_dictionary
       ON trips.dropoff_nyct2010_gid = taxi_zone_dictionary.LocationID
   WHERE tip_amount > 0
   ORDER BY tip_amount DESC
   LIMIT 1000