-- Create External for TPC-DS 3TB dataset

CREATE EXTERNAL TABLE ext_tpcds_3t.date_dim (
    d_date_sk                 integer ,
    d_date_id                 char(16),
    d_date                    date,
    d_month_seq               integer,
    d_week_seq                integer,
    d_quarter_seq             integer,
    d_year                    integer,
    d_dow                     integer,
    d_moy                     integer,
    d_dom                     integer,
    d_qoy                     integer,
    d_fy_year                 integer,
    d_fy_quarter_seq          integer,
    d_fy_week_seq             integer,
    d_day_name                char(9),
    d_quarter_name            char(6),
    d_holiday                 char(1),
    d_weekend                 char(1),
    d_following_holiday       char(1),
    d_first_dom               integer,
    d_last_dom                integer,
    d_same_day_ly             integer,
    d_same_day_lq             integer,
    d_current_day             char(1),
    d_current_week            char(1),
    d_current_month           char(1),
    d_current_quarter         char(1),
    d_current_year            char(1)                       
    )
    row format delimited
    fields terminated by '|'
    stored as textfile
    LOCATION 's3://redshift-downloads/TPC-DS/2.13/3TB/date_dim/';



CREATE EXTERNAL TABLE ext_tpcds_3t.web_sales
(
 ws_sold_date_sk int4 ,             
  ws_sold_time_sk int4 ,        
  ws_ship_date_sk int4 ,        
  ws_item_sk int4  ,    
  ws_bill_customer_sk int4 ,    
  ws_bill_cdemo_sk int4 ,       
  ws_bill_hdemo_sk int4 ,       
  ws_bill_addr_sk int4 ,        
  ws_ship_customer_sk int4 ,    
  ws_ship_cdemo_sk int4 ,       
  ws_ship_hdemo_sk int4 ,       
  ws_ship_addr_sk int4 ,        
  ws_web_page_sk int4 ,         
  ws_web_site_sk int4 ,         
  ws_ship_mode_sk int4 ,        
  ws_warehouse_sk int4 ,        
  ws_promo_sk int4 ,            
  ws_order_number int8 ,
  ws_quantity int4 ,            
  ws_wholesale_cost numeric(7,2) ,                
  ws_list_price numeric(7,2) ,  
  ws_sales_price numeric(7,2) , 
  ws_ext_discount_amt numeric(7,2) ,              
  ws_ext_sales_price numeric(7,2) ,
  ws_ext_wholesale_cost numeric(7,2) ,               
  ws_ext_list_price numeric(7,2) , 
  ws_ext_tax numeric(7,2) ,     
  ws_coupon_amt numeric(7,2) ,  
  ws_ext_ship_cost numeric(7,2) ,                 
  ws_net_paid numeric(7,2) ,    
  ws_net_paid_inc_tax numeric(7,2) ,              
  ws_net_paid_inc_ship numeric(7,2) ,             
  ws_net_paid_inc_ship_tax numeric(7,2) ,         
  ws_net_profit numeric(7,2)
)
row format delimited
fields terminated by '|'
stored as textfile
LOCATION 's3://redshift-downloads/TPC-DS/2.13/3TB/web_sales/';


CREATE EXTERNAL TABLE ext_tpcds_3t.catalog_sales
(
 cs_sold_date_sk int4 ,          
  cs_sold_time_sk int4 ,        
  cs_ship_date_sk int4 ,        
  cs_bill_customer_sk int4 ,    
  cs_bill_cdemo_sk int4 ,       
  cs_bill_hdemo_sk int4 ,       
  cs_bill_addr_sk int4 ,        
  cs_ship_customer_sk int4 ,    
  cs_ship_cdemo_sk int4 ,       
  cs_ship_hdemo_sk int4 ,       
  cs_ship_addr_sk int4 ,        
  cs_call_center_sk int4 ,      
  cs_catalog_page_sk int4 ,     
  cs_ship_mode_sk int4 ,        
  cs_warehouse_sk int4 ,        
  cs_item_sk int4  ,    
  cs_promo_sk int4 ,            
  cs_order_number int8  ,                 
  cs_quantity int4 ,            
  cs_wholesale_cost numeric(7,2) ,                
  cs_list_price numeric(7,2) ,  
  cs_sales_price numeric(7,2) , 
  cs_ext_discount_amt numeric(7,2) ,              
  cs_ext_sales_price numeric(7,2) ,               
  cs_ext_wholesale_cost numeric(7,2) ,            
  cs_ext_list_price numeric(7,2) ,
  cs_ext_tax numeric(7,2) ,     
  cs_coupon_amt numeric(7,2) , 
  cs_ext_ship_cost numeric(7,2) ,                
  cs_net_paid numeric(7,2) ,   
  cs_net_paid_inc_tax numeric(7,2) ,             
  cs_net_paid_inc_ship numeric(7,2) ,            
  cs_net_paid_inc_ship_tax numeric(7,2) ,        
  cs_net_profit numeric(7,2)
)
row format delimited
fields terminated by '|'
stored as textfile
LOCATION 's3://redshift-downloads/TPC-DS/2.13/3TB/catalog_sales/';



