| --! qt:dataset:src |
| set hive.mapred.mode=nonstrict; |
| set hive.optimize.listbucketing=true; |
| set mapred.input.dir.recursive=true; |
| set hive.input.format=org.apache.hadoop.hive.ql.io.HiveInputFormat; |
| |
| -- SORT_QUERY_RESULTS |
| |
| -- List bucketing query logic test case. We simulate the directory structure by DML here. |
| -- Test condition: |
| -- 1. where clause has multiple skewed columns and non-skewed columns |
| -- 3. where clause has a few operators |
| -- Test focus: |
| -- 1. basic list bucketing query work |
| -- Test result: |
| -- 1. pruner only pick up right directory |
| -- 2. query result is right |
| |
| -- create a skewed table |
| create table fact_daily_n2 (key String, value String) |
| partitioned by (ds String, hr String) |
| skewed by (key, value) on (('484','val_484'),('238','val_238')) |
| stored as DIRECTORIES; |
| |
| insert overwrite table fact_daily_n2 partition (ds = '1', hr = '4') |
| select key, value from src; |
| |
| describe formatted fact_daily_n2 PARTITION (ds = '1', hr='4'); |
| |
| SELECT count(1) FROM fact_daily_n2 WHERE ds='1' and hr='4'; |
| |
| -- pruner only pick up default directory |
| -- explain plan shows which directory selected: Truncated Path -> Alias |
| explain extended SELECT key, value FROM fact_daily_n2 WHERE ds='1' and hr='4' and value= 'val_484'; |
| -- List Bucketing Query |
| SELECT key, value FROM fact_daily_n2 WHERE ds='1' and hr='4' and value= 'val_484'; |
| |
| -- pruner only pick up default directory |
| -- explain plan shows which directory selected: Truncated Path -> Alias |
| explain extended SELECT key FROM fact_daily_n2 WHERE ds='1' and hr='4' and key= '406'; |
| -- List Bucketing Query |
| SELECT key, value FROM fact_daily_n2 WHERE ds='1' and hr='4' and key= '406'; |
| |
| -- pruner only pick up skewed-value directory |
| -- explain plan shows which directory selected: Truncated Path -> Alias |
| explain extended SELECT key, value FROM fact_daily_n2 WHERE ds='1' and hr='4' and ( (key='484' and value ='val_484') or (key='238' and value= 'val_238')); |
| -- List Bucketing Query |
| SELECT key, value FROM fact_daily_n2 WHERE ds='1' and hr='4' and ( (key='484' and value ='val_484') or (key='238' and value= 'val_238')); |
| |
| -- clean up |
| drop table fact_daily_n2; |