CREATE DATABASE yellow_taxi location 'hdfs://user/root/2020';

CREATE external TABLE dim_vendor
(
    vendor_id INT,
    name string
)
stored AS parquet;

CREATE external TABLE taxi_data
(
vendor_id INT,
tpep_pickup_datetime TIMESTAMP,
tpep_dropoff_datetime TIMESTAMP,
passenger_count INT,
trip_distance DOUBLE,
pulocation_id INT,
dolocation_id INT,
ratecode_id INT,
store_and_fwd_flag string,
payment_type INT,
fare_amount DOUBLE,
extra DOUBLE,
mta_tax DOUBLE,
improvement_surcharge DOUBLE,
tip_amount DOUBLE,
tolls_amount DOUBLE,
total_amount DOUBLE)

ROW format delimited
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n'
location 'hdfs:///user/root/2020'
TBLPROPERTIES ("skip.header.line.count"="1");


CREATE external TABLE part_taxi_data
(
vendor_id INT,
tpep_dropoff_datetime TIMESTAMP,
passenger_count INT,
trip_distance DOUBLE,
pulocation_id INT,
dolocation_id INT,
ratecode_id INT,
store_and_fwd_flag string,
payment_type INT,
fare_amount DOUBLE,
extra DOUBLE,
mta_tax DOUBLE,
improvement_surcharge DOUBLE,
tip_amount DOUBLE,
tolls_amount DOUBLE,
total_amount DOUBLE)
partitioned BY (tpep_pickup_datetime DATE)
ROW format delimited
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n'
stored AS parquet
location 'hdfs:///user/root/2020/part-yellow-taxi'
TBLPROPERTIES ("skip.header.line.count"="1");

INSERT INTO part_taxi_data partition(tpep_pickup_datetime) SELECT * FROM taxi_data;