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

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

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.

Sélectionnez console SQL dans le menu de gauche
Cliquez sur l’onglet + à côté de l’icône d’accueil pour créer une nouvelle requête
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;

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

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

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

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

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.

Premiers pas

Configuration

Exemples de jeux de données

Migrer

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

Prérequis

Créez la table

Insérez les données

Analysez les données

Créer un dictionnaire

Exécutez des requêtes à l’aide du dictionnaire

Effectuer une jointure

Prochaines étapes

​Prérequis

Créez la table

Insérez les données

Analysez les données

Créer un dictionnaire

Exécutez des requêtes à l’aide du dictionnaire

Effectuer une jointure

​Prochaines étapes

Prérequis

Prochaines étapes