--
-- modify this file to contain the correct:
-- * schema name (currently tpcds_10000_text)
-- * location path (currently ${location})

create database if not exists ${db} location '${location}';
use ${db};

create external table if not exists call_center (
  cc_call_center_sk int,
  cc_call_center_id varchar(16),
  cc_rec_start_date varchar(10),
  cc_rec_end_date varchar(10),
  cc_closed_date_sk int,
  cc_open_date_sk int,
  cc_name varchar(50),
  cc_class varchar(50),
  cc_employees int,
  cc_sq_ft int,
  cc_hours varchar(20),
  cc_manager varchar(40),
  cc_mkt_id int,
  cc_mkt_class varchar(50),
  cc_mkt_desc varchar(100),
  cc_market_manager varchar(40),
  cc_division int,
  cc_division_name varchar(50),
  cc_company int,
  cc_company_name varchar(50),
  cc_street_number varchar(10),
  cc_street_name varchar(60),
  cc_street_type varchar(15),
  cc_suite_number varchar(10),
  cc_city varchar(60),
  cc_county varchar(30),
  cc_state varchar(2),
  cc_zip varchar(10),
  cc_country varchar(20),
  cc_gmt_offset decimal(5,2),
  cc_tax_percentage decimal(5,2)
)
row format delimited fields terminated by '|'
stored as textfile
location '${location}/call_center'
tblproperties ('serialization.null.format'='')
;

create external table if not exists catalog_page (
  cp_catalog_page_sk int,
  cp_catalog_page_id varchar(16),
  cp_start_date_sk int,
  cp_end_date_sk int,
  cp_department varchar(50),
  cp_catalog_number int,
  cp_catalog_page_number int,
  cp_description varchar(100),
  cp_type varchar(100)
)
row format delimited fields terminated by '|'
stored as textfile
location '${location}/catalog_page'
tblproperties ('serialization.null.format'='')
;

create external table if not exists catalog_returns (
  cr_returned_date_sk int,
  cr_returned_time_sk int,
  cr_item_sk int,
  cr_refunded_customer_sk int,
  cr_refunded_cdemo_sk int,
  cr_refunded_hdemo_sk int,
  cr_refunded_addr_sk int,
  cr_returning_customer_sk int,
  cr_returning_cdemo_sk int,
  cr_returning_hdemo_sk int,
  cr_returning_addr_sk int,
  cr_call_center_sk int,
  cr_catalog_page_sk int,
  cr_ship_mode_sk int,
  cr_warehouse_sk int,
  cr_reason_sk int,
  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)
)
row format delimited fields terminated by '|'
stored as textfile
location '${location}/catalog_returns'
tblproperties ('serialization.null.format'='')
;

create external table if not exists catalog_sales (
  cs_sold_date_sk int,
  cs_sold_time_sk int,
  cs_ship_date_sk int,
  cs_bill_customer_sk int,
  cs_bill_cdemo_sk int,
  cs_bill_hdemo_sk int,
  cs_bill_addr_sk int,
  cs_ship_customer_sk int,
  cs_ship_cdemo_sk int,
  cs_ship_hdemo_sk int,
  cs_ship_addr_sk int,
  cs_call_center_sk int,
  cs_catalog_page_sk int,
  cs_ship_mode_sk int,
  cs_warehouse_sk int,
  cs_item_sk int,
  cs_promo_sk int,
  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)
)
row format delimited fields terminated by '|'
stored as textfile
location '${location}/catalog_sales'
tblproperties ('serialization.null.format'='')
;

