set hive.acid.direct.insert.enabled=true;
set hive.support.concurrency=true;
set hive.txn.manager=org.apache.hadoop.hive.ql.lockmgr.DbTxnManager;
set hive.stats.autogather=false;

drop table if exists io_test_acid_part;
drop table if exists io_test_text_1;
drop table if exists io_test_text_2;
drop table if exists io_test_text_3;
drop table if exists io_test_acid_1;
drop table if exists io_test_acid_2;
drop table if exists io_test_acid_3;

create external table io_test_text_1 (a int, b int, c int) stored as textfile;

insert into io_test_text_1 values (1111, 11, 1111), (2222, 22, 1111), (3333, 33, 2222), (4444, 44, NULL), (5555, 55, NULL);

create external table io_test_text_2 (a int, b int, c int) stored as textfile;

insert into io_test_text_2 values (1111, 11, 1111), (2222, 22, 1111), (3333, 33, 2222), (4444, 44, 4444), (5555, 55, 4444);

-- non-partitioned table

create table io_test_acid (a int, b int, c int) stored as orc tblproperties('transactional'='true');

insert overwrite table io_test_acid select a, b, c from io_test_text_1 where c is not null;

select * from io_test_acid order by a;

insert overwrite table io_test_acid select a, b, c from io_test_text_1 where c is null;

select * from io_test_acid order by a;

insert overwrite table io_test_acid select a, b, c from io_test_text_2 where c is null;

select * from io_test_acid order by a;

insert overwrite table io_test_acid select a, b, c from io_test_text_2 where c is not null;

select * from io_test_acid order by a;

drop table io_test_acid;

create table io_test_acid_1 (a int, b int, c int) stored as orc tblproperties('transactional'='true');
create table io_test_acid_2 (a int, b int, c int) stored as orc tblproperties('transactional'='true');
create table io_test_acid_3 (a int, b int, c int) stored as orc tblproperties('transactional'='true');

from io_test_text_2
insert overwrite table io_test_acid_1 select a, b, c where c=1111
insert overwrite table io_test_acid_2 select a, b, c where c=2222
insert overwrite table io_test_acid_3 select a, b, c where c=4444
;

select * from io_test_acid_1 order by a;
select * from io_test_acid_2 order by a;
select * from io_test_acid_3 order by a;

from io_test_text_1
insert overwrite table io_test_acid_1 select a, b, c where c is null
insert overwrite table io_test_acid_2 select a, b, c where c=7777
insert overwrite table io_test_acid_3 select a, b, c where c is not null
;

select * from io_test_acid_1 order by a;
select * from io_test_acid_2 order by a;
select * from io_test_acid_3 order by a;

drop table io_test_acid_1;
drop table io_test_acid_2;
drop table io_test_acid_3;


-- static partitioning

create table io_test_acid_part (a int, b int) partitioned by (c int) stored as orc tblproperties('transactional'='true');

insert overwrite table io_test_acid_part partition (c=1) select a, b from io_test_text_1 where c is not null;

select * from io_test_acid_part order by a;

insert overwrite table io_test_acid_part partition (c=2) select a, b from io_test_text_1 where c is null;

select * from io_test_acid_part order by a;

insert overwrite table io_test_acid_part partition (c=1) select a, b from io_test_text_1 where c is null;

select * from io_test_acid_part order by a, c;

insert overwrite table io_test_acid_part partition (c=1) select a, b from io_test_text_2 where c is null;

select * from io_test_acid_part order by a;

insert overwrite table io_test_acid_part partition (c=1) select a, b from io_test_text_2 where c is not null;

select * from io_test_acid_part order by a, c;

drop table io_test_acid_part;

create table io_test_acid_part (a int, b int) partitioned by (c int) stored as orc tblproperties('transactional'='true');

from io_test_text_2
insert overwrite table io_test_acid_part partition (c=1) select a, b where c=1111
insert overwrite table io_test_acid_part partition (c=2) select a, b where c=2222
insert overwrite table io_test_acid_part partition (c=3) select a, b where c=4444
;

select * from io_test_acid_part order by a;

from io_test_text_1
insert overwrite table io_test_acid_part partition (c=1) select a, b where c is null
insert overwrite table io_test_acid_part partition (c=2) select a, b where c=7777
insert overwrite table io_test_acid_part partition (c=3) select a, b where c is not null
;

select * from io_test_acid_part order by a;

from io_test_text_1
insert overwrite table io_test_acid_part partition (c=1) select a, b where c=8888
insert overwrite table io_test_acid_part partition (c=2) select a, b where c=7777
insert overwrite table io_test_acid_part partition (c=3) select a, b where c=9999
;

select * from io_test_acid_part order by a;

