| set hive.cli.print.header=true; |
| set hive.vectorized.execution.enabled=true; |
| set hive.vectorized.execution.ptf.enabled=true; |
| set hive.fetch.task.conversion=none; |
| |
| CREATE TABLE vector_ptf_part_simple_text(p_mfgr string, p_name string, p_date date, p_retailprice double, |
| p_char char(1), p_varchar varchar(5), p_boolean boolean, rowindex int) |
| ROW FORMAT DELIMITED |
| FIELDS TERMINATED BY ',' |
| STORED AS TEXTFILE; |
| LOAD DATA LOCAL INPATH '../../data/files/vector_ptf_part_simple_all_datatypes.txt' OVERWRITE INTO TABLE vector_ptf_part_simple_text; |
| |
| CREATE TABLE vector_ptf_part_simple_orc (p_mfgr string, p_name string, p_date date, p_timestamp timestamp, |
| p_int int, p_retailprice double, p_decimal decimal(10,4), p_char char(1), p_varchar varchar(5), |
| p_boolean boolean, rowindex int) stored as orc; |
| |
| SELECT * FROM vector_ptf_part_simple_text; |
| |
| INSERT INTO TABLE vector_ptf_part_simple_orc |
| SELECT |
| p_mfgr, p_name, p_date, |
| CAST(FROM_UNIXTIME(UNIX_TIMESTAMP(p_date)) as TIMESTAMP), |
| CAST(UNIX_TIMESTAMP(p_date) as int), p_retailprice, |
| CAST(p_retailprice as DECIMAL(10,4)), |
| p_char, |
| p_varchar, |
| p_boolean, |
| rowindex |
| FROM vector_ptf_part_simple_text; |
| |
| SELECT * FROM vector_ptf_part_simple_orc; |
| |
| set hive.vectorized.execution.ptf.enabled=false; |
| select "************ NON_VECTORIZED REFERENCE ************"; |
| select p_mfgr, p_name, rowindex, p_date, p_retailprice, |
| count(*) over(partition by p_mfgr order by p_date range between 1 preceding and current row) as cs1, |
| count(*) over(partition by p_mfgr order by p_date range between 3 preceding and current row) as cs2, |
| count(rowindex) over(partition by p_mfgr order by p_date range between 1 preceding and current row) as c1, |
| count(rowindex) over(partition by p_mfgr order by p_date range between 3 preceding and current row) as c2, |
| count(p_date) over(partition by p_mfgr order by p_date range between 3 preceding and current row) as c_order, |
| sum(p_retailprice) over(partition by p_mfgr order by p_date range between 1 preceding and current row) as s1, |
| sum(p_retailprice) over(partition by p_mfgr order by p_date range between 3 preceding and current row) as s2, |
| min(p_retailprice) over(partition by p_mfgr order by p_date range between 1 preceding and current row) as min1, |
| min(p_retailprice) over(partition by p_mfgr order by p_date range between 3 preceding and current row) as min2, |
| max(p_retailprice) over(partition by p_mfgr order by p_date range between 1 preceding and current row) as max1, |
| max(p_retailprice) over(partition by p_mfgr order by p_date range between 3 preceding and current row) as max2, |
| avg(p_retailprice) over(partition by p_mfgr order by p_date range between 1 preceding and current row) as avg1, |
| avg(p_retailprice) over(partition by p_mfgr order by p_date range between 3 preceding and current row) as avg2, |
| row_number() over(partition by p_mfgr) as rn, |
| rank() over(partition by p_mfgr) as r, |
| dense_rank() over(partition by p_mfgr) as dr, |
| rank() over(partition by p_mfgr order by p_date) as r_date, |
| dense_rank() over(partition by p_mfgr order by p_date) as dr_date, |
| first_value(p_retailprice) over(partition by p_mfgr) as fv, |
| last_value(p_retailprice) over(partition by p_mfgr) as lv, |
| lead(p_retailprice) over(partition by p_mfgr) as lead1, |
| lag(p_retailprice) over(partition by p_mfgr) as lag1 |
| from vector_ptf_part_simple_orc; |
| |
| |
| set hive.vectorized.execution.ptf.enabled=true; |
| |
| EXPLAIN VECTORIZATION DETAIL |
| select p_mfgr, p_name, rowindex, p_date, p_retailprice, |
| count(*) over(partition by p_mfgr order by p_date range between 1 preceding and current row) as cs1, |
| count(*) over(partition by p_mfgr order by p_date range between 3 preceding and current row) as cs2, |
| count(rowindex) over(partition by p_mfgr order by p_date range between 1 preceding and current row) as c1, |
| count(rowindex) over(partition by p_mfgr order by p_date range between 3 preceding and current row) as c2, |
| count(p_date) over(partition by p_mfgr order by p_date range between 3 preceding and current row) as c_order, |
| sum(p_retailprice) over(partition by p_mfgr order by p_date range between 1 preceding and current row) as s1, |
| sum(p_retailprice) over(partition by p_mfgr order by p_date range between 3 preceding and current row) as s2, |
| min(p_retailprice) over(partition by p_mfgr order by p_date range between 1 preceding and current row) as min1, |
| min(p_retailprice) over(partition by p_mfgr order by p_date range between 3 preceding and current row) as min2, |
| max(p_retailprice) over(partition by p_mfgr order by p_date range between 1 preceding and current row) as max1, |
| max(p_retailprice) over(partition by p_mfgr order by p_date range between 3 preceding and current row) as max2, |
| avg(p_retailprice) over(partition by p_mfgr order by p_date range between 1 preceding and current row) as avg1, |
| avg(p_retailprice) over(partition by p_mfgr order by p_date range between 3 preceding and current row) as avg2, |
| row_number() over(partition by p_mfgr) as rn, |
| rank() over(partition by p_mfgr) as r, |
| dense_rank() over(partition by p_mfgr) as dr, |
| rank() over(partition by p_mfgr order by p_date) as r_date, |
| dense_rank() over(partition by p_mfgr order by p_date) as dr_date, |
| first_value(p_retailprice) over(partition by p_mfgr) as fv, |
| last_value(p_retailprice) over(partition by p_mfgr) as lv, |
| lead(p_retailprice) over(partition by p_mfgr) as lead1, |
| lag(p_retailprice) over(partition by p_mfgr) as lag1 |
| from vector_ptf_part_simple_orc; |
| |
| |
| select "************ BATCH SIZE=2, BUFFERED BATCHES: 2 ************"; |
| set hive.vectorized.ptf.max.memory.buffering.batch.count=2; |
| set hive.vectorized.testing.reducer.batch.size=2; |
| select p_mfgr, p_name, rowindex, p_date, p_retailprice, |
| count(*) over(partition by p_mfgr order by p_date range between 1 preceding and current row) as cs1, |
| count(*) over(partition by p_mfgr order by p_date range between 3 preceding and current row) as cs2, |
| count(rowindex) over(partition by p_mfgr order by p_date range between 1 preceding and current row) as c1, |
| count(rowindex) over(partition by p_mfgr order by p_date range between 3 preceding and current row) as c2, |
| count(p_date) over(partition by p_mfgr order by p_date range between 3 preceding and current row) as c_order, |
| sum(p_retailprice) over(partition by p_mfgr order by p_date range between 1 preceding and current row) as s1, |
| sum(p_retailprice) over(partition by p_mfgr order by p_date range between 3 preceding and current row) as s2, |
| min(p_retailprice) over(partition by p_mfgr order by p_date range between 1 preceding and current row) as min1, |
| min(p_retailprice) over(partition by p_mfgr order by p_date range between 3 preceding and current row) as min2, |
| max(p_retailprice) over(partition by p_mfgr order by p_date range between 1 preceding and current row) as max1, |
| max(p_retailprice) over(partition by p_mfgr order by p_date range between 3 preceding and current row) as max2, |
| avg(p_retailprice) over(partition by p_mfgr order by p_date range between 1 preceding and current row) as avg1, |
| avg(p_retailprice) over(partition by p_mfgr order by p_date range between 3 preceding and current row) as avg2, |
| row_number() over(partition by p_mfgr) as rn, |
| rank() over(partition by p_mfgr) as r, |
| dense_rank() over(partition by p_mfgr) as dr, |
| rank() over(partition by p_mfgr order by p_date) as r_date, |
| dense_rank() over(partition by p_mfgr order by p_date) as dr_date, |
| first_value(p_retailprice) over(partition by p_mfgr) as fv, |
| last_value(p_retailprice) over(partition by p_mfgr) as lv, |
| lead(p_retailprice) over(partition by p_mfgr) as lead1, |
| lag(p_retailprice) over(partition by p_mfgr) as lag1 |
| from vector_ptf_part_simple_orc; |
| |
| |
| select "************ BATCH SIZE=2, BUFFERED BATCHES: 3 ************"; |
| set hive.vectorized.ptf.max.memory.buffering.batch.count=3; |
| set hive.vectorized.testing.reducer.batch.size=2; |
| select p_mfgr, p_name, rowindex, p_date, p_retailprice, |
| count(*) over(partition by p_mfgr order by p_date range between 1 preceding and current row) as cs1, |
| count(*) over(partition by p_mfgr order by p_date range between 3 preceding and current row) as cs2, |
| count(rowindex) over(partition by p_mfgr order by p_date range between 1 preceding and current row) as c1, |
| count(rowindex) over(partition by p_mfgr order by p_date range between 3 preceding and current row) as c2, |
| count(p_date) over(partition by p_mfgr order by p_date range between 3 preceding and current row) as c_order, |
| sum(p_retailprice) over(partition by p_mfgr order by p_date range between 1 preceding and current row) as s1, |
| sum(p_retailprice) over(partition by p_mfgr order by p_date range between 3 preceding and current row) as s2, |
| min(p_retailprice) over(partition by p_mfgr order by p_date range between 1 preceding and current row) as min1, |
| min(p_retailprice) over(partition by p_mfgr order by p_date range between 3 preceding and current row) as min2, |
| max(p_retailprice) over(partition by p_mfgr order by p_date range between 1 preceding and current row) as max1, |
| max(p_retailprice) over(partition by p_mfgr order by p_date range between 3 preceding and current row) as max2, |
| avg(p_retailprice) over(partition by p_mfgr order by p_date range between 1 preceding and current row) as avg1, |
| avg(p_retailprice) over(partition by p_mfgr order by p_date range between 3 preceding and current row) as avg2, |
| row_number() over(partition by p_mfgr) as rn, |
| rank() over(partition by p_mfgr) as r, |
| dense_rank() over(partition by p_mfgr) as dr, |
| rank() over(partition by p_mfgr order by p_date) as r_date, |
| dense_rank() over(partition by p_mfgr order by p_date) as dr_date, |
| first_value(p_retailprice) over(partition by p_mfgr) as fv, |
| last_value(p_retailprice) over(partition by p_mfgr) as lv, |
| lead(p_retailprice) over(partition by p_mfgr) as lead1, |
| lag(p_retailprice) over(partition by p_mfgr) as lag1 |
| from vector_ptf_part_simple_orc; |
| |
| |
| select "************ BATCH SIZE=2, BUFFERED BATCHES: 4 ************"; |
| set hive.vectorized.ptf.max.memory.buffering.batch.count=4; |
| set hive.vectorized.testing.reducer.batch.size=2; |
| select p_mfgr, p_name, rowindex, p_date, p_retailprice, |
| count(*) over(partition by p_mfgr order by p_date range between 1 preceding and current row) as cs1, |
| count(*) over(partition by p_mfgr order by p_date range between 3 preceding and current row) as cs2, |
| count(rowindex) over(partition by p_mfgr order by p_date range between 1 preceding and current row) as c1, |
| count(rowindex) over(partition by p_mfgr order by p_date range between 3 preceding and current row) as c2, |
| count(p_date) over(partition by p_mfgr order by p_date range between 3 preceding and current row) as c_order, |
| sum(p_retailprice) over(partition by p_mfgr order by p_date range between 1 preceding and current row) as s1, |
| sum(p_retailprice) over(partition by p_mfgr order by p_date range between 3 preceding and current row) as s2, |
| min(p_retailprice) over(partition by p_mfgr order by p_date range between 1 preceding and current row) as min1, |
| min(p_retailprice) over(partition by p_mfgr order by p_date range between 3 preceding and current row) as min2, |
| max(p_retailprice) over(partition by p_mfgr order by p_date range between 1 preceding and current row) as max1, |
| max(p_retailprice) over(partition by p_mfgr order by p_date range between 3 preceding and current row) as max2, |
| avg(p_retailprice) over(partition by p_mfgr order by p_date range between 1 preceding and current row) as avg1, |
| avg(p_retailprice) over(partition by p_mfgr order by p_date range between 3 preceding and current row) as avg2, |
| row_number() over(partition by p_mfgr) as rn, |
| rank() over(partition by p_mfgr) as r, |
| dense_rank() over(partition by p_mfgr) as dr, |
| rank() over(partition by p_mfgr order by p_date) as r_date, |
| dense_rank() over(partition by p_mfgr order by p_date) as dr_date, |
| first_value(p_retailprice) over(partition by p_mfgr) as fv, |
| last_value(p_retailprice) over(partition by p_mfgr) as lv, |
| lead(p_retailprice) over(partition by p_mfgr) as lead1, |
| lag(p_retailprice) over(partition by p_mfgr) as lag1 |
| from vector_ptf_part_simple_orc; |
| |
| |
| select "************ BATCH SIZE=1, BUFFERED BATCHES: 2 ************"; |
| set hive.vectorized.ptf.max.memory.buffering.batch.count=2; |
| set hive.vectorized.testing.reducer.batch.size=1; |
| select p_mfgr, p_name, rowindex, p_date, p_retailprice, |
| count(*) over(partition by p_mfgr order by p_date range between 1 preceding and current row) as cs1, |
| count(*) over(partition by p_mfgr order by p_date range between 3 preceding and current row) as cs2, |
| count(rowindex) over(partition by p_mfgr order by p_date range between 1 preceding and current row) as c1, |
| count(rowindex) over(partition by p_mfgr order by p_date range between 3 preceding and current row) as c2, |
| count(p_date) over(partition by p_mfgr order by p_date range between 3 preceding and current row) as c_order, |
| sum(p_retailprice) over(partition by p_mfgr order by p_date range between 1 preceding and current row) as s1, |
| sum(p_retailprice) over(partition by p_mfgr order by p_date range between 3 preceding and current row) as s2, |
| min(p_retailprice) over(partition by p_mfgr order by p_date range between 1 preceding and current row) as min1, |
| min(p_retailprice) over(partition by p_mfgr order by p_date range between 3 preceding and current row) as min2, |
| max(p_retailprice) over(partition by p_mfgr order by p_date range between 1 preceding and current row) as max1, |
| max(p_retailprice) over(partition by p_mfgr order by p_date range between 3 preceding and current row) as max2, |
| avg(p_retailprice) over(partition by p_mfgr order by p_date range between 1 preceding and current row) as avg1, |
| avg(p_retailprice) over(partition by p_mfgr order by p_date range between 3 preceding and current row) as avg2, |
| row_number() over(partition by p_mfgr) as rn, |
| rank() over(partition by p_mfgr) as r, |
| dense_rank() over(partition by p_mfgr) as dr, |
| rank() over(partition by p_mfgr order by p_date) as r_date, |
| dense_rank() over(partition by p_mfgr order by p_date) as dr_date, |
| first_value(p_retailprice) over(partition by p_mfgr) as fv, |
| last_value(p_retailprice) over(partition by p_mfgr) as lv, |
| lead(p_retailprice) over(partition by p_mfgr) as lead1, |
| lag(p_retailprice) over(partition by p_mfgr) as lag1 |
| from vector_ptf_part_simple_orc; |
| |
| |
| |
| select "************ BATCH SIZE=2, BUFFERED BATCHES: 3 ************"; |
| set hive.vectorized.ptf.max.memory.buffering.batch.count=3; |
| set hive.vectorized.testing.reducer.batch.size=2; |
| select p_mfgr, p_name, rowindex, p_date, p_retailprice, |
| count(*) over(partition by p_mfgr order by p_date range between 1 preceding and current row) as cs1, |
| count(*) over(partition by p_mfgr order by p_date range between 3 preceding and current row) as cs2, |
| count(rowindex) over(partition by p_mfgr order by p_date range between 1 preceding and current row) as c1, |
| count(rowindex) over(partition by p_mfgr order by p_date range between 3 preceding and current row) as c2, |
| count(p_date) over(partition by p_mfgr order by p_date range between 3 preceding and current row) as c_order, |
| sum(p_retailprice) over(partition by p_mfgr order by p_date range between 1 preceding and current row) as s1, |
| sum(p_retailprice) over(partition by p_mfgr order by p_date range between 3 preceding and current row) as s2, |
| min(p_retailprice) over(partition by p_mfgr order by p_date range between 1 preceding and current row) as min1, |
| min(p_retailprice) over(partition by p_mfgr order by p_date range between 3 preceding and current row) as min2, |
| max(p_retailprice) over(partition by p_mfgr order by p_date range between 1 preceding and current row) as max1, |
| max(p_retailprice) over(partition by p_mfgr order by p_date range between 3 preceding and current row) as max2, |
| avg(p_retailprice) over(partition by p_mfgr order by p_date range between 1 preceding and current row) as avg1, |
| avg(p_retailprice) over(partition by p_mfgr order by p_date range between 3 preceding and current row) as avg2, |
| row_number() over(partition by p_mfgr) as rn, |
| rank() over(partition by p_mfgr) as r, |
| dense_rank() over(partition by p_mfgr) as dr, |
| rank() over(partition by p_mfgr order by p_date) as r_date, |
| dense_rank() over(partition by p_mfgr order by p_date) as dr_date, |
| first_value(p_retailprice) over(partition by p_mfgr) as fv, |
| last_value(p_retailprice) over(partition by p_mfgr) as lv, |
| lead(p_retailprice) over(partition by p_mfgr) as lead1, |
| lag(p_retailprice) over(partition by p_mfgr) as lag1 |
| from vector_ptf_part_simple_orc; |
| |
| |
| select "************ BATCH SIZE=2, BUFFERED BATCHES: 5 ************"; |
| set hive.vectorized.ptf.max.memory.buffering.batch.count=5; |
| set hive.vectorized.testing.reducer.batch.size=2; |
| select p_mfgr, p_name, rowindex, p_date, p_retailprice, |
| count(*) over(partition by p_mfgr order by p_date range between 1 preceding and current row) as cs1, |
| count(*) over(partition by p_mfgr order by p_date range between 3 preceding and current row) as cs2, |
| count(rowindex) over(partition by p_mfgr order by p_date range between 1 preceding and current row) as c1, |
| count(rowindex) over(partition by p_mfgr order by p_date range between 3 preceding and current row) as c2, |
| count(p_date) over(partition by p_mfgr order by p_date range between 3 preceding and current row) as c_order, |
| sum(p_retailprice) over(partition by p_mfgr order by p_date range between 1 preceding and current row) as s1, |
| sum(p_retailprice) over(partition by p_mfgr order by p_date range between 3 preceding and current row) as s2, |
| min(p_retailprice) over(partition by p_mfgr order by p_date range between 1 preceding and current row) as min1, |
| min(p_retailprice) over(partition by p_mfgr order by p_date range between 3 preceding and current row) as min2, |
| max(p_retailprice) over(partition by p_mfgr order by p_date range between 1 preceding and current row) as max1, |
| max(p_retailprice) over(partition by p_mfgr order by p_date range between 3 preceding and current row) as max2, |
| avg(p_retailprice) over(partition by p_mfgr order by p_date range between 1 preceding and current row) as avg1, |
| avg(p_retailprice) over(partition by p_mfgr order by p_date range between 3 preceding and current row) as avg2, |
| row_number() over(partition by p_mfgr) as rn, |
| rank() over(partition by p_mfgr) as r, |
| dense_rank() over(partition by p_mfgr) as dr, |
| rank() over(partition by p_mfgr order by p_date) as r_date, |
| dense_rank() over(partition by p_mfgr order by p_date) as dr_date, |
| first_value(p_retailprice) over(partition by p_mfgr) as fv, |
| last_value(p_retailprice) over(partition by p_mfgr) as lv, |
| lead(p_retailprice) over(partition by p_mfgr) as lead1, |
| lag(p_retailprice) over(partition by p_mfgr) as lag1 |
| from vector_ptf_part_simple_orc; |
| |
| |
| |
| select "************ BATCH SIZE=2, BUFFERED BATCHES: 10 ************"; |
| set hive.vectorized.ptf.max.memory.buffering.batch.count=10; |
| set hive.vectorized.testing.reducer.batch.size=2; |
| select p_mfgr, p_name, rowindex, p_date, p_retailprice, |
| count(*) over(partition by p_mfgr order by p_date range between 1 preceding and current row) as cs1, |
| count(*) over(partition by p_mfgr order by p_date range between 3 preceding and current row) as cs2, |
| count(rowindex) over(partition by p_mfgr order by p_date range between 1 preceding and current row) as c1, |
| count(rowindex) over(partition by p_mfgr order by p_date range between 3 preceding and current row) as c2, |
| count(p_date) over(partition by p_mfgr order by p_date range between 3 preceding and current row) as c_order, |
| sum(p_retailprice) over(partition by p_mfgr order by p_date range between 1 preceding and current row) as s1, |
| sum(p_retailprice) over(partition by p_mfgr order by p_date range between 3 preceding and current row) as s2, |
| min(p_retailprice) over(partition by p_mfgr order by p_date range between 1 preceding and current row) as min1, |
| min(p_retailprice) over(partition by p_mfgr order by p_date range between 3 preceding and current row) as min2, |
| max(p_retailprice) over(partition by p_mfgr order by p_date range between 1 preceding and current row) as max1, |
| max(p_retailprice) over(partition by p_mfgr order by p_date range between 3 preceding and current row) as max2, |
| avg(p_retailprice) over(partition by p_mfgr order by p_date range between 1 preceding and current row) as avg1, |
| avg(p_retailprice) over(partition by p_mfgr order by p_date range between 3 preceding and current row) as avg2, |
| row_number() over(partition by p_mfgr) as rn, |
| rank() over(partition by p_mfgr) as r, |
| dense_rank() over(partition by p_mfgr) as dr, |
| rank() over(partition by p_mfgr order by p_date) as r_date, |
| dense_rank() over(partition by p_mfgr order by p_date) as dr_date, |
| first_value(p_retailprice) over(partition by p_mfgr) as fv, |
| last_value(p_retailprice) over(partition by p_mfgr) as lv, |
| lead(p_retailprice) over(partition by p_mfgr) as lead1, |
| lag(p_retailprice) over(partition by p_mfgr) as lag1 |
| from vector_ptf_part_simple_orc; |
| |
| |
| -- this now falls back to non-vectorized, "x following" should be handled in HIVE-24905 |
| select "************ FOLLOWING ROWS NON-VECTORIZED REFERENCE ************"; |
| set hive.vectorized.execution.ptf.enabled=false; |
| select p_mfgr, p_name, rowindex, p_date, p_retailprice, |
| count(*) over(partition by p_mfgr order by p_date range between 1 preceding and 3 following) as cs1, |
| count(*) over(partition by p_mfgr order by p_date range between 3 preceding and 1 following) as cs2, |
| count(rowindex) over(partition by p_mfgr order by p_date range between 1 preceding and 3 following) as c1, |
| count(rowindex) over(partition by p_mfgr order by p_date range between 3 preceding and 1 following) as c2, |
| count(p_date) over(partition by p_mfgr order by p_date range between 3 preceding and 1 following) as c_order, |
| sum(p_retailprice) over(partition by p_mfgr order by p_date range between 1 preceding and 3 following) as s1, |
| sum(p_retailprice) over(partition by p_mfgr order by p_date range between 3 preceding and 1 following) as s2, |
| min(p_retailprice) over(partition by p_mfgr order by p_date range between 1 preceding and 3 following) as min1, |
| min(p_retailprice) over(partition by p_mfgr order by p_date range between 3 preceding and 1 following) as min2, |
| max(p_retailprice) over(partition by p_mfgr order by p_date range between 1 preceding and 3 following) as max1, |
| max(p_retailprice) over(partition by p_mfgr order by p_date range between 3 preceding and 1 following) as max2, |
| avg(p_retailprice) over(partition by p_mfgr order by p_date range between 1 preceding and 3 following) as avg1, |
| avg(p_retailprice) over(partition by p_mfgr order by p_date range between 3 preceding and 1 following) as avg2, |
| row_number() over(partition by p_mfgr) as rn, |
| rank() over(partition by p_mfgr) as r, |
| dense_rank() over(partition by p_mfgr) as dr, |
| rank() over(partition by p_mfgr order by p_date) as r_date, |
| dense_rank() over(partition by p_mfgr order by p_date) as dr_date, |
| first_value(p_retailprice) over(partition by p_mfgr) as fv, |
| last_value(p_retailprice) over(partition by p_mfgr) as lv, |
| lead(p_retailprice) over(partition by p_mfgr) as lead1, |
| lag(p_retailprice) over(partition by p_mfgr) as lag1 |
| from vector_ptf_part_simple_orc; |
| |
| select "************ FOLLOWING ROWS ************"; |
| set hive.vectorized.execution.ptf.enabled=true; |
| set hive.vectorized.ptf.max.memory.buffering.batch.count=2; |
| set hive.vectorized.testing.reducer.batch.size=2; |
| |
| EXPLAIN VECTORIZATION DETAIL select p_mfgr, p_name, rowindex, p_date, p_retailprice, |
| count(*) over(partition by p_mfgr order by p_date range between 1 preceding and 3 following) as cs1, |
| count(*) over(partition by p_mfgr order by p_date range between 3 preceding and 1 following) as cs2, |
| count(rowindex) over(partition by p_mfgr order by p_date range between 1 preceding and 3 following) as c1, |
| count(rowindex) over(partition by p_mfgr order by p_date range between 3 preceding and 1 following) as c2, |
| count(p_date) over(partition by p_mfgr order by p_date range between 3 preceding and 1 following) as c_order, |
| sum(p_retailprice) over(partition by p_mfgr order by p_date range between 1 preceding and 3 following) as s1, |
| sum(p_retailprice) over(partition by p_mfgr order by p_date range between 3 preceding and 1 following) as s2, |
| min(p_retailprice) over(partition by p_mfgr order by p_date range between 1 preceding and 3 following) as min1, |
| min(p_retailprice) over(partition by p_mfgr order by p_date range between 3 preceding and 1 following) as min2, |
| max(p_retailprice) over(partition by p_mfgr order by p_date range between 1 preceding and 3 following) as max1, |
| max(p_retailprice) over(partition by p_mfgr order by p_date range between 3 preceding and 1 following) as max2, |
| avg(p_retailprice) over(partition by p_mfgr order by p_date range between 1 preceding and 3 following) as avg1, |
| avg(p_retailprice) over(partition by p_mfgr order by p_date range between 3 preceding and 1 following) as avg2, |
| row_number() over(partition by p_mfgr) as rn, |
| rank() over(partition by p_mfgr) as r, |
| dense_rank() over(partition by p_mfgr) as dr, |
| rank() over(partition by p_mfgr order by p_date) as r_date, |
| dense_rank() over(partition by p_mfgr order by p_date) as dr_date, |
| first_value(p_retailprice) over(partition by p_mfgr) as fv, |
| last_value(p_retailprice) over(partition by p_mfgr) as lv, |
| lead(p_retailprice) over(partition by p_mfgr) as lead1, |
| lag(p_retailprice) over(partition by p_mfgr) as lag1 |
| from vector_ptf_part_simple_orc; |
| |
| select p_mfgr, p_name, rowindex, p_date, p_retailprice, |
| count(*) over(partition by p_mfgr order by p_date range between 1 preceding and 3 following) as cs1, |
| count(*) over(partition by p_mfgr order by p_date range between 3 preceding and 1 following) as cs2, |
| count(rowindex) over(partition by p_mfgr order by p_date range between 1 preceding and 3 following) as c1, |
| count(rowindex) over(partition by p_mfgr order by p_date range between 3 preceding and 1 following) as c2, |
| count(p_date) over(partition by p_mfgr order by p_date range between 3 preceding and 1 following) as c_order, |
| sum(p_retailprice) over(partition by p_mfgr order by p_date range between 1 preceding and 3 following) as s1, |
| sum(p_retailprice) over(partition by p_mfgr order by p_date range between 3 preceding and 1 following) as s2, |
| min(p_retailprice) over(partition by p_mfgr order by p_date range between 1 preceding and 3 following) as min1, |
| min(p_retailprice) over(partition by p_mfgr order by p_date range between 3 preceding and 1 following) as min2, |
| max(p_retailprice) over(partition by p_mfgr order by p_date range between 1 preceding and 3 following) as max1, |
| max(p_retailprice) over(partition by p_mfgr order by p_date range between 3 preceding and 1 following) as max2, |
| avg(p_retailprice) over(partition by p_mfgr order by p_date range between 1 preceding and 3 following) as avg1, |
| avg(p_retailprice) over(partition by p_mfgr order by p_date range between 3 preceding and 1 following) as avg2, |
| row_number() over(partition by p_mfgr) as rn, |
| rank() over(partition by p_mfgr) as r, |
| dense_rank() over(partition by p_mfgr) as dr, |
| rank() over(partition by p_mfgr order by p_date) as r_date, |
| dense_rank() over(partition by p_mfgr order by p_date) as dr_date, |
| first_value(p_retailprice) over(partition by p_mfgr) as fv, |
| last_value(p_retailprice) over(partition by p_mfgr) as lv, |
| lead(p_retailprice) over(partition by p_mfgr) as lead1, |
| lag(p_retailprice) over(partition by p_mfgr) as lag1 |
| from vector_ptf_part_simple_orc; |
| |
| |
| -- so far, we tested only date based range, it's time to quickly check all types |
| -- here are partition type checks as well, which are not closely related to ranges and boundaries, |
| -- but were seriously touched by changes in HIVE-24761 |
| set hive.vectorized.ptf.max.memory.buffering.batch.count=2; |
| set hive.vectorized.testing.reducer.batch.size=2; |
| |
| |
| |
| |
| |
| select "************ STRING WINDOW RANGE TYPE ************"; |
| set hive.vectorized.execution.ptf.enabled=false; |
| |
| select "************ STRING WINDOW RANGE TYPE (NON-VECTORIZED REFERENCE) ************"; |
| select p_mfgr, p_name, p_timestamp, rowindex, p_date, p_retailprice, |
| count(*) over(partition by p_mfgr order by p_name range between 3 preceding and current row) as cs, |
| sum(p_retailprice) over(partition by p_mfgr order by p_name range between 3 preceding and current row) as s |
| from vector_ptf_part_simple_orc; |
| |
| select "************ STRING WINDOW RANGE TYPE (VECTORIZED) ************"; |
| set hive.vectorized.execution.ptf.enabled=true; |
| |
| EXPLAIN VECTORIZATION DETAIL select p_mfgr, p_name, p_timestamp, rowindex, p_date, p_retailprice, |
| count(*) over(partition by p_mfgr order by p_name range between 3 preceding and current row) as cs, |
| sum(p_retailprice) over(partition by p_mfgr order by p_name range between 3 preceding and current row) as s |
| from vector_ptf_part_simple_orc; |
| |
| select p_mfgr, p_name, p_timestamp, rowindex, p_date, p_retailprice, |
| count(*) over(partition by p_mfgr order by p_name range between 3 preceding and current row) as cs, |
| sum(p_retailprice) over(partition by p_mfgr order by p_name range between 3 preceding and current row) as s |
| from vector_ptf_part_simple_orc; |
| |
| select "************ STRING PARTITION ************"; |
| EXPLAIN VECTORIZATION DETAIL select p_mfgr, p_name, p_timestamp, rowindex, p_date, p_retailprice, |
| count(*) over(partition by p_name) as cs, |
| sum(p_retailprice) over(partition by p_name) as s |
| from vector_ptf_part_simple_orc; |
| |
| select p_mfgr, p_name, p_timestamp, rowindex, p_date, p_retailprice, |
| count(*) over(partition by p_name) as cs, |
| sum(p_retailprice) over(partition by p_name) as s |
| from vector_ptf_part_simple_orc; |
| |
| select "************ STRING PARTITION WITH CONSTANT PARTITION EXPRESSION ************"; |
| select p_mfgr, p_name, p_timestamp, rowindex, p_date, p_retailprice, |
| count(*) over(partition by p_name) as cs, |
| sum(p_retailprice) over(partition by p_name) as s |
| from vector_ptf_part_simple_orc |
| where p_name = 'almond antique chartreuse lavender yellow'; |
| |
| EXPLAIN VECTORIZATION DETAIL select p_mfgr, p_name, p_timestamp, rowindex, p_date, p_retailprice, |
| count(*) over(partition by p_name) as cs, |
| sum(p_retailprice) over(partition by p_name) as s |
| from vector_ptf_part_simple_orc |
| where p_name = 'almond antique chartreuse lavender yellow'; |
| |
| |
| |
| select "************ TIMESTAMP WINDOW RANGE TYPE ************"; |
| set hive.vectorized.execution.ptf.enabled=false; |
| |
| select "************ TIMESTAMP WINDOW RANGE TYPE (NON-VECTORIZED REFERENCE) ************"; |
| select p_mfgr, p_name, p_timestamp, rowindex, p_date, p_retailprice, |
| count(*) over(partition by p_mfgr order by p_timestamp range between 3 preceding and current row) as cs, |
| sum(p_retailprice) over(partition by p_mfgr order by p_timestamp range between 3 preceding and current row) as s |
| from vector_ptf_part_simple_orc; |
| |
| select "************ TIMESTAMP WINDOW RANGE TYPE (VECTORIZED) ************"; |
| set hive.vectorized.execution.ptf.enabled=true; |
| |
| EXPLAIN VECTORIZATION DETAIL select p_mfgr, p_name, p_timestamp, rowindex, p_date, p_retailprice, |
| count(*) over(partition by p_mfgr order by p_timestamp range between 3 preceding and current row) as cs, |
| sum(p_retailprice) over(partition by p_mfgr order by p_timestamp range between 3 preceding and current row) as s |
| from vector_ptf_part_simple_orc; |
| |
| select p_mfgr, p_name, p_timestamp, rowindex, p_date, p_retailprice, |
| count(*) over(partition by p_mfgr order by p_timestamp range between 3 preceding and current row) as cs, |
| sum(p_retailprice) over(partition by p_mfgr order by p_timestamp range between 3 preceding and current row) as s |
| from vector_ptf_part_simple_orc; |
| |
| select "************ TIMESTAMP PARTITION ************"; |
| EXPLAIN VECTORIZATION DETAIL select p_mfgr, p_name, p_timestamp, rowindex, p_date, p_retailprice, |
| count(*) over(partition by p_timestamp) as cs, |
| sum(p_retailprice) over(partition by p_timestamp) as s |
| from vector_ptf_part_simple_orc; |
| |
| select p_mfgr, p_name, p_timestamp, rowindex, p_date, p_retailprice, |
| count(*) over(partition by p_timestamp) as cs, |
| sum(p_retailprice) over(partition by p_timestamp) as s |
| from vector_ptf_part_simple_orc; |
| |
| select "************ TIMESTAMP PARTITION WITH CONSTANT PARTITION EXPRESSION ************"; |
| EXPLAIN VECTORIZATION DETAIL select p_mfgr, p_name, p_timestamp, rowindex, p_date, p_retailprice, |
| count(*) over(partition by p_timestamp) as cs, |
| sum(p_retailprice) over(partition by p_timestamp) as s |
| from vector_ptf_part_simple_orc |
| where p_timestamp = '1970-01-03 00:00:00.0'; |
| |
| select p_mfgr, p_name, p_timestamp, rowindex, p_date, p_retailprice, |
| count(*) over(partition by p_timestamp) as cs, |
| sum(p_retailprice) over(partition by p_timestamp) as s |
| from vector_ptf_part_simple_orc |
| where p_timestamp = '1970-01-03 00:00:00.0'; |
| |
| |
| |
| |
| |
| |
| select "************ DATE WINDOW RANGE TYPE ************"; |
| set hive.vectorized.execution.ptf.enabled=false; |
| |
| select "************ DATE WINDOW RANGE TYPE (NON-VECTORIZED REFERENCE) ************"; |
| select p_mfgr, p_name, p_timestamp, rowindex, p_date, p_retailprice, |
| count(*) over(partition by p_mfgr order by p_date range between 3 preceding and current row) as cs, |
| sum(p_retailprice) over(partition by p_mfgr order by p_date range between 3 preceding and current row) as s |
| from vector_ptf_part_simple_orc; |
| |
| select "************ DATE WINDOW RANGE TYPE (VECTORIZED) ************"; |
| set hive.vectorized.execution.ptf.enabled=true; |
| |
| EXPLAIN VECTORIZATION DETAIL select p_mfgr, p_name, p_timestamp, rowindex, p_date, p_retailprice, |
| count(*) over(partition by p_mfgr order by p_date range between 3 preceding and current row) as cs, |
| sum(p_retailprice) over(partition by p_mfgr order by p_date range between 3 preceding and current row) as s |
| from vector_ptf_part_simple_orc; |
| |
| select p_mfgr, p_name, p_timestamp, rowindex, p_date, p_retailprice, |
| count(*) over(partition by p_mfgr order by p_date range between 3 preceding and current row) as cs, |
| sum(p_retailprice) over(partition by p_mfgr order by p_date range between 3 preceding and current row) as s |
| from vector_ptf_part_simple_orc; |
| |
| select "************ DATE PARTITION ************"; |
| EXPLAIN VECTORIZATION DETAIL select p_mfgr, p_name, p_timestamp, rowindex, p_date, p_retailprice, |
| count(*) over(partition by p_date) as cs, |
| sum(p_retailprice) over(partition by p_date) as s |
| from vector_ptf_part_simple_orc; |
| |
| select p_mfgr, p_name, p_timestamp, rowindex, p_date, p_retailprice, |
| count(*) over(partition by p_date) as cs, |
| sum(p_retailprice) over(partition by p_date) as s |
| from vector_ptf_part_simple_orc; |
| |
| select "************ DATE PARTITION WITH CONSTANT PARTITION EXPRESSION ************"; |
| EXPLAIN VECTORIZATION DETAIL select p_mfgr, p_name, p_timestamp, rowindex, p_date, p_retailprice, |
| count(*) over(partition by p_date) as cs, |
| sum(p_retailprice) over(partition by p_date) as s |
| from vector_ptf_part_simple_orc |
| where p_date = '1970-01-03'; |
| |
| select p_mfgr, p_name, p_timestamp, rowindex, p_date, p_retailprice, |
| count(*) over(partition by p_date) as cs, |
| sum(p_retailprice) over(partition by p_date) as s |
| from vector_ptf_part_simple_orc |
| where p_date = '1970-01-03'; |
| |
| |
| |
| |
| |
| |
| |
| select "************ INT WINDOW RANGE TYPE ************"; |
| set hive.vectorized.execution.ptf.enabled=false; |
| |
| select "************ INT WINDOW RANGE TYPE (NON-VECTORIZED REFERENCE) ************"; |
| select p_mfgr, p_name, p_timestamp, rowindex, p_date, p_retailprice, p_int, |
| count(*) over(partition by p_mfgr order by p_int range between 3 preceding and current row) as cs, |
| sum(p_retailprice) over(partition by p_mfgr order by p_int range between 3 preceding and current row) as s |
| from vector_ptf_part_simple_orc; |
| |
| select "************ INT WINDOW RANGE TYPE (VECTORIZED) ************"; |
| set hive.vectorized.execution.ptf.enabled=true; |
| |
| EXPLAIN VECTORIZATION DETAIL select p_mfgr, p_name, p_timestamp, rowindex, p_date, p_retailprice, p_int, |
| count(*) over(partition by p_mfgr order by p_int range between 3 preceding and current row) as cs, |
| sum(p_retailprice) over(partition by p_mfgr order by p_int range between 3 preceding and current row) as s |
| from vector_ptf_part_simple_orc; |
| |
| select p_mfgr, p_name, p_timestamp, rowindex, p_date, p_retailprice, p_int, |
| count(*) over(partition by p_mfgr order by p_int range between 3 preceding and current row) as cs, |
| sum(p_retailprice) over(partition by p_mfgr order by p_int range between 3 preceding and current row) as s |
| from vector_ptf_part_simple_orc; |
| |
| select "************ INT PARTITION ************"; |
| EXPLAIN VECTORIZATION DETAIL select p_mfgr, p_name, p_timestamp, rowindex, p_date, p_retailprice, p_int, |
| count(*) over(partition by p_int) as cs, |
| sum(p_retailprice) over(partition by p_int) as s |
| from vector_ptf_part_simple_orc; |
| |
| select p_mfgr, p_name, p_timestamp, rowindex, p_date, p_retailprice, p_int, |
| count(*) over(partition by p_int) as cs, |
| sum(p_retailprice) over(partition by p_int) as s |
| from vector_ptf_part_simple_orc; |
| |
| select "************ INT PARTITION WITH CONSTANT PARTITION EXPRESSION ************"; |
| EXPLAIN VECTORIZATION DETAIL select p_mfgr, p_name, p_timestamp, rowindex, p_date, p_retailprice, p_int, |
| count(*) over(partition by p_int) as cs, |
| sum(p_retailprice) over(partition by p_int) as s |
| from vector_ptf_part_simple_orc |
| where p_date = 115200; |
| |
| select p_mfgr, p_name, p_timestamp, rowindex, p_date, p_retailprice, |
| count(*) over(partition by p_int) as cs, |
| sum(p_retailprice) over(partition by p_int) as s |
| from vector_ptf_part_simple_orc |
| where p_int = 115200; |
| |
| |
| |
| |
| |
| |
| select "************ DECIMAL WINDOW RANGE TYPE ************"; |
| set hive.vectorized.execution.ptf.enabled=false; |
| |
| select "************ DECIMAL WINDOW RANGE TYPE (NON-VECTORIZED REFERENCE) ************"; |
| select p_mfgr, p_name, p_timestamp, rowindex, p_date, p_decimal, p_retailprice, |
| count(*) over(partition by p_mfgr order by p_decimal range between 3 preceding and current row) as cs, |
| sum(p_retailprice) over(partition by p_mfgr order by p_decimal range between 3 preceding and current row) as s |
| from vector_ptf_part_simple_orc; |
| |
| select "************ DECIMAL WINDOW RANGE TYPE (VECTORIZED) ************"; |
| set hive.vectorized.execution.ptf.enabled=true; |
| |
| EXPLAIN VECTORIZATION DETAIL select p_mfgr, p_name, p_timestamp, rowindex, p_date, p_decimal, p_retailprice, |
| count(*) over(partition by p_mfgr order by p_decimal range between 3 preceding and current row) as cs, |
| sum(p_retailprice) over(partition by p_mfgr order by p_decimal range between 3 preceding and current row) as s |
| from vector_ptf_part_simple_orc; |
| |
| select p_mfgr, p_name, p_timestamp, rowindex, p_date, p_decimal, p_retailprice, |
| count(*) over(partition by p_mfgr order by p_decimal range between 3 preceding and current row) as cs, |
| sum(p_retailprice) over(partition by p_mfgr order by p_decimal range between 3 preceding and current row) as s |
| from vector_ptf_part_simple_orc; |
| |
| select "************ DECIMAL PARTITION ************"; |
| EXPLAIN VECTORIZATION DETAIL select p_mfgr, p_name, p_timestamp, rowindex, p_date, p_decimal, p_retailprice, |
| count(*) over(partition by p_decimal) as cs, |
| sum(p_retailprice) over(partition by p_decimal) as s |
| from vector_ptf_part_simple_orc; |
| |
| select p_mfgr, p_name, p_timestamp, rowindex, p_date, p_decimal, p_retailprice, |
| count(*) over(partition by p_decimal) as cs, |
| sum(p_retailprice) over(partition by p_decimal) as s |
| from vector_ptf_part_simple_orc; |
| |
| select "************ DECIMAL PARTITION WITH CONSTANT PARTITION EXPRESSION ************"; |
| EXPLAIN VECTORIZATION DETAIL select p_mfgr, p_name, p_timestamp, rowindex, p_date, p_decimal, p_retailprice, |
| count(*) over(partition by p_decimal) as cs, |
| sum(p_retailprice) over(partition by p_decimal) as s |
| from vector_ptf_part_simple_orc |
| where p_decimal = 1800.7000; |
| |
| select p_mfgr, p_name, p_timestamp, rowindex, p_date, p_decimal, p_retailprice, |
| count(*) over(partition by p_decimal) as cs, |
| sum(p_retailprice) over(partition by p_decimal) as s |
| from vector_ptf_part_simple_orc |
| where p_decimal = 1800.7000; |
| |
| |
| select "************ CHAR WINDOW RANGE TYPE ************"; |
| set hive.vectorized.execution.ptf.enabled=false; |
| |
| select "************ CHAR WINDOW RANGE TYPE (NON-VECTORIZED REFERENCE) ************"; |
| select p_mfgr, p_name, p_timestamp, rowindex, p_date, p_retailprice, p_char, |
| count(*) over(partition by p_mfgr order by p_char range between 3 preceding and current row) as cs, |
| sum(p_retailprice) over(partition by p_mfgr order by p_char range between 3 preceding and current row) as s |
| from vector_ptf_part_simple_orc; |
| |
| select "************ CHAR WINDOW RANGE TYPE (VECTORIZED) ************"; |
| set hive.vectorized.execution.ptf.enabled=true; |
| |
| EXPLAIN VECTORIZATION DETAIL select p_mfgr, p_name, p_timestamp, rowindex, p_date, p_retailprice,p_char, |
| count(*) over(partition by p_mfgr order by p_char range between 3 preceding and |
| current row) as cs, |
| sum(p_retailprice) over(partition by p_mfgr order by p_char range between 3 preceding and current row) as s |
| from vector_ptf_part_simple_orc; |
| |
| select p_mfgr, p_name, p_timestamp, rowindex, p_date, p_retailprice,p_char, |
| count(*) over(partition by p_mfgr order by p_char range between 3 preceding and current row) as cs, |
| sum(p_retailprice) over(partition by p_mfgr order by p_char range between 3 preceding and current row) as s |
| from vector_ptf_part_simple_orc; |
| |
| select "************ CHAR PARTITION ************"; |
| EXPLAIN VECTORIZATION DETAIL select p_mfgr, p_name, p_timestamp, rowindex, p_date, p_retailprice,p_char, |
| count(*) over(partition by p_char) as cs, |
| sum(p_retailprice) over(partition by p_char) as s |
| from vector_ptf_part_simple_orc; |
| |
| select p_mfgr, p_name, p_timestamp, rowindex, p_date, p_retailprice,p_char, |
| count(*) over(partition by p_char) as cs, |
| sum(p_retailprice) over(partition by p_char) as s |
| from vector_ptf_part_simple_orc; |
| |
| select "************ CHAR PARTITION WITH CONSTANT PARTITION EXPRESSION ************"; |
| EXPLAIN VECTORIZATION DETAIL select p_mfgr, p_name, p_timestamp, rowindex, p_date, p_retailprice,p_char, |
| count(*) over(partition by p_char) as cs, |
| sum(p_retailprice) over(partition by p_char) as s |
| from vector_ptf_part_simple_orc |
| where p_char = 'D'; |
| |
| select p_mfgr, p_name, p_timestamp, rowindex, p_date, p_retailprice, p_char, |
| count(*) over(partition by p_char) as cs, |
| sum(p_retailprice) over(partition by p_char) as s |
| from vector_ptf_part_simple_orc |
| where p_char = 'D'; |
| |
| |
| select "************ VARCHAR WINDOW RANGE TYPE ************"; |
| set hive.vectorized.execution.ptf.enabled=false; |
| |
| select "************ VARCHAR WINDOW RANGE TYPE (NON-VECTORIZED REFERENCE) ************"; |
| select p_mfgr, p_name, p_timestamp, rowindex, p_date, p_retailprice,p_varchar, |
| count(*) over(partition by p_mfgr order by p_varchar range between 3 preceding and current row) as cs, |
| sum(p_retailprice) over(partition by p_mfgr order by p_varchar range between 3 preceding and current row) as s |
| from vector_ptf_part_simple_orc; |
| |
| select "************ VARCHAR WINDOW RANGE TYPE (VECTORIZED) ************"; |
| set hive.vectorized.execution.ptf.enabled=true; |
| |
| EXPLAIN VECTORIZATION DETAIL select p_mfgr, p_name, p_timestamp, rowindex, p_date, p_retailprice,p_varchar, |
| count(*) over(partition by p_mfgr order by p_varchar range between 3 preceding and current row) as cs, |
| sum(p_retailprice) over(partition by p_mfgr order by p_varchar range between 3 preceding and current row) as s |
| from vector_ptf_part_simple_orc; |
| |
| select p_mfgr, p_name, p_timestamp, rowindex, p_date, p_retailprice,p_varchar, |
| count(*) over(partition by p_mfgr order by p_varchar range between 3 preceding and current row) as cs, |
| sum(p_retailprice) over(partition by p_mfgr order by p_varchar range between 3 preceding and current row) as s |
| from vector_ptf_part_simple_orc; |
| |
| select "************ VARCHAR PARTITION ************"; |
| EXPLAIN VECTORIZATION DETAIL select p_mfgr, p_name, p_timestamp, rowindex, p_date, p_retailprice,p_varchar, |
| count(*) over(partition by p_varchar) as cs, |
| sum(p_retailprice) over(partition by p_varchar) as s |
| from vector_ptf_part_simple_orc; |
| |
| select p_mfgr, p_name, p_timestamp, rowindex, p_date, p_retailprice,p_varchar, |
| count(*) over(partition by p_varchar) as cs, |
| sum(p_retailprice) over(partition by p_varchar) as s |
| from vector_ptf_part_simple_orc; |
| |
| select "************ VARCHAR PARTITION WITH CONSTANT PARTITION EXPRESSION ************"; |
| select p_mfgr, p_name, p_timestamp, rowindex, p_date, p_retailprice,p_varchar, |
| count(*) over(partition by p_varchar) as cs, |
| sum(p_retailprice) over(partition by p_varchar) as s |
| from vector_ptf_part_simple_orc |
| where p_name = 'DA'; |
| |
| EXPLAIN VECTORIZATION DETAIL select p_mfgr, p_name, p_timestamp, rowindex, p_date, p_retailprice, p_varchar, |
| count(*) over(partition by p_varchar) as cs, |
| sum(p_retailprice) over(partition by p_varchar) as s |
| from vector_ptf_part_simple_orc |
| where p_varchar = 'DA'; |
| |
| select "************ BOOLEAN WINDOW RANGE TYPE ************"; |
| set hive.vectorized.execution.ptf.enabled=false; |
| |
| select "************ BOOLEAN WINDOW RANGE TYPE (NON-VECTORIZED REFERENCE) ************"; |
| select p_mfgr, p_name, p_timestamp, rowindex, p_date, p_retailprice, p_char, p_boolean, |
| count(*) over(partition by p_mfgr order by p_boolean range between 3 preceding and current row) as cs, |
| sum(p_retailprice) over(partition by p_mfgr order by p_boolean range between 3 preceding and current row) as s |
| from vector_ptf_part_simple_orc; |
| |
| select "************ BOOLEAN WINDOW RANGE TYPE (VECTORIZED) ************"; |
| set hive.vectorized.execution.ptf.enabled=true; |
| |
| EXPLAIN VECTORIZATION DETAIL select p_mfgr, p_name, p_timestamp, rowindex, p_date, p_retailprice,p_char, p_boolean, |
| count(*) over(partition by p_mfgr order by p_boolean range between 3 preceding and |
| current row) as cs, |
| sum(p_retailprice) over(partition by p_mfgr order by p_boolean range between 3 preceding and current row) as s |
| from vector_ptf_part_simple_orc; |
| |
| select p_mfgr, p_name, p_timestamp, rowindex, p_date, p_retailprice, p_char, p_boolean, |
| count(*) over(partition by p_mfgr order by p_boolean range between 3 preceding and current row) as cs, |
| sum(p_retailprice) over(partition by p_mfgr order by p_boolean range between 3 preceding and current row) as s |
| from vector_ptf_part_simple_orc; |
| |
| select "************ BOOLEAN PARTITION ************"; |
| EXPLAIN VECTORIZATION DETAIL select p_mfgr, p_name, p_timestamp, rowindex, p_date, p_retailprice,p_char,p_boolean, |
| count(*) over(partition by p_boolean) as cs, |
| sum(p_retailprice) over(partition by p_boolean) as s |
| from vector_ptf_part_simple_orc; |
| |
| select p_mfgr, p_name, p_timestamp, rowindex, p_date, p_retailprice,p_char,p_boolean, |
| count(*) over(partition by p_boolean) as cs, |
| sum(p_retailprice) over(partition by p_boolean) as s |
| from vector_ptf_part_simple_orc; |
| |
| select "************ BOOLEAN PARTITION WITH CONSTANT PARTITION EXPRESSION ************"; |
| EXPLAIN VECTORIZATION DETAIL select p_mfgr, p_name, p_timestamp, rowindex, p_date, p_retailprice,p_char,p_boolean, |
| count(*) over(partition by p_boolean) as cs, |
| sum(p_retailprice) over(partition by p_boolean) as s |
| from vector_ptf_part_simple_orc |
| where p_boolean = true; |
| |
| select p_mfgr, p_name, p_timestamp, rowindex, p_date, p_retailprice, p_char,p_boolean, |
| count(*) over(partition by p_boolean) as cs, |
| sum(p_retailprice) over(partition by p_boolean) as s |
| from vector_ptf_part_simple_orc |
| where p_boolean = false; |
