This is an automated email from the ASF dual-hosted git repository. jcamacho pushed a commit to branch master in repository https://gitbox.apache.org/repos/asf/hive.git
commit e59e673babf66214194a44289361f4b2947015a7 Author: Jesus Camacho Rodriguez <[email protected]> AuthorDate: Wed Jul 10 17:31:21 2019 -0700 HIVE-21934: Materialized view on top of Druid not pushing everything (Jesus Camacho Rodriguez, reviewed by Slim Bouguerra) Close apache/hive#717 --- .../test/resources/testconfiguration.properties | 1 + .../hadoop/hive/ql/parse/CalcitePlanner.java | 4 +- .../druid_materialized_view_rewrite_ssb.q | 317 ++++++++ .../druid_materialized_view_rewrite_ssb.q.out | 813 +++++++++++++++++++++ 4 files changed, 1133 insertions(+), 2 deletions(-) diff --git a/itests/src/test/resources/testconfiguration.properties b/itests/src/test/resources/testconfiguration.properties index 3ccba2b..1c7905d 100644 --- a/itests/src/test/resources/testconfiguration.properties +++ b/itests/src/test/resources/testconfiguration.properties @@ -1876,6 +1876,7 @@ druid.query.files=druidmini_test1.q,\ druidmini_joins.q,\ druidmini_test_insert.q,\ druidmini_mv.q,\ + druid_materialized_view_rewrite_ssb.q,\ druid_timestamptz.q,\ druid_timestamptz2.q,\ druid_topn.q,\ diff --git a/ql/src/java/org/apache/hadoop/hive/ql/parse/CalcitePlanner.java b/ql/src/java/org/apache/hadoop/hive/ql/parse/CalcitePlanner.java index 6f42183..212d27a 100644 --- a/ql/src/java/org/apache/hadoop/hive/ql/parse/CalcitePlanner.java +++ b/ql/src/java/org/apache/hadoop/hive/ql/parse/CalcitePlanner.java @@ -2291,8 +2291,8 @@ public class CalcitePlanner extends SemanticAnalyzer { // TODO: If we want to make use of convention (e.g., while directly generating operator // tree instead of AST), this should be changed. newScan = DruidQuery.create(optCluster, optCluster.traitSetOf(BindableConvention.INSTANCE), - scan.getTable(), dq.getDruidTable(), - ImmutableList.<RelNode>of(dq.getTableScan())); + scan.getTable(), dq.getDruidTable(), ImmutableList.of(dq.getTableScan()), + DruidSqlOperatorConverter.getDefaultMap()); } else { newScan = new HiveTableScan(optCluster, optCluster.traitSetOf(HiveRelNode.CONVENTION), (RelOptHiveTable) scan.getTable(), ((RelOptHiveTable) scan.getTable()).getName(), diff --git a/ql/src/test/queries/clientpositive/druid_materialized_view_rewrite_ssb.q b/ql/src/test/queries/clientpositive/druid_materialized_view_rewrite_ssb.q new file mode 100644 index 0000000..c173fb8 --- /dev/null +++ b/ql/src/test/queries/clientpositive/druid_materialized_view_rewrite_ssb.q @@ -0,0 +1,317 @@ +--! 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`; diff --git a/ql/src/test/results/clientpositive/druid/druid_materialized_view_rewrite_ssb.q.out b/ql/src/test/results/clientpositive/druid/druid_materialized_view_rewrite_ssb.q.out new file mode 100644 index 0000000..4aee9c2 --- /dev/null +++ b/ql/src/test/results/clientpositive/druid/druid_materialized_view_rewrite_ssb.q.out @@ -0,0 +1,813 @@ +PREHOOK: query: 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 +PREHOOK: type: CREATETABLE +PREHOOK: Output: database:default +PREHOOK: Output: default@customer_ext_n0 +POSTHOOK: query: 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 +POSTHOOK: type: CREATETABLE +POSTHOOK: Output: database:default +POSTHOOK: Output: default@customer_ext_n0 +PREHOOK: query: LOAD DATA LOCAL INPATH '../../data/files/ssb/customer/' into table `customer_ext_n0` +PREHOOK: type: LOAD +#### A masked pattern was here #### +PREHOOK: Output: default@customer_ext_n0 +POSTHOOK: query: LOAD DATA LOCAL INPATH '../../data/files/ssb/customer/' into table `customer_ext_n0` +POSTHOOK: type: LOAD +#### A masked pattern was here #### +POSTHOOK: Output: default@customer_ext_n0 +PREHOOK: query: 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') +PREHOOK: type: CREATETABLE +PREHOOK: Output: database:default +PREHOOK: Output: default@customer_n0 +POSTHOOK: query: 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') +POSTHOOK: type: CREATETABLE +POSTHOOK: Output: database:default +POSTHOOK: Output: default@customer_n0 +PREHOOK: query: INSERT INTO `customer_n0` +SELECT * FROM `customer_ext_n0` +PREHOOK: type: QUERY +PREHOOK: Input: default@customer_ext_n0 +PREHOOK: Output: default@customer_n0 +POSTHOOK: query: INSERT INTO `customer_n0` +SELECT * FROM `customer_ext_n0` +POSTHOOK: type: QUERY +POSTHOOK: Input: default@customer_ext_n0 +POSTHOOK: Output: default@customer_n0 +POSTHOOK: Lineage: customer_n0.c_address SIMPLE [(customer_ext_n0)customer_ext_n0.FieldSchema(name:c_address, type:string, comment:null), ] +POSTHOOK: Lineage: customer_n0.c_city SIMPLE [(customer_ext_n0)customer_ext_n0.FieldSchema(name:c_city, type:string, comment:null), ] +POSTHOOK: Lineage: customer_n0.c_custkey SIMPLE [(customer_ext_n0)customer_ext_n0.FieldSchema(name:c_custkey, type:bigint, comment:null), ] +POSTHOOK: Lineage: customer_n0.c_mktsegment SIMPLE [(customer_ext_n0)customer_ext_n0.FieldSchema(name:c_mktsegment, type:string, comment:null), ] +POSTHOOK: Lineage: customer_n0.c_name SIMPLE [(customer_ext_n0)customer_ext_n0.FieldSchema(name:c_name, type:string, comment:null), ] +POSTHOOK: Lineage: customer_n0.c_nation SIMPLE [(customer_ext_n0)customer_ext_n0.FieldSchema(name:c_nation, type:string, comment:null), ] +POSTHOOK: Lineage: customer_n0.c_phone SIMPLE [(customer_ext_n0)customer_ext_n0.FieldSchema(name:c_phone, type:string, comment:null), ] +POSTHOOK: Lineage: customer_n0.c_region SIMPLE [(customer_ext_n0)customer_ext_n0.FieldSchema(name:c_region, type:string, comment:null), ] +PREHOOK: query: 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 +PREHOOK: type: CREATETABLE +PREHOOK: Output: database:default +PREHOOK: Output: default@dates_ext_n0 +POSTHOOK: query: 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 +POSTHOOK: type: CREATETABLE +POSTHOOK: Output: database:default +POSTHOOK: Output: default@dates_ext_n0 +PREHOOK: query: LOAD DATA LOCAL INPATH '../../data/files/ssb/date/' into table `dates_ext_n0` +PREHOOK: type: LOAD +#### A masked pattern was here #### +PREHOOK: Output: default@dates_ext_n0 +POSTHOOK: query: LOAD DATA LOCAL INPATH '../../data/files/ssb/date/' into table `dates_ext_n0` +POSTHOOK: type: LOAD +#### A masked pattern was here #### +POSTHOOK: Output: default@dates_ext_n0 +PREHOOK: query: 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') +PREHOOK: type: CREATETABLE +PREHOOK: Output: database:default +PREHOOK: Output: default@dates_n0 +POSTHOOK: query: 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') +POSTHOOK: type: CREATETABLE +POSTHOOK: Output: database:default +POSTHOOK: Output: default@dates_n0 +PREHOOK: query: 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` +PREHOOK: type: QUERY +PREHOOK: Input: default@dates_ext_n0 +PREHOOK: Output: default@dates_n0 +POSTHOOK: query: 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` +POSTHOOK: type: QUERY +POSTHOOK: Input: default@dates_ext_n0 +POSTHOOK: Output: default@dates_n0 +POSTHOOK: Lineage: dates_n0.__time EXPRESSION [(dates_ext_n0)dates_ext_n0.FieldSchema(name:d_year, type:int, comment:null), (dates_ext_n0)dates_ext_n0.FieldSchema(name:d_monthnuminyear, type:int, comment:null), (dates_ext_n0)dates_ext_n0.FieldSchema(name:d_daynuminmonth, type:int, comment:null), ] +POSTHOOK: Lineage: dates_n0.d_date SIMPLE [(dates_ext_n0)dates_ext_n0.FieldSchema(name:d_date, type:string, comment:null), ] +POSTHOOK: Lineage: dates_n0.d_datekey SIMPLE [(dates_ext_n0)dates_ext_n0.FieldSchema(name:d_datekey, type:bigint, comment:null), ] +POSTHOOK: Lineage: dates_n0.d_daynuminmonth SIMPLE [(dates_ext_n0)dates_ext_n0.FieldSchema(name:d_daynuminmonth, type:int, comment:null), ] +POSTHOOK: Lineage: dates_n0.d_daynuminweek SIMPLE [(dates_ext_n0)dates_ext_n0.FieldSchema(name:d_daynuminweek, type:int, comment:null), ] +POSTHOOK: Lineage: dates_n0.d_daynuminyear SIMPLE [(dates_ext_n0)dates_ext_n0.FieldSchema(name:d_daynuminyear, type:int, comment:null), ] +POSTHOOK: Lineage: dates_n0.d_dayofweek SIMPLE [(dates_ext_n0)dates_ext_n0.FieldSchema(name:d_dayofweek, type:string, comment:null), ] +POSTHOOK: Lineage: dates_n0.d_holidayfl SIMPLE [(dates_ext_n0)dates_ext_n0.FieldSchema(name:d_holidayfl, type:int, comment:null), ] +POSTHOOK: Lineage: dates_n0.d_lastdayinmonthfl SIMPLE [(dates_ext_n0)dates_ext_n0.FieldSchema(name:d_lastdayinmonthfl, type:int, comment:null), ] +POSTHOOK: Lineage: dates_n0.d_lastdayinweekfl SIMPLE [(dates_ext_n0)dates_ext_n0.FieldSchema(name:d_lastdayinweekfl, type:int, comment:null), ] +POSTHOOK: Lineage: dates_n0.d_month SIMPLE [(dates_ext_n0)dates_ext_n0.FieldSchema(name:d_month, type:string, comment:null), ] +POSTHOOK: Lineage: dates_n0.d_monthnuminyear SIMPLE [(dates_ext_n0)dates_ext_n0.FieldSchema(name:d_monthnuminyear, type:int, comment:null), ] +POSTHOOK: Lineage: dates_n0.d_sellingseason SIMPLE [(dates_ext_n0)dates_ext_n0.FieldSchema(name:d_sellingseason, type:string, comment:null), ] +POSTHOOK: Lineage: dates_n0.d_weekdayfl SIMPLE [(dates_ext_n0)dates_ext_n0.FieldSchema(name:d_weekdayfl, type:int, comment:null), ] +POSTHOOK: Lineage: dates_n0.d_weeknuminyear SIMPLE [(dates_ext_n0)dates_ext_n0.FieldSchema(name:d_weeknuminyear, type:int, comment:null), ] +POSTHOOK: Lineage: dates_n0.d_year SIMPLE [(dates_ext_n0)dates_ext_n0.FieldSchema(name:d_year, type:int, comment:null), ] +POSTHOOK: Lineage: dates_n0.d_yearmonth SIMPLE [(dates_ext_n0)dates_ext_n0.FieldSchema(name:d_yearmonth, type:string, comment:null), ] +POSTHOOK: Lineage: dates_n0.d_yearmonthnum SIMPLE [(dates_ext_n0)dates_ext_n0.FieldSchema(name:d_yearmonthnum, type:int, comment:null), ] +PREHOOK: query: 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 +PREHOOK: type: CREATETABLE +PREHOOK: Output: database:default +PREHOOK: Output: default@ssb_part_ext_n0 +POSTHOOK: query: 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 +POSTHOOK: type: CREATETABLE +POSTHOOK: Output: database:default +POSTHOOK: Output: default@ssb_part_ext_n0 +PREHOOK: query: LOAD DATA LOCAL INPATH '../../data/files/ssb/part/' into table `ssb_part_ext_n0` +PREHOOK: type: LOAD +#### A masked pattern was here #### +PREHOOK: Output: default@ssb_part_ext_n0 +POSTHOOK: query: LOAD DATA LOCAL INPATH '../../data/files/ssb/part/' into table `ssb_part_ext_n0` +POSTHOOK: type: LOAD +#### A masked pattern was here #### +POSTHOOK: Output: default@ssb_part_ext_n0 +PREHOOK: query: 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') +PREHOOK: type: CREATETABLE +PREHOOK: Output: database:default +PREHOOK: Output: default@ssb_part_n0 +POSTHOOK: query: 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') +POSTHOOK: type: CREATETABLE +POSTHOOK: Output: database:default +POSTHOOK: Output: default@ssb_part_n0 +PREHOOK: query: INSERT INTO `ssb_part_n0` +SELECT * FROM `ssb_part_ext_n0` +PREHOOK: type: QUERY +PREHOOK: Input: default@ssb_part_ext_n0 +PREHOOK: Output: default@ssb_part_n0 +POSTHOOK: query: INSERT INTO `ssb_part_n0` +SELECT * FROM `ssb_part_ext_n0` +POSTHOOK: type: QUERY +POSTHOOK: Input: default@ssb_part_ext_n0 +POSTHOOK: Output: default@ssb_part_n0 +POSTHOOK: Lineage: ssb_part_n0.p_brand1 SIMPLE [(ssb_part_ext_n0)ssb_part_ext_n0.FieldSchema(name:p_brand1, type:string, comment:null), ] +POSTHOOK: Lineage: ssb_part_n0.p_category SIMPLE [(ssb_part_ext_n0)ssb_part_ext_n0.FieldSchema(name:p_category, type:string, comment:null), ] +POSTHOOK: Lineage: ssb_part_n0.p_color SIMPLE [(ssb_part_ext_n0)ssb_part_ext_n0.FieldSchema(name:p_color, type:string, comment:null), ] +POSTHOOK: Lineage: ssb_part_n0.p_container SIMPLE [(ssb_part_ext_n0)ssb_part_ext_n0.FieldSchema(name:p_container, type:string, comment:null), ] +POSTHOOK: Lineage: ssb_part_n0.p_mfgr SIMPLE [(ssb_part_ext_n0)ssb_part_ext_n0.FieldSchema(name:p_mfgr, type:string, comment:null), ] +POSTHOOK: Lineage: ssb_part_n0.p_name SIMPLE [(ssb_part_ext_n0)ssb_part_ext_n0.FieldSchema(name:p_name, type:string, comment:null), ] +POSTHOOK: Lineage: ssb_part_n0.p_partkey SIMPLE [(ssb_part_ext_n0)ssb_part_ext_n0.FieldSchema(name:p_partkey, type:bigint, comment:null), ] +POSTHOOK: Lineage: ssb_part_n0.p_size SIMPLE [(ssb_part_ext_n0)ssb_part_ext_n0.FieldSchema(name:p_size, type:int, comment:null), ] +POSTHOOK: Lineage: ssb_part_n0.p_type SIMPLE [(ssb_part_ext_n0)ssb_part_ext_n0.FieldSchema(name:p_type, type:string, comment:null), ] +PREHOOK: query: 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 +PREHOOK: type: CREATETABLE +PREHOOK: Output: database:default +PREHOOK: Output: default@supplier_ext_n0 +POSTHOOK: query: 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 +POSTHOOK: type: CREATETABLE +POSTHOOK: Output: database:default +POSTHOOK: Output: default@supplier_ext_n0 +PREHOOK: query: LOAD DATA LOCAL INPATH '../../data/files/ssb/supplier/' into table `supplier_ext_n0` +PREHOOK: type: LOAD +#### A masked pattern was here #### +PREHOOK: Output: default@supplier_ext_n0 +POSTHOOK: query: LOAD DATA LOCAL INPATH '../../data/files/ssb/supplier/' into table `supplier_ext_n0` +POSTHOOK: type: LOAD +#### A masked pattern was here #### +POSTHOOK: Output: default@supplier_ext_n0 +PREHOOK: query: 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') +PREHOOK: type: CREATETABLE +PREHOOK: Output: database:default +PREHOOK: Output: default@supplier_n0 +POSTHOOK: query: 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') +POSTHOOK: type: CREATETABLE +POSTHOOK: Output: database:default +POSTHOOK: Output: default@supplier_n0 +PREHOOK: query: INSERT INTO `supplier_n0` +SELECT * FROM `supplier_ext_n0` +PREHOOK: type: QUERY +PREHOOK: Input: default@supplier_ext_n0 +PREHOOK: Output: default@supplier_n0 +POSTHOOK: query: INSERT INTO `supplier_n0` +SELECT * FROM `supplier_ext_n0` +POSTHOOK: type: QUERY +POSTHOOK: Input: default@supplier_ext_n0 +POSTHOOK: Output: default@supplier_n0 +POSTHOOK: Lineage: supplier_n0.s_address SIMPLE [(supplier_ext_n0)supplier_ext_n0.FieldSchema(name:s_address, type:string, comment:null), ] +POSTHOOK: Lineage: supplier_n0.s_city SIMPLE [(supplier_ext_n0)supplier_ext_n0.FieldSchema(name:s_city, type:string, comment:null), ] +POSTHOOK: Lineage: supplier_n0.s_name SIMPLE [(supplier_ext_n0)supplier_ext_n0.FieldSchema(name:s_name, type:string, comment:null), ] +POSTHOOK: Lineage: supplier_n0.s_nation SIMPLE [(supplier_ext_n0)supplier_ext_n0.FieldSchema(name:s_nation, type:string, comment:null), ] +POSTHOOK: Lineage: supplier_n0.s_phone SIMPLE [(supplier_ext_n0)supplier_ext_n0.FieldSchema(name:s_phone, type:string, comment:null), ] +POSTHOOK: Lineage: supplier_n0.s_region SIMPLE [(supplier_ext_n0)supplier_ext_n0.FieldSchema(name:s_region, type:string, comment:null), ] +POSTHOOK: Lineage: supplier_n0.s_suppkey SIMPLE [(supplier_ext_n0)supplier_ext_n0.FieldSchema(name:s_suppkey, type:bigint, comment:null), ] +PREHOOK: query: 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 +PREHOOK: type: CREATETABLE +PREHOOK: Output: database:default +PREHOOK: Output: default@lineorder_ext_n0 +POSTHOOK: query: 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 +POSTHOOK: type: CREATETABLE +POSTHOOK: Output: database:default +POSTHOOK: Output: default@lineorder_ext_n0 +PREHOOK: query: LOAD DATA LOCAL INPATH '../../data/files/ssb/lineorder/' into table `lineorder_ext_n0` +PREHOOK: type: LOAD +#### A masked pattern was here #### +PREHOOK: Output: default@lineorder_ext_n0 +POSTHOOK: query: LOAD DATA LOCAL INPATH '../../data/files/ssb/lineorder/' into table `lineorder_ext_n0` +POSTHOOK: type: LOAD +#### A masked pattern was here #### +POSTHOOK: Output: default@lineorder_ext_n0 +PREHOOK: query: 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') +PREHOOK: type: CREATETABLE +PREHOOK: Output: database:default +PREHOOK: Output: default@lineorder_n0 +POSTHOOK: query: 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') +POSTHOOK: type: CREATETABLE +POSTHOOK: Output: database:default +POSTHOOK: Output: default@lineorder_n0 +PREHOOK: query: INSERT INTO `lineorder_n0` +SELECT * FROM `lineorder_ext_n0` +PREHOOK: type: QUERY +PREHOOK: Input: default@lineorder_ext_n0 +PREHOOK: Output: default@lineorder_n0 +POSTHOOK: query: INSERT INTO `lineorder_n0` +SELECT * FROM `lineorder_ext_n0` +POSTHOOK: type: QUERY +POSTHOOK: Input: default@lineorder_ext_n0 +POSTHOOK: Output: default@lineorder_n0 +POSTHOOK: Lineage: lineorder_n0.lo_commitdate SIMPLE [(lineorder_ext_n0)lineorder_ext_n0.FieldSchema(name:lo_commitdate, type:bigint, comment:null), ] +POSTHOOK: Lineage: lineorder_n0.lo_custkey SIMPLE [(lineorder_ext_n0)lineorder_ext_n0.FieldSchema(name:lo_custkey, type:bigint, comment:null), ] +POSTHOOK: Lineage: lineorder_n0.lo_discount SIMPLE [(lineorder_ext_n0)lineorder_ext_n0.FieldSchema(name:lo_discount, type:double, comment:null), ] +POSTHOOK: Lineage: lineorder_n0.lo_extendedprice SIMPLE [(lineorder_ext_n0)lineorder_ext_n0.FieldSchema(name:lo_extendedprice, type:double, comment:null), ] +POSTHOOK: Lineage: lineorder_n0.lo_linenumber SIMPLE [(lineorder_ext_n0)lineorder_ext_n0.FieldSchema(name:lo_linenumber, type:int, comment:null), ] +POSTHOOK: Lineage: lineorder_n0.lo_orderdate SIMPLE [(lineorder_ext_n0)lineorder_ext_n0.FieldSchema(name:lo_orderdate, type:bigint, comment:null), ] +POSTHOOK: Lineage: lineorder_n0.lo_orderkey SIMPLE [(lineorder_ext_n0)lineorder_ext_n0.FieldSchema(name:lo_orderkey, type:bigint, comment:null), ] +POSTHOOK: Lineage: lineorder_n0.lo_ordpriority SIMPLE [(lineorder_ext_n0)lineorder_ext_n0.FieldSchema(name:lo_ordpriority, type:string, comment:null), ] +POSTHOOK: Lineage: lineorder_n0.lo_ordtotalprice SIMPLE [(lineorder_ext_n0)lineorder_ext_n0.FieldSchema(name:lo_ordtotalprice, type:double, comment:null), ] +POSTHOOK: Lineage: lineorder_n0.lo_partkey SIMPLE [(lineorder_ext_n0)lineorder_ext_n0.FieldSchema(name:lo_partkey, type:bigint, comment:null), ] +POSTHOOK: Lineage: lineorder_n0.lo_quantity SIMPLE [(lineorder_ext_n0)lineorder_ext_n0.FieldSchema(name:lo_quantity, type:double, comment:null), ] +POSTHOOK: Lineage: lineorder_n0.lo_revenue SIMPLE [(lineorder_ext_n0)lineorder_ext_n0.FieldSchema(name:lo_revenue, type:double, comment:null), ] +POSTHOOK: Lineage: lineorder_n0.lo_shipmode SIMPLE [(lineorder_ext_n0)lineorder_ext_n0.FieldSchema(name:lo_shipmode, type:string, comment:null), ] +POSTHOOK: Lineage: lineorder_n0.lo_shippriority SIMPLE [(lineorder_ext_n0)lineorder_ext_n0.FieldSchema(name:lo_shippriority, type:string, comment:null), ] +POSTHOOK: Lineage: lineorder_n0.lo_suppkey SIMPLE [(lineorder_ext_n0)lineorder_ext_n0.FieldSchema(name:lo_suppkey, type:bigint, comment:null), ] +POSTHOOK: Lineage: lineorder_n0.lo_supplycost SIMPLE [(lineorder_ext_n0)lineorder_ext_n0.FieldSchema(name:lo_supplycost, type:double, comment:null), ] +POSTHOOK: Lineage: lineorder_n0.lo_tax SIMPLE [(lineorder_ext_n0)lineorder_ext_n0.FieldSchema(name:lo_tax, type:double, comment:null), ] +PREHOOK: query: 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 +PREHOOK: type: CREATE_MATERIALIZED_VIEW +PREHOOK: Input: default@customer_n0 +PREHOOK: Input: default@dates_n0 +PREHOOK: Input: default@lineorder_n0 +PREHOOK: Input: default@ssb_part_n0 +PREHOOK: Input: default@supplier_n0 +PREHOOK: Output: database:default +PREHOOK: Output: default@ssb_mv_druid_100 +POSTHOOK: query: 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 +POSTHOOK: type: CREATE_MATERIALIZED_VIEW +POSTHOOK: Input: default@customer_n0 +POSTHOOK: Input: default@dates_n0 +POSTHOOK: Input: default@lineorder_n0 +POSTHOOK: Input: default@ssb_part_n0 +POSTHOOK: Input: default@supplier_n0 +POSTHOOK: Output: database:default +POSTHOOK: Output: default@ssb_mv_druid_100 +PREHOOK: query: 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`) +PREHOOK: type: QUERY +PREHOOK: Input: default@ssb_mv_druid_100 +PREHOOK: Output: hdfs://### HDFS PATH ### +POSTHOOK: query: 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`) +POSTHOOK: type: QUERY +POSTHOOK: Input: default@ssb_mv_druid_100 +POSTHOOK: Output: hdfs://### HDFS PATH ### +CBO PLAN: +HiveProject(mn___time_ok=[$0], qr___time_ok=[$1], sum_number_of_records_ok=[$3], yr___time_ok=[$2]) + DruidQuery(table=[[default, ssb_mv_druid_100]], intervals=[[1900-01-01T00:00:00.000Z/3000-01-01T00:00:00.000Z]], projects=[[MONTH(FLAG(MONTH), $0), CAST(+(/(CAST(-(MONTH(FLAG(MONTH), $0), 1)):DOUBLE, 3), 1)):BIGINT, YEAR(FLAG(YEAR), $0), 1]], groups=[{0, 1, 2}], aggs=[[sum($3)]]) + +PREHOOK: query: 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`) +PREHOOK: type: QUERY +PREHOOK: Input: default@ssb_mv_druid_100 +PREHOOK: Output: hdfs://### HDFS PATH ### +POSTHOOK: query: 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`) +POSTHOOK: type: QUERY +POSTHOOK: Input: default@ssb_mv_druid_100 +POSTHOOK: Output: hdfs://### HDFS PATH ### +PREHOOK: query: 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`) +PREHOOK: type: QUERY +PREHOOK: Input: default@customer_n0 +PREHOOK: Input: default@dates_n0 +PREHOOK: Input: default@lineorder_n0 +PREHOOK: Input: default@ssb_mv_druid_100 +PREHOOK: Input: default@ssb_part_n0 +PREHOOK: Input: default@supplier_n0 +PREHOOK: Output: hdfs://### HDFS PATH ### +POSTHOOK: query: 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`) +POSTHOOK: type: QUERY +POSTHOOK: Input: default@customer_n0 +POSTHOOK: Input: default@dates_n0 +POSTHOOK: Input: default@lineorder_n0 +POSTHOOK: Input: default@ssb_mv_druid_100 +POSTHOOK: Input: default@ssb_part_n0 +POSTHOOK: Input: default@supplier_n0 +POSTHOOK: Output: hdfs://### HDFS PATH ### +CBO PLAN: +HiveProject(mn___time_ok=[$0], qr___time_ok=[$1], sum_number_of_records_ok=[$3], yr___time_ok=[$2]) + DruidQuery(table=[[default, ssb_mv_druid_100]], intervals=[[1900-01-01T00:00:00.000Z/3000-01-01T00:00:00.000Z]], projects=[[MONTH(FLAG(MONTH), $0), CAST(+(/(CAST(-(MONTH(FLAG(MONTH), $0), 1)):DOUBLE, 3), 1)):BIGINT, YEAR(FLAG(YEAR), $0), 1]], groups=[{0, 1, 2}], aggs=[[sum($3)]]) + +PREHOOK: query: 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`) +PREHOOK: type: QUERY +PREHOOK: Input: default@customer_n0 +PREHOOK: Input: default@dates_n0 +PREHOOK: Input: default@lineorder_n0 +PREHOOK: Input: default@ssb_mv_druid_100 +PREHOOK: Input: default@ssb_part_n0 +PREHOOK: Input: default@supplier_n0 +PREHOOK: Output: hdfs://### HDFS PATH ### +POSTHOOK: query: 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`) +POSTHOOK: type: QUERY +POSTHOOK: Input: default@customer_n0 +POSTHOOK: Input: default@dates_n0 +POSTHOOK: Input: default@lineorder_n0 +POSTHOOK: Input: default@ssb_mv_druid_100 +POSTHOOK: Input: default@ssb_part_n0 +POSTHOOK: Input: default@supplier_n0 +POSTHOOK: Output: hdfs://### HDFS PATH ### +PREHOOK: query: DROP MATERIALIZED VIEW `ssb_mv_druid_100` +PREHOOK: type: DROP_MATERIALIZED_VIEW +PREHOOK: Input: default@ssb_mv_druid_100 +PREHOOK: Output: default@ssb_mv_druid_100 +POSTHOOK: query: DROP MATERIALIZED VIEW `ssb_mv_druid_100` +POSTHOOK: type: DROP_MATERIALIZED_VIEW +POSTHOOK: Input: default@ssb_mv_druid_100 +POSTHOOK: Output: default@ssb_mv_druid_100
