path: root/src/s3select/TPCDS/ddl/create_tpcds_tables.sql

-- this DDL can be run from Trino client  ( trino --schema XXXX --catalog HHHH -f <create_tables_file> )
-- the external_location should be modified according to generated data-set


-- Table<store_sales (23 cols)  partition=ss_sold_date_sk>

drop table if exists store_sales;
create table store_sales(
      ss_sold_date_sk bigint
,     ss_sold_time_sk bigint
,     ss_item_sk bigint
,     ss_customer_sk bigint
,     ss_cdemo_sk bigint
,     ss_hdemo_sk bigint
,     ss_addr_sk bigint
,     ss_store_sk bigint
,     ss_promo_sk bigint
,     ss_ticket_number bigint
,     ss_quantity int
,     ss_wholesale_cost decimal(7,2)
,     ss_list_price decimal(7,2)
,     ss_sales_price decimal(7,2)
,     ss_ext_discount_amt decimal(7,2)
,     ss_ext_sales_price decimal(7,2)
,     ss_ext_wholesale_cost decimal(7,2)
,     ss_ext_list_price decimal(7,2)
,     ss_ext_tax decimal(7,2)
,     ss_coupon_amt decimal(7,2)
,     ss_net_paid decimal(7,2)
,     ss_net_paid_inc_tax decimal(7,2)
,     ss_net_profit decimal(7,2)  
)
with( format = 'TEXTFILE',
textfile_field_separator = '|',
external_location = 's3a://tpcds2/4/store_sales');
;

-- Table<store_returns (20 cols)  partition=sr_returned_date_sk>

drop table if exists store_returns;
create table store_returns(
      sr_returned_date_sk bigint
,     sr_return_time_sk bigint
,     sr_item_sk bigint
,     sr_customer_sk bigint
,     sr_cdemo_sk bigint
,     sr_hdemo_sk bigint
,     sr_addr_sk bigint
,     sr_store_sk bigint
,     sr_reason_sk bigint
,     sr_ticket_number bigint
,     sr_return_quantity int
,     sr_return_amt decimal(7,2)
,     sr_return_tax decimal(7,2)
,     sr_return_amt_inc_tax decimal(7,2)
,     sr_fee decimal(7,2)
,     sr_return_ship_cost decimal(7,2)
,     sr_refunded_cash decimal(7,2)
,     sr_reversed_charge decimal(7,2)
,     sr_store_credit decimal(7,2)
,     sr_net_loss decimal(7,2)
)
with( format = 'TEXTFILE',
textfile_field_separator = '|',
external_location = 's3a://tpcds2/4/store_returns');
;

-- Table<catalog_sales (34 cols)  partition=cs_sold_date_sk>

drop table if exists catalog_sales;
create table catalog_sales(
      cs_sold_date_sk bigint
,     cs_sold_time_sk bigint
,     cs_ship_date_sk bigint
,     cs_bill_customer_sk bigint
,     cs_bill_cdemo_sk bigint
,     cs_bill_hdemo_sk bigint
,     cs_bill_addr_sk bigint
,     cs_ship_customer_sk bigint
,     cs_ship_cdemo_sk bigint
,     cs_ship_hdemo_sk bigint
,     cs_ship_addr_sk bigint
,     cs_call_center_sk bigint
,     cs_catalog_page_sk bigint
,     cs_ship_mode_sk bigint
,     cs_warehouse_sk bigint
,     cs_item_sk bigint
,     cs_promo_sk bigint
,     cs_order_number bigint
,     cs_quantity int
,     cs_wholesale_cost decimal(7,2)
,     cs_list_price decimal(7,2)
,     cs_sales_price decimal(7,2)
,     cs_ext_discount_amt decimal(7,2)
,     cs_ext_sales_price decimal(7,2)
,     cs_ext_wholesale_cost decimal(7,2)
,     cs_ext_list_price decimal(7,2)
,     cs_ext_tax decimal(7,2)
,     cs_coupon_amt decimal(7,2)
,     cs_ext_ship_cost decimal(7,2)
,     cs_net_paid decimal(7,2)
,     cs_net_paid_inc_tax decimal(7,2)
,     cs_net_paid_inc_ship decimal(7,2)
,     cs_net_paid_inc_ship_tax decimal(7,2)
,     cs_net_profit decimal(7,2)
)
with( format = 'TEXTFILE',
textfile_field_separator = '|',
external_location = 's3a://tpcds2/4/catalog_sales');
;

