| --! qt:dataset:cbo_t2 |
| --! qt:dataset:cbo_t1 |
| set hive.mapred.mode=nonstrict; |
| 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_n18 as select c_int, value, c_boolean, dt from cbo_t1; |
| create view v2_n11 as select c_int, value from cbo_t2; |
| |
| set hive.cbo.returnpath.hiveop=true; |
| select value from v1_n18 where c_boolean=false; |
| select max(c_int) from v1_n18 group by (c_boolean); |
| |
| select count(v1_n18.c_int) from v1_n18 join cbo_t2 on v1_n18.c_int = cbo_t2.c_int; |
| select count(v1_n18.c_int) from v1_n18 join v2_n11 on v1_n18.c_int = v2_n11.c_int; |
| |
| select count(*) from v1_n18 a join v1_n18 b on a.value = b.value; |
| |
| set hive.cbo.returnpath.hiveop=false; |
| create view v3_n4 as select v1_n18.value val from v1_n18 join cbo_t1 on v1_n18.c_boolean = cbo_t1.c_boolean; |
| |
| set hive.cbo.returnpath.hiveop=true; |
| select count(val) from v3_n4 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_n18 where c_boolean = false) |
| select count(value) from q1 ; |
| |
| set hive.cbo.returnpath.hiveop=false; |
| create view v4_n4 as |
| with q1 as ( select key,c_int from cbo_t1 where key = '1') |
| select * from q1 |
| ; |
| |
| set hive.cbo.returnpath.hiveop=true; |
| with q1 as ( select c_int from q2 where c_boolean = false), |
| q2 as ( select c_int,c_boolean from v1_n18 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_n18 where value = '1' or dt = '14') |
| select count(*) from q1 join q2 join v4_n4 on q1.c_int = q2.c_int and v4_n4.c_int = q2.c_int; |
| |
| |
| drop view v1_n18; |
| drop view v2_n11; |
| drop view v3_n4; |
| drop view v4_n4; |