--! qt:dataset:srcpart
--! qt:dataset:src1
--! qt:dataset:src
set hive.strict.checks.bucketing=false;

set hive.stats.column.autogather=true;
set hive.stats.fetch.column.stats=true;
set hive.exec.dynamic.partition=true;
set hive.auto.convert.join=true;
set hive.join.emit.interval=2;
set hive.auto.convert.join.noconditionaltask=true;
set hive.auto.convert.join.noconditionaltask.size=10000;
set hive.auto.convert.sortmerge.join.bigtable.selection.policy = org.apache.hadoop.hive.ql.optimizer.TableSizeBasedBigTableSelectorForAutoSMJ;
set hive.optimize.bucketingsorting=false;

drop table src_multi1_n1;

create table src_multi1_n1 like src;

insert overwrite table src_multi1_n1 select * from src;

explain extended select * from src_multi1_n1;

describe formatted src_multi1_n1;

drop table a_n12;
drop table b_n9;
create table a_n12 like src;
create table b_n9 like src;

from src
insert overwrite table a_n12 select *
insert overwrite table b_n9 select *;

describe formatted a_n12;
describe formatted b_n9;

drop table a_n12;
drop table b_n9;
create table a_n12 like src;
create table b_n9 like src;

from src
insert overwrite table a_n12 select *
insert into table b_n9 select *;

describe formatted a_n12;
describe formatted b_n9;


drop table src_multi2_n2;

create table src_multi2_n2 like src;

insert overwrite table src_multi2_n2 select subq.key, src.value from (select * from src union select * from src1)subq join src on subq.key=src.key;

describe formatted src_multi2_n2;


drop table nzhang_part14_n1;

create table if not exists nzhang_part14_n1 (key string)
  partitioned by (value string);

desc formatted nzhang_part14_n1;

insert overwrite table nzhang_part14_n1 partition(value) 
select key, value from (
  select * from (select 'k1' as key, cast(null as string) as value from src limit 2)a_n12 
  union all
  select * from (select 'k2' as key, '' as value from src limit 2)b_n9
  union all 
  select * from (select 'k3' as key, ' ' as value from src limit 2)c_n2
) T;

desc formatted nzhang_part14_n1 partition (value=' ');

explain select key from nzhang_part14_n1;


drop table src5_n0;

create table src5_n0 as select key, value from src limit 5; 

insert overwrite table nzhang_part14_n1 partition(value)
select key, value from src5_n0; 

explain select key from nzhang_part14_n1;


create table alter5_n0 ( col1 string ) partitioned by (dt string);

alter table alter5_n0 add partition (dt='a') location 'parta';

describe formatted alter5_n0 partition (dt='a');

insert overwrite table alter5_n0 partition (dt='a') select key from src ;

describe formatted alter5_n0 partition (dt='a');

explain select * from alter5_n0 where dt='a';


drop table src_stat_part_n0;
create table src_stat_part_n0(key string, value string) partitioned by (partitionId int);

insert overwrite table src_stat_part_n0 partition (partitionId=1)
select * from src1 limit 5;

describe formatted src_stat_part_n0 PARTITION(partitionId=1);

insert overwrite table src_stat_part_n0 partition (partitionId=2)
select * from src1;

describe formatted src_stat_part_n0 PARTITION(partitionId=2);

drop table srcbucket_mapjoin_n6;
CREATE TABLE srcbucket_mapjoin_n6(key int, value string) partitioned by (ds string) CLUSTERED BY (key) INTO 2 BUCKETS STORED AS TEXTFILE;
drop table tab_part_n4;
CREATE TABLE tab_part_n4 (key int, value string) PARTITIONED BY(ds STRING) CLUSTERED BY (key) SORTED BY (key) INTO 4 BUCKETS STORED AS TEXTFILE;
drop table srcbucket_mapjoin_part_n7;
CREATE TABLE srcbucket_mapjoin_part_n7 (key int, value string) partitioned by (ds string) CLUSTERED BY (key) INTO 4 BUCKETS STORED AS TEXTFILE;