create external table if not exists customer (
  c_customer_sk int,
  c_customer_id varchar(16),
  c_current_cdemo_sk int,
  c_current_hdemo_sk int,
  c_current_addr_sk int,
  c_first_shipto_date_sk int,
  c_first_sales_date_sk int,
  c_salutation varchar(10),
  c_first_name varchar(20),
  c_last_name varchar(30),
  c_preferred_cust_flag varchar(1),
  c_birth_day int,
  c_birth_month int,
  c_birth_year int,
  c_birth_country varchar(20),
  c_login varchar(13),
  c_email_address varchar(50),
  c_last_review_date varchar(10)
)
row format delimited fields terminated by '|'
stored as textfile
location '${location}/customer'
tblproperties ('serialization.null.format'='')
;

create external table if not exists customer_address (
  ca_address_sk int,
  ca_address_id varchar(16),
  ca_street_number varchar(10),
  ca_street_name varchar(60),
  ca_street_type varchar(15),
  ca_suite_number varchar(10),
  ca_city varchar(60),
  ca_county varchar(30),
  ca_state varchar(2),
  ca_zip varchar(10),
  ca_country varchar(20),
  ca_gmt_offset decimal(5,2),
  ca_location_type varchar(20)
)
row format delimited fields terminated by '|'
stored as textfile
location '${location}/customer_address'
tblproperties ('serialization.null.format'='')
;

create external table if not exists customer_demographics (
  cd_demo_sk int,
  cd_gender varchar(1),
  cd_marital_status varchar(1),
  cd_education_status varchar(20),
  cd_purchase_estimate int,
  cd_credit_rating varchar(10),
  cd_dep_count int,
  cd_dep_employed_count int,
  cd_dep_college_count int
)
row format delimited fields terminated by '|'
stored as textfile
location '${location}/customer_demographics'
tblproperties ('serialization.null.format'='')
;

create external table if not exists date_dim (
  d_date_sk int,
  d_date_id varchar(16),
  d_date varchar(10),
  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(9),
  d_quarter_name varchar(6),
  d_holiday varchar(1),
  d_weekend varchar(1),
  d_following_holiday varchar(1),
  d_first_dom int,
  d_last_dom int,
  d_same_day_ly int,
  d_same_day_lq int,
  d_current_day varchar(1),
  d_current_week varchar(1),
  d_current_month varchar(1),
  d_current_quarter varchar(1),
  d_current_year varchar(1)
)
row format delimited fields terminated by '|'
stored as textfile
location '${location}/date_dim'
tblproperties ('serialization.null.format'='')
;

create external table if not exists household_demographics (
  hd_demo_sk int,
  hd_income_band_sk int,
  hd_buy_potential varchar(15),
  hd_dep_count int,
  hd_vehicle_count int
)
row format delimited fields terminated by '|'
stored as textfile
location '${location}/household_demographics'
tblproperties ('serialization.null.format'='')
;

create external table if not exists income_band (
  ib_income_band_sk int,
  ib_lower_bound int,
  ib_upper_bound int
)
row format delimited fields terminated by '|'
stored as textfile
location '${location}/income_band'
tblproperties ('serialization.null.format'='')
;

create external table if not exists inventory (
  inv_date_sk int,
  inv_item_sk int,
  inv_warehouse_sk int,
  inv_quantity_on_hand int
)
row format delimited fields terminated by '|'
stored as textfile
location '${location}/inventory'
tblproperties ('serialization.null.format'='')
;

create external table if not exists item (
  i_item_sk int,
  i_item_id varchar(16),
  i_rec_start_date varchar(10),
  i_rec_end_date varchar(10),
  i_item_desc varchar(200),
  i_current_price decimal(7,2),
  i_wholesale_cost decimal(7,2),
  i_brand_id int,
  i_brand varchar(50),
  i_class_id int,
  i_class varchar(50),
  i_category_id int,
  i_category varchar(50),
  i_manufact_id int,
  i_manufact varchar(50),
  i_size varchar(20),
  i_formulation varchar(20),
  i_color varchar(20),
  i_units varchar(10),
  i_container varchar(10),
  i_manager_id int,
  i_product_name varchar(50)
)
row format delimited fields terminated by '|'
stored as textfile
location '${location}/item'
tblproperties ('serialization.null.format'='')
;