-- Table<catalog_returns (27 cols)  partition=cr_returned_date_sk>

drop table if exists catalog_returns;
create table catalog_returns(
      cr_returned_date_sk bigint
,     cr_returned_time_sk bigint
,     cr_item_sk bigint
,     cr_refunded_customer_sk bigint
,     cr_refunded_cdemo_sk bigint
,     cr_refunded_hdemo_sk bigint
,     cr_refunded_addr_sk bigint
,     cr_returning_customer_sk bigint
,     cr_returning_cdemo_sk bigint
,     cr_returning_hdemo_sk bigint
,     cr_returning_addr_sk bigint
,     cr_call_center_sk bigint
,     cr_catalog_page_sk bigint
,     cr_ship_mode_sk bigint
,     cr_warehouse_sk bigint
,     cr_reason_sk bigint
,     cr_order_number bigint
,     cr_return_quantity int
,     cr_return_amount decimal(7,2)
,     cr_return_tax decimal(7,2)
,     cr_return_amt_inc_tax decimal(7,2)
,     cr_fee decimal(7,2)
,     cr_return_ship_cost decimal(7,2)
,     cr_refunded_cash decimal(7,2)
,     cr_reversed_charge decimal(7,2)
,     cr_store_credit decimal(7,2)
,     cr_net_loss decimal(7,2)  
)
with( format = 'TEXTFILE',
textfile_field_separator = '|',
external_location = 's3a://tpcds2/4/catalog_returns');
;

-- Table<web_sales (34 cols)  partition=ws_sold_date_sk>

drop table if exists web_sales;
create table web_sales(
      ws_sold_date_sk bigint
,     ws_sold_time_sk bigint
,     ws_ship_date_sk bigint
,     ws_item_sk bigint
,     ws_bill_customer_sk bigint
,     ws_bill_cdemo_sk bigint
,     ws_bill_hdemo_sk bigint
,     ws_bill_addr_sk bigint
,     ws_ship_customer_sk bigint
,     ws_ship_cdemo_sk bigint
,     ws_ship_hdemo_sk bigint
,     ws_ship_addr_sk bigint
,     ws_web_page_sk bigint
,     ws_web_site_sk bigint
,     ws_ship_mode_sk bigint
,     ws_warehouse_sk bigint
,     ws_promo_sk bigint
,     ws_order_number bigint
,     ws_quantity int
,     ws_wholesale_cost decimal(7,2)
,     ws_list_price decimal(7,2)
,     ws_sales_price decimal(7,2)
,     ws_ext_discount_amt decimal(7,2)
,     ws_ext_sales_price decimal(7,2)
,     ws_ext_wholesale_cost decimal(7,2)
,     ws_ext_list_price decimal(7,2)
,     ws_ext_tax decimal(7,2)
,     ws_coupon_amt decimal(7,2)
,     ws_ext_ship_cost decimal(7,2)
,     ws_net_paid decimal(7,2)
,     ws_net_paid_inc_tax decimal(7,2)
,     ws_net_paid_inc_ship decimal(7,2)
,     ws_net_paid_inc_ship_tax decimal(7,2)
,     ws_net_profit decimal(7,2)
)
with( format = 'TEXTFILE',
textfile_field_separator = '|',
external_location = 's3a://tpcds2/4/web_sales');
;

-- Table<web_returns (24 cols)  partition=wr_returned_date_sk>

