| --! qt:dataset:part |
| |
| set hive.support.concurrency=true; |
| set hive.txn.manager=org.apache.hadoop.hive.ql.lockmgr.DbTxnManager; |
| set hive.strict.checks.cartesian.product=false; |
| set hive.materializedview.rewriting=true; |
| set hive.stats.column.autogather=true; |
| |
| CREATE TABLE `customer_ext_n3`( |
| `c_custkey` bigint, |
| `c_name` string, |
| `c_address` string, |
| `c_city` string, |
| `c_nation` string, |
| `c_region` string, |
| `c_phone` string, |
| `c_mktsegment` string) |
| ROW FORMAT DELIMITED |
| FIELDS TERMINATED BY '|' |
| STORED AS TEXTFILE; |
| |
| LOAD DATA LOCAL INPATH '../../data/files/ssb/customer/' into table `customer_ext_n3`; |
| |
| CREATE TABLE `customer_n3`( |
| `c_custkey` bigint, |
| `c_name` string, |
| `c_address` string, |
| `c_city` string, |
| `c_nation` string, |
| `c_region` string, |
| `c_phone` string, |
| `c_mktsegment` string, |
| primary key (`c_custkey`) disable rely) |
| STORED AS ORC |
| TBLPROPERTIES ('transactional'='true'); |
| |
| INSERT INTO `customer_n3` |
| SELECT * FROM `customer_ext_n3`; |
| |
| CREATE TABLE `dates_ext_n3`( |
| `d_datekey` bigint, |
| `d_date` string, |
| `d_dayofweek` string, |
| `d_month` string, |
| `d_year` int, |
| `d_yearmonthnum` int, |
| `d_yearmonth` string, |
| `d_daynuminweek` int, |
| `d_daynuminmonth` int, |
| `d_daynuminyear` int, |
| `d_monthnuminyear` int, |
| `d_weeknuminyear` int, |
| `d_sellingseason` string, |
| `d_lastdayinweekfl` int, |
| `d_lastdayinmonthfl` int, |
| `d_holidayfl` int , |
| `d_weekdayfl`int) |
| ROW FORMAT DELIMITED |
| FIELDS TERMINATED BY '|' |
| STORED AS TEXTFILE; |
| |
| LOAD DATA LOCAL INPATH '../../data/files/ssb/date/' into table `dates_ext_n3`; |
| |
| CREATE TABLE `dates_n3`( |
| `d_datekey` bigint, |
| `d_date` string, |
| `d_dayofweek` string, |
| `d_month` string, |
| `d_year` int, |
| `d_yearmonthnum` int, |
| `d_yearmonth` string, |
| `d_daynuminweek` int, |
| `d_daynuminmonth` int, |
| `d_daynuminyear` int, |
| `d_monthnuminyear` int, |
| `d_weeknuminyear` int, |
| `d_sellingseason` string, |
| `d_lastdayinweekfl` int, |
| `d_lastdayinmonthfl` int, |
| `d_holidayfl` int , |
| `d_weekdayfl`int, |
| primary key (`d_datekey`) disable rely |
| ) |
| STORED AS ORC |
| TBLPROPERTIES ('transactional'='true'); |
| |
| INSERT INTO `dates_n3` |
| SELECT * FROM `dates_ext_n3`; |
| |
| CREATE TABLE `ssb_part_ext_n3`( |
| `p_partkey` bigint, |
| `p_name` string, |
| `p_mfgr` string, |
| `p_category` string, |
| `p_brand1` string, |
| `p_color` string, |
| `p_type` string, |
| `p_size` int, |
| `p_container` string) |
| ROW FORMAT DELIMITED |
| FIELDS TERMINATED BY '|' |
| STORED AS TEXTFILE; |
| |
| LOAD DATA LOCAL INPATH '../../data/files/ssb/part/' into table `ssb_part_ext_n3`; |
| |
| CREATE TABLE `ssb_part_n3`( |
| `p_partkey` bigint, |
| `p_name` string, |
| `p_mfgr` string, |
| `p_category` string, |
| `p_brand1` string, |
| `p_color` string, |
| `p_type` string, |
| `p_size` int, |
| `p_container` string, |
| primary key (`p_partkey`) disable rely) |
| STORED AS ORC |
| TBLPROPERTIES ('transactional'='true'); |
| |
| INSERT INTO `ssb_part_n3` |
| SELECT * FROM `ssb_part_ext_n3`; |
| |
| CREATE TABLE `supplier_ext_n3`( |
| `s_suppkey` bigint, |
| `s_name` string, |
| `s_address` string, |
| `s_city` string, |
| `s_nation` string, |
| `s_region` string, |
| `s_phone` string) |
| ROW FORMAT DELIMITED |
| FIELDS TERMINATED BY '|' |
| STORED AS TEXTFILE; |
| |
| LOAD DATA LOCAL INPATH '../../data/files/ssb/supplier/' into table `supplier_ext_n3`; |
| |
| CREATE TABLE `supplier_n3`( |
| `s_suppkey` bigint, |
| `s_name` string, |
| `s_address` string, |
| `s_city` string, |
| `s_nation` string, |
| `s_region` string, |
| `s_phone` string, |
| primary key (`s_suppkey`) disable rely) |
| STORED AS ORC |
| TBLPROPERTIES ('transactional'='true'); |
| |
| INSERT INTO `supplier_n3` |
| SELECT * FROM `supplier_ext_n3`; |
| |
| CREATE TABLE `lineorder_ext_n3`( |
| `lo_orderkey` bigint, |
| `lo_linenumber` int, |
| `lo_custkey` bigint not null disable rely, |
| `lo_partkey` bigint not null disable rely, |
| `lo_suppkey` bigint not null disable rely, |
| `lo_orderdate` bigint not null disable rely, |
| `lo_ordpriority` string, |
| `lo_shippriority` string, |
| `lo_quantity` double, |
| `lo_extendedprice` double, |
| `lo_ordtotalprice` double, |
| `lo_discount` double, |
| `lo_revenue` double, |
| `lo_supplycost` double, |
| `lo_tax` double, |
| `lo_commitdate` bigint, |
| `lo_shipmode` string) |
| ROW FORMAT DELIMITED |
| FIELDS TERMINATED BY '|' |
| STORED AS TEXTFILE; |
| |
| LOAD DATA LOCAL INPATH '../../data/files/ssb/lineorder/' into table `lineorder_ext_n3`; |
| |
| CREATE TABLE `lineorder_n3`( |
| `lo_orderkey` bigint, |
| `lo_linenumber` int, |
| `lo_custkey` bigint not null disable rely, |
| `lo_partkey` bigint not null disable rely, |
| `lo_suppkey` bigint not null disable rely, |
| `lo_orderdate` bigint not null disable rely, |
| `lo_ordpriority` string, |
| `lo_shippriority` string, |
| `lo_quantity` double, |
| `lo_extendedprice` double, |
| `lo_ordtotalprice` double, |
| `lo_discount` double, |
| `lo_revenue` double, |
| `lo_supplycost` double, |
| `lo_tax` double, |
| `lo_commitdate` bigint, |
| `lo_shipmode` string, |
| primary key (`lo_orderkey`) disable rely, |
| constraint fk1 foreign key (`lo_custkey`) references `customer_n3`(`c_custkey`) disable rely, |
| constraint fk2 foreign key (`lo_orderdate`) references `dates_n3`(`d_datekey`) disable rely, |
| constraint fk3 foreign key (`lo_partkey`) references `ssb_part_n3`(`p_partkey`) disable rely, |
| constraint fk4 foreign key (`lo_suppkey`) references `supplier_n3`(`s_suppkey`) disable rely) |
| STORED AS ORC |
| TBLPROPERTIES ('transactional'='true'); |
| |
| INSERT INTO `lineorder_n3` |
| SELECT * FROM `lineorder_ext_n3`; |
| |
| CREATE MATERIALIZED VIEW `ssb_mv_n3` |
| PARTITIONED ON (GROUPING__ID) |
| AS |
| SELECT |
| c_city, |
| c_nation, |
| c_region, |
| d_weeknuminyear, |
| d_year, |
| d_yearmonth, |
| d_yearmonthnum, |
| lo_discount, |
| lo_quantity, |
| p_brand1, |
| p_category, |
| p_mfgr, |
| s_city, |
| s_nation, |
| s_region, |
| GROUPING__ID, |
| sum(lo_revenue), |
| sum(lo_extendedprice * lo_discount) discounted_price, |
| sum(lo_revenue - lo_supplycost) net_revenue |
| FROM |
| customer_n3, dates_n3, lineorder_n3, ssb_part_n3, supplier_n3 |
| WHERE |
| lo_orderdate = d_datekey |
| and lo_partkey = p_partkey |
| and lo_suppkey = s_suppkey |
| and lo_custkey = c_custkey |
| GROUP BY |
| c_city, |
| c_nation, |
| c_region, |
| d_weeknuminyear, |
| d_year, |
| d_yearmonth, |
| d_yearmonthnum, |
| lo_discount, |
| lo_quantity, |
| p_brand1, |
| p_category, |
| p_mfgr, |
| s_city, |
| s_nation, |
| s_region |
| GROUPING SETS ( |
| (d_year, d_yearmonthnum, d_weeknuminyear, lo_discount, lo_quantity), |
| (d_year, d_yearmonth, c_city, c_nation, c_region, s_city, s_nation, s_region, p_brand1, p_category, p_mfgr) |
| ); |
| |
| -- Q1.1 |
| explain cbo |
| select |
| sum(lo_extendedprice*lo_discount) as revenue |
| from |
| lineorder_n3, dates_n3 |
| where |
| lo_orderdate = d_datekey |
| and d_year = 1993 |
| and lo_discount between 1 and 3 |
| and lo_quantity < 25; |
| |
| -- Q1.2 |
| explain cbo |
| select |
| sum(lo_extendedprice*lo_discount) as revenue |
| from |
| lineorder_n3, dates_n3 |
| where |
| lo_orderdate = d_datekey |
| and d_yearmonthnum = 199401 |
| and lo_discount between 4 and 6 |
| and lo_quantity between 26 and 35; |
| |
| -- Q1.3 |
| explain cbo |
| select |
| sum(lo_extendedprice*lo_discount) as revenue |
| from |
| lineorder_n3, dates_n3 |
| where |
| lo_orderdate = d_datekey |
| and d_weeknuminyear = 6 |
| and d_year = 1994 |
| and lo_discount between 5 and 7 |
| and lo_quantity between 26 and 35; |
| |
| -- Q2.1 |
| explain cbo |
| select |
| sum(lo_revenue) as lo_revenue, d_year, p_brand1 |
| from |
| lineorder_n3, dates_n3, ssb_part_n3, supplier_n3 |
| where |
| lo_orderdate = d_datekey |
| and lo_partkey = p_partkey |
| and lo_suppkey = s_suppkey |
| and p_category = 'MFGR#12' |
| and s_region = 'AMERICA' |
| group by |
| d_year, p_brand1 |
| order by |
| d_year, p_brand1; |
| |
| -- Q2.2 |
| explain cbo |
| select |
| sum(lo_revenue) as lo_revenue, d_year, p_brand1 |
| from |
| lineorder_n3, dates_n3, ssb_part_n3, supplier_n3 |
| where |
| lo_orderdate = d_datekey |
| and lo_partkey = p_partkey |
| and lo_suppkey = s_suppkey |
| and p_brand1 between 'MFGR#2221' and 'MFGR#2228' |
| and s_region = 'ASIA' |
| group by |
| d_year, p_brand1 |
| order by |
| d_year, p_brand1; |
| |
| -- Q2.3 |
| explain cbo |
| select |
| sum(lo_revenue) as lo_revenue, d_year, p_brand1 |
| from |
| lineorder_n3, dates_n3, ssb_part_n3, supplier_n3 |
| where |
| lo_orderdate = d_datekey |
| and lo_partkey = p_partkey |
| and lo_suppkey = s_suppkey |
| and p_brand1 = 'MFGR#2239' |
| and s_region = 'EUROPE' |
| group by |
| d_year, p_brand1 |
| order by |
| d_year, p_brand1; |
| |
| -- Q3.1 |
| explain cbo |
| select |
| c_nation, s_nation, d_year, |
| sum(lo_revenue) as lo_revenue |
| from |
| customer_n3, lineorder_n3, supplier_n3, dates_n3 |
| where |
| lo_custkey = c_custkey |
| and lo_suppkey = s_suppkey |
| and lo_orderdate = d_datekey |
| and c_region = 'ASIA' |
| and s_region = 'ASIA' |
| and d_year >= 1992 and d_year <= 1997 |
| group by |
| c_nation, s_nation, d_year |
| order by |
| d_year asc, lo_revenue desc; |
| |
| -- Q3.2 |
| explain cbo |
| select |
| c_city, s_city, d_year, sum(lo_revenue) as lo_revenue |
| from |
| customer_n3, lineorder_n3, supplier_n3, dates_n3 |
| where |
| lo_custkey = c_custkey |
| and lo_suppkey = s_suppkey |
| and lo_orderdate = d_datekey |
| and c_nation = 'UNITED STATES' |
| and s_nation = 'UNITED STATES' |
| and d_year >= 1992 and d_year <= 1997 |
| group by |
| c_city, s_city, d_year |
| order by |
| d_year asc, lo_revenue desc; |
| |
| -- Q3.3 |
| explain cbo |
| select |
| c_city, s_city, d_year, sum(lo_revenue) as lo_revenue |
| from |
| customer_n3, lineorder_n3, supplier_n3, dates_n3 |
| where |
| lo_custkey = c_custkey |
| and lo_suppkey = s_suppkey |
| and lo_orderdate = d_datekey |
| and (c_city='UNITED KI1' or c_city='UNITED KI5') |
| and (s_city='UNITED KI1' or s_city='UNITED KI5') |
| and d_year >= 1992 and d_year <= 1997 |
| group by |
| c_city, s_city, d_year |
| order by |
| d_year asc, lo_revenue desc; |
| |
| -- Q3.4 |
| explain cbo |
| select |
| c_city, s_city, d_year, sum(lo_revenue) as lo_revenue |
| from |
| customer_n3, lineorder_n3, supplier_n3, dates_n3 |
| where |
| lo_custkey = c_custkey |
| and lo_suppkey = s_suppkey |
| and lo_orderdate = d_datekey |
| and (c_city='UNITED KI1' or c_city='UNITED KI5') |
| and (s_city='UNITED KI1' or s_city='UNITED KI5') |
| and d_yearmonth = 'Dec1997' |
| group by |
| c_city, s_city, d_year |
| order by |
| d_year asc, lo_revenue desc; |
| |
| -- Q4.1 |
| explain cbo |
| select |
| d_year, c_nation, |
| sum(lo_revenue - lo_supplycost) as profit |
| from |
| dates_n3, customer_n3, supplier_n3, ssb_part_n3, lineorder_n3 |
| where |
| lo_custkey = c_custkey |
| and lo_suppkey = s_suppkey |
| and lo_partkey = p_partkey |
| and lo_orderdate = d_datekey |
| and c_region = 'AMERICA' |
| and s_region = 'AMERICA' |
| and (p_mfgr = 'MFGR#1' or p_mfgr = 'MFGR#2') |
| group by |
| d_year, c_nation |
| order by |
| d_year, c_nation; |
| |
| -- Q4.2 |
| explain cbo |
| select |
| d_year, s_nation, p_category, |
| sum(lo_revenue - lo_supplycost) as profit |
| from |
| dates_n3, customer_n3, supplier_n3, ssb_part_n3, lineorder_n3 |
| where |
| lo_custkey = c_custkey |
| and lo_suppkey = s_suppkey |
| and lo_partkey = p_partkey |
| and lo_orderdate = d_datekey |
| and c_region = 'AMERICA' |
| and s_region = 'AMERICA' |
| and (d_year = 1997 or d_year = 1998) |
| and (p_mfgr = 'MFGR#1' or p_mfgr = 'MFGR#2') |
| group by |
| d_year, s_nation, p_category |
| order by |
| d_year, s_nation, p_category; |
| |
| -- Q4.3 |
| explain cbo |
| select |
| d_year, s_city, p_brand1, |
| sum(lo_revenue - lo_supplycost) as profit |
| from |
| dates_n3, customer_n3, supplier_n3, ssb_part_n3, lineorder_n3 |
| where |
| lo_custkey = c_custkey |
| and lo_suppkey = s_suppkey |
| and lo_partkey = p_partkey |
| and lo_orderdate = d_datekey |
| and c_region = 'AMERICA' |
| and s_nation = 'UNITED STATES' |
| and (d_year = 1997 or d_year = 1998) |
| and p_category = 'MFGR#14' |
| group by |
| d_year, s_city, p_brand1 |
| order by |
| d_year, s_city, p_brand1; |
| |
| DROP MATERIALIZED VIEW `ssb_mv_n3`; |