Taxi Data Hive CBO Test

About

Instructions

Dataset Download

https://s3.amazonaws.com/nyc-tlc/trip+data/yellow_tripdata_2016-01.csv

RAW Table DDL

CREATE TABLE `trips_csv`(                                                       
   `trip_id` int,                                                                
   `vendor_id` varchar(3),                                                       
   `pickup_datetime` timestamp,                                                  
   `dropoff_datetime` timestamp,                                                 
   `store_and_fwd_flag` varchar(1),                                              
   `rate_code_id` smallint,                                                      
   `pickup_longitude` decimal(18,14),                                            
   `pickup_latitude` decimal(18,14),                                             
   `dropoff_longitude` decimal(18,14),                                           
   `dropoff_latitude` decimal(18,14),                                            
   `passenger_count` smallint,                                                   
   `trip_distance` decimal(6,3),                                                 
   `fare_amount` decimal(6,2),                                                   
   `extra` decimal(6,2),                                                         
   `mta_tax` decimal(6,2),                                                       
   `tip_amount` decimal(6,2),                                                    
   `tolls_amount` decimal(6,2),                                                  
   `ehail_fee` decimal(6,2),                                                     
   `improvement_surcharge` decimal(6,2),                                         
   `total_amount` decimal(6,2),                                                  
   `payment_type` varchar(3),                                                    
   `trip_type` smallint,                                                         
   `pickup` varchar(50),                                                         
   `dropoff` varchar(50),                                                        
   `cab_type` varchar(6),                                                        
   `precipitation` smallint,                                                     
   `snow_depth` smallint,                                                        
   `snowfall` smallint,                                                          
   `max_temperature` smallint,                                                   
   `min_temperature` smallint,                                                   
   `average_wind_speed` smallint,                                                
   `pickup_nyct2010_gid` smallint,                                               
   `pickup_ctlabel` varchar(10),                                                 
   `pickup_borocode` smallint,                                                   
   `pickup_boroname` varchar(13),                                                
   `pickup_ct2010` varchar(6),                                                   
   `pickup_boroct2010` varchar(7),                                               
   `pickup_cdeligibil` varchar(1),                                               
   `pickup_ntacode` varchar(4),                                                  
   `pickup_ntaname` varchar(56),                                                 
   `pickup_puma` varchar(4),                                                     
   `dropoff_nyct2010_gid` smallint,                                              
   `dropoff_ctlabel` varchar(10),                                                
   `dropoff_borocode` smallint,                                                  
   `dropoff_boroname` varchar(13),                                               
   `dropoff_ct2010` varchar(6),                                                  
   `dropoff_boroct2010` varchar(7),                                              
   `dropoff_cdeligibil` varchar(1),                                              
   `dropoff_ntacode` varchar(4),                                                 
   `dropoff_ntaname` varchar(56),                                                
   `dropoff_puma` varchar(4))                                                    
 ROW FORMAT DELIMITED                                                            
   FIELDS TERMINATED BY ','                                                      
 STORED AS INPUTFORMAT                                                           
   'org.apache.hadoop.mapred.TextInputFormat'                                    
 OUTPUTFORMAT                                                                    
   'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat';

Upload File to Hive Table

hdfs dfs -put yellow_tripdata_2016-01.csv /apps/hive/warehouse/trips_csv

Create Sorted ORC Table

create table taxi_yellow_trips_orc_trip_id stored as ORC as select * from trips_csv sort by trip_id;

Create Table Stats

ANALYZE TABLE taxi_yellow_trips_orc_trip_id COMPUTE STATISTICS FOR COLUMNS;

Query without CBO/PDD/ETC

set hive.vectorized.execution.enabled = false;
set hive.vectorized.execution.reduce.enabled = false;
set hive.exec.parallel = false;
set hive.support.concurrency = false;
set hive.optimize.ppd = false;
set hive.optimize.ppd.storage = false; 
set hive.cbo.enable = false; 
set hive.compute.query.using.stats=false; 
set hive.stats.fetch.column.stats=false; 
set hive.stats.fetch.partition.stats=false; 
set hive.tez.auto.reducer.parallelism=false; 
set hive.tez.max.partition.factor=20; 
set hive.exec.reducers.bytes.per.reducer=128000000; 

select count(*) from taxi_yellow_trips_orc_trip_id; 
select count(*), trip_id from taxi_yellow_trips_orc_trip_id group by trip_id;
select count(*) from taxi_yellow_trips_orc_trip_id  where trip_id=2 group by trip_id;
select distinct trip_id from taxi_yellow_trips_orc_trip_id;

Query with CBO/PDD/ETC

set hive.vectorized.execution.enabled = true;
set hive.vectorized.execution.reduce.enabled = true;
set hive.exec.parallel = true;
set hive.support.concurrency = true;
set hive.optimize.ppd = true;
set hive.optimize.ppd.storage = true; 
set hive.cbo.enable = true; 
set hive.compute.query.using.stats=true; 
set hive.stats.fetch.column.stats=true; 
set hive.stats.fetch.partition.stats=true; 
set hive.tez.auto.reducer.parallelism=true; 
set hive.tez.max.partition.factor=20; 
set hive.exec.reducers.bytes.per.reducer=128000000; 

select count(*) from taxi_yellow_trips_orc_trip_id;
select count(*), trip_id from taxi_yellow_trips_orc_trip_id group by trip_id;
select count(*) from taxi_yellow_trips_orc_trip_id  where trip_id=2 group by trip_id;
select distinct trip_id from taxi_yellow_trips_orc_trip_id;

Sample Run Results

MapReduce

select count(*) from taxi_yellow_trips_orc_trip_id;

OFF = 34.83 seconds
ON = 0.073 seconds (using calcualted result from metastore)

select count(*), trip_id from taxi_yellow_trips_orc_trip_id group by trip_id;

OFF = 32.397 seconds
ON = 36.113 seconds

select count(*) from taxi_yellow_trips_orc_trip_id where trip_id=2 group by trip_id;

OFF = 32.512 seconds
ON = 28.138 seconds

select distinct trip_id from taxi_yellow_trips_orc_trip_id;

OFF = 30.104 seconds
ON = 32.087 seconds

Tez(Hot Sessions - ran multiple queries to get an open session with resources)

select count(*) from taxi_yellow_trips_orc_trip_id;

OFF = 9.764 seconds
ON = 0.085 seconds (metastore)

select count(*), trip_id from taxi_yellow_trips_orc_trip_id group by trip_id;

OFF = 18.51 seconds
ON = 8.203 seconds

select count(*) from taxi_yellow_trips_orc_trip_id where trip_id=2 group by trip_id;

OFF = 11.013 seconds
ON = 8.099 seconds

select distinct trip_id from taxi_yellow_trips_orc_trip_id;

OFF = 12.868 seconds
ON = 8.148 seconds