| set hive.support.concurrency=true; |
| set hive.txn.manager=org.apache.hadoop.hive.ql.lockmgr.DbTxnManager; |
| set hive.mapred.mode=nonstrict; |
| |
| |
| drop table if exists customer_address; |
| create table customer_address |
| ( |
| ca_address_sk int, |
| ca_address_id string, |
| ca_street_number string, |
| ca_street_name string, |
| ca_street_type string, |
| ca_suite_number string, |
| ca_city string, |
| ca_county string, |
| ca_state string, |
| ca_zip string, |
| ca_country string, |
| ca_gmt_offset decimal(5,2), |
| ca_location_type string |
| ) |
| row format delimited fields terminated by '\t' |
| STORED AS ORC tblproperties ("transactional"="true", "orc.compress"="ZLIB"); |
| |
| |
| drop table if exists date_dim; |
| create table date_dim |
| ( |
| d_date_sk int, |
| d_date_id string, |
| d_date string, |
| 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 string, |
| d_quarter_name string, |
| d_holiday string, |
| d_weekend string, |
| d_following_holiday string, |
| d_first_dom int, |
| d_last_dom int, |
| d_same_day_ly int, |
| d_same_day_lq int, |
| d_current_day string, |
| d_current_week string, |
| d_current_month string, |
| d_current_quarter string, |
| d_current_year string |
| ) |
| row format delimited fields terminated by '\t' |
| STORED AS ORC tblproperties ("transactional"="true", "orc.compress"="ZLIB"); |
| |
| |
| drop table if exists web_returns; |
| create table 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 int, |
| 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 '\t' |
| STORED AS ORC tblproperties ("transactional"="true", "orc.compress"="ZLIB"); |
| |
| |
| drop table if exists web_sales; |
| create table 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 int, |
| 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 '\t' |
| STORED AS ORC tblproperties ("transactional"="true", "orc.compress"="ZLIB"); |
| |
| |
| drop table if exists web_site; |
| create table web_site |
| ( |
| web_site_sk int, |
| web_site_id string, |
| web_rec_start_date string, |
| web_rec_end_date string, |
| web_name string, |
| web_open_date_sk int, |
| web_close_date_sk int, |
| web_class string, |
| web_manager string, |
| web_mkt_id int, |
| web_mkt_class string, |
| web_mkt_desc string, |
| web_market_manager string, |
| web_company_id int, |
| web_company_name string, |
| web_street_number string, |
| web_street_name string, |
| web_street_type string, |
| web_suite_number string, |
| web_city string, |
| web_county string, |
| web_state string, |
| web_zip string, |
| web_country string, |
| web_gmt_offset decimal(5,2), |
| web_tax_percentage decimal(5,2) |
| ) |
| row format delimited fields terminated by '\t' |
| STORED AS ORC tblproperties ("transactional"="true", "orc.compress"="ZLIB"); |
| |
| |
| explain |
| vectorization detail |
| with ws_wh as |
| (select ws1.ws_order_number,ws1.ws_warehouse_sk wh1,ws2.ws_warehouse_sk wh2 |
| from web_sales ws1,web_sales ws2 |
| where ws1.ws_order_number = ws2.ws_order_number |
| and ws1.ws_warehouse_sk <> ws2.ws_warehouse_sk) |
| select |
| count(distinct ws_order_number) as `order count` |
| ,sum(ws_ext_ship_cost) as `total shipping cost` |
| ,sum(ws_net_profit) as `total net profit` |
| from |
| web_sales ws1 |
| ,date_dim |
| ,customer_address |
| ,web_site |
| where |
| d_date between '1999-5-01' and |
| (cast('1999-5-01' as date) + 60 days) |
| and ws1.ws_ship_date_sk = d_date_sk |
| and ws1.ws_ship_addr_sk = ca_address_sk |
| and ca_state = 'TX' |
| and ws1.ws_web_site_sk = web_site_sk |
| and web_company_name = 'pri' |
| and ws1.ws_order_number in (select ws_order_number |
| from ws_wh) |
| and ws1.ws_order_number in (select wr_order_number |
| from web_returns,ws_wh |
| where wr_order_number = ws_wh.ws_order_number) |
| order by count(distinct ws_order_number) |
| limit 100; |