where not exists 避免重复插入SQL语句

项目场景：

避免重复插入SQL语句：

insert into TABLE2
select *
from TABLE1 
where not exists (select 1 from TABLE2 where TABLE2.id=TABLE1.id and TABLE2.NAME = TABLE1.NAME)

---

问题描述：

SQL数据写入经常遇到重复插入数据导致数据样本变大，对测试和磁盘造成压力，浪费资源，故需要修改写入语句

1. 原语句：

0. 建表
create table IF NOT EXISTS dbgen_version 
(
    dv_version                varchar(10),
    dv_create_date            date,
    dv_create_time            varchar(10),
    dv_cmdline_args           varchar(10)              
) WITH (format = 'ORC')
1. 清空表（如果有数据）
trino:hivetest> delete from hive.hivetest.dbgen_version;
DELETE

Query 20210225_072035_00059_zvank, FINISHED, 1 node
Splits: 1 total, 1 done (100.00%)
0.24 [0 rows, 0B] [0 rows/s, 0B/s]

 查看语句：
trino:hivetest> select * from hive.hivetest.dbgen_version;       
 dv_version | dv_create_date | dv_create_time | dv_cmdline_args 
------------+----------------+----------------+-----------------
(0 rows)

Query 20210225_072059_00060_zvank, FINISHED, 1 node
Splits: 17 total, 17 done (100.00%)
0.21 [0 rows, 0B] [0 rows/s, 0B/s]

2. 插入语句

trino:hivetest> INSERT INTO hive.hivetest.dbgen_version select * from hive.hive.dbgen_version;
INSERT: 1 row

Query 20210225_072113_00061_zvank, FINISHED, 3 nodes
Splits: 69 total, 69 done (100.00%)
0.53 [1 rows, 67B] [1 rows/s, 127B/s]

查看语句
trino:hivetest> select * from hive.hivetest.dbgen_version;                                        
 dv_version | dv_create_date | dv_create_time | dv_cmdline_args 
------------+----------------+----------------+-----------------
 2.0.0      | 2021-02-02     | 18:32:49       | --scale 10      
(1 row)

Query 20210225_072152_00062_zvank, FINISHED, 2 nodes
Splits: 17 total, 17 done (100.00%)
0.21 [1 rows, 0B] [4 rows/s, 0B/s]

3. 插入 where not exists限定语句

trino:hivetest> insert into hive.hivetest.dbgen_version
             -> select *
             -> from hive.hive.dbgen_version TABLE1 
             -> where not exists (select 1 from hive.hivetest.dbgen_version TABLE2 where TABLE2.dv_version=TABLE1.dv_version and TABLE2.dv_create_date = TABLE1.dv_create_date);
INSERT: 0 rows

查看语句
Query 20210225_072210_00063_zvank, FINISHED, 3 nodes
Splits: 198 total, 198 done (100.00%)
0.44 [3 rows, 176B] [6 rows/s, 400B/s]

trino:hivetest> select * from hive.hivetest.dbgen_version;
 dv_version | dv_create_date | dv_create_time | dv_cmdline_args 
------------+----------------+----------------+-----------------
 2.0.0      | 2021-02-02     | 18:32:49       | --scale 10      
(1 row)

查看语句
Query 20210225_072216_00064_zvank, FINISHED, 1 node
Splits: 17 total, 17 done (100.00%)
0.21 [1 rows, 0B] [4 rows/s, 0B/s]

4. 插入一般语句

trino:hivetest> INSERT INTO hive.hivetest.dbgen_version select * from hive.hive.dbgen_version; 
INSERT: 1 row

查看语句
Query 20210225_072237_00065_zvank, FINISHED, 3 nodes
Splits: 69 total, 69 done (100.00%)
0.60 [1 rows, 67B] [1 rows/s, 111B/s]

重复插入！！！

trino:hivetest> select * from hive.hivetest.dbgen_version;                     
            
 dv_version | dv_create_date | dv_create_time | dv_cmdline_args 
------------+----------------+----------------+-----------------
 2.0.0      | 2021-02-02     | 18:32:49       | --scale 10      
 2.0.0      | 2021-02-02     | 18:32:49       | --scale 10      
(2 rows)

Query 20210225_072245_00066_zvank, FINISHED, 3 nodes
Splits: 18 total, 18 done (100.00%)
0.21 [2 rows, 0B] [9 rows/s, 0B/s]  

---

原因分析：

where not exists (select 1 from TABLE2 where TABLE2.id=TABLE1.id and TABLE2.NAME = TABLE1.NAME)限定了两个条件相等情况下不予插入。如果仅限定一个条件，对于有重复数据的情况可能会写入数据不成功，需要检查确认。

---