> ## Documentation Index > Fetch the complete documentation index at: https://private-7c7dfe99-trino-dialect.mintlify.site/llms.txt > Use this file to discover all available pages before exploring further. # Tutoriel avancé > Découvrez comment 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. { e.preventDefault(); window.location.href = (window.location.pathname.startsWith('/docs') ? '/docs' : '') + '/get-started/quickstarts/home'; }} className="inline-flex items-center gap-1.5 text-sm text-gray-500 dark:text-zinc-500 hover:text-gray-900 dark:hover:text-[#fdff75] transition-colors font-normal no-underline">All quickstarts
Analytique en temps réel Entrepôt de données Cloud OSS
## Vue d’ensemble
Découvrez comment ingérer et interroger des données dans ClickHouse à l’aide du jeu de données d’exemple sur les taxis de New York.
### Prérequis
Pour suivre ce tutoriel, vous devez avoir accès à un service ClickHouse en cours d’exécution. Consultez le guide [Quick Start](/fr/get-started/setup/install) pour obtenir les instructions. Le jeu de données des taxis de New York contient des informations sur des millions de courses, notamment le montant des pourboires, les péages, le type de paiement et bien plus encore. Créez une table pour stocker ces données. 1. Connectez-vous à la console SQL : * Dans ClickHouse Cloud, sélectionnez un service dans le menu déroulant, puis **SQL Console** dans le menu de navigation de gauche. * Pour une instance ClickHouse autogérée, connectez-vous à la console SQL à l’adresse `https://_hostname_:8443/play`. Renseignez-vous auprès de votre administrateur ClickHouse. 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; ``` Maintenant que vous avez créé une table, ajoutez les données des taxis de New York à partir de fichiers CSV stockés 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'opération `INSERT`. Le téléchargement des 150 Mo de données peut prendre un moment. 3. Une fois l'insertion terminée, vérifiez qu'elle a réussi : ```sql theme={null} SELECT count() FROM trips ``` Cette requête devrait renvoyer 1 999 657 lignes. Exécutez quelques 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 en fonction du nombre de passagers : ```sql theme={null} SELECT passenger_count, ceil(avg(total_amount),2) AS average_total_amount FROM trips GROUP BY passenger_count ```

`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 quotidien de courses prises en charge 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 par durée de 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 courses prises en charge dans chaque quartier, ventilé 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 vers les aéroports LaGuardia ou 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 │ ```

Un dictionary est un mapping de key-value pairs 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 s'appuient sur un fichier CSV contenant une ligne par quartier de New York. Les neighborhoods sont associés aux noms des cinq boroughs 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 table. La colonne `LocationID` du fichier correspond aux colonnes `pickup_nyct2010_gid` et `dropoff_nyct2010_gid` de votre table `trips` : | ID de lieu | Arrondissement | Zone | zone de service | | ---------- | -------------- | ----------------------- | --------------- | | 1 | EWR | Aéroport de Newark | EWR | | 2 | Queens | Jamaica Bay | Zone Boro | | 3 | Bronx | Allerton/Pelham Gardens | Zone Boro | | 4 | Manhattan | Alphabet City | Zone jaune | | 5 | Staten Island | Arden Heights | Zone Boro | 1. Exécutez la commande SQL suivante, qui crée un dictionnaire nommé `taxi_zone_dictionary` et le peuple à 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` sur 0 désactive les mises à jour automatiques afin d’éviter un trafic inutile vers notre bucket S3. Dans d’autres cas, vous pouvez le configurer différemment. Pour plus de détails, consultez [Actualisation des données du dictionnaire à l’aide de LIFETIME](/fr/reference/statements/create/dictionary/lifetime). 3. Vérifiez que cela a 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 l’une de 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, correspond à la colonne `LocationID` de `taxi_zone_dictionary`). Par exemple, la requête suivante renvoie le `Borough` dont le `LocationID` est 132, correspondant à 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 pratiquement 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` (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 récupérer le nom d’un borough dans une requête. 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 total de trajets en taxi par arrondissement qui se terminent aux aéroports de LaGuardia ou de JFK. Le résultat se présente comme suit ; notez qu’un nombre assez important de trajets ont un quartier de prise en charge 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.) ``` Écrivez quelques requêtes qui joignent le dictionnaire `taxi_zone_dictionary` à votre table `trips`. 1. Commencez par une simple jointure `JOIN` qui fonctionne de manière similaire à la requête précédente sur les aéroports : ```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` (à ceci près que les valeurs `Unknown` ne sont pas incluses). En interne, ClickHouse appelle en fait 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 1 000 trajets dont le montant du pourboire est le plus élevé, puis effectue une jointure interne 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 règle générale, évitez d'utiliser fréquemment `SELECT *` dans ClickHouse. Ne récupérez que les colonnes dont vous avez réellement besoin.
## Étapes suivantes
Pour en savoir plus sur ClickHouse, consultez la documentation suivante : * [Introduction aux index primaires dans ClickHouse](/fr/guides/clickhouse/data-modelling/sparse-primary-indexes) : découvrez comment ClickHouse utilise des index primaires creux pour localiser efficacement les données pertinentes lors des requêtes. * [Intégrer une source de données externe](/fr/integrations/home) : explorez 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 les données dans ClickHouse](/fr/integrations/connectors/data-visualization/index) : connectez votre outil d’UI/BI préféré à ClickHouse. * [Référence SQL](/fr/reference/home) : parcourez les fonctions SQL disponibles dans ClickHouse pour transformer, traiter et analyser les données.
{ e.preventDefault(); window.location.href = (window.location.pathname.startsWith('/docs') ? '/docs' : '') + '/get-started/quickstarts/home'; }} className="inline-flex items-center gap-1.5 text-sm text-gray-500 dark:text-zinc-500 hover:text-gray-900 dark:hover:text-[#fdff75] transition-colors font-normal no-underline">All quickstarts