CREATE EXTERNAL TABLE ext_tpcds_3t.store_sales
(
ss_sold_date_sk int4 ,
  ss_sold_time_sk int4 , 
  ss_item_sk int4  ,  
  ss_customer_sk int4 ,   
  ss_cdemo_sk int4 ,  
  ss_hdemo_sk int4 , 
  ss_addr_sk int4 ,   
  ss_store_sk int4 ,   
  ss_promo_sk int4 ,   
  ss_ticket_number int8 ,
  ss_quantity int4 ,   
  ss_wholesale_cost numeric(7,2) ,  
  ss_list_price numeric(7,2) ,  
  ss_sales_price numeric(7,2) ,
  ss_ext_discount_amt numeric(7,2) , 
  ss_ext_sales_price numeric(7,2) ,  
  ss_ext_wholesale_cost numeric(7,2) ,   
  ss_ext_list_price numeric(7,2) ,   
  ss_ext_tax numeric(7,2) , 
  ss_coupon_amt numeric(7,2) , 
  ss_net_paid numeric(7,2) ,   
  ss_net_paid_inc_tax numeric(7,2) , 
  ss_net_profit numeric(7,2)
)
row format delimited
fields terminated by '|'
stored as textfile
LOCATION 's3://redshift-downloads/TPC-DS/2.13/3TB/store_sales/';



CREATE EXTERNAL TABLE ext_tpcds_3t.store_returns
(
sr_returned_date_sk int4 ,
  sr_return_time_sk int4 ,
  sr_item_sk int4  ,  
  sr_customer_sk int4 ,   
  sr_cdemo_sk int4 ,  
  sr_hdemo_sk int4 ,  
  sr_addr_sk int4 ,   
  sr_store_sk int4 ,  
  sr_reason_sk int4 , 
  sr_ticket_number int8 ,   
  sr_return_quantity int4 ,   
  sr_return_amt numeric(7,2) ,
  sr_return_tax numeric(7,2) ,
  sr_return_amt_inc_tax numeric(7,2) ,  
  sr_fee numeric(7,2) ,   
  sr_return_ship_cost numeric(7,2) ,
  sr_refunded_cash numeric(7,2) ,   
  sr_reversed_charge numeric(7,2) , 
  sr_store_credit numeric(7,2) ,
  sr_net_loss numeric(7,2)
)
row format delimited
fields terminated by '|'
stored as textfile
LOCATION 's3://redshift-downloads/TPC-DS/2.13/3TB/store_returns/';



CREATE EXTERNAL TABLE ext_tpcds_3t.catalog_returns
(
 cr_returned_date_sk int4 ,  
  cr_returned_time_sk int4 , 
  cr_item_sk int4  , 
  cr_refunded_customer_sk int4 ,
  cr_refunded_cdemo_sk int4 ,   
  cr_refunded_hdemo_sk int4 ,   
  cr_refunded_addr_sk int4 ,
  cr_returning_customer_sk int4 ,
  cr_returning_cdemo_sk int4 ,   
  cr_returning_hdemo_sk int4 ,  
  cr_returning_addr_sk int4 ,   
  cr_call_center_sk int4 ,  
  cr_catalog_page_sk int4 , 
  cr_ship_mode_sk int4 ,
  cr_warehouse_sk int4 ,
  cr_reason_sk int4 ,   
  cr_order_number int8 ,
  cr_return_quantity int4 , 
  cr_return_amount numeric(7,2) ,
  cr_return_tax numeric(7,2) ,   
  cr_return_amt_inc_tax numeric(7,2) ,
  cr_fee numeric(7,2) , 
  cr_return_ship_cost numeric(7,2) , 
  cr_refunded_cash numeric(7,2) ,
  cr_reversed_charge numeric(7,2) ,  
  cr_store_credit numeric(7,2) ,
  cr_net_loss numeric(7,2)
)
row format delimited
fields terminated by '|'
stored as textfile
LOCATION 's3://redshift-downloads/TPC-DS/2.13/3TB/catalog_returns/';
;

CREATE EXTERNAL TABLE ext_tpcds_3t.web_returns
(
wr_returned_date_sk int4 ,   
  wr_returned_time_sk int4 , 
  wr_item_sk int4  , 
  wr_refunded_customer_sk int4 ,
  wr_refunded_cdemo_sk int4 ,   
  wr_refunded_hdemo_sk int4 ,   
  wr_refunded_addr_sk int4 ,
  wr_returning_customer_sk int4 ,
  wr_returning_cdemo_sk int4 ,   
  wr_returning_hdemo_sk int4 ,  
  wr_returning_addr_sk int4 ,   
  wr_web_page_sk int4 , 
  wr_reason_sk int4 ,   
  wr_order_number int8 ,
  wr_return_quantity int4 , 
  wr_return_amt numeric(7,2) ,  
  wr_return_tax numeric(7,2) ,  
  wr_return_amt_inc_tax numeric(7,2) ,
  wr_fee numeric(7,2) , 
  wr_return_ship_cost numeric(7,2) ,
  wr_refunded_cash numeric(7,2) ,   
  wr_reversed_charge numeric(7,2) ,  
  wr_account_credit numeric(7,2) ,   
  wr_net_loss numeric(7,2)
)
row format delimited
fields terminated by '|'
stored as textfile
LOCATION 's3://redshift-downloads/TPC-DS/2.13/3TB/web_returns/';