tencent cloud

腾讯云数据仓库 TCHouse-P

产品动态
产品简介
产品概述
应用场景
节点规格
集群架构
购买指南
计费概述
购买方式
欠费说明
快速入门
操作指南
管理集群
访问数据仓库
监控告警
访问管理
性能指标
TPC-B
工具及下载
数据接入
使用 DataX 离线导入 TencentDB 数据
DataX 增量同步导入 MySQL 数据
使用外表高速导入或导出 COS 数据
使用外表同步 EMR 数据
使用 rule 规则实现云数据仓库 PostgreSQL upsert 操作
数仓开发
云上搭建 Airflow
API 文档
History
Introduction
API Category
Making API Requests
Information Query APIs
Instance APIs
Query APIs
Cluster Management APIs
Cluster Operation APIs
Data Types
Error Codes
实践教程
数仓表开发
表分布键选择
表存储格式选择
表分区使用
插件使用
冷备数据
统计信息和空间维护
常见问题
联系我们
文档腾讯云数据仓库 TCHouse-P数据接入使用 rule 规则实现云数据仓库 PostgreSQL upsert 操作

使用 rule 规则实现云数据仓库 PostgreSQL upsert 操作

PDF
聚焦模式
字号
最后更新时间： 2024-11-27 15:36:05

背景说明

腾讯云数据仓库 TCHouse-P 底层是基于 greenplum6 来构建，postgresql 内核为9.4版本，目前并不能很好支持 postgresql 的 insert .. on conflict 特性，所以对于 upsert 场景需要采用额外的方式来进行处理，这里提供一种利用 postgresql rule 特性来进行 upsert 的方法。

规则介绍

PostgreSQL 规则系统允许在更新、插入、删除时执行一个其它的预定义动作。简单的说，规则就是在指定表上执行指定动作的时候，将导致一些额外的动作被执行。另外，一个INSTEAD规则可以用另外一个命令取代特定的命令，或者完全不执行该命令。规则还可以用于实现表视图。规则实际上只是一个命令转换机制，或者说命令宏。这种转换发生在命令开始执行之前。
详细信息可参考 rule 使用手册。

upsert rule

如果需要实现 upsert 的操作，那么需要这样一条规则：当进行 insert 操作时，判断是否已经有相应的记录，如果存在记录则改为进行 update 操作，如果不存在记录则进行正常 insert 操作。
下面以一个数据库实例来进行说明：
创建一个测试数据库。
CREATE TABLE my_test(
   id integer,
   num1 integer,
   num2 decimal,
   str1 varchar(20),
   str2 text,
   PRIMARY KEY(id)
) distributed by (id);
然后给表增加 rule 规则。
create rule r1 as on insert to my_test where exists (select 1 from e t1 where t1.id=NEW.id limit 1) do instead update my_test set num1=NEW.num1,num2=NEW.num2,str1=NEW.str1,str2=NEW.str2 where id=NEW.id;
这条 rule 命令的含义就是针对 insert 操作，如果新的 insert 语句的 id 是存在，那么就直接用新 insert 里面的值 update 原来的数据，语句中的 NEW.XXX，即新 insert 语句的值，操作完成后可以看到。数据表中存在 rule 规则，接着进行 insert 操作，如果 id 存在，那么不会因为主键约束报错，而是进行 update 操作。
\\d my_test
                                   Table "public.my_test"
 Column |         Type          | Collation | Nullable |               Default               
--------+-----------------------+-----------+----------+-------------------------------------
 id     | integer               |           | not null | nextval('my_test_id_seq'::regclass)
 num1   | integer               |           |          | 
 num2   | numeric               |           |          | 
 str1   | character varying(20) |           |          | 
 str2   | text                  |           |          | 
Indexes:
    "my_test_pkey" PRIMARY KEY, btree (id)
Rules:
    r1 AS
    ON INSERT TO my_test
   WHERE (EXISTS ( SELECT 1
     FROM my_test my_test_1
     WHERE my_test_1.id = new.id
     LIMIT 1)) DO INSTEAD  UPDATE my_test SET num1 = new.num1, num2 = new.num2, str1 = new.str1, str2 = new.str2

使用注意

rule 规则使用存在一定局限，如下所示：
1. 因为 exists 对于 insert 批量插入处理不完善，如果语句没有设置唯一约束或者主键约束，可能在 insert 批量插入时产生重复数据，应尽量避免使用批量 insert，使用时也要避免需要判断 upsert 的字段不重复，或者对需要判断的字段增加唯一约束。例如下面这种批量操作，如果 id 没有主键约束，那么可能执行后存在重复数据。
insert into my_test (id,num1,num2,str1,str2)values(1,2,1.0,'111','555'),(1,3,2.0,'111','666');
2. rule 不支持 COPY 语句，COPY 语句效果和 insert 批处理类似，都可能导致重复数据。
3. 在设置 update 规则时，如果设置了 rule 用法，但是 insert 语句没有传 num1 和 num2 字段，这两个字段更新后会为空值，导致原数据丢失。
update my_test set num1=NEW.num1,num2=NEW.num2,str1=NEW.str1,str2=NEW.str2

上一篇: 使用外表同步 EMR 数据下一篇: 云上搭建 Airflow

帮助和支持

本页内容是否解决了您的问题？

填写满意度调查问卷，共创更好文档体验。

文档反馈