--! qt:dataset:srcpart
set hive.mapred.mode=nonstrict;
set hive.exec.dynamic.partition=true;
set hive.input.format=org.apache.hadoop.hive.ql.io.BucketizedHiveInputFormat;
set hive.merge.smallfiles.avgsize=200;
set mapred.input.dir.recursive=true;
set hive.merge.mapfiles=false;	
set hive.merge.mapredfiles=false;

-- list bucketing DML: dynamic partition. multiple skewed columns. merge.
-- The following explains merge example used in this test case
-- DML will generated 2 partitions
-- ds=2008-04-08/hr=a1
-- ds=2008-04-08/hr=b1
-- without merge, each partition has more files
-- ds=2008-04-08/hr=a1 has 2 files
-- ds=2008-04-08/hr=b1 has 6 files
-- with merge each partition has more files
-- ds=2008-04-08/hr=a1 has 1 files
-- ds=2008-04-08/hr=b1 has 4 files
-- The following shows file size and name in each directory
-- hr=a1/HIVE_DEFAULT_LIST_BUCKETING_DIR_NAME/HIVE_DEFAULT_LIST_BUCKETING_DIR_NAME:
-- without merge
-- 155 000000_0
-- 155 000001_0
-- with merge
-- 254 000000_0
-- hr=b1/key=103/value=val_103:
-- without merge
-- 99 000000_0
-- 99 000001_0
-- with merge
-- 142 000001_0
-- hr=b1/HIVE_DEFAULT_LIST_BUCKETING_DIR_NAME/HIVE_DEFAULT_LIST_BUCKETING_DIR_NAME:
-- without merge
-- 5181 000000_0
-- 5181 000001_0
-- with merge
-- 5181 000000_0
-- 5181 000001_0
-- hr=b1/key=484/value=val_484
-- without merge
-- 87 000000_0
-- 87 000001_0
-- with merge
-- 118 000002_0 

-- SORT_QUERY_RESULTS

-- create a skewed table
create table list_bucketing_dynamic_part_n3 (key String, value String) 
    partitioned by (ds String, hr String) 
    skewed by (key, value) on (('484','val_484'),('51','val_14'),('103','val_103'))
    stored as DIRECTORIES
    STORED AS RCFILE;

-- list bucketing DML without merge. use bucketize to generate a few small files.
explain extended
insert overwrite table list_bucketing_dynamic_part_n3 partition (ds = '2008-04-08', hr)
select key, value, if(key % 100 == 0, 'a1', 'b1') from srcpart where ds = '2008-04-08';

insert overwrite table list_bucketing_dynamic_part_n3 partition (ds = '2008-04-08', hr)
select key, value, if(key % 100 == 0, 'a1', 'b1') from srcpart where ds = '2008-04-08';

-- check DML result
show partitions list_bucketing_dynamic_part_n3;
desc formatted list_bucketing_dynamic_part_n3 partition (ds='2008-04-08', hr='a1');	
desc formatted list_bucketing_dynamic_part_n3 partition (ds='2008-04-08', hr='b1');

set hive.merge.mapfiles=true;	
set hive.merge.mapredfiles=true; 
-- list bucketing DML with merge. use bucketize to generate a few small files.
explain extended
insert overwrite table list_bucketing_dynamic_part_n3 partition (ds = '2008-04-08', hr)
select key, value, if(key % 100 == 0, 'a1', 'b1') from srcpart where ds = '2008-04-08';

insert overwrite table list_bucketing_dynamic_part_n3 partition (ds = '2008-04-08', hr)
select key, value, if(key % 100 == 0, 'a1', 'b1') from srcpart where ds = '2008-04-08';

-- check DML result
show partitions list_bucketing_dynamic_part_n3;
desc formatted list_bucketing_dynamic_part_n3 partition (ds='2008-04-08', hr='a1');	
desc formatted list_bucketing_dynamic_part_n3 partition (ds='2008-04-08', hr='b1');

select count(1) from srcpart where ds = '2008-04-08';
select count(*) from list_bucketing_dynamic_part_n3;

set hive.input.format=org.apache.hadoop.hive.ql.io.HiveInputFormat;
set hive.optimize.listbucketing=true;
explain extended
select * from list_bucketing_dynamic_part_n3 where key = '484' and value = 'val_484';
select * from list_bucketing_dynamic_part_n3 where key = '484' and value = 'val_484';
select * from srcpart where ds = '2008-04-08' and key = '484' and value = 'val_484';

-- clean up
drop table list_bucketing_dynamic_part_n3;