> ## Documentation Index > Fetch the complete documentation index at: https://private-7c7dfe99-mintlify-8c05c8a2.mintlify.site/llms.txt > Use this file to discover all available pages before exploring further. # 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](/fr/get-started/setup/install). ## 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` : ```sql theme={null} 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 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` : ```sql theme={null} 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é : ```sql theme={null} SELECT count() FROM trips ``` Cette requête doit renvoyer 1,999,657 lignes. ## 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 : ```sql theme={null} SELECT round(avg(tip_amount), 2) FROM trips ```

```response theme={null} ┌─round(avg(tip_amount), 2)─┐ │ 1.68 │ └───────────────────────────┘ ```

* Calculez le coût moyen selon le nombre de passagers : ```sql theme={null} SELECT passenger_count, ceil(avg(total_amount),2) AS average_total_amount FROM trips GROUP BY passenger_count ```

Le `passenger_count` est compris entre 0 et 9 : ```response theme={null} ┌─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 : ```sql theme={null} SELECT pickup_date, pickup_ntaname, SUM(1) AS number_of_trips FROM trips GROUP BY pickup_date, pickup_ntaname ORDER BY pickup_date ASC ```

```response theme={null} ┌─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 : ```sql theme={null} 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 ```

```response theme={null} ┌──────────────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 : ```sql theme={null} 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 ```

```response theme={null} ┌─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 : ```sql theme={null} 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 ```

```response theme={null} ┌─────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 Un dictionnaire est une correspondance de paires clé-valeur stockées en mémoire. Pour plus de détails, voir [Dictionaries](/fr/reference/statements/create/dictionary) 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`. ```sql theme={null} 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](/fr/reference/statements/create/dictionary/lifetime). 3. Vérifiez que tout a bien fonctionné. La requête suivante doit renvoyer 265 lignes, soit une ligne pour chaque quartier : ```sql theme={null} SELECT * FROM taxi_zone_dictionary ``` 4. Utilisez la fonction `dictGet` ([ou ses variantes](/fr/reference/functions/regular-functions/ext-dict-functions)) 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) : ```sql theme={null} 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 : ```response theme={null} ┌─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) : ```sql theme={null} 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 : ```sql theme={null} SELECT dictHas('taxi_zone_dictionary', 4567) ``` 7. Utilisez la fonction `dictGet` pour obtenir le nom d’un borough dans une requête. Par exemple : ```sql theme={null} 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 : ```response theme={null} ┌─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 É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 : ```sql theme={null} 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` : ```response theme={null} ┌─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 : ```sql theme={null} 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](/fr/guides/clickhouse/data-modelling/sparse-primary-indexes) : 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](/fr/integrations/home) : 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](/fr/integrations/connectors/data-visualization/index) : connectez votre outil UI/BI favori à ClickHouse. * [Référence SQL](/fr/reference/home) : parcourez les fonctions SQL disponibles dans ClickHouse pour transformer, traiter et analyser les données.