本文为您介绍在Hologres中INSERT ON CONFLICT语句的用法。
应用场景
INSERT ON CONFLICT
命令适用于通过SQL方式导入数据的场景。
- 通过DataWorks的数据集成导入数据。数据集成已内置
INSERT ON CONFLICT
功能,该功能的实现原理请参见Hologres Writer。同时,您需要进行如下配置:- 离线同步数据时,写入冲突策略选择忽略(Ignore)或者更新(Replace)。
- 实时同步数据时,写入冲突策略选择忽略(Ignore)或者更新(Replace)。
说明 同步数据时,Hologres的表均需要设置主键,才能更新数据。 - 通过Flink写入数据。
通过Flink写入数据默认写入冲突策略使用
InsertOrIgnore
(保留首次出现的数据,忽略后续所有数据),但是需要您在Hologres建表时设置主键。详情请参见Flink全托管。如果使用ctas
语法,则写入冲突策略默认为InsertOrUpdate
(替换部分已有数据)。
命令介绍
INSERT ON CONFLICT
的语法格式如下。INSERT INTO <table_name> [ AS <alias> ] [ ( <column_name> [, ...] ) ]
{ VALUES ( { <expression> } [, ...] ) [, ...] | <query> }
[ ON CONFLICT [ conflict_target ] conflict_action ]
where conflict_target is pk
ON CONSTRAINT constraint_name
and conflict_action is one of:
DO NOTHING
DO UPDATE SET { <column_name> = { <expression> } |
( <column_name> [, ...] ) = ( { <expression> } [, ...] ) |
} [, ...]
[ WHERE condition ]
参数说明如下表所示。参数 | 描述 |
---|---|
table_name | 插入数据的目标表名称。 |
alias | 别名。目标表的替代名称。 |
column_name | 目标表中目标列名称。 |
DO NOTHING | 在指定列插入某行数据时,如果主键存在重复的行数据,则对该数据执行跳过操作。 |
DO UPDATE | 在指定列插入某行数据时,如果主键存在重复的行数据,则对该数据执行更新操作。 |
expression | 对应列执行的相关表达式,您可以参考Postgres来设置表达式,详情请参见INSERT ON CONFLICT。 常用表达式 |
技术原理
InsertOrIgnore
、InsertOrReplace
、InsertOrUpdate
,三者的具体区别如下:更新模式 | 说明 |
---|---|
InsertOrIgnore | 写入时忽略更新,结果表有主键,实时写入时如果主键重复,丢弃后到的数据。 |
InsertOrReplace | 写入覆盖,结果表有主键,实时写入时如果主键重复,按照主键更新。如果写入的一行数据不包含所有列,缺失的列的数据补Null。 |
InsertOrUpdate | 写入更新,结果表有主键,实时写入时如果主键重复,按照主键更新。分为整行更新和部分列更新,部分列更新指如果写入的一行数据不包含所有列,缺失的列不更新。 |
- 列存表不同写入模式的性能排序如下。
- 结果表无主键性能最高。
- 结果表有主键时:
InsertOrIgnore > InsertOrReplace >= InsertOrUpdate(整行)> InsertOrUpdate(部分列)
。
- 行存表不同写入模式的性能排序如下。
InsertOrReplcae = InsertOrUpdate(整行)>= InsertOrUpdate(部分列) >= InsertOrIgnore
。
使用限制
INSERT ON CONFLICT
语句的条件必须包含所有主键。
使用示例
INSERT ON CONFLICT
语句的示例用法:BEGIN; CREATE TABLE tbl_1 ( a int NOT NULL PRIMARY KEY, b int, c int ); CREATE TABLE tbl_2 ( d int NOT NULL PRIMARY KEY, e int, f int ); COMMIT; INSERT INTO tbl_1 VALUES (1, 1, 1), (2, 3, 4); --此时tbl_1数据如下 a b c 2 3 4 1 1 1 INSERT INTO tbl_2 VALUES (1, 5, 6); --此时tbl_2数据如下 d e f 1 5 6 --主键相同时,将表tbl_2的某列数据更新到表tbl_1中。 INSERT INTO tbl_1 (a, b) SELECT d,e FROM tbl_2 ON CONFLICT (a) DO UPDATE SET b = excluded.b; --此时tbl_1数据如下 a b c 2 3 4 1 5 1 --主键相同时,将表tbl_2的某一行数据全部插入至表tbl_1中。 INSERT INTO tbl_1 VALUES (2, 7, 8) ON CONFLICT (a) DO UPDATE SET b = excluded.b, c = excluded.c WHERE tbl_1.c = 4; --此时tbl_1数据如下 a b c 2 7 8 1 5 1 --主键相同时,向表tbl_1插入表tbl_2的数据,系统直接跳过表tbl_2的数据(即插入数据失败)。 INSERT INTO tbl_1 SELECT * FROM tbl_2 ON CONFLICT (a) DO NOTHING; --此时tbl_1数据如下 a b c 2 7 8 1 5 1 --do nothing不指定冲突列时,默认冲突列为主键。 INSERT INTO tbl_1 SELECT * FROM tbl_2 ON CONFLICT DO NOTHING; --此时tbl_1数据如下 a b c 2 7 8 1 5 1 ---更新整行数据。 INSERT INTO tbl_1 VALUES (1, 2, 3) ON CONFLICT (a) DO UPDATE SET (a, b, c) = ROW (excluded.*); --此时tbl_1数据如下 a b c 2 7 8 1 2 3 --将tbl_2整表替换tbl1表相同主键的行 INSERT INTO tbl_1 (a, b, c) SELECT d,e,f FROM tbl_2 ON CONFLICT (a) DO UPDATE SET (a,b,c) = ROW (excluded.*) --此时tbl_1数据如下 a b c 2 7 8 1 5 6 --将tbl_2整表替换tbl1表相同主键的行,但调整了更新映射关系,即tbl_2的e列更新到c,f列更新到b INSERT INTO tbl_1 (a, b, c) SELECT d,e,f FROM tbl_2 ON CONFLICT (a) DO UPDATE SET (a,c,b) = ROW (excluded.*) --此时tbl_1数据如下 a b c 2 7 8 1 6 5
- 行存表
INSERT ON CONFLICT
语句的优化:Hologres对行存表的更新场景实行了优化,建议您在使用时将UPDATE列的顺序与INSERT的顺序保持一致,并且更新为整行更新。INSERT INTO tbl_1 (a, b, c) SELECT d,e,f FROM tbl_2 ON CONFLICT (a) DO UPDATE SET a = excluded.a, b = excluded.b, c = excluded.c; INSERT INTO tbl_1 (a, b, c) SELECT d,e,f FROM tbl_2 ON CONFLICT (a) DO UPDATE SET (a,b,c) = ROW (excluded.*)
常见报错
- 问题现象对数据源执行
INSERT ON CONFLICT
语句时出现如下两种报错其中一个。- 报错一:
Update row with Key ()=() multiple times.
- 报错二:
ERROR:duplicate key value violates unique constraint
- 报错一:
- 问题原因一:数据源存在重复数据。Hologres兼容PostgreSQL,使用的也是标准PostgreSQL语法。在标准的PostgreSQL语义中,对数据源执行解决方法:
INSERT ON CONFLICT
语句时,数据源不能包含重复数据,如果包含重复数据则会产生上述报错。使用说明 数据源重复是指待插入的数据中包含重复数据,不是指待插入的数据与表里的数据重复。INSERT ON CONFLICT
语句插入数据时包含重复数据,示例语句如下。INSERT INTO tbl_1 VALUES (1, 2, 3), (1, 2, 3) ON CONFLICT (a) DO UPDATE SET (a, b, c) = ROW (excluded.*);
如果数据源包含重复数据,可以配置如下参数,分别选择保留重复数据的第一条数据和最后一条数据。- 保留重复数据的第一条数据
set hg_experimental_affect_row_multiple_times_keep_first = on;
- 保留重复数据的最后一条数据
set hg_experimental_affect_row_multiple_times_keep_last = on;
- 保留重复数据的第一条数据
- 问题原因二:数据源因TTL过期出现重复数据。
数据源中有表设置过表数据生命周期(TTL),表中有部分数据已经过了TTL,因TTL不是准确的时间,导致过期的数据未被清理,导入时主键数据重复,从而出现报错。
解决方法:Hologres从 V1.3.23版本开始,通过以下命令能快速修正因TTL过期PK重复的数据。执行该命令后,系统会将该表PK重复的数据清理掉,清理策略默认为Keep Last即保留重复PK中最后一条写入的PK数据,其余重复PK数据进行清理。
使用示例:假设有两个表,call hg_remove_duplicated_pk('<schema>.<table_name>');
tbl_1
为目标表,tbl_2
为源表且配置了TTL,时间设置为300s
。将tbl_2
的数据整行更新至tbl_1
,因TTL过期后,tbl_2
的主键重复,导致报错。BEGIN; CREATE TABLE tbl_1 ( a int NOT NULL PRIMARY KEY, b int, c int ); CREATE TABLE tbl_2 ( d int NOT NULL PRIMARY KEY, e int, f int ); CALL set_table_property('tbl_2', 'time_to_live_in_seconds', '300'); COMMIT; INSERT INTO tbl_1 VALUES (1, 1, 1), (2, 3, 4); INSERT INTO tbl_2 VALUES (1, 5, 6); --过300s后再向tbl_2插入数据 INSERT INTO tbl_2 VALUES (1, 3, 6); --将tbl_2整表替换tbl_1表相同主键的行,pk因ttl重复了导致更新报错 INSERT INTO tbl_1 (a, b, c) SELECT d,e,f FROM tbl_2 ON CONFLICT (a) DO UPDATE SET (a,b,c) = ROW (excluded.*); --执行报错:ERROR: internal error: Duplicate keys detected when building hash table. --guc清理tbl_2的PK重复数据,策略为keep last, call hg_remove_duplicated_pk('tbl_2'); --再重新导入tbl_1数据,数据导入成功 INSERT INTO tbl_1 (a, b, c) SELECT d,e,f FROM tbl_2 ON CONFLICT (a) DO UPDATE SET (a,b,c) = ROW (excluded.*);