| --! 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; |
| |
| create table src_multi1 like src; |
| |
| insert into table src_multi1 select * from src; |
| |
| explain extended select * from src_multi1; |
| |
| describe formatted src_multi1; |
| |
| drop table a_n3; |
| drop table b_n3; |
| create table a_n3 like src; |
| create table b_n3 like src; |
| |
| from src |
| insert into table a_n3 select * |
| insert into table b_n3 select *; |
| |
| describe formatted a_n3 key; |
| describe formatted b_n3 key; |
| |
| from src |
| insert overwrite table a_n3 select * |
| insert into table b_n3 select *; |
| |
| describe formatted a_n3; |
| describe formatted b_n3; |
| |
| describe formatted b_n3 key; |
| describe formatted b_n3 value; |
| |
| insert into table b_n3 select NULL, NULL from src limit 10; |
| |
| describe formatted b_n3 key; |
| describe formatted b_n3 value; |
| |
| insert into table b_n3(value) select key+100000 from src limit 10; |
| |
| describe formatted b_n3 key; |
| describe formatted b_n3 value; |
| |
| drop table src_multi2; |
| |
| create table src_multi2 like src; |
| |
| insert into table src_multi2 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; |
| |
| |
| drop table nzhang_part14; |
| |
| create table if not exists nzhang_part14 (key string) |
| partitioned by (value string); |
| |
| insert into table nzhang_part14 partition(value) |
| select key, value from ( |
| select * from (select 'k1' as key, cast(null as string) as value from src limit 2)a_n3 |
| union all |
| select * from (select 'k2' as key, '' as value from src limit 2)b_n3 |
| union all |
| select * from (select 'k3' as key, ' ' as value from src limit 2)c_n1 |
| ) T; |
| |
| explain select key from nzhang_part14; |
| |
| |
| drop table src5; |
| |
| create table src5 as select key, value from src limit 5; |
| |
| insert into table nzhang_part14 partition(value) |
| select key, value from src5; |
| |
| explain select key from nzhang_part14; |
| |
| drop table alter5; |
| |
| create table alter5 ( col1 string ) partitioned by (dt string); |
| |
| alter table alter5 add partition (dt='a'); |
| |
| describe formatted alter5 partition (dt='a'); |
| |
| insert into table alter5 partition (dt='a') select key from src ; |
| |
| describe formatted alter5 partition (dt='a'); |
| |
| explain select * from alter5 where dt='a'; |
| |
| drop table alter5; |
| |
| create table alter5 ( col1 string ) partitioned by (dt string); |
| |
| alter table alter5 add partition (dt='a') location 'parta'; |
| |
| describe formatted alter5 partition (dt='a'); |
| |
| insert into table alter5 partition (dt='a') select key from src ; |
| |
| describe formatted alter5 partition (dt='a'); |
| |
| explain select * from alter5 where dt='a'; |
| |
| |
| drop table src_stat_part; |
| create table src_stat_part(key string, value string) partitioned by (partitionId int); |
| |
| insert into table src_stat_part partition (partitionId=1) |
| select * from src1 limit 5; |
| |
| describe formatted src_stat_part PARTITION(partitionId=1); |
| |
| insert into table src_stat_part partition (partitionId=2) |
| select * from src1; |
| |
| describe formatted src_stat_part PARTITION(partitionId=2); |
| |
| drop table srcbucket_mapjoin_n2; |
| CREATE TABLE srcbucket_mapjoin_n2(key int, value string) partitioned by (ds string) CLUSTERED BY (key) INTO 2 BUCKETS STORED AS TEXTFILE; |
| drop table tab_part_n1; |
| CREATE TABLE tab_part_n1 (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_n2; |
| CREATE TABLE srcbucket_mapjoin_part_n2 (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_n2 partition(ds='2008-04-08'); |
| load data local inpath '../../data/files/bmj1/000001_0' INTO TABLE srcbucket_mapjoin_n2 partition(ds='2008-04-08'); |
| |
| load data local inpath '../../data/files/bmj/000000_0' INTO TABLE srcbucket_mapjoin_part_n2 partition(ds='2008-04-08'); |
| load data local inpath '../../data/files/bmj/000001_0' INTO TABLE srcbucket_mapjoin_part_n2 partition(ds='2008-04-08'); |
| load data local inpath '../../data/files/bmj/000002_0' INTO TABLE srcbucket_mapjoin_part_n2 partition(ds='2008-04-08'); |
| load data local inpath '../../data/files/bmj/000003_0' INTO TABLE srcbucket_mapjoin_part_n2 partition(ds='2008-04-08'); |
| |
| insert into table tab_part_n1 partition (ds='2008-04-08') |
| select key,value from srcbucket_mapjoin_part_n2; |
| |
| describe formatted tab_part_n1 partition (ds='2008-04-08'); |
| |
| CREATE TABLE tab_n0(key int, value string) PARTITIONED BY(ds STRING) CLUSTERED BY (key) SORTED BY (key) INTO 2 BUCKETS STORED AS TEXTFILE; |
| insert into table tab_n0 partition (ds='2008-04-08') |
| select key,value from srcbucket_mapjoin_n2; |
| |
| describe formatted tab_n0 partition (ds='2008-04-08'); |
| |
| drop table nzhang_part14; |
| |
| create table if not exists nzhang_part14 (key string, value string) |
| partitioned by (ds string, hr string); |
| |
| describe formatted nzhang_part14; |
| |
| insert into table nzhang_part14 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_n3 |
| union all |
| select * from (select 'k2' as key, '' as value, '1' as ds, '3' as hr from src limit 2)b_n3 |
| union all |
| select * from (select 'k3' as key, ' ' as value, '2' as ds, '1' as hr from src limit 2)c_n1 |
| ) T; |
| |
| desc formatted nzhang_part14 partition(ds='1', hr='3'); |
| |
| |
| INSERT into TABLE nzhang_part14 PARTITION (ds='2010-03-03', hr) |
| SELECT key, value, hr FROM srcpart WHERE ds is not null and hr>10; |
| |
| desc formatted nzhang_part14 PARTITION(ds='2010-03-03', hr='12'); |
| |
| |
| drop table nzhang_part14; |
| create table if not exists nzhang_part14 (key string, value string) |
| partitioned by (ds string, hr string); |
| |
| INSERT into TABLE nzhang_part14 PARTITION (ds='2010-03-03', hr) |
| SELECT key, value, hr FROM srcpart WHERE ds is not null and hr>10; |
| |
| desc formatted nzhang_part14 PARTITION(ds='2010-03-03', hr='12'); |
| |
| drop table a_n3; |
| create table a_n3 (key string, value string) |
| partitioned by (ds string, hr string); |
| |
| drop table b_n3; |
| create table b_n3 (key string, value string) |
| partitioned by (ds string, hr string); |
| |
| drop table c_n1; |
| create table c_n1 (key string, value string) |
| partitioned by (ds string, hr string); |
| |
| |
| FROM srcpart |
| INSERT into TABLE a_n3 PARTITION (ds='2010-03-11', hr) SELECT key, value, hr WHERE ds is not null and hr>10 |
| INSERT into TABLE b_n3 PARTITION (ds='2010-04-11', hr) SELECT key, value, hr WHERE ds is not null and hr>11 |
| INSERT into TABLE c_n1 PARTITION (ds='2010-05-11', hr) SELECT key, value, hr WHERE hr>0; |
| |
| explain select key from a_n3; |
| explain select value from b_n3; |
| explain select key from b_n3; |
| explain select value from c_n1; |
| explain select key from c_n1; |
| |
| describe formatted a_n3 PARTITION (ds='2010-03-11', hr) key; |
| describe formatted a_n3 PARTITION (ds='2010-03-11', hr) value; |
| |