| --! qt:dataset:src |
| set hive.stats.column.autogather=false; |
| set hive.strict.checks.bucketing=false; |
| |
| set hive.mapred.mode=nonstrict; |
| set hive.explain.user=false; |
| set hive.auto.convert.join=true; |
| set hive.auto.convert.join.noconditionaltask=true; |
| set hive.auto.convert.join.noconditionaltask.size=30000; |
| |
| set hive.optimize.bucketingsorting=false; |
| |
| set hive.optimize.ppd=true; |
| set hive.optimize.index.filter=true; |
| set hive.tez.bucket.pruning=true; |
| set hive.fetch.task.conversion=none; |
| set hive.support.concurrency=true; |
| set hive.txn.manager=org.apache.hadoop.hive.ql.lockmgr.DbTxnManager; |
| |
| |
| create transactional table acid_ptn_bucket1 (a int, b int) partitioned by(ds string) |
| clustered by (a) into 2 buckets stored as ORC |
| TBLPROPERTIES('bucketing_version'='1', 'transactional'='true', 'transactional_properties'='default'); |
| |
| explain extended insert into acid_ptn_bucket1 partition (ds) values(1,2,'today'),(1,3,'today'),(1,4,'yesterday'),(2,2,'yesterday'),(2,3,'today'),(2,4,'today'); |
| insert into acid_ptn_bucket1 partition (ds) values(1,2,'today'),(1,3,'today'),(1,4,'yesterday'),(2,2,'yesterday'),(2,3,'today'),(2,4,'today'); |
| |
| alter table acid_ptn_bucket1 add columns(c int); |
| |
| insert into acid_ptn_bucket1 partition (ds) values(3,2,1000,'yesterday'),(3,3,1001,'today'),(3,4,1002,'yesterday'),(4,2,1003,'today'), (4,3,1004,'yesterday'),(4,4,1005,'today'); |
| select ROW__ID, * from acid_ptn_bucket1 where ROW__ID.bucketid = 536870912 and ds='today'; |
| select ROW__ID, * from acid_ptn_bucket1 where ROW__ID.bucketid = 536936448 and ds='today'; |
| |
| --create table s1 as select key, value from src where value > 2 group by key, value limit 10; |
| --create table s2 as select key, '45' from src s2 where key > 1 group by key limit 10; |
| |
| create table s1 (key int, value int) stored as ORC; |
| create table s2 (key int, value int) stored as ORC; |
| |
| insert into s1 values(111, 33), (10, 45), (103, 44), (129, 34), (128, 11); |
| insert into s2 values(10, 45), (100, 45), (103, 44), (110, 12), (128, 34), (117, 71); |
| insert into table acid_ptn_bucket1 partition(ds='today') select key, count(value), key from (select * from s1 union all select * from s2) sub group by key; |
| select ROW__ID, * from acid_ptn_bucket1 where ROW__ID.bucketid = 536870912 and ds='today'; |
| select ROW__ID, * from acid_ptn_bucket1 where ROW__ID.bucketid = 536936448 and ds='today'; |