| --! qt:dataset:srcpart |
| set hive.mapred.mode=nonstrict; |
| SET hive.exec.post.hooks=org.apache.hadoop.hive.ql.hooks.CheckColumnAccessHook; |
| SET hive.stats.collect.scancols=true; |
| |
| -- SORT_QUERY_RESULTS |
| -- This test is used for testing the ColumnAccessAnalyzer |
| |
| CREATE TABLE T1_n127(key STRING, val STRING) STORED AS TEXTFILE; |
| LOAD DATA LOCAL INPATH '../../data/files/T1.txt' INTO TABLE T1_n127; |
| |
| CREATE TABLE T2_n75(key STRING, val STRING) STORED AS TEXTFILE; |
| CREATE TABLE T3_n29(key STRING, val STRING) STORED AS TEXTFILE; |
| CREATE TABLE T4_n16(key STRING, val STRING) PARTITIONED BY (p STRING); |
| |
| -- Simple select queries |
| SELECT key FROM T1_n127; |
| SELECT key, val FROM T1_n127; |
| SELECT 1 FROM T1_n127; |
| SELECT key, val from T4_n16 where p=1; |
| SELECT val FROM T4_n16 where p=1; |
| SELECT p, val FROM T4_n16 where p=1; |
| |
| -- More complicated select queries |
| EXPLAIN SELECT key FROM (SELECT key, val FROM T1_n127) subq1; |
| SELECT key FROM (SELECT key, val FROM T1_n127) subq1; |
| EXPLAIN SELECT k FROM (SELECT key as k, val as v FROM T1_n127) subq1; |
| SELECT k FROM (SELECT key as k, val as v FROM T1_n127) subq1; |
| SELECT key + 1 as k FROM T1_n127; |
| SELECT key + val as k FROM T1_n127; |
| |
| -- Work with union |
| EXPLAIN |
| SELECT * FROM ( |
| SELECT key as c FROM T1_n127 |
| UNION ALL |
| SELECT val as c FROM T1_n127 |
| ) subq1; |
| |
| SELECT * FROM ( |
| SELECT key as c FROM T1_n127 |
| UNION ALL |
| SELECT val as c FROM T1_n127 |
| ) subq1; |
| |
| EXPLAIN |
| SELECT * FROM ( |
| SELECT key as c FROM T1_n127 |
| UNION ALL |
| SELECT key as c FROM T1_n127 |
| ) subq1; |
| |
| SELECT * FROM ( |
| SELECT key as c FROM T1_n127 |
| UNION ALL |
| SELECT key as c FROM T1_n127 |
| ) subq1; |
| |
| -- Work with insert overwrite |
| FROM T1_n127 |
| INSERT OVERWRITE TABLE T2_n75 SELECT key, count(1) GROUP BY key |
| INSERT OVERWRITE TABLE T3_n29 SELECT key, sum(val) GROUP BY key; |
| |
| -- Simple joins |
| SELECT * |
| FROM T1_n127 JOIN T2_n75 |
| ON T1_n127.key = T2_n75.key ; |
| |
| EXPLAIN |
| SELECT T1_n127.key |
| FROM T1_n127 JOIN T2_n75 |
| ON T1_n127.key = T2_n75.key; |
| |
| SELECT T1_n127.key |
| FROM T1_n127 JOIN T2_n75 |
| ON T1_n127.key = T2_n75.key; |
| |
| SELECT * |
| FROM T1_n127 JOIN T2_n75 |
| ON T1_n127.key = T2_n75.key AND T1_n127.val = T2_n75.val; |
| |
| -- Map join |
| SELECT /*+ MAPJOIN(a) */ * |
| FROM T1_n127 a JOIN T2_n75 b |
| ON a.key = b.key; |
| |
| -- More joins |
| EXPLAIN |
| SELECT * |
| FROM T1_n127 JOIN T2_n75 |
| ON T1_n127.key = T2_n75.key AND T1_n127.val = 3 and T2_n75.val = 3; |
| |
| SELECT * |
| FROM T1_n127 JOIN T2_n75 |
| ON T1_n127.key = T2_n75.key AND T1_n127.val = 3 and T2_n75.val = 3; |
| |
| EXPLAIN |
| SELECT subq1.val |
| FROM |
| ( |
| SELECT val FROM T1_n127 WHERE key = 5 |
| ) subq1 |
| JOIN |
| ( |
| SELECT val FROM T2_n75 WHERE key = 6 |
| ) subq2 |
| ON subq1.val = subq2.val; |
| |
| SELECT subq1.val |
| FROM |
| ( |
| SELECT val FROM T1_n127 WHERE key = 5 |
| ) subq1 |
| JOIN |
| ( |
| SELECT val FROM T2_n75 WHERE key = 6 |
| ) subq2 |
| ON subq1.val = subq2.val; |
| |
| -- Join followed by join |
| EXPLAIN |
| SELECT * |
| FROM |
| ( |
| SELECT subq1.key as key |
| FROM |
| ( |
| SELECT key, val FROM T1_n127 |
| ) subq1 |
| JOIN |
| ( |
| SELECT key, 'teststring' as val FROM T2_n75 |
| ) subq2 |
| ON subq1.key = subq2.key |
| ) T4_n16 |
| JOIN T3_n29 |
| ON T3_n29.key = T4_n16.key; |
| |
| SELECT * |
| FROM |
| ( |
| SELECT subq1.key as key |
| FROM |
| ( |
| SELECT key, val FROM T1_n127 |
| ) subq1 |
| JOIN |
| ( |
| SELECT key, 'teststring' as val FROM T2_n75 |
| ) subq2 |
| ON subq1.key = subq2.key |
| ) T4_n16 |
| JOIN T3_n29 |
| ON T3_n29.key = T4_n16.key; |
| |
| -- for partitioned table |
| SELECT * FROM srcpart TABLESAMPLE (10 ROWS); |
| SELECT key,ds FROM srcpart TABLESAMPLE (10 ROWS) WHERE hr='11'; |
| SELECT value FROM srcpart TABLESAMPLE (10 ROWS) WHERE ds='2008-04-08'; |