Adding upstream version 18.2.2.upstream/18.2.2

Signed-off-by: Daniel Baumann <daniel.baumann@progress-linux.org>

1 files changed, 651 insertions, 0 deletions

diff --git a/src/s3select/TPCDS/ddl/create_tpcds_tables.sql b/src/s3select/TPCDS/ddl/create_tpcds_tables.sql
new file mode 100644
index 000000000..692539b9d
--- /dev/null
+++ b/src/s3select/TPCDS/ddl/create_tpcds_tables.sql
@@ -0,0 +1,651 @@
+-- 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');
+