drop table if exists web_returns;
create table web_returns(
      wr_returned_date_sk bigint
,     wr_returned_time_sk bigint
,     wr_item_sk bigint
,     wr_refunded_customer_sk bigint
,     wr_refunded_cdemo_sk bigint
,     wr_refunded_hdemo_sk bigint
,     wr_refunded_addr_sk bigint
,     wr_returning_customer_sk bigint
,     wr_returning_cdemo_sk bigint
,     wr_returning_hdemo_sk bigint
,     wr_returning_addr_sk bigint
,     wr_web_page_sk bigint
,     wr_reason_sk bigint
,     wr_order_number bigint
,     wr_return_quantity int
,     wr_return_amt decimal(7,2)
,     wr_return_tax decimal(7,2)
,     wr_return_amt_inc_tax decimal(7,2)
,     wr_fee decimal(7,2)
,     wr_return_ship_cost decimal(7,2)
,     wr_refunded_cash decimal(7,2)
,     wr_reversed_charge decimal(7,2)
,     wr_account_credit decimal(7,2)
,     wr_net_loss decimal(7,2) 
)
with( format = 'TEXTFILE',
textfile_field_separator = '|',
external_location = 's3a://tpcds2/4/web_returns');
;

-- Table<inventory (4 cols)>

drop table if exists inventory;
create table inventory(
      inv_date_sk bigint
,     inv_item_sk bigint
,     inv_warehouse_sk bigint
,     inv_quantity_on_hand int
)
with( format = 'TEXTFILE',
textfile_field_separator = '|',
external_location = 's3a://tpcds2/4/inventory');

-- Table<store (29 cols)>

drop table if exists store;
create table store(
      s_store_sk bigint
,     s_store_id varchar
,     s_rec_start_date date
,     s_rec_end_date date
,     s_closed_date_sk bigint
,     s_store_name varchar
,     s_number_employees int
,     s_floor_space int
,     s_hours varchar
,     S_manager varchar
,     S_market_id int
,     S_geography_class varchar
,     S_market_desc varchar
,     s_market_manager varchar
,     s_division_id int
,     s_division_name varchar
,     s_company_id int
,     s_company_name varchar
,     s_street_number varchar
,     s_street_name varchar
,     s_street_type varchar
,     s_suite_number varchar
,     s_city varchar
,     s_county varchar
,     s_state varchar
,     s_zip varchar
,     s_country varchar
,     s_gmt_offset decimal(5,2)
,     s_tax_percentage decimal(5,2)
)
with( format = 'TEXTFILE',
textfile_field_separator = '|',
external_location = 's3a://tpcds2/4/store');

-- Table<call_center (31 cols)>

drop table if exists call_center;
create table call_center(
      cc_call_center_sk bigint
,     cc_call_center_id varchar
,     cc_rec_start_date date
,     cc_rec_end_date date
,     cc_closed_date_sk bigint
,     cc_open_date_sk bigint
,     cc_name varchar
,     cc_class varchar
,     cc_employees int
,     cc_sq_ft int
,     cc_hours varchar
,     cc_manager varchar
,     cc_mkt_id int
,     cc_mkt_class varchar
,     cc_mkt_desc varchar
,     cc_market_manager varchar
,     cc_division int
,     cc_division_name varchar
,     cc_company int
,     cc_company_name varchar
,     cc_street_number varchar
,     cc_street_name varchar
,     cc_street_type varchar
,     cc_suite_number varchar
,     cc_city varchar
,     cc_county varchar
,     cc_state varchar
,     cc_zip varchar
,     cc_country varchar
,     cc_gmt_offset decimal(5,2)
,     cc_tax_percentage decimal(5,2)
)
with( format = 'TEXTFILE',
textfile_field_separator = '|',
external_location = 's3a://tpcds2/4/call_center');

-- Table<catalog_page (9 cols)>

drop table if exists catalog_page;
create table catalog_page(
      cp_catalog_page_sk bigint
,     cp_catalog_page_id varchar
,     cp_start_date_sk bigint
,     cp_end_date_sk bigint
,     cp_department varchar
,     cp_catalog_number int
,     cp_catalog_page_number int
,     cp_description varchar
,     cp_type varchar
)
with( format = 'TEXTFILE',
textfile_field_separator = '|',
external_location = 's3a://tpcds2/4/catalog_page');

-- Table<web_site (26 cols)>

