| --! qt:dataset:src1 |
| --! qt:dataset:src |
| SET hive.vectorized.execution.enabled=false; |
| set hive.mapred.mode=nonstrict; |
| set hive.exec.post.hooks=org.apache.hadoop.hive.ql.hooks.LineageLogger; |
| |
| drop table if exists src2; |
| create table src2 as select key key2, value value2 from src1; |
| |
| select * from src1 where key is not null and value is not null limit 3; |
| select * from src1 where key > 10 and value > 'val' order by key limit 5; |
| |
| drop table if exists dest1_n56; |
| create table dest1_n56 as select * from src1; |
| insert into table dest1_n56 select * from src2; |
| |
| select key k, dest1_n56.value from dest1_n56; |
| select key from src1 union select key2 from src2 order by key; |
| select key k from src1 union select key2 from src2 order by k; |
| |
| select key, count(1) a from dest1_n56 group by key; |
| select key k, count(*) from dest1_n56 group by key; |
| select key k, count(value) from dest1_n56 group by key; |
| select value, max(length(key)) from dest1_n56 group by value; |
| select value, max(length(key)) from dest1_n56 group by value order by value limit 5; |
| |
| select key, length(value) from dest1_n56; |
| select length(value) + 3 from dest1_n56; |
| select 5 from dest1_n56; |
| select 3 * 5 from dest1_n56; |
| |
| drop table if exists dest2_n11; |
| create table dest2_n11 as select * from src1 JOIN src2 ON src1.key = src2.key2; |
| insert overwrite table dest2_n11 select * from src1 JOIN src2 ON src1.key = src2.key2; |
| insert into table dest2_n11 select * from src1 JOIN src2 ON src1.key = src2.key2; |
| insert into table dest2_n11 |
| select * from src1 JOIN src2 ON length(src1.value) = length(src2.value2) + 1; |
| |
| select * from src1 where length(key) > 2; |
| select * from src1 where length(key) > 2 and value > 'a'; |
| |
| drop table if exists dest3_n0; |
| create table dest3_n0 as |
| select * from src1 JOIN src2 ON src1.key = src2.key2 WHERE length(key) > 1; |
| insert overwrite table dest2_n11 |
| select * from src1 JOIN src2 ON src1.key = src2.key2 WHERE length(key) > 3; |
| |
| drop table if exists dest_l1_n0; |
| CREATE TABLE dest_l1_n0(key INT, value STRING) STORED AS TEXTFILE; |
| |
| INSERT OVERWRITE TABLE dest_l1_n0 |
| SELECT j.* |
| FROM (SELECT t1.key, p1.value |
| FROM src1 t1 |
| LEFT OUTER JOIN src p1 |
| ON (t1.key = p1.key) |
| UNION ALL |
| SELECT t2.key, p2.value |
| FROM src1 t2 |
| LEFT OUTER JOIN src p2 |
| ON (t2.key = p2.key)) j; |
| |
| drop table if exists emp; |
| drop table if exists dept_n10; |
| drop table if exists project_n10; |
| drop table if exists tgt_n10; |
| create table emp(emp_id int, name string, mgr_id int, dept_id int); |
| create table dept_n10(dept_id int, dept_name string); |
| create table project_n10(project_id int, project_name string); |
| create table tgt_n10(dept_name string, name string, |
| emp_id int, mgr_id int, proj_id int, proj_name string); |
| |
| INSERT INTO TABLE tgt_n10 |
| SELECT emd.dept_name, emd.name, emd.emp_id, emd.mgr_id, p.project_id, p.project_name |
| FROM ( |
| SELECT d.dept_name, em.name, em.emp_id, em.mgr_id, em.dept_id |
| FROM ( |
| SELECT e.name, e.dept_id, e.emp_id emp_id, m.emp_id mgr_id |
| FROM emp e JOIN emp m ON e.emp_id = m.emp_id |
| ) em |
| JOIN dept_n10 d ON d.dept_id = em.dept_id |
| ) emd JOIN project_n10 p ON emd.dept_id = p.project_id; |
| |
| drop table if exists dest_l2; |
| create table dest_l2 (id int, c1 tinyint, c2 int, c3 bigint) stored as textfile; |
| insert into dest_l2 values(0, 1, 100, 10000); |
| |
| select * from ( |
| select c1 + c2 x from dest_l2 |
| union all |
| select sum(c3) y from (select c3 from dest_l2) v1) v2 order by x; |
| |
| drop table if exists dest_l3; |
| create table dest_l3 (id int, c1 string, c2 string, c3 int) stored as textfile; |
| insert into dest_l3 values(0, "s1", "s2", 15); |
| |
| select sum(a.c1) over (partition by a.c1 order by a.id) |
| from dest_l2 a |
| where a.c2 != 10 |
| group by a.c1, a.c2, a.id |
| having count(a.c2) > 0; |
| |
| select sum(a.c1), count(b.c1), b.c2, b.c3 |
| from dest_l2 a join dest_l3 b on (a.id = b.id) |
| where a.c2 != 10 and b.c3 > 0 |
| group by a.c1, a.c2, a.id, b.c1, b.c2, b.c3 |
| having count(a.c2) > 0 |
| order by b.c3 limit 5; |
| |
| drop table if exists t_n10; |
| create table t_n10 as |
| select distinct a.c2, a.c3 from dest_l2 a |
| inner join dest_l3 b on (a.id = b.id) |
| where a.id > 0 and b.c3 = 15; |
| |
| SELECT substr(src1.key,1,1), count(DISTINCT substr(src1.value,5)), |
| concat(substr(src1.key,1,1),sum(substr(src1.value,5))) |
| from src1 |
| GROUP BY substr(src1.key,1,1); |
| |
| drop table if exists relations; |
| create table relations (identity char(32), type string, |
| ep1_src_type string, ep1_type string, ep2_src_type string, ep2_type string, |
| ep1_ids array<string>, ep2_ids array<string>); |
| |
| drop table if exists rels_exploded; |
| create table rels_exploded (identity char(32), type string, |
| ep1_src_type string, ep1_type string, ep2_src_type string, ep2_type string, |
| ep1_id char(32), ep2_id char(32)); |
| |
| select identity, ep1_id from relations |
| lateral view explode(ep1_ids) nav_rel as ep1_id; |
| |
| insert into rels_exploded select identity, type, |
| ep1_src_type, ep1_type, ep2_src_type, ep2_type, ep1_id, ep2_id |
| from relations lateral view explode(ep1_ids) rel1 as ep1_id |
| lateral view explode (ep2_ids) rel2 as ep2_id; |
| |