Hologres中INSERTONCONFLICT語句的用法 - Hologres

本文為您介紹在Hologres中INSERT ON CONFLICT語句的用法。

應用情境

INSERT ON CONFLICT命令適用於通過SQL方式匯入資料的情境。

使用Data Integration或Flink寫入資料時，如果需要對主鍵重複的行資料執行更新或跳過操作，則需進行如下配置：

通過DataWorks的Data Integration匯入資料。
Data Integration已內建INSERT ON CONFLICT功能，該功能的實現原理請參見Hologres Writer。同時，您需要進行如下配置：
- 離線同步資料時，寫入衝突策略選擇忽略（Ignore）或者更新（Replace）。
- 即時同步資料時，寫入衝突策略選擇忽略（Ignore）或者更新（Replace）。
說明
同步資料時，Hologres的表均需要設定主鍵，才能更新資料。
通過Flink寫入資料。
通過Flink寫入資料預設寫入衝突策略使用InsertOrIgnore（保留首次出現的資料，忽略後續所有資料），但是需要您在Hologres建表時設定主鍵。如果使用ctas文法，則寫入衝突策略預設為InsertOrUpdate（替換部分已有資料）。

命令介紹

INSERT ON CONFLICT語句用於在指定列插入某行資料時，如果主鍵存在重複的行資料，則對該資料執行更新或跳過操作，實現UPSERT（INSERT OR UPDATE）的效果。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	InsertOrIgnore，即在指定列插入某行資料時，如果主鍵存在重複的行資料，則對該資料執行跳過操作。
DO UPDATE	InsertOrUpdate，即在指定列插入某行資料時，如果主鍵存在重複的行資料，則對該資料執行更新操作。存在如下情況：更新全部列：全部列的資料都更新，即整行更新。更新部分列：即局部列更新，缺失的列不更新。如果要實現InsertOrReplace的效果，同時缺失的列補null，需要手動在值內傳null，見下方使用樣本。重要當列中有預設（default）值時，DO UPDATE不更新有預設值的列，效能會比較低。通過SQL（INSERT ON CONFLICT）實現的InsertOrReplace，缺失的列補`null`，需要在`insert`的值內傳`null`；如果是使用Flink、Data Integration等方式，選擇InsertOrReplace會自動補`null`。
expression	對應列執行的相關運算式，您可以參考Postgres來設定運算式。常用運算式 `b=excluded.b`，或者`(a, b, c) = ROW (excluded.)`簡化表達，等式左側表示要被更新的欄位，等式右側表示插入的運算式，即values部分的值，或者select運算式，excluded是對插入運算式的別名，不是寫入源頭表的別名。例如，`column_name = excluded.column_name`，column_name為插入資料至目標表指定列的列名稱，假設column_name為目標表的第N列，則excluded.column_name為插入運算式的第N列，當使用excluded.時，表示選擇所有列，列的順序為插入運算式中列的順序，需要保證插入目標列的順序與被寫入表的DDL順序一致。

技術原理

INSERT ON CONFLICT的技術實現原理同UPDATE，詳情請參見UPDATE。不同表格儲存體格式（行存、列存、行列共存）在更新時的細節處理會略有不同，這就導致不同儲存模式的表在更新時，效能會有不同。而根據業務的需求，INSERT ON CONFLICT又可以分為InsertOrIgnore、InsertOrReplace、InsertOrUpdate，三者的具體區別如下：

更新模式	說明
InsertOrIgnore	寫入時忽略更新，結果表有主鍵，即時寫入時如果主鍵重複，丟棄後到的資料，通過`insert on conflict do nothing`實現。
InsertOrUpdate	寫入更新，結果表有主鍵，即時寫入時如果主鍵重複，按照主鍵更新。分為整行更新和部分列更新，部分列更新指如果寫入的一行資料不包含所有列，缺失的列不更新。通過`insert on conflict do update`實現。
InsertOrReplace	寫入覆蓋，結果表有主鍵，即時寫入時如果主鍵重複，按照主鍵更新。如果寫入的一行資料不包含所有列，缺失的列的資料補Null，需要通過`insert on conflict do update`和手動補Null值一起實現。

根據UPDATE的原理，當表設定不同的儲存格式時，不同UPDATE模式下的更新效能如下：

列存表不同寫入模式的效能排序如下。
- 結果表無主鍵效能最高。
- 結果表有主鍵時：InsertOrIgnore > InsertOrReplace >= InsertOrUpdate（整行）> InsertOrUpdate（部分列）。
行存表不同寫入模式的效能排序如下。
InsertOrReplcae = InsertOrUpdate（整行）>= InsertOrUpdate（部分列） >= InsertOrIgnore。

使用限制

INSERT ON CONFLICT語句的條件必須包含所有主鍵。
Hologres HQE在執行INSERT ON CONFLICT時，本身不會保序（保證順序），因此不能實現keep first、keep last的效果，都是keep any。但在實際應用中，如果資料來源有主鍵重複資料需要去重，建議使用keep last，命令如下：
```
--保留重複資料的最後一條資料
set hg_experimental_affect_row_multiple_times_keep_last = on;
```

使用樣本

INSERT ON CONFLICT語句的樣本用法：

準備表和資料：

begin ;
create table test1 (
    a int NOT NULL PRIMARY KEY,
    b int,
    c int
);
commit ;

