Converting a Snowflake data load into a StarRocks data load

[alberttwong]

Snowflake (using https://gist.github.com/mikeharding/b64b47289037f6c10a2983285a56e596):

create database nyc_taxi;

create or replace stage taxi_stage url='s3://nyc-tlc/';

list @taxi_stage/  pattern = '.*trip.data/yellow.*';

create or replace table taxi_yellow (
    csv_filename varchar                   -- 2020   2016
  , vendorid int                           --    1      1
  , tpep_pickup_datetime timestamp_ntz     --    2      2
  , tpep_dropoff_datetime timestamp_ntz    --    3      3
  , passenger_count int                    --    4      4
  , pickup_longitude varchar               --           6
  , pickup_latitude varchar                --           7
  , trip_distance float                    --    5      5
  , ratecodeid int                         --    6      8
  , store_and_fwd_flag varchar             --    7      9
  , dropoff_longitude varchar              --          10
  , dropoff_latitude varchar               --          11
  , pulocationid int                       --    8
  , dolocationid int                       --    9
  , payment_type int                       --   10     12
  , fare_amount float                      --   11     13
  , extra float                            --   12     14
  , mta_tax float                          --   13     15
  , tip_amount float                       --   14     16
  , tolls_amount float                     --   15     17
  , improvement_surcharge float            --   16     18
  , total_amount float                     --   17     19
  , congestion_surcharge float             --   18     
);


create warehouse taxi_wh with warehouse_size='X-SMALL';

-- Load 2020 Yellow Cab data
copy into taxi_yellow from (
select metadata$filename, $1, $2, $3, $4, NULL, NULL, $5, $6, $7, NULL, NULL, $8, $9, $10, $11, $12, $13, $14, $15, $16, $17, $18
from @taxi_stage/) pattern='.*trip.data/yellow.*2020.*' file_format = (type = csv skip_header = 1);

StarRocks:

create database nyc_taxi;
drop table taxi_yellow;
create table taxi_yellow (
     tpep_pickup_datetime DATETIME     
  , VendorID int                          
  , tpep_dropoff_datetime DATETIME   
  , passenger_count int                   
  , trip_distance float                   
  , PULocationID varchar(5)              
  , DOLocationID varchar(5)                
  , RatecodeID int                        
  , store_and_fwd_flag varchar            
  , payment_type int                       
  , fare_amount float                      
  , extra float                           
  , mta_tax float                          
  , improvement_surcharge float                         
  , tip_amount float                      
  , tolls_amount float                   
  , total_amount float                     
  , congestion_surcharge float            
  , airport_fee float            

)
ENGINE=OLAP
DUPLICATE KEY(`tpep_pickup_datetime`)
DISTRIBUTED BY HASH(`tpep_pickup_datetime`) BUCKETS 9;

LOAD LABEL demo.s3upload
(
    DATA INFILE("s3a://s3loadtable1/yellow_tripdata_2023-01.parquet")
    INTO TABLE taxi_yellow
    (VendorID, tpep_pickup_datetime, tpep_dropoff_datetime, passenger_count, RatecodeID, trip_distance, store_and_fwd_flag, PULocationID, DOLocationID, payment_type, fare_amount, extra, mta_tax, tip_amount, tolls_amount, improvement_surcharge, total_amount, congestion_surcharge, airport_fee )
)
WITH BROKER
(
    "aws.s3.access_key" = "XXXXX",
    "aws.s3.secret_key" = "YYYY",
    "aws.s3.use_instance_profile" = "false",
    "aws.s3.region" = "us-west-2"
);