| set hive.strict.checks.bucketing=false; |
| |
| set hive.mapred.mode=nonstrict; |
| set hive.limit.optimize.enable=true; |
| set hive.limit.optimize.limit.file=2; |
| |
| drop table gl_tgt; |
| drop table gl_src1; |
| drop table gl_src2; |
| drop table gl_src_part1; |
| |
| |
| create table gl_src1 (key int, value string) stored as textfile; |
| load data local inpath '../../data/files/srcbucket20.txt' INTO TABLE gl_src1; |
| load data local inpath '../../data/files/srcbucket20.txt' INTO TABLE gl_src1; |
| load data local inpath '../../data/files/srcbucket20.txt' INTO TABLE gl_src1; |
| |
| dfs -touchz ${hiveconf:hive.metastore.warehouse.dir}/gl_src1/_SUCCESS; |
| |
| |
| |
| set hive.limit.row.max.size=100; |
| |
| -- need one file |
| create table gl_tgt as select key from gl_src1 limit 1; |
| select * from gl_tgt ORDER BY key ASC; |
| -- need two files |
| select 'x' as key_new , split(value,',') as value_new from gl_src1 ORDER BY key_new ASC, value_new[0] ASC limit 20; |
| -- no sufficient files |
| select key, value, split(value,',') as value_new from gl_src1 ORDER BY key ASC, value ASC, value_new[0] ASC limit 30; |
| -- need all files |
| select key from gl_src1 ORDER BY key ASC limit 100; |
| set hive.limit.optimize.limit.file=4; |
| select key from gl_src1 ORDER BY key ASC limit 30; |
| |
| -- not qualified cases |
| select key, count(1) from gl_src1 group by key ORDER BY key ASC limit 5; |
| select distinct key from gl_src1 ORDER BY key ASC limit 10; |
| select count(1) from gl_src1 limit 1; |
| select transform(*) using "tr _ \n" as t from |
| (select "a_a_a_a_a_a_" from gl_src1 limit 100) subq ORDER BY t; |
| select key from (select * from (select key,value from gl_src1)t1 limit 10)t2 ORDER BY key ASC limit 2000; |
| |
| -- complicated queries |
| select key from (select * from (select key,value from gl_src1 limit 10)t1 )t2 ORDER BY key ASC; |
| select key from (select * from (select key,value from gl_src1)t1 limit 10)t2 ORDER BY key ASC; |
| insert overwrite table gl_tgt select key+1 from (select * from (select key,value from gl_src1)t1)t2 limit 10; |
| select * from gl_tgt ORDER BY key ASC; |
| |
| -- empty table |
| create table gl_src2 (key int, value string) stored as textfile; |
| select key from gl_src2 ORDER BY key ASC limit 10; |
| |
| -- partition |
| create table gl_src_part1 (key int, value string) partitioned by (p string) stored as textfile; |
| load data local inpath '../../data/files/srcbucket21.txt' INTO TABLE gl_src_part1 partition(p='11'); |
| load data local inpath '../../data/files/srcbucket20.txt' INTO TABLE gl_src_part1 partition(p='12'); |
| load data local inpath '../../data/files/srcbucket20.txt' INTO TABLE gl_src_part1 partition(p='12'); |
| load data local inpath '../../data/files/srcbucket20.txt' INTO TABLE gl_src_part1 partition(p='12'); |
| |
| select key from gl_src_part1 where p like '1%' ORDER BY key ASC limit 10; |
| select key from gl_src_part1 where p='11' ORDER BY key ASC limit 10; |
| select key from gl_src_part1 where p='12' ORDER BY key ASC limit 10; |
| select key from gl_src_part1 where p='13' ORDER BY key ASC limit 10; |
| alter table gl_src_part1 add partition (p='13'); |
| select key from gl_src_part1 where p='13' ORDER BY key ASC limit 10; |
| select key from gl_src_part1 where p='12' ORDER BY key ASC limit 1000; |
| |
| set hive.fetch.task.conversion=none; |
| select * from gl_src1 limit 1; |
| |
| drop table gl_src1; |
| drop table gl_src2; |
| drop table gl_src_part1; |
| drop table gl_tgt; |