| --! qt:dataset:src |
| set hive.mapred.mode=nonstrict; |
| set hive.optimize.listbucketing=true; |
| set mapred.input.dir.recursive=true; |
| set hive.merge.mapfiles=false; |
| set hive.merge.mapredfiles=false; |
| set hive.input.format=org.apache.hadoop.hive.ql.io.HiveInputFormat; |
| |
| |
| -- List bucketing query logic test case. We simulate the directory structure by DML here. |
| -- Test condition: |
| -- 1. where clause has multiple skewed columns |
| -- 2. where clause doesn't have non-skewed column |
| -- 3. where clause has one and operator |
| -- 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 (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 partition (ds = '1', hr = '4') |
| select key, value from src; |
| |
| describe formatted fact_daily PARTITION (ds = '1', hr='4'); |
| |
| SELECT count(1) FROM fact_daily WHERE ds='1' and hr='4'; |
| |
| -- pruner only pick up skewed-value directory |
| -- explain plan shows which directory selected: Truncated Path -> Alias |
| explain extended SELECT key FROM fact_daily WHERE ( ds='1' and hr='4') and (key='484' and value= 'val_484'); |
| -- List Bucketing Query |
| SELECT key FROM fact_daily WHERE ( ds='1' and hr='4') and (key='484' and value= 'val_484'); |
| |
| -- pruner only pick up skewed-value directory |
| -- explain plan shows which directory selected: Truncated Path -> Alias |
| explain extended SELECT key,value FROM fact_daily WHERE ( ds='1' and hr='4') and (key='238' and value= 'val_238'); |
| -- List Bucketing Query |
| SELECT key,value FROM fact_daily WHERE ( ds='1' and hr='4') and (key='238' and value= 'val_238'); |
| |
| -- pruner only pick up default directory |
| -- explain plan shows which directory selected: Truncated Path -> Alias |
| explain extended SELECT key FROM fact_daily WHERE ( ds='1' and hr='4') and (value = "3"); |
| -- List Bucketing Query |
| SELECT key FROM fact_daily WHERE ( ds='1' and hr='4') and (value = "3"); |
| |
| -- pruner only pick up default directory |
| -- explain plan shows which directory selected: Truncated Path -> Alias |
| explain extended SELECT key,value FROM fact_daily WHERE ( ds='1' and hr='4') and key = '495'; |
| -- List Bucketing Query |
| SELECT key,value FROM fact_daily WHERE ( ds='1' and hr='4') and key = '369'; |