| --! qt:dataset:cbo_t2 |
| --! qt:dataset:cbo_t1 |
| set hive.mapred.mode=nonstrict; |
| set hive.cbo.enable=true; |
| set hive.exec.check.crossproducts=false; |
| |
| set hive.stats.fetch.column.stats=true; |
| set hive.auto.convert.join=false; |
| |
| -- 10. Test views |
| create view v1_n12 as select c_int, value, c_boolean, dt from cbo_t1; |
| create view v2_n6 as select c_int, value from cbo_t2; |
| |
| select value from v1_n12 where c_boolean=false; |
| select max(c_int) from v1_n12 group by (c_boolean); |
| |
| select count(v1_n12.c_int) from v1_n12 join cbo_t2 on v1_n12.c_int = cbo_t2.c_int; |
| select count(v1_n12.c_int) from v1_n12 join v2_n6 on v1_n12.c_int = v2_n6.c_int; |
| |
| select count(*) from v1_n12 a join v1_n12 b on a.value = b.value; |
| |
| create view v3_n2 as select v1_n12.value val from v1_n12 join cbo_t1 on v1_n12.c_boolean = cbo_t1.c_boolean; |
| |
| select count(val) from v3_n2 where val != '1'; |
| with q1 as ( select key from cbo_t1 where key = '1') |
| select count(*) from q1; |
| |
| with q1 as ( select value from v1_n12 where c_boolean = false) |
| select count(value) from q1 ; |
| |
| create view v4_n2 as |
| with q1 as ( select key,c_int from cbo_t1 where key = '1') |
| select * from q1 |
| ; |
| |
| with q1 as ( select c_int from q2 where c_boolean = false), |
| q2 as ( select c_int,c_boolean from v1_n12 where value = '1') |
| select sum(c_int) from (select c_int from q1) a; |
| |
| with q1 as ( select cbo_t1.c_int c_int from q2 join cbo_t1 where q2.c_int = cbo_t1.c_int and cbo_t1.dt='2014'), |
| q2 as ( select c_int,c_boolean from v1_n12 where value = '1' or dt = '14') |
| select count(*) from q1 join q2 join v4_n2 on q1.c_int = q2.c_int and v4_n2.c_int = q2.c_int; |
| |
| |
| drop view v1_n12; |
| drop view v2_n6; |
| drop view v3_n2; |
| drop view v4_n2; |