drop table if exists web_site;
create table web_site(
      web_site_sk bigint
,     web_site_id varchar
,     web_rec_start_date date
,     web_rec_end_date date
,     web_name varchar
,     web_open_date_sk bigint
,     web_close_date_sk bigint
,     web_class varchar
,     web_manager varchar
,     web_mkt_id int
,     web_mkt_class varchar
,     web_mkt_desc varchar
,     web_market_manager varchar
,     web_company_id int
,     web_company_name varchar
,     web_street_number varchar
,     web_street_name varchar
,     web_street_type varchar
,     web_suite_number varchar
,     web_city varchar
,     web_county varchar
,     web_state varchar
,     web_zip varchar
,     web_country varchar
,     web_gmt_offset decimal(5,2)  
,     web_tax_percentage decimal(5,2)
)
with( format = 'TEXTFILE',
textfile_field_separator = '|',
external_location = 's3a://tpcds2/4/web_site');

-- Table<web_page (14 cols)>

drop table if exists web_page;
create table web_page(
      wp_web_page_sk bigint
,     wp_web_page_id varchar
,     wp_rec_start_date date
,     wp_rec_end_date date
,     wp_creation_date_sk bigint
,     wp_access_date_sk bigint
,     wp_autogen_flag varchar
,     wp_customer_sk bigint
,     wp_url varchar
,     wp_type varchar
,     wp_char_count int
,     wp_link_count int
,     wp_image_count int
,     wp_max_ad_count int
)
with( format = 'TEXTFILE',
textfile_field_separator = '|',
external_location = 's3a://tpcds2/4/web_page');

-- Table<warehouse (14 cols)>

drop table if exists warehouse;
create table warehouse(
      w_warehouse_sk bigint
,     w_warehouse_id varchar
,     w_warehouse_name varchar
,     w_warehouse_sq_ft int
,     w_street_number varchar
,     w_street_name varchar
,     w_street_type varchar
,     w_suite_number varchar
,     w_city varchar
,     w_county varchar
,     w_state varchar
,     w_zip varchar
,     w_country varchar
,     w_gmt_offset decimal(5,2)
)
with( format = 'TEXTFILE',
textfile_field_separator = '|',
external_location = 's3a://tpcds2/4/warehouse');

-- Table<customer (18 cols)>

drop table if exists customer;
create table customer(
      c_customer_sk bigint
,     c_customer_id varchar
,     c_current_cdemo_sk bigint
,     c_current_hdemo_sk bigint
,     c_current_addr_sk bigint
,     c_first_shipto_date_sk bigint
,     c_first_sales_date_sk bigint
,     c_salutation varchar
,     c_first_name varchar
,     c_last_name varchar
,     c_preferred_cust_flag varchar
,     c_birth_day int
,     c_birth_month int
,     c_birth_year int
,     c_birth_country varchar
,     c_login varchar
,     c_email_address varchar
,     c_last_review_date_sk bigint
)
with( format = 'TEXTFILE',
textfile_field_separator = '|',
external_location = 's3a://tpcds2/4/customer');

-- Table<customer_address (13 cols)>

drop table if exists customer_address;
create table customer_address(
      ca_address_sk bigint
,     ca_address_id varchar
,     ca_street_number varchar
,     ca_street_name varchar
,     ca_street_type varchar
,     ca_suite_number varchar
,     ca_city varchar
,     ca_county varchar
,     ca_state varchar
,     ca_zip varchar
,     ca_country varchar
,     ca_gmt_offset decimal(5,2)
,     ca_location_type varchar
)
with( format = 'TEXTFILE',
textfile_field_separator = '|',
external_location = 's3a://tpcds2/4/customer_address');

-- Table<customer_demographics (9 cols)>

drop table if exists customer_demographics;
create table customer_demographics(
      cd_demo_sk bigint
,     cd_gender varchar
,     cd_marital_status varchar
,     cd_education_status varchar
,     cd_purchase_estimate int
,     cd_credit_rating varchar
,     cd_dep_count int
,     cd_dep_employed_count int
,     cd_dep_college_count int
)
with( format = 'TEXTFILE',
textfile_field_separator = '|',
external_location = 's3a://tpcds2/4/customer_demographics');

-- Table<date_dim (28 cols)>

