Dans la première partie de cette série, nous avons vu comment créer un cluster Google Dataproc, créer des tables externes dans HIVE, pointer vers les données stockées sur le stockage cloud et effectuer une analyse exploratoire des données dans un environnement de transfert. Dans le cadre de cette analyse, nous avons découvert que nos exemples d’ensembles de données comportaient environ :
- ~ 11% des enregistrements non confirmatifs pour l’ensemble de données Green Taxi Y2019
- ~ 33% des enregistrements non confirmatifs pour l’ensemble de données Yellow Taxi Y2019
L’identification des enregistrements non confirmatifs est l’une des étapes importantes de l’analyse exploratoire des données, car elles peuvent conduire à une interprétation erronée ou erronée des résultats. Ainsi, dans le cadre de l’étape suivante, nous allons créer un nouvel environnement, c’est-à-dire de nouvelles tables externes dans HIVE avec uniquement les données valides requises pour une analyse approfondie et éliminer les enregistrements non confirmatifs.
Étape 5: Créer de nouvelles tables externes HIVE (principales) et télécharger des données filtrées pour une analyse détaillée
Le partitionnement dans Hive signifie diviser le tableau en plusieurs parties en fonction des valeurs d’une colonne particulière comme la date, le cours, la ville ou le pays. L’avantage du partitionnement est que puisque les données sont stockées en tranches, le temps de réponse aux requêtes devient plus rapide. Dans cet exercice, nous allons créer de nouvelles tables externes HIVE partitionnées par mois. On peut aussi partitionner par jour, semaine, année, etc.
Créez une table externe Green Taxi (ny_green_taxi) et téléchargez des données valides de stg_ny_green_taxi dans cette table pour une analyse détaillée. La table est partitionnée par mois et pointée vers le nouvel emplacement « gs://2019ah04024/tlc_parted/Les données/Taxi vert«
/*Green Taxi*/
CREATE EXTERNAL TABLE IF NOT EXISTS 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
)
PARTITIONED BY (mnth int)
STORED AS ORC
LOCATION 'gs://2019ah04024/tlc_parted/data/GreenTaxi/'
TBLPROPERTIES ("orc.compress" = "SNAPPY");
/* Insert valid & confirming records from staging to main*/
INSERT INTO TABLE ny_green_taxi PARTITION(mnth)
SELECT vendor_id, lpep_pickup_datetime, lpep_dropoff_datetime, store_and_fwd_flag,
rate_code_id, pu_location_id, do_location_id, passenger_count, trip_distance,
fare_amount, extra_charge, mta_tax, tip_amount, tolls_amount, ehail_fee,
improvement_surcharge, total_amount, payment_type, trip_type, congestion_surcharge,
month(lpep_pickup_datetime) as mnth
from stg_ny_green_taxi
where year(lpep_pickup_datetime) =2019 and year(lpep_dropoff_datetime) =2019 and
unix_timestamp(lpep_pickup_datetime) < unix_timestamp(lpep_dropoff_datetime) and
passenger_count in (1,2,3,4,5,6,7,8,9) and trip_distance > 0.0 and
rate_code_id in (1,2,3,4,5,6) andpayment_type in (1,2,3,4,5,6) and
fare_amount > 0 and extra_charge in (0,0.5,1) and mta_tax in (0,0.5) and
tip_amount > 0.0 and tolls_amount >= 0.0 and improvement_surcharge in (0,0.3) and
total_amount > 0 and vendor_id is not nullDe la même manière, nous allons créer une table externe Yellow Taxi (ny_yellow_taxi) et télécharger des données valides de stg_ny_yellow_taxi dans cette table pour une analyse détaillée. La table est partitionnée par mois et pointée vers le nouvel emplacement « gs://2019ah04024/tlc_parted/Les données/Taxi jaune«
/* Yellow Tax */
CREATE EXTERNAL TABLE IF NOT EXISTS 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)
PARTITIONED BY (mnth int)
STORED AS ORC
LOCATION 'gs://2019ah04024/tlc_parted/data/YellowTaxi/'
TBLPROPERTIES ("orc.compress" = "SNAPPY");
/* insert valid & confirming records from staging to main */
INSERT INTO TABLE ny_yellow_taxi PARTITION(mnth)
SELECT vendor_id, tpep_pickup_datetime, tpep_dropoff_datetime, passenger_count,
trip_distance, rate_code_id, store_and_fwd_flag, pu_location_id,
do_location_id, payment_type, fare_amount, extra_charge, mta_tax,
tip_amount, tolls_amount, improvement_surcharge, total_amount,
congestion_surcharge, month(tpep_pickup_datetime) as mnth
from stg_ny_yellow_taxi
where year(tpep_pickup_datetime)=2019 and year(tpep_dropoff_datetime)=2019 and
unix_timestamp(tpep_pickup_datetime) < unix_timestamp(tpep_dropoff_datetime) and
passenger_count in (1,2,3,4,5,6,7,8,9) and trip_distance > 0.0 and
rate_code_id in (1,2,3,4,5,6) and payment_type in (1,2,3,4,5,6) and
fare_amount > 0 and extra_charge in (0,0.5,1) and mta_tax in(0,0.5) and
tip_amount>=0.0 and tolls_amount >= 0.0 and improvement_surcharge in (0,0.3) and
total_amount > 0 and vendor_id is not null;Une chose à noter est qu’en effectuant le partitionnement, à l’intérieur du nouvel emplacement de stockage Google Cloud « tlc_parted/Les données/YellowTaxi ou /GreenTaxi », les fichiers de données seront désormais mappés et regroupés sous des dossiers enfants par mois.
Il existe également un ensemble de données de recherche fourni par la société New York Taxi qui contient des détails communs pouvant être utilisés par tous les fournisseurs, tels que l’emplacement, l’arrondissement, la zone de service, etc. Nous utiliserons également ce fichier pour effectuer notre analyse approfondie. Comme indiqué dans les étapes 1 et 2, nous allons télécharger ce fichier dans le stockage cloud, créer une autre table externe pour l’emplacement – « lk_location » et pointer vers cet emplacement.
CREATE EXTERNAL TABLE lk_location (
location_id int, borough string,
tlc_zone string, service_zone string)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY ','
LINES TERMINATED BY 'n'
STORED AS TEXTFILE
LOCATION 'gs://2019ah04024/tlc/data/LookUp/'
tblproperties ("skip.header.line.count"="1");Étape 6 : Effectuer une analyse en profondeur réelle
Maintenant, les tables externes – « ny_green_taxi » et « ny_yellow_taxi » sont les tables contenant les enregistrements filtrés et confirmant. Toute organisation comme UBER ou OLA qui traite avec Taxi souhaite analyser les informations telles que
- Top des itinéraires les plus fréquentés desservis
- Heures les plus chargées de la journée pour les taxis
- Mode de paiement préféré des passagers
- La tendance hebdomadaire observée pour les modes de paiement
- Nombre de trajets mensuels, distance moyenne et nombre moyen de passagers à partir des trajets effectués
- Quel fournisseur fournit les données les plus utiles
Remarque : Pour toutes les requêtes ci-dessus, j’avais exporté la sortie des résultats HIVE et créé des visualisations/graphiques dans Excel.
Analyse 1 : quel fournisseur fournit les données les plus utiles ?
Vérifions quel fournisseur a fourni les enregistrements les plus probants :
/*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,
count(*)as Total_Vendors from ny_green_taxi;La sortie est la suivante :
/* 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,
count(*)as Total_Vendors from ny_yellow_taxi;La sortie est la suivante :
Pour les taxis verts et jaunes, le vendeur Verifone Inc a fourni les données les plus utiles avec des enregistrements de confirmation de 4641694 (87 % du total) pour un taxi vert et de 52661660 (92 % du total) pour un taxi jaune.
Analyse 2 : Trouver le nombre de trajets mensuels, la distance moyenne et le nombre moyen de passagers à partir des trajets effectués par les taxis jaunes et verts en 2019.
/*Green Taxi*/
select mnth_activity.mnth as month, sum(mnth_activity.trips) as trips_count,
round(avg(mnth_activity.trip_dist),2) as Avg_trip_Dist,
round(avg(mnth_activity.psngr_cnt),1) as passenger_count
from (select month(lpep_pickup_datetime) as mnth,
pu_location_id, do_location_id,
count(*) as trips,
avg(trip_distance) as trip_dist,
avg(passenger_count) as psngr_cnt
from ny_green_taxi
group by month(lpep_pickup_datetime), pu_location_id, do_location_id) as mnth_activity
group by mnth_activity.mnth
order by mnth_activity.mnth;
hive -e 'select month(lpep_pickup_datetime) as mnth, pu_location_id, do_location_id, count(*) as trips, avg(trip_distance) as trip_dist, avg(passenger_count) as psngr_cnt from ny_green_taxi group by month(lpep_pickup_datetime), pu_location_id, do_location_id' | sed 's/[[:space:]]+/,/g' > gt_monthly_trend.csv;
gsutil mv gt_monthly_trend.csv gs://2019ah04024/csv/data/GreenTaxi/
/* Yellow Taxi */
select mnth_activity.mnth as month, sum(mnth_activity.trips) as trips_count,
round(avg(mnth_activity.trip_dist),2) as Avg_trip_Dist,
round(avg(mnth_activity.psngr_cnt),1) as passenger_count
from (select month(tpep_pickup_datetime) as mnth,
pu_location_id, do_location_id,
count(*) as trips,
avg(trip_distance) as trip_dist,
avg(passenger_count) as psngr_cnt
from ny_yellow_taxi
group by month(tpep_pickup_datetime), pu_location_id, do_location_id) as mnth_activity
group by mnth_activity.mnth
order by mnth_activity.mnth;
hive -e 'select month(tpep_pickup_datetime) as mnth, pu_location_id, do_location_id, count(*) as trips, avg(trip_distance) as trip_dist, avg(passenger_count) as psngr_cnt from ny_yellow_taxi group by month(tpep_pickup_datetime), pu_location_id, do_location_id' | sed 's/[[:space:]]+/,/g' > yt_monthly_trend.csv;
gsutil mv yt_monthly_trend.csv gs://2019ah04024/csv/data/YellowTaxi/
- La distance moyenne du trajet pour les taxis verts varie entre 7,87 et 10,29, tandis que pour les jaunes varie entre 8,9 et 10,1
- Le nombre de trajets mensuels est maximum pour janvier, février et mars pour les taxis verts. Pour le taxi jaune, c’est en janvier.
- Le nombre moyen de passagers pour le taxi vert est de 1 et le taxi jaune est de 2
Analyse 3 : Connaître les cinq itinéraires les plus fréquentés des taxis jaunes et verts au cours de l’année 2019 avec le nom des points de départ et de dépose à fournir.
/* Green Taxi */
select loc1.borough as Pickup_Borough, loc1.tlc_zone as Pickup_Zone,
loc2.borough as Dropof_Borough, loc2.tlc_zone as Dropof_Zone, count(*) as trip_count
from ny_green_taxi gt
join lk_location loc1
on (gt.pu_location_id = loc1.location_id)
join lk_location loc2
on (gt.do_location_id = loc2.location_id)
group by loc1.borough, loc1.tlc_zone,loc2.borough, loc2.tlc_zone
order by trip_count desc limit 5;
/* Yellow Taxi */
select loc1.borough as Pickup_Borough, loc1.tlc_zone as Pickup_Zone,
loc2.borough as Dropof_Borough, loc2.tlc_zone as Dropof_Zone, count(*) as trip_count
from ny_yellow_taxi yt
join lk_location loc1
on (yt.pu_location_id = loc1.location_id)
join lk_location loc2
on (yt.do_location_id = loc2.location_id)
group by loc1.borough, loc1.tlc_zone,loc2.borough, loc2.tlc_zone
order by trip_count desc limit 5;
Analyse 4 : Quelles sont les 3 heures les plus chargées de la journée pour les taxis ?
/* Green Taxi */
select...