实时数仓Hologres：INSERT ON CONFLICT(UPSERT)

应用场景

INSERT ON CONFLICT命令适用于通过SQL方式导入数据的场景。

命令介绍

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 ]

技术原理

使用限制

• 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语句的示例用法：

  说明

  Hologres从V2.1.17版本起支持Serverless Computing能力，针对大数据量离线导入、大型ETL作业、外表大数据量查询等场景，使用Serverless Computing执行该类任务可以直接使用额外的Serverless资源，避免使用实例自身资源，无需为实例预留额外的计算资源，显著提升实例稳定性、减少OOM概率，且仅需为任务单独付费。Serverless Computing详情请参见Serverless Computing概述，Serverless Computing使用方法请参见Serverless Computing使用指南。

  1. 准备表和数据：

     begin ;
     create table test1 (
         a int NOT NULL PRIMARY KEY,
         b int,
         c int
     );
     commit ;
     
     insert into test1 values (1,2,3);
     

  2. 不同场景下的使用示例：

     说明

     下面的每个场景示例结果不相互依赖，没有顺序关系，都是基于上述已创建的表和数据的结果。

     • 场景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.*)

常见报错