drop table if exists date_dim;
create table date_dim(
      d_date_sk bigint
,     d_date_id varchar
,     d_date date
,     d_month_seq int
,     d_week_seq int
,     d_quarter_seq int
,     d_year int
,     d_dow int
,     d_moy int
,     d_dom int
,     d_qoy int
,     d_fy_year int
,     d_fy_quarter_seq int
,     d_fy_week_seq int
,     d_day_name varchar
,     d_quarter_name varchar
,     d_holiday varchar
,     d_weekend varchar
,     d_following_holiday varchar
,     d_first_dom int
,     d_last_dom int
,     d_same_day_ly int
,     d_same_day_lq int
,     d_current_day varchar
,     d_current_week varchar
,     d_current_month varchar
,     d_current_quarter varchar
,     d_current_year varchar
)
with( format = 'TEXTFILE',
textfile_field_separator = '|',
external_location = 's3a://tpcds2/4/date_dim');

-- Table<household_demographics (5 cols)>

drop table if exists household_demographics;
create table household_demographics(
      hd_demo_sk bigint
,     hd_income_band_sk bigint
,     hd_buy_potential varchar
,     hd_dep_count int
,     hd_vehicle_count int
)
with( format = 'TEXTFILE',
textfile_field_separator = '|',
external_location = 's3a://tpcds2/4/household_demographics');

-- Table<item (22 cols)>

drop table if exists item;
create table item(
      i_item_sk bigint
,     i_item_id varchar
,     i_rec_start_date date
,     i_rec_end_date date
,     i_item_desc varchar
,     i_current_price decimal(7,2)
,     i_wholesale_cost decimal(7,2)
,     i_brand_id int
,     i_brand varchar
,     i_class_id int
,     i_class varchar
,     i_category_id int
,     i_category varchar
,     i_manufact_id int
,     i_manufact varchar
,     i_size varchar
,     i_formulation varchar
,     i_color varchar
,     i_units varchar
,     i_container varchar
,     i_manager_id int
,     i_product_name varchar
)
with( format = 'TEXTFILE',
textfile_field_separator = '|',
external_location = 's3a://tpcds2/4/item');

-- Table<income_band (3 cols)>

drop table if exists income_band;
create table income_band(
      ib_income_band_sk bigint
,     ib_lower_bound int
,     ib_upper_bound int
)
with( format = 'TEXTFILE',
textfile_field_separator = '|',
external_location = 's3a://tpcds2/4/income_band');

-- Table<promotion (19 cols)>

drop table if exists promotion;
create table promotion(
      p_promo_sk bigint
,     p_promo_id varchar
,     p_start_date_sk bigint
,     p_end_date_sk bigint
,     p_item_sk bigint
,     p_cost decimal(15,2)
,     p_response_target int
,     p_promo_name varchar
,     p_channel_dmail varchar
,     p_channel_email varchar
,     p_channel_catalog varchar
,     p_channel_tv varchar
,     p_channel_radio varchar
,     p_channel_press varchar
,     p_channel_event varchar
,     p_channel_demo varchar
,     p_channel_details varchar
,     p_purpose varchar
,     p_discount_active varchar
)
with( format = 'TEXTFILE',
textfile_field_separator = '|',
external_location = 's3a://tpcds2/4/promotion');

-- Table<reason (3 cols)>

drop table if exists reason;
create table reason(
      r_reason_sk bigint
,     r_reason_id varchar
,     r_reason_desc varchar
)
with( format = 'TEXTFILE',
textfile_field_separator = '|',
external_location = 's3a://tpcds2/4/reason');

-- Table<ship_mode (6 cols)>

drop table if exists ship_mode;
create table ship_mode(
      sm_ship_mode_sk bigint
,     sm_ship_mode_id varchar
,     sm_type varchar
,     sm_code varchar
,     sm_carrier varchar
,     sm_contract varchar
)
with( format = 'TEXTFILE',
textfile_field_separator = '|',
external_location = 's3a://tpcds2/4/ship_mode');

-- Table<time_dim (10 cols)>

drop table if exists time_dim;
create table time_dim(
      t_time_sk bigint
,     t_time_id varchar
,     t_time int
,     t_hour int
,     t_minute int
,     t_second int
,     t_am_pm varchar
,     t_shift varchar
,     t_sub_shift varchar
,     t_meal_time varchar
)
with( format = 'TEXTFILE',
textfile_field_separator = '|',
external_location = 's3a://tpcds2/4/time_dim');