drop table io_test_acid_part;


-- dynamic partitioning

create table io_test_acid_part (a int, b int) partitioned by (c int) stored as orc tblproperties('transactional'='true');

insert overwrite table io_test_acid_part partition (c) select a, b, c from io_test_text_1 where c is not null;

select * from io_test_acid_part order by a;

insert overwrite table io_test_acid_part partition (c) select a, b, c from io_test_text_1 where c is null;

select * from io_test_acid_part order by a;

insert overwrite table io_test_acid_part partition (c) select a, b, c from io_test_text_2 where b=11 or b=44 or b=99;

select * from io_test_acid_part order by a, c;

insert overwrite table io_test_acid_part partition (c) select a, b, c from io_test_text_2 where b=99;

select * from io_test_acid_part order by a, c;

drop table io_test_acid_part;

create table io_test_acid_part (a int, b int) partitioned by (c int) stored as orc tblproperties('transactional'='true');

from io_test_text_1
insert overwrite table io_test_acid_part partition (c) select a, b, c where c is not null
insert overwrite table io_test_acid_part partition (c) select a, b, c where c is null
;

select * from io_test_acid_part order by a;

from io_test_text_1
insert overwrite table io_test_acid_part partition (c) select a, b, c where b=11
insert overwrite table io_test_acid_part partition (c) select a, b, c where b=99
insert overwrite table io_test_acid_part partition (c) select a, b, c where b=44
;

select * from io_test_acid_part order by a;

from io_test_text_2
insert overwrite table io_test_acid_part partition (c) select a, b, c where b=7
insert overwrite table io_test_acid_part partition (c) select a, b, c where b=44
insert overwrite table io_test_acid_part partition (c) select a, b, c where b=9
;

select * from io_test_acid_part order by a, c;

drop table io_test_acid_part;

create table io_test_acid_part (a int, b int) partitioned by (c int) stored as orc tblproperties('transactional'='true');

from io_test_text_2
insert overwrite table io_test_acid_part partition (c) select a, b, c where c is not null
insert overwrite table io_test_acid_part partition (c) select a, b, c where c is null
;

select * from io_test_acid_part order by a;

drop table io_test_acid_part;

create table io_test_acid_part (a int, b int) partitioned by (c int) stored as orc tblproperties('transactional'='true');

from io_test_text_2
insert overwrite table io_test_acid_part partition (c) select a, b, c where b=11
insert overwrite table io_test_acid_part partition (c) select a, b, c where b=99
insert overwrite table io_test_acid_part partition (c) select a, b, c where b=33
insert overwrite table io_test_acid_part partition (c) select a, b, c where b=88
;

select * from io_test_acid_part order by a;

drop table io_test_acid_part;

set hive.acid.direct.insert.enabled=true;


create external table io_test_text_3 (a int, b int, pc1 int, pc2 int, pc3 int) stored as textfile;

insert into io_test_text_3 values (11,11,11,11,11),
(12,12,11,11,11),
(13,13,11,11,22),
(14,15,11,11,NULL),
(16,16,11,22,11),
(17,17,11,22,22),
(18,18,11,22,NULL),
(19,19,11,22,NULL),
(20,20,22,11,11),
(21,21,22,11,11),
(22,22,22,22,11),
(23,23,22,NUll,11),
(24,24,22,NUll,22),
(25,25,22,NULL,NULL),
(26,26,NULL,11,11),
(27,27,NULL,22,11),
(28,28,NULL,NULL,11),
(29,29,NULL,NULL,22),
(30,30,NULL,NULL,NULL);

create table io_test_acid_part (a int, b int) partitioned by (pc1 int, pc2 int, pc3 int) stored as orc  tblproperties('transactional'='true');

from io_test_text_3
insert overwrite table io_test_acid_part partition (pc1, pc2, pc3)
  select a, b, pc1, pc2, pc3
  where pc1 is not null and pc2 is not null and pc3 is not null
insert overwrite table io_test_acid_part partition (pc1, pc2, pc3)
  select a, b, pc1, pc2, pc3
  where pc2 is null and pc1 is not null
insert overwrite table io_test_acid_part partition (pc1, pc2, pc3)
  select a, b, pc1, pc2, pc3
  where pc3 is null and pc2 is not null and pc1 is not null
insert overwrite table io_test_acid_part partition (pc1, pc2, pc3)
  select a, b, pc1, pc2, pc3
  where pc1 is null
insert overwrite table io_test_acid_part partition (pc1, pc2, pc3)
  select a, b, pc1, pc2, pc3
  where a=111
;

select * from io_test_acid_part order by a;

drop table io_test_acid_part;
drop table io_test_text_1;
drop table io_test_text_2;
drop table io_test_text_3;