create external table if not exists promotion (
  p_promo_sk int,
  p_promo_id varchar(16),
  p_start_date_sk int,
  p_end_date_sk int,
  p_item_sk int,
  p_cost decimal(15,2),
  p_response_target int,
  p_promo_name varchar(50),
  p_channel_dmail varchar(1),
  p_channel_email varchar(1),
  p_channel_catalog varchar(1),
  p_channel_tv varchar(1),
  p_channel_radio varchar(1),
  p_channel_press varchar(1),
  p_channel_event varchar(1),
  p_channel_demo varchar(1),
  p_channel_details varchar(100),
  p_purpose varchar(15),
  p_discount_active varchar(1)
)
row format delimited fields terminated by '|'
stored as textfile
location '${location}/promotion'
tblproperties ('serialization.null.format'='')
;

create external table if not exists reason (
  r_reason_sk int,
  r_reason_id varchar(16),
  r_reason_desc varchar(100)
)
row format delimited fields terminated by '|'
stored as textfile
location '${location}/reason'
tblproperties ('serialization.null.format'='')
;

create external table if not exists ship_mode (
  sm_ship_mode_sk int,
  sm_ship_mode_id varchar(16),
  sm_type varchar(30),
  sm_code varchar(10),
  sm_carrier varchar(20),
  sm_contract varchar(20)
)
row format delimited fields terminated by '|'
stored as textfile
location '${location}/ship_mode'
tblproperties ('serialization.null.format'='')
;

create external table if not exists store (
  s_store_sk int,
  s_store_id varchar(16),
  s_rec_start_date varchar(10),
  s_rec_end_date varchar(10),
  s_closed_date_sk int,
  s_store_name varchar(50),
  s_number_employees int,
  s_floor_space int,
  s_hours varchar(20),
  s_manager varchar(40),
  s_market_id int,
  s_geography_class varchar(100),
  s_market_desc varchar(100),
  s_market_manager varchar(40),
  s_division_id int,
  s_division_name varchar(50),
  s_company_id int,
  s_company_name varchar(50),
  s_street_number varchar(10),
  s_street_name varchar(60),
  s_street_type varchar(15),
  s_suite_number varchar(10),
  s_city varchar(60),
  s_county varchar(30),
  s_state varchar(2),
  s_zip varchar(10),
  s_country varchar(20),
  s_gmt_offset decimal(5,2),
  s_tax_precentage decimal(5,2)
)
row format delimited fields terminated by '|'
stored as textfile
location '${location}/store'
tblproperties ('serialization.null.format'='')
;

create external table if not exists store_returns (
  sr_returned_date_sk int,
  sr_return_time_sk int,
  sr_item_sk int,
  sr_customer_sk int,
  sr_cdemo_sk int,
  sr_hdemo_sk int,
  sr_addr_sk int,
  sr_store_sk int,
  sr_reason_sk int,
  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)
)
row format delimited fields terminated by '|'
stored as textfile
location '${location}/store_returns'
tblproperties ('serialization.null.format'='')
;

create external table if not exists store_sales (
  ss_sold_date_sk int,
  ss_sold_time_sk int,
  ss_item_sk int,
  ss_customer_sk int,
  ss_cdemo_sk int,
  ss_hdemo_sk int,
  ss_addr_sk int,
  ss_store_sk int,
  ss_promo_sk int,
  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)
)
row format delimited fields terminated by '|'
stored as textfile
location '${location}/store_sales'
tblproperties ('serialization.null.format'='')
;

create external table if not exists time_dim (
  t_time_sk int,
  t_time_id varchar(16),
  t_time int,
  t_hour int,
  t_minute int,
  t_second int,
  t_am_pm varchar(2),
  t_shift varchar(20),
  t_sub_shift varchar(20),
  t_meal_time varchar(20)
)
row format delimited fields terminated by '|'
stored as textfile
location '${location}/time_dim'
tblproperties ('serialization.null.format'='')
;