insert into test1 values (1,2,3);

不同情境下的使用樣本：

說明

下面的每個情境樣本結果不相互依賴，沒有循序關聯性，都是基於上述已建立的表和資料的結果。

情境1：實現InsertOrIgnore，即主鍵重複不更新。

INSERT INTO test1 (a, b, c) VALUES (1, 1, 1)
ON CONFLICT (a)
DO NOTHING;

--更新後test1表的資料為：
a	b	c
1	2	3

情境2：實現InsertOrUpdate的整行更新，可以通過如下兩種方式實現。

方式1：在SET..EXCLUDED中列出所有的列。

INSERT INTO test1 (a, b, c) VALUES (1, 1, 1)
ON CONFLICT (a)
DO UPDATE SET b = EXCLUDED.b, c = EXCLUDED.c;

--更新後test1表的資料為：
a	b	c
1	1	1

方式2：使用ROW(EXCLUDED.*)代表更新所有列。

INSERT INTO test1 (a, b, c)VALUES (1, 1, 1)
ON CONFLICT (a)
DO UPDATE SET (a,b,c) = ROW(EXCLUDED.*);

--更新後test1表的資料為：
a	b	c
1	1	1

情境3：實現InsertOrUpdate的部分列更新，即只更新指定列，缺失的列不更新。

--要實現部分列更新的效果，需要在set後列出想要更新的列
INSERT INTO test1 (a, b, c) VALUES (1, 1, 1)
ON CONFLICT (a)
DO UPDATE SET b = EXCLUDED.b;

--表中c列不更新，更新後test1表的資料為:
a	b	c
1	1	3

情境4：實現InsertOrReplace，即整行覆蓋，如果有缺失的列，缺失的列補null。

--如果要實現InsertOrReplace，且缺失的列補null，則需要在insert的值中手動補null。
INSERT INTO test1 (a, b,c) VALUES (1, 1,null)
ON CONFLICT (a)
DO UPDATE SET b = EXCLUDED.b,c = EXCLUDED.c;

--更新後test1表的資料為：
a	b	c
1	1	\N

情境5：從另外一張test2表更新test1表資料。

--準備test2表和資料
CREATE TABLE test2 (
    d int NOT NULL PRIMARY KEY,
    e int,
    f int
);
INSERT INTO test2 VALUES (1, 5, 6);


--將test2整表替換test1表相同主鍵的行
INSERT INTO test1 (a, b, c) SELECT d,e,f FROM test2 ON CONFLICT (a) DO UPDATE SET
(a,b,c) = ROW (excluded.*);

--更新後test1表資料如下：
a	b	c
1	5	6

--將test2整表替換test1表相同主鍵的行,但調整了更新映射關係，即test2的e列更新到c列，f列更新到b列
INSERT INTO test1 (a, b, c) SELECT d,e,f FROM test2 ON CONFLICT (a) DO UPDATE SET
(a,c,b) = ROW (excluded.*);
--更新後test1表資料如下：       
a	b	c
1	6	5

行存表INSERT ON CONFLICT語句的最佳化：

Hologres對行存表的更新情境實行了最佳化，建議您在使用時將UPDATE列的順序與INSERT的順序保持一致，並且更新為整行更新。

INSERT INTO test1 (a, b, c) 
SELECT
    d,e,f
FROM
    test2
ON CONFLICT (a)
    DO UPDATE SET
        a = excluded.a,
        b = excluded.b,
        c = excluded.c;
        
INSERT INTO test1 (a, b, c)
SELECT d,e,f FROM test2
ON CONFLICT (a)
DO UPDATE SET(a,b,c) = ROW (excluded.*)

常見報錯

問題現象
對資料來源執行INSERT ON CONFLICT語句時出現如下兩種報錯其中一個。
- 報錯一：duplicate key value violates unique constraint。
- 報錯二：Update row with Key (xxx)=(yyy) multiple times。
問題原因一：資料來源存在重複資料。
Hologres相容PostgreSQL，使用的也是標準PostgreSQL文法。在標準的PostgreSQL語義中，對資料來源執行INSERT ON CONFLICT語句時，資料來源不能包含重複資料，如果包含重複資料則會產生上述報錯。
說明
資料來源重複是指待插入的資料中包含重複資料，不是指待插入的資料與表裡的資料重複。
使用INSERT ON CONFLICT語句插入資料時包含重複資料，樣本語句如下。
```
INSERT INTO test1 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_last = on;
```
問題原因二：資料來源因TTL到期出現重複資料。
資料來源中有表設定過表資料生命週期（TTL），表中有部分資料已經過了TTL，因TTL不是準確的時間，導致到期的資料未被清理，匯入時主鍵（PK）資料重複，從而出現報錯。
解決方案：
Hologres從 V1.3.23版本開始，通過以下命令能快速修正因TTL到期PK重複的資料。執行該命令後，系統會將該表PK重複的資料清理掉，清理策略預設為Keep Last即保留重複PK中最後一條寫入的PK資料，其餘重複PK資料進行清理。
說明
- 原則上來說PK不會出現重複資料，因此該命令僅清理因TTL導致PK重複的資料。
- 該命令僅Hologres V1.3.23及以上版本使用，若執行個體版本較低，請升級執行個體。
```
call public.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 public.hg_remove_duplicated_pk('tbl_2');

--再重新匯入tbl_1資料，資料匯入成功
```