Google Cloud Dataproc est un service Spark et Hadoop géré qui vous permet de profiter de outils de données open source pour le traitement par lots, les requêtes, la diffusion en continu et l’apprentissage automatique. Cela inclut l’écosystème Hadoop (HDFS, cadre de traitement Map/Reduce et un certain nombre d’applications telles que Hive, Mahout, Pig, Spark et Hue qui sont construites sur Hadoop). Hive offre une interface de type SQL pour interroger les données stockées dans diverses bases de données et systèmes de fichiers qui s’intègrent à Hadoop. Les requêtes soumises via HIVE sont converties en tâches Map/Reduce qui accèdent aux données stockées, les résultats sont ensuite agrégés et renvoyés à l’utilisateur ou à l’application.
Pour cet exercice, nous utiliserons les données de trajet en taxi jaune et vert de la ville de New York accumulées pour l’année 2019. Les taxis jaunes sont les seuls véhicules autorisés à prendre en charge les passagers appelant partout à New York, tandis que les taxis verts fournissent un service d’appel et un service pré-arrangé. dans le nord de Manhattan (au-dessus de E 96th St et W 110th St) et dans les arrondissements extérieurs. L’ensemble de données est disponible sur le portail de la ville.
Les grandes étapes seront les suivantes :
Étape 1 : Télécharger les données brutes TLC (données de taxi vert et jaune pour Y2019) dans le stockage en nuage
Tout d’abord, créez un bucket GCP Cloud Storage approprié et créez des dossiers pour stocker les ensembles de données de Green Taxi, Yellow Taxi pour l’année 2019, comme indiqué ci-dessous :
Notez l’emplacement de stockage cloud des fichiers de données. Ceux-ci seraient utilisés pour créer des tables externes dans les étapes suivantes :
- Taxi jaune : gs://2019ah04024/TLC/data/YellowTaxi/
- Taxi vert : gs://2019ah04024/TLC/data/GreenTaxi/
Ensuite, téléchargez les ensembles de données du portail de la ville dans le bucket à l’aide de fichiers de téléchargement. Toutes les données des taxis jaunes 2019 seront téléchargées dans les dossiers « YellowTaxi/ » et les taxis verts dans les dossiers « GreenTaxi/ ».
Étape 2 : Créer un environnement de cluster Hadoop à l’aide de Google DataProc
Créez un cluster Hadoop dans GCP à l’aide de DataProc et accéderez au nœud maître via la CLI. Vous pouvez créer des clusters avec plusieurs maîtres et nœuds de travail, mais, pour cet exercice, j’ai créé un seul nœud qui agit à la fois comme nœud maître et comme nœud de travail.
Une fois le cluster créé et prêt, accédez à « INSTANCES DE VM » pour afficher les détails de la VM principale et de travail.
Sélectionnez le nœud maître. Cliquez sur la flèche vers le bas à côté de l’icône SSH et sélectionnez Ouvrir dans une fenêtre de navigateur dans le menu déroulant. Une nouvelle fenêtre de navigateur s’ouvrira et une icône apparaîtra au centre vous alertant de la connexion SSH en cours de configuration. Une fois la connexion SSH établie, l’invite du shell apparaîtra.
Exécutez le shell Beeline à l’aide de l’interface JDBC HIVE. Hive s’exécute sur localhost au port 10000.
utilisez le nom d’utilisateur Google Cloud sur le nom d’hôte du nœud maître (cluster-tlc-m dans cet exemple). La ligne de commande sera la suivante :
beeline -u jdbc:hive2://localhost:10000/default -n myusername@cluster-tlc-m -d org.apache.hive.jdbc.HiveDriverÉtape 3 : créer des tables externes HIVE pour EDA (environnement intermédiaire)
Une table externe est une table pour laquelle Hive ne gère pas le stockage. Si vous supprimez une table externe, seule la définition dans Hive est supprimée. Dans notre cas, même si les tables HIVE sont supprimées, les données restent toujours dans le stockage cloud.
Le dictionnaire de données pour les taxis jaunes et verts contient les champs, les descriptions ainsi que les valeurs appropriées à stocker. À partir des fichiers du dictionnaire de données, nous pouvons remarquer qu’il y a 20 colonnes dans le jeu de données Green Taxi alors qu’il y a 18 colonnes pour le jeu de données Yellow Taxi. Nous allons donc créer ci-dessous 2 tables externes différentes (environnement intermédiaire) et pointer vers les ensembles de données Green, Yellow Taxi en spécifiant l' »emplacement » respectif dans le stockage cloud.
- stg_ny_yellow_taxi
- stg_ny_green_taxi
/*Create External table for yellow taxi*/
CREATE EXTERNAL TABLE stg_ny_yellow_taxi (
vendor_id int,
tpep_pickup_datetime string,
tpep_dropoff_datetime string,
passenger_count int,
trip_distance double,
rate_code_id smallint,
store_and_fwd_flag string,
pu_location_id int,
do_location_id int,
payment_type smallint,
fare_amount double,
extra_charge double,
mta_tax double,
tip_amount double,
tolls_amount double,
improvement_surcharge double,
total_amount double,
congestion_surcharge double
)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY ','
LINES TERMINATED BY 'n'
STORED AS TEXTFILE
LOCATION 'gs://2019ah04024/tlc/data/YellowTaxi/';
/*Create External table for green taxi*/
CREATE EXTERNAL TABLE stg_ny_green_taxi (
vendor_id int,
lpep_pickup_datetime string,
lpep_dropoff_datetime string,
store_and_fwd_flag string,
rate_code_id smallint,
pu_location_id int,
do_location_id int,
passenger_count int,
trip_distance double,
fare_amount double,
extra_charge double,
mta_tax double,
tip_amount double,
tolls_amount double,
ehail_fee double,
improvement_surcharge double,
total_amount double,
payment_type smallint,
trip_type smallint,
congestion_surcharge double
)
ROW FORMAT DELIMITEDÉtape 4: Ingestion de données et analyse exploratoire de données (EDA)
Nous effectuerons une analyse exploratoire des données pour obtenir une compréhension générale des ensembles de données Yellow et Green Taxis, extraire des statistiques de base, vérifier la validité des données et mettre en évidence toute valeur non conforme.
EDA1 : D’après le dictionnaire de données, il existe deux fournisseurs (1= Creative Mobile Technologies, LLC ; 2= VeriFone Inc). Nous vérifierons s’il existe des enregistrements dans lesquels l’identifiant du fournisseur est nul ou l’identifiant du fournisseur n’est pas dans (1,2).
/*Green Taxi*/
SELECT sum(CASE when vendor_id = 1 THEN 1 ELSE 0 END)as Creative_Mobile_Tech,
sum(CASE when vendor_id = 2 THEN 1 ELSE 0 END)as Verifone_Inc,
sum(CASE when vendor_id < 1 or vendor_id > 2 THEN 1 ELSE 0 END)as Others,
sum(CASE when vendor_id IS NULL THEN 1 ELSE 0 END) as NULL_Values,
count(*) as Total_Vendors
from stg_ny_green_taxi;
La sortie de la requête pour le Taxi vert est comme ci-dessous :
- Il y a un total de 6044062 enregistrements dans l’ensemble de données.
- Les enregistrements pour Creative Mobile Tech sont 894041 et Verifone Inc est 4735902.
- Il y a 414119 enregistrements où l’identifiant du fournisseur est nul ou non dans (1, 2).
/*Yellow Taxi*/
SELECT sum(CASE when vendor_id = 1 THEN 1 ELSE 0 END)as Creative_Mobile_Tech,
sum(CASE when vendor_id = 2 THEN 1 ELSE 0 END)as Verifone_Inc,
sum(CASE when vendor_id < 1 or vendor_id > 2 THEN 1 ELSE 0 END)as Others,
sum(CASE when vendor_id IS NULL THEN 1 ELSE 0 END) as NULL_Values,
count(*) as Total_Vendors
from stg_ny_yellow_taxi;
La sortie de la requête pour le Taxi jaune est comme ci-dessous :
- Il y a un total de 84399031 enregistrements dans l’ensemble de données.
- Les enregistrements pour Creative Mobile Tech sont 30368157 et Verifone Inc est 53517181.
- Il y a 246613 enregistrements où l’identifiant du fournisseur est nul ou non dans (1, 2).
- Il y a 267080 enregistrements autres que 1 ou 2 et non NULL.
EDA2 : Nous effectuerons une analyse exploratoire des données associées aux déplacements des fournisseurs :
- Les fournisseurs distincts comptent.
- Plage de temps min et max pour les ramassages et les baisses.
- Nombre de passagers minimum et maximum dans un voyage.
- Des types de paiement distincts sont utilisés pour compter.
- Des codes de taux distincts ont été utilisés.
- Vérifiez si des enregistrements impliquent que seuls les voyages commencés et terminés sont dans l’année 2019. Seules les données entre janvier et décembre 2019 seront prises en compte pour notre analyse.
/*Green Taxi*/
select count(*) as number_of_records,
count(distinct vendor_id) as number_of_tpep_vendors,
min(to_date(lpep_pickup_datetime)) as oldest_pickup_timestamp,
max(to_date(lpep_pickup_datetime)) as recent_pickup_timestamp,
min(to_date(lpep_dropoff_datetime)) as oldest_dropoff_timestamp,
max(to_date(lpep_dropoff_datetime)) as recent_dropoff_timestamp,
min(passenger_count) as min_passengers_pertrip,
max(passenger_count) as max_passengers_pertrip,
avg(passenger_count) as average_passengers_pertrip,
min(trip_distance) as min_trip_distance,
max(trip_distance) as max_trip_distance,
avg(trip_distance) as average_trip_distance,
count(distinct rate_code_id) as number_of_rate_codes,
count(distinct store_and_fwd_flag) as types_of_store_forward_flag,
count(distinct pu_location_id) as num_of_pickup_zones,
count(distinct do_location_id) as num_of_dropoff_zones,
count(distinct payment_type) as number_of_payment_types
from stg_ny_green_taxi;La sortie de la requête pour le Taxi vert est comme ci-dessous :
- Il y a un total de 6044062 enregistrements dans l’ensemble de données.
- Il existe 2 fournisseurs de TPEP.
- Les horodatages de ramassage et les horodatages de dépôt varient entre 21st Août 2008 et 15e Août 2062. C’est un non-conformité.
- Le nombre de passagers varie entre 0 et 9.
- Il y a 7 codes de taux distincts dans l’ensemble de données lorsque le dictionnaire de données le limite à 6. Il s’agit d’un non-conformité.
- Il y a 262 emplacements de ramassage et 264 emplacements de dépôt enregistrés.
- Il existe 5 types de paiement distincts dans l’ensemble de données.
/*Yellow Taxi*/
select count(*) as number_of_records,
count(distinct vendor_id) as number_of_tpep_vendors,
min(to_date(tpep_pickup_datetime)) as oldest_pickup_timestamp,
max(to_date(tpep_pickup_datetime)) as recent_pickup_timestamp,
min(to_date(tpep_dropoff_datetime)) as oldest_dropoff_timestamp,
max(to_date(tpep_dropoff_datetime)) as recent_dropoff_timestamp,
min(passenger_count) as min_passengers_pertrip,
max(passenger_count) as max_passengers_pertrip,
avg(passenger_count) as average_passengers_pertrip,
min(trip_distance) as min_trip_distance,
max(trip_distance) as max_trip_distance,
avg(trip_distance) as average_trip_distance,
count(distinct rate_code_id) as number_of_rate_codes,
count(distinct store_and_fwd_flag) as types_of_store_forward_flag,
count(distinct pu_location_id) as num_of_pickup_zones,
count(distinct do_location_id) as num_of_dropoff_zones,
count(distinct payment_type) as number_of_payment_types
from stg_ny_yellow_taxi;La sortie de la requête pour le Taxi jaune est comme ci-dessous :
- Il y a un total de 84399031 enregistrements dans l’ensemble de données.
- Il y a 3 fournisseurs de TPEP. Mais, les fournisseurs TPEP donnés sont 2. Il s’agit d’un non-conformité
- Les horodatages de ramassage et les horodatages de dépôt se situent entre le 1st du janvier 2001 et le 31st de décembre 2090. Il s’agit d’un non-conformité.
- Le nombre de passagers varie entre 0 et 9.
- Il y a 7 codes de taux distincts dans l’ensemble de données lorsque le dictionnaire le limite à 6. Il s’agit d’un non-conformité.
- Il y a 263 emplacements de ramassage enregistrés et 263 emplacements de dépôt enregistrés.
- Il existe 5 types de paiement distincts dans l’ensemble de données.
EDA3 : Nous effectuerons une analyse exploratoire des données des composants associés aux détails tarifaires, comme suit :
• Tarif min, max et moyen
• Frais supplémentaires min, max et moyen
• Frais MTA min, max et moyen
• Nombre distinct de types de MTA
• Montant minimum, maximum et moyen du pourboire
• Frais de péage min, max et moyen
• Frais de supplément d’amélioration min, max et moyen
• Nombre distinct d’améliorations…