Analyse géospatiale avec le jeu de données des taxis new-yorkais

Explorez ClickHouse à l’aide d’un jeu de données portant sur des milliards de trajets en taxi et en VTC (Uber, Lyft, etc.) au départ de New York depuis 2009

Dans ce tutoriel, vous découvrirez comment utiliser ClickHouse pour exécuter des requêtes analytiques sur de grands volumes de données. Vous verrez également comment utiliser un dictionnaire pour enrichir les données et rédiger des requêtes avec jointures.

​

Prérequis

Pour ce tutoriel, vous aurez besoin des éléments suivants :

• Un compte ClickHouse Cloud (300 $ de crédits offerts à l’inscription)
• Un service ClickHouse Cloud

1. Créez la table

   Le jeu de données utilisé dans ce tutoriel est celui des taxis de New York. Il contient des informations sur des millions de courses de taxi, avec des colonnes telles que le montant du pourboire, les péages, le type de paiement, etc.

   1. Sélectionnez console SQL dans le menu de gauche
   2. Cliquez sur l’onglet + à côté de l’icône d’accueil pour créer une nouvelle requête
   3. Dans l’éditeur SQL, saisissez la requête suivante, puis cliquez sur Run :

   Expandable

   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. Insérez les données

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

   La commande suivante insère environ 2 000 000 de lignes dans votre table trips à partir de deux fichiers distincts stockés dans S3 : trips_1.tsv.gz et trips_2.tsv.gz :

   Expandable

   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
   

   Attendez la fin de l’insertion des données. Environ 150 Mo de données seront téléchargés. Une fois l’insertion des données terminée, vérifiez le nombre de lignes de la table trips :

   SELECT count() FROM trips
   

   Vous devriez obtenir 1 999 657 lignes

3. Analysez les données

   Une fois les données chargées, vous pouvez exécuter quelques requêtes pour les analyser.

   • Calculer le montant moyen des pourboires :

     SELECT round(avg(tip_amount), 2) FROM trips
     

   • Calculer le coût moyen en fonction du nombre de passagers :

     SELECT
         passenger_count,
         ceil(avg(total_amount),2) AS average_total_amount
     FROM trips
     GROUP BY passenger_count
     

   • Calculer le nombre quotidien de prises en charge 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
     

   • Calculer la durée de chaque trajet en minutes, puis regrouper les résultats selon cette durée :

     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
     

   • Afficher le nombre de prises en charge dans chaque quartier, ventilé 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
     
4. Créer un dictionnaire

   Vous allez ensuite créer un dictionnaire (un ensemble de paires clé-valeur stockées en mémoire) nommé taxi_zone_dictionary afin d’établir une correspondance entre les identifiants de lieu et les noms des boroughs de NYC, à partir d’un fichier CSV répertoriant tous les quartiers de New York. Ces identifiants correspondent aux colonnes pickup_nyct2010_gid et dropoff_nyct2010_gid de la table trips.

   Voici un extrait, présenté sous forme de tableau, du fichier CSV que vous utilisez. 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	Newark Airport	EWR
   2	Queens	Jamaica Bay	Boro Zone
   3	Bronx	Allerton/Pelham Gardens	Boro Zone
   4	Manhattan	Alphabet City	Yellow Zone
   5	Staten Island	Arden Heights	Boro Zone

   Exécutez la commande SQL suivante, qui crée un dictionnaire nommé taxi_zone_dictionary et le remplit à 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 sur 0 désactive les mises à jour automatiques afin d’éviter tout trafic inutile vers notre bucket S3. Dans d’autres cas, vous pouvez le configurer différemment. Pour plus de détails, consultez Rafraîchissement des données de dictionnaire avec LIFETIME.

   Vérifiez que tout a fonctionné. La requête suivante doit renvoyer 265 lignes, soit une ligne par quartier :

   SELECT * FROM taxi_zone_dictionary
   
5. Exécutez des requêtes à l’aide du dictionnaire

   Vous pouvez utiliser la fonction dictGet (ou l’une de ses variantes) pour récupérer une valeur dans un dictionnaire. Il suffit de lui passer le nom du dictionnaire, la valeur souhaitée et la clé (qui, dans notre exemple, correspond à la colonne LocationID de taxi_zone_dictionary) pour obtenir la valeur correspondante. 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 pratiquement nul :

   ┌─dictGet('taxi_zone_dictionary', 'Borough', 132)─┐
   │ Queens                                          │
   └─────────────────────────────────────────────────┘
   
   1 rows in set. Elapsed: 0.004 sec.
   

   Utilisez la fonction dictHas pour vérifier si une clé existe dans le dictionnaire. Par exemple, la requête suivante renvoie 1 (ce qui équivaut à “true” dans ClickHouse) :

   SELECT dictHas('taxi_zone_dictionary', 132)
   

   La requête suivante renvoie 0, car 4567 ne figure pas parmi les valeurs de LocationID dans le dictionnaire :

   SELECT dictHas('taxi_zone_dictionary', 4567)
   

   Utilisez la fonction dictGet pour récupérer 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 total de courses de taxi par borough se terminant à l’aéroport de LaGuardia ou de JFK. Le résultat se présente comme suit ; notez qu’un nombre important de trajets ont un quartier de prise en charge 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.)
   
6. Effectuer une jointure

   Enfin, écrivez quelques requêtes qui effectuent une jointure entre taxi_zone_dictionary et votre table trips.

   Commencez par un simple JOIN qui fonctionne de manière similaire à la requête précédente sur les aéroports :

   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 la sortie de la requête JOIN ci-dessus est identique à celle de la requête précédente, qui utilisait dictGetOrDefault (à ceci près que les valeurs Unknown n’y figurent pas). En coulisses, ClickHouse appelle en réalité la fonction dictGet sur le dictionnaire taxi_zone_dictionary, mais la syntaxe JOIN est plus familière aux développeurs SQL.

   Cette requête renvoie les lignes des 1 000 trajets aux pourboires les plus élevés, puis effectue une jointure interne 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
   

   Privilégiez l’énumération des seules colonnes nécessaires à votre requête plutôt que d’utiliser SELECT *. ClickHouse stockant les données par colonne, sélectionner moins de colonnes réduit directement le volume de données lues, décompressées et traitées.

​

Prochaines étapes

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

• Introduction aux index primaires dans ClickHouse : découvrez comment ClickHouse utilise des index primaires creux pour localiser efficacement les données pertinentes lors de l’exécution 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 de visualisation ou de BI préféré à ClickHouse.
• Référence SQL : parcourez les fonctions SQL disponibles dans ClickHouse pour transformer, traiter et analyser vos données.