Tutoriel avancé

Apprenez à ingérer et à interroger des données dans ClickHouse à l’aide d’un jeu de données d’exemple sur les taxis de New York.

Vue d’ensemble

Apprenez à ingérer et à interroger des données dans ClickHouse à l’aide du jeu de données d’exemple des taxis de New York.

Prérequis

Vous devez avoir accès à un service ClickHouse actif pour suivre ce tutoriel. Pour obtenir les instructions, consultez le guide de Démarrage rapide.

1. Créer une nouvelle table

   Le jeu de données des taxis de New York contient des informations sur des millions de courses, avec des colonnes telles que le montant du pourboire, les péages, le type de paiement, etc. Créez une table pour stocker ces données.

   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;
      
2. Ajouter le jeu de données

   Maintenant que vous avez créé une table, ajoutez les données des taxis de New York à partir de fichiers CSV dans S3.

   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.

3. Analyser les données

   Exécutez des requêtes pour analyser les données. Explorez les exemples suivants ou essayez votre propre requête SQL.

   • 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 │
      

4. Créer un Dictionary

   Un dictionnaire est une correspondance de paires clé-valeur stockées en mémoire. Pour plus de détails, voir Dictionaries

   Créez un dictionnaire associé à une table dans votre service ClickHouse. La table et le dictionnaire sont basés sur un fichier CSV contenant une ligne par quartier de New York City.

   Les quartiers sont associés aux noms des cinq arrondissements de New York (Bronx, Brooklyn, Manhattan, Queens et Staten Island), ainsi qu’à l’aéroport de Newark (EWR).

   Voici un extrait du fichier CSV que vous utilisez, présenté sous forme de tableau. La colonne LocationID du fichier correspond aux colonnes pickup_nyct2010_gid et dropoff_nyct2010_gid de votre table trips :

   LocationID	Borough	Zone	service_zone
   1	EWR	Aéroport de Newark	EWR
   2	Queens	Jamaica Bay	Boro Zone
   3	Bronx	Allerton/Pelham Gardens	Boro Zone
   4	Manhattan	Alphabet City	Zone jaune
   5	Staten Island	Arden Heights	Zone de borough

   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())
   

   Définir LIFETIME à 0 désactive les mises à jour automatiques afin d’éviter tout trafic inutile vers notre bucket S3. Dans les autres cas, vous pouvez le configurer autrement. Pour plus de détails, consultez Actualisation des données du dictionnaire avec LIFETIME.

   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.)
      
5. Effectuer une jointure

   Écrivez quelques requêtes qui joignent taxi_zone_dictionary à votre table trips.

   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.)
      

   Notez que le résultat de la requête JOIN ci-dessus est identique à celui de la requête précédente utilisant dictGetOrDefault (sauf que les valeurs Unknown ne sont pas incluses). En arrière-plan, ClickHouse appelle en réalité la fonction dictGet pour le dictionnaire taxi_zone_dictionary, mais la syntaxe JOIN est plus familière aux développeurs SQL.

   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
      

   En général, évitez d’utiliser trop souvent SELECT * dans ClickHouse. Récupérez uniquement les colonnes dont vous avez réellement besoin.

Prochaines étapes

Pour en savoir plus sur ClickHouse, consultez la documentation suivante :

• Introduction aux index primaires clairsemés dans ClickHouse : découvrez comment ClickHouse utilise des index primaires clairsemés pour localiser efficacement les données pertinentes lors des requêtes.
• Intégrer une source de données externe : découvrez les options d’intégration de sources de données, notamment les fichiers, Kafka, PostgreSQL, les pipelines de données et bien d’autres.
• Visualiser des données dans ClickHouse : connectez votre outil UI/BI favori à ClickHouse.
• Référence SQL : parcourez les fonctions SQL disponibles dans ClickHouse pour transformer, traiter et analyser les données.