====
---- QUERY
create table insert_overwrite_nopart like $ORIGINAL_DB.insert_overwrite_nopart;
create table insert_overwrite_partitioned like $ORIGINAL_DB.insert_overwrite_partitioned;
====
---- QUERY
# insert overwrite into unpartitioned table
insert overwrite table insert_overwrite_nopart
select int_col
from functional.tinyinttable
---- RUNTIME_PROFILE
NumModifiedRows: 10
====
---- QUERY
# Check results - note larger limit than expected in case there's more data written than there should be
select col1
from insert_overwrite_nopart
order by col1
limit 20
---- TYPES
int
---- RESULTS
0
1
2
3
4
5
6
7
8
9
====
---- QUERY
# Now do an overwrite that should delete what was just written
insert overwrite table insert_overwrite_nopart
select 10
from functional.tinyinttable
---- RUNTIME_PROFILE
NumModifiedRows: 10
====
---- QUERY
# check results from previous insert
select col1
from insert_overwrite_nopart
order by col1
limit 20
---- TYPES
int
---- RESULTS
10
10
10
10
10
10
10
10
10
10
====
---- QUERY
insert overwrite table insert_overwrite_nopart
select 3
from functional.tinyinttable
limit 0
---- RUNTIME_PROFILE
NumModifiedRows: 0
====
---- QUERY
select count(*) from insert_overwrite_nopart
---- TYPES
bigint
---- RESULTS
0
====
---- QUERY
# Static partitioned insert
insert overwrite table insert_overwrite_partitioned
PARTITION(col2=5)
select int_col
from functional.tinyinttable
---- RUNTIME_PROFILE
Partition: col2=5
NumModifiedRows: 10
====
---- QUERY
# Check results of previous insert
select col1, col2 from insert_overwrite_partitioned
order by col1
limit 20
---- TYPES
int,int
---- RESULTS
0,5
1,5
2,5
3,5
4,5
5,5
6,5
7,5
8,5
9,5
====
---- QUERY
# Insert into another partition, to check that original partition stays intact
insert overwrite table insert_overwrite_partitioned
PARTITION(col2=6)
select int_col
from functional.tinyinttable
---- RUNTIME_PROFILE
Partition: col2=6
NumModifiedRows: 10
====
---- QUERY
# Check results of previous insert
select col1, col2 from
insert_overwrite_partitioned
order by col2, col1
limit 30
---- TYPES
int,int
---- RESULTS
0,5
1,5
2,5
3,5
4,5
5,5
6,5
7,5
8,5
9,5
0,6
1,6
2,6
3,6
4,6
5,6
6,6
7,6
8,6
9,6
====
---- QUERY
# Overwrite one partition, check that the other partition remains intact
insert overwrite table insert_overwrite_partitioned
partition(col2=5)
select 10 from functional.tinyinttable
---- RUNTIME_PROFILE
Partition: col2=5
NumModifiedRows: 10
====
---- QUERY
# Confirm that one partition is still intact
select col1, col2 from insert_overwrite_partitioned
order by col2, col1 limit 30
---- TYPES
int,int
---- RESULTS
10,5
10,5
10,5
10,5
10,5
10,5
10,5
10,5
10,5
10,5
0,6
1,6
2,6
3,6
4,6
5,6
6,6
7,6
8,6
9,6
====
---- QUERY
# Dynamic partitions
insert overwrite table insert_overwrite_partitioned
partition(col2)
select int_col, int_col
from functional.tinyinttable
---- RUNTIME_PROFILE
Partition: col2=0
Partition: col2=1
Partition: col2=2
Partition: col2=3
Partition: col2=4
Partition: col2=5
Partition: col2=6
Partition: col2=7
Partition: col2=8
Partition: col2=9
NumModifiedRows: 1
aggregation(SUM, RowsInserted): 10
====
---- QUERY
# Confirm results of previous insert
select col1, col2
from insert_overwrite_partitioned
order by col2
limit 30
---- TYPES
int,int
---- RESULTS
0,0
1,1
2,2
3,3
4,4
5,5
6,6
7,7
8,8
9,9
====
---- QUERY
# Overwrite dynamic partition. Limit to 1 row without actually using limit, which forces non-parallel insert
insert overwrite table insert_overwrite_partitioned
partition(col2)
select 10, 0 from functional.tinyinttable
where int_col = 0
---- RUNTIME_PROFILE
Partition: col2=0
NumModifiedRows: 1
====
---- QUERY
select col1, col2
from insert_overwrite_partitioned
order by col2
limit 30
---- TYPES
int,int
---- RESULTS
10,0
1,1
2,2
3,3
4,4
5,5
6,6
7,7
8,8
9,9
====