create external table if not exists warehouse (
  w_warehouse_sk int,
  w_warehouse_id varchar(16),
  w_warehouse_name varchar(20),
  w_warehouse_sq_ft int,
  w_street_number varchar(10),
  w_street_name varchar(60),
  w_street_type varchar(15),
  w_suite_number varchar(10),
  w_city varchar(60),
  w_county varchar(30),
  w_state varchar(2),
  w_zip varchar(10),
  w_country varchar(20),
  w_gmt_offset decimal(5,2)
)
row format delimited fields terminated by '|'
stored as textfile
location '${location}/warehouse'
tblproperties ('serialization.null.format'='')
;

create external table if not exists web_page (
  wp_web_page_sk int,
  wp_web_page_id varchar(16),
  wp_rec_start_date varchar(10),
  wp_rec_end_date varchar(10),
  wp_creation_date_sk int,
  wp_access_date_sk int,
  wp_autogen_flag varchar(1),
  wp_customer_sk int,
  wp_url varchar(100),
  wp_type varchar(50),
  wp_char_count int,
  wp_link_count int,
  wp_image_count int,
  wp_max_ad_count int
)
row format delimited fields terminated by '|'
stored as textfile
location '${location}/web_page'
tblproperties ('serialization.null.format'='')
;

create external table if not exists web_returns (
  wr_returned_date_sk int,
  wr_returned_time_sk int,
  wr_item_sk int,
  wr_refunded_customer_sk int,
  wr_refunded_cdemo_sk int,
  wr_refunded_hdemo_sk int,
  wr_refunded_addr_sk int,
  wr_returning_customer_sk int,
  wr_returning_cdemo_sk int,
  wr_returning_hdemo_sk int,
  wr_returning_addr_sk int,
  wr_web_page_sk int,
  wr_reason_sk int,
  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)
)
row format delimited fields terminated by '|'
stored as textfile
location '${location}/web_returns'
tblproperties ('serialization.null.format'='')
;

create external table if not exists web_sales (
  ws_sold_date_sk int,
  ws_sold_time_sk int,
  ws_ship_date_sk int,
  ws_item_sk int,
  ws_bill_customer_sk int,
  ws_bill_cdemo_sk int,
  ws_bill_hdemo_sk int,
  ws_bill_addr_sk int,
  ws_ship_customer_sk int,
  ws_ship_cdemo_sk int,
  ws_ship_hdemo_sk int,
  ws_ship_addr_sk int,
  ws_web_page_sk int,
  ws_web_site_sk int,
  ws_ship_mode_sk int,
  ws_warehouse_sk int,
  ws_promo_sk int,
  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)
)
row format delimited fields terminated by '|'
stored as textfile
location '${location}/web_sales'
tblproperties ('serialization.null.format'='')
;

create external table if not exists web_site (
  web_site_sk int,
  web_site_id varchar(16),
  web_rec_start_date varchar(10),
  web_rec_end_date varchar(10),
  web_name varchar(50),
  web_open_date_sk int,
  web_close_date_sk int,
  web_class varchar(50),
  web_manager varchar(40),
  web_mkt_id int,
  web_mkt_class varchar(50),
  web_mkt_desc varchar(100),
  web_market_manager varchar(40),
  web_company_id int,
  web_company_name varchar(50),
  web_street_number varchar(10),
  web_street_name varchar(60),
  web_street_type varchar(15),
  web_suite_number varchar(10),
  web_city varchar(60),
  web_county varchar(30),
  web_state varchar(2),
  web_zip varchar(10),
  web_country varchar(20),
  web_gmt_offset decimal(5,2),
  web_tax_percentage decimal(5,2)
)
row format delimited fields terminated by '|'
stored as textfile
location '${location}/web_site'
tblproperties ('serialization.null.format'='')
;

show tables;