| -- Mask the totalSize value as it can have slight variability, causing test flakiness |
| --! qt:replace:/(\s+totalSize\s+)\S+(\s+)/$1#Masked#$2/ |
| -- SORT_QUERY_RESULTS |
| |
| set hive.support.concurrency=true; |
| set hive.txn.manager=org.apache.hadoop.hive.ql.lockmgr.DbTxnManager; |
| set hive.strict.checks.cartesian.product=false; |
| set hive.stats.fetch.column.stats=true; |
| set hive.materializedview.rewriting=true; |
| |
| set hive.materializedview.fileformat=parquet; |
| set hive.materializedview.serde=org.apache.hadoop.hive.ql.io.parquet.serde.ParquetHiveSerDe; |
| |
| create table emps_parquet_n3 ( |
| empid int, |
| deptno int, |
| name varchar(256), |
| salary float, |
| commission int) |
| stored as parquet TBLPROPERTIES ('transactional'='true', 'transactional_properties'='insert_only'); |
| insert into emps_parquet_n3 values (100, 10, 'Bill', 10000, 1000), (200, 20, 'Eric', 8000, 500), |
| (150, 10, 'Sebastian', 7000, null), (110, 10, 'Theodore', 10000, 250), (120, 10, 'Bill', 10000, 250); |
| |
| create table depts_parquet_n2 ( |
| deptno int, |
| name varchar(256), |
| locationid int) |
| stored as parquet TBLPROPERTIES ('transactional'='true', 'transactional_properties'='insert_only'); |
| insert into depts_parquet_n2 values (10, 'Sales', 10), (30, 'Marketing', null), (20, 'HR', 20); |
| |
| create table dependents_parquet_n2 ( |
| empid int, |
| name varchar(256)) |
| stored as parquet TBLPROPERTIES ('transactional'='true', 'transactional_properties'='insert_only'); |
| insert into dependents_parquet_n2 values (10, 'Michael'), (20, 'Jane'); |
| |
| create table locations_parquet_n2 ( |
| locationid int, |
| name varchar(256)) |
| stored as parquet TBLPROPERTIES ('transactional'='true', 'transactional_properties'='insert_only'); |
| insert into locations_parquet_n2 values (10, 'San Francisco'), (20, 'San Diego'); |
| |
| alter table emps_parquet_n3 add constraint pk1 primary key (empid) disable novalidate rely; |
| alter table depts_parquet_n2 add constraint pk2 primary key (deptno) disable novalidate rely; |
| alter table dependents_parquet_n2 add constraint pk3 primary key (empid) disable novalidate rely; |
| alter table locations_parquet_n2 add constraint pk4 primary key (locationid) disable novalidate rely; |
| |
| alter table emps_parquet_n3 add constraint fk1 foreign key (deptno) references depts_parquet_n2(deptno) disable novalidate rely; |
| alter table depts_parquet_n2 add constraint fk2 foreign key (locationid) references locations_parquet_n2(locationid) disable novalidate rely; |
| |
| -- EXAMPLE 1 |
| create materialized view mv1_parquet_n2 |
| TBLPROPERTIES ('transactional'='true', 'transactional_properties'='insert_only') as |
| select * from emps_parquet_n3 where empid < 150; |
| |
| describe formatted mv1_parquet_n2; |
| |
| explain cbo |
| select * |
| from (select * from emps_parquet_n3 where empid < 120) t |
| join depts_parquet_n2 using (deptno); |
| |
| select * |
| from (select * from emps_parquet_n3 where empid < 120) t |
| join depts_parquet_n2 using (deptno); |
| |
| drop materialized view mv1_parquet_n2; |
| |
| -- EXAMPLE 2 |
| create materialized view mv1_parquet_n2 |
| TBLPROPERTIES ('transactional'='true', 'transactional_properties'='insert_only') as |
| select deptno, name, salary, commission |
| from emps_parquet_n3; |
| |
| explain cbo |
| select emps_parquet_n3.name, emps_parquet_n3.salary, emps_parquet_n3.commission |
| from emps_parquet_n3 |
| join depts_parquet_n2 using (deptno); |
| |
| select emps_parquet_n3.name, emps_parquet_n3.salary, emps_parquet_n3.commission |
| from emps_parquet_n3 |
| join depts_parquet_n2 using (deptno); |
| |
| drop materialized view mv1_parquet_n2; |
| |
| -- EXAMPLE 3 |
| create materialized view mv1_parquet_n2 |
| TBLPROPERTIES ('transactional'='true', 'transactional_properties'='insert_only') as |
| select empid deptno from emps_parquet_n3 |
| join depts_parquet_n2 using (deptno); |
| |
| explain cbo |
| select empid deptno from emps_parquet_n3 |
| join depts_parquet_n2 using (deptno) where empid = 1; |
| |
| select empid deptno from emps_parquet_n3 |
| join depts_parquet_n2 using (deptno) where empid = 1; |
| |
| drop materialized view mv1_parquet_n2; |
| |
| -- EXAMPLE 4 |
| create materialized view mv1_parquet_n2 |
| TBLPROPERTIES ('transactional'='true', 'transactional_properties'='insert_only') as |
| select * from emps_parquet_n3 where empid < 200; |
| |
| explain cbo |
| select * from emps_parquet_n3 where empid > 120 |
| union all select * from emps_parquet_n3 where empid < 150; |
| |
| select * from emps_parquet_n3 where empid > 120 |
| union all select * from emps_parquet_n3 where empid < 150; |
| |
| drop materialized view mv1_parquet_n2; |
| |
| -- EXAMPLE 5 - NO MV, ALREADY UNIQUE |
| create materialized view mv1_parquet_n2 |
| TBLPROPERTIES ('transactional'='true', 'transactional_properties'='insert_only') as |
| select empid, deptno from emps_parquet_n3 group by empid, deptno; |
| |
| explain cbo |
| select empid, deptno from emps_parquet_n3 group by empid, deptno; |
| |
| select empid, deptno from emps_parquet_n3 group by empid, deptno; |
| |
| drop materialized view mv1_parquet_n2; |
| |
| -- EXAMPLE 5 - NO MV, ALREADY UNIQUE |
| create materialized view mv1_parquet_n2 |
| TBLPROPERTIES ('transactional'='true', 'transactional_properties'='insert_only') as |
| select empid, name from emps_parquet_n3 group by empid, name; |
| |
| explain cbo |
| select empid, name from emps_parquet_n3 group by empid, name; |
| |
| select empid, name from emps_parquet_n3 group by empid, name; |
| |
| drop materialized view mv1_parquet_n2; |
| |
| -- EXAMPLE 5 |
| create materialized view mv1_parquet_n2 |
| TBLPROPERTIES ('transactional'='true', 'transactional_properties'='insert_only') as |
| select name, salary from emps_parquet_n3 group by name, salary; |
| |
| explain cbo |
| select name, salary from emps_parquet_n3 group by name, salary; |
| |
| select name, salary from emps_parquet_n3 group by name, salary; |
| |
| drop materialized view mv1_parquet_n2; |
| |
| -- EXAMPLE 6 |
| create materialized view mv1_parquet_n2 |
| TBLPROPERTIES ('transactional'='true', 'transactional_properties'='insert_only') as |
| select name, salary from emps_parquet_n3 group by name, salary; |
| |
| explain cbo |
| select name from emps_parquet_n3 group by name; |
| |
| select name from emps_parquet_n3 group by name; |
| |
| drop materialized view mv1_parquet_n2; |
| |
| -- EXAMPLE 7 |
| create materialized view mv1_parquet_n2 |
| TBLPROPERTIES ('transactional'='true', 'transactional_properties'='insert_only') as |
| select name, salary from emps_parquet_n3 where deptno = 10 group by name, salary; |
| |
| explain cbo |
| select name from emps_parquet_n3 where deptno = 10 group by name; |
| |
| select name from emps_parquet_n3 where deptno = 10 group by name; |
| |
| drop materialized view mv1_parquet_n2; |
| |
| -- EXAMPLE 9 |
| create materialized view mv1_parquet_n2 |
| TBLPROPERTIES ('transactional'='true', 'transactional_properties'='insert_only') as |
| select name, salary, count(*) as c, sum(empid) as s |
| from emps_parquet_n3 group by name, salary; |
| |
| explain cbo |
| select name from emps_parquet_n3 group by name; |
| |
| select name from emps_parquet_n3 group by name; |
| |
| insert into emps_parquet_n3 values (400, 10, 'Bill', 50000, 500); |
| |
| explain |
| alter materialized view mv1_parquet_n2 rebuild; |
| |
| alter materialized view mv1_parquet_n2 rebuild; |
| |
| explain cbo |
| select name, sum(empid) from emps_parquet_n3 group by name; |
| |
| select name, sum(empid) from emps_parquet_n3 group by name; |
| |
| drop materialized view mv1_parquet_n2; |
| |
| select name, sum(empid) from emps_parquet_n3 group by name; |