--! qt:disabled:unstable; fails sometimes HIVE-23450
--! 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;

CREATE TABLE `customer_ext_n0`(
  `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_n0`;

CREATE TABLE `customer_n0`(
  `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_n0`
SELECT * FROM `customer_ext_n0`;

CREATE TABLE `dates_ext_n0`(
  `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_n0`;

CREATE TABLE `dates_n0`(
  `d_datekey` bigint, 
  `__time` timestamp, 
  `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_n0`
SELECT
  `d_datekey`, 
  cast(`d_year` || '-' || `d_monthnuminyear` || '-' || `d_daynuminmonth` as timestamp), 
  `d_date`, 
  `d_dayofweek`, 
  `d_month`, 
  `d_year`, 
  `d_yearmonthnum`, 
  `d_yearmonth`, 
  `d_daynuminweek`,
  `d_daynuminmonth`,
  `d_daynuminyear`,
  `d_monthnuminyear`,
  `d_weeknuminyear`,
  `d_sellingseason`,
  `d_lastdayinweekfl`,
  `d_lastdayinmonthfl`,
  `d_holidayfl`,
  `d_weekdayfl`
FROM `dates_ext_n0`;

CREATE TABLE `ssb_part_ext_n0`(
  `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_n0`;

CREATE TABLE `ssb_part_n0`(
  `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_n0`
SELECT * FROM `ssb_part_ext_n0`;

CREATE TABLE `supplier_ext_n0`(
  `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_n0`;

CREATE TABLE `supplier_n0`(
  `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_n0`
SELECT * FROM `supplier_ext_n0`;

CREATE TABLE `lineorder_ext_n0`(
  `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_n0`;

CREATE TABLE `lineorder_n0`(
  `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_n0`(`c_custkey`) disable rely,
  constraint fk2 foreign key (`lo_orderdate`) references `dates_n0`(`d_datekey`) disable rely,
  constraint fk3 foreign key (`lo_partkey`) references `ssb_part_n0`(`p_partkey`) disable rely,
  constraint fk4 foreign key (`lo_suppkey`) references `supplier_n0`(`s_suppkey`) disable rely)
STORED AS ORC
TBLPROPERTIES ('transactional'='true');

INSERT INTO `lineorder_n0`
SELECT * FROM `lineorder_ext_n0`;


-- CREATE MV
CREATE MATERIALIZED VIEW `ssb_mv_druid_100`
STORED BY 'org.apache.hadoop.hive.druid.DruidStorageHandler'
TBLPROPERTIES ("druid.segment.granularity" = "MONTH")
AS
SELECT
 `__time` as `__time` ,
 cast(c_city as string) c_city,
 cast(c_nation as string) c_nation,
 cast(c_region as string) c_region,
 c_mktsegment as c_mktsegment,
 cast(d_weeknuminyear as string) d_weeknuminyear,
 cast(d_year as string) d_year,
 cast(d_yearmonth as string) d_yearmonth,
 cast(d_yearmonthnum as string) d_yearmonthnum,
 cast(p_brand1 as string) p_brand1,
 cast(p_category as string) p_category,
 cast(p_mfgr as string) p_mfgr,
 p_type,
 s_name,
 cast(s_city as string) s_city,
 cast(s_nation as string) s_nation,
 cast(s_region as string) s_region,
 cast(`lo_ordpriority` as string) lo_ordpriority, 
 cast(`lo_shippriority` as string) lo_shippriority, 
 `d_sellingseason`
 `lo_shipmode`, 
 lo_revenue,
 lo_supplycost ,
 lo_discount ,
 `lo_quantity`, 
 `lo_extendedprice`, 
 `lo_ordtotalprice`, 
 lo_extendedprice * lo_discount discounted_price,
 lo_revenue - lo_supplycost net_revenue
FROM
 customer_n0, dates_n0, lineorder_n0, ssb_part_n0, supplier_n0
where
 lo_orderdate = d_datekey
 and lo_partkey = p_partkey
 and lo_suppkey = s_suppkey
 and lo_custkey = c_custkey;


-- QUERY OVER MV
EXPLAIN CBO
SELECT MONTH(`__time`) AS `mn___time_ok`,
CAST((MONTH(`__time`) - 1) / 3 + 1 AS BIGINT) AS `qr___time_ok`,
SUM(1) AS `sum_number_of_records_ok`,
YEAR(`__time`) AS `yr___time_ok`
FROM `ssb_mv_druid_100`
GROUP BY MONTH(`__time`),
CAST((MONTH(`__time`) - 1) / 3 + 1 AS BIGINT),
YEAR(`__time`);

SELECT MONTH(`__time`) AS `mn___time_ok`,
CAST((MONTH(`__time`) - 1) / 3 + 1 AS BIGINT) AS `qr___time_ok`,
SUM(1) AS `sum_number_of_records_ok`,
YEAR(`__time`) AS `yr___time_ok`
FROM `ssb_mv_druid_100`
GROUP BY MONTH(`__time`),
CAST((MONTH(`__time`) - 1) / 3 + 1 AS BIGINT),
YEAR(`__time`);


-- QUERY OVER ORIGINAL TABLES
EXPLAIN CBO
SELECT MONTH(`dates_n0`.`__time`) AS `mn___time_ok`,
CAST((MONTH(`dates_n0`.`__time`) - 1) / 3 + 1 AS BIGINT) AS `qr___time_ok`,
SUM(1) AS `sum_number_of_records_ok`,
YEAR(`dates_n0`.`__time`) AS `yr___time_ok`
FROM `lineorder_n0` `lineorder_n0`
JOIN `dates_n0` `dates_n0` ON (`lineorder_n0`.`lo_orderdate` = `dates_n0`.`d_datekey`)
JOIN `customer_n0` `customer_n0` ON (`lineorder_n0`.`lo_custkey` = `customer_n0`.`c_custkey`)
JOIN `supplier_n0` `supplier_n0` ON (`lineorder_n0`.`lo_suppkey` = `supplier_n0`.`s_suppkey`)
JOIN `ssb_part_n0` `ssb_part_n0` ON (`lineorder_n0`.`lo_partkey` = `ssb_part_n0`.`p_partkey`)
GROUP BY MONTH(`dates_n0`.`__time`),
CAST((MONTH(`dates_n0`.`__time`) - 1) / 3 + 1 AS BIGINT),
YEAR(`dates_n0`.`__time`);

SELECT MONTH(`dates_n0`.`__time`) AS `mn___time_ok`,
CAST((MONTH(`dates_n0`.`__time`) - 1) / 3 + 1 AS BIGINT) AS `qr___time_ok`,
SUM(1) AS `sum_number_of_records_ok`,
YEAR(`dates_n0`.`__time`) AS `yr___time_ok`
FROM `lineorder_n0` `lineorder_n0`
JOIN `dates_n0` `dates_n0` ON (`lineorder_n0`.`lo_orderdate` = `dates_n0`.`d_datekey`)
JOIN `customer_n0` `customer_n0` ON (`lineorder_n0`.`lo_custkey` = `customer_n0`.`c_custkey`)
JOIN `supplier_n0` `supplier_n0` ON (`lineorder_n0`.`lo_suppkey` = `supplier_n0`.`s_suppkey`)
JOIN `ssb_part_n0` `ssb_part_n0` ON (`lineorder_n0`.`lo_partkey` = `ssb_part_n0`.`p_partkey`)
GROUP BY MONTH(`dates_n0`.`__time`),
CAST((MONTH(`dates_n0`.`__time`) - 1) / 3 + 1 AS BIGINT),
YEAR(`dates_n0`.`__time`);

DROP MATERIALIZED VIEW `ssb_mv_druid_100`;