load data local inpath '../../data/files/bmj/000000_0' INTO TABLE srcbucket_mapjoin_n6 partition(ds='2008-04-08');
load data local inpath '../../data/files/bmj1/000001_0' INTO TABLE srcbucket_mapjoin_n6 partition(ds='2008-04-08');

load data local inpath '../../data/files/bmj/000000_0' INTO TABLE srcbucket_mapjoin_part_n7 partition(ds='2008-04-08');
load data local inpath '../../data/files/bmj/000001_0' INTO TABLE srcbucket_mapjoin_part_n7 partition(ds='2008-04-08');
load data local inpath '../../data/files/bmj/000002_0' INTO TABLE srcbucket_mapjoin_part_n7 partition(ds='2008-04-08');
load data local inpath '../../data/files/bmj/000003_0' INTO TABLE srcbucket_mapjoin_part_n7 partition(ds='2008-04-08');

insert overwrite table tab_part_n4 partition (ds='2008-04-08')
select key,value from srcbucket_mapjoin_part_n7;

describe formatted tab_part_n4 partition (ds='2008-04-08');

CREATE TABLE tab_n3(key int, value string) PARTITIONED BY(ds STRING) CLUSTERED BY (key) SORTED BY (key) INTO 2 BUCKETS STORED AS TEXTFILE;
insert overwrite table tab_n3 partition (ds='2008-04-08')
select key,value from srcbucket_mapjoin_n6;

describe formatted tab_n3 partition (ds='2008-04-08');

drop table nzhang_part14_n1;

create table if not exists nzhang_part14_n1 (key string, value string)
  partitioned by (ds string, hr string);

describe formatted nzhang_part14_n1;

insert overwrite table nzhang_part14_n1 partition(ds, hr) 
select key, value, ds, hr from (
  select * from (select 'k1' as key, cast(null as string) as value, '1' as ds, '2' as hr from src limit 2)a_n12 
  union all
  select * from (select 'k2' as key, '' as value, '1' as ds, '3' as hr from src limit 2)b_n9
  union all 
  select * from (select 'k3' as key, ' ' as value, '2' as ds, '1' as hr from src limit 2)c_n2
) T;

desc formatted nzhang_part14_n1 partition(ds='1', hr='3');


INSERT OVERWRITE TABLE nzhang_part14_n1 PARTITION (ds='2010-03-03', hr)
SELECT key, value, hr FROM srcpart WHERE ds is not null and hr>10;

desc formatted nzhang_part14_n1 PARTITION(ds='2010-03-03', hr='12');


drop table nzhang_part14_n1;
create table if not exists nzhang_part14_n1 (key string, value string)
partitioned by (ds string, hr string);

INSERT OVERWRITE TABLE nzhang_part14_n1 PARTITION (ds='2010-03-03', hr)
SELECT key, value, hr FROM srcpart WHERE ds is not null and hr>10;

desc formatted nzhang_part14_n1 PARTITION(ds='2010-03-03', hr='12');

drop table a_n12;
create table a_n12 (key string, value string)
partitioned by (ds string, hr string);

drop table b_n9;
create table b_n9 (key string, value string)
partitioned by (ds string, hr string);

drop table c_n2;
create table c_n2 (key string, value string)
partitioned by (ds string, hr string);


FROM srcpart 
INSERT OVERWRITE TABLE a_n12 PARTITION (ds='2010-03-11', hr) SELECT key, value, hr WHERE ds is not null and hr>10
INSERT OVERWRITE TABLE b_n9 PARTITION (ds='2010-04-11', hr) SELECT key, value, hr WHERE ds is not null and hr>11
INSERT OVERWRITE TABLE c_n2 PARTITION (ds='2010-05-11', hr) SELECT key, value, hr WHERE hr>0;

explain select key from a_n12;
explain select value from b_n9;
explain select key from b_n9;
explain select value from c_n2;
explain select key from c_n2;