数据入库实时转换 - trigger , rule

2 minute read

背景

PostgreSQL是一个功能很强大的数据库,仅仅在数据类型、索引接口的支持方面,就远超各种开源和商业数据库。

有些用户从其他数据源迁移到PostgreSQL,或者有其他数据源来的数据,大多数是字符串、数字,到了PostgreSQL可以转换为更精准的描述(数据类型),提升性能或功能。

例如

1、经纬度,在其他数据库中可能存成两个字段,分别表示经度和纬度。

PostgreSQL支持geometry类型,支持点、线、面、栅格、TOP等GIS类型。

2、数据范围,在其他数据库中可能存成两个字段,分别表示下限和上限。

PostgreSQL通过range支持数值、时间、IP地址等数据类型的范围。

3、IP地址,在其他数据库中可能存为字符串。

PostgreSQL支持network类型。

4、JSON,在其他数据库中可能存成字符串。

PostgreSQL支持JSON类型。

5、图像特征值、线段、多边形、圆形、UUID、XML、数组等,在其他数据库中可能存成字符串。

PostgreSQL支持以上类型。

6、全文检索,在其他数据库中可能需要借助搜索引擎支持。

PostgreSQL支持全文检索类型。

7、枚举类型,在其他数据库中可能存成字符串。

PostgreSQL支持枚举类型。

8、用户数据为JSON格式,但是想在数据库中存储为结构化的数据。

PostgreSQL 本身支持JSON的数据类型,也就是说用户可以直接存JSON,也可以存格式化数据,用户甚至可以在导入时,自动将JSON转成结构化数据。

那么如何将数据从字符串平滑、实时的在入库阶段转换为PostgreSQL支持的类型呢?

PostgreSQL支持trigger和rule,使用任意方法,都可以做到数据的平滑转换。

rule 转换 例子1

数据来源是2个字段表示的经纬度,要求实时转换为geometry类型。

创建来源表结构

postgres=# create table nt(id int, c1 numeric, c2 numeric);    
CREATE TABLE    

创建目标表结构

postgres=# create table nt_geo (id int, geo geometry);    
CREATE TABLE    

对来源表创建规则或触发器,例如

postgres=# create rule r1 as on insert to nt do instead insert into nt_geo values (NEW.id, ST_MakePoint(NEW.c1,NEW.c2));    
CREATE RULE    

使用来源数据结构,将数据插入来源数据表

postgres=# insert into nt values (1,1,1);    
INSERT 0 1    

数据会自动写入目标表,来源表只是个转换入口,不会落数据

postgres=# select * from nt;    
 id | c1 | c2     
----+----+----    
(0 rows)    
    
postgres=# select * from nt_geo ;    
 id |                    geo                         
----+--------------------------------------------    
  1 | 0101000000000000000000F03F000000000000F03F    
(1 row)    

rule 转换 例子2

将JSON转为结构化数据:

源表,JSONB非结构化

postgres=# create table t1 (id int, info text, j jsonb);  
CREATE TABLE  

目标表,结构化

postgres=# create table t2 (id int, info text, c1 int, c2 int, c3 text);  
CREATE TABLE  

在源表创建规则,自动将JSONB非结构化数据,转换为结构化数据插入

postgres=# create rule r1 as on insert to t1 do instead insert into t2 values (NEW.ID, NEW.INFO, ((NEW.J)->>'c1')::int, ((NEW.j)->>'c2')::int, (NEW.j)->>'c3');  
CREATE RULE  

insert测试,成功完成转换

postgres=# insert into t1 values (1,'test',jsonb '{"c1":1, "c2":2, "c3":"text"}');  
INSERT 0 1  
postgres=# select * from t1;  
 id | info | j   
----+------+---  
(0 rows)  
  
postgres=# select * from t2;  
 id | info | c1 | c2 |  c3    
----+------+----+----+------  
  1 | test |  1 |  2 | text  
(1 row)  

copy 测试,因为copy接口不触发规则,所以规则转换对copy无效。

postgres=# copy (select 1,'test',jsonb '{"c1":1, "c2":2, "c3":"text"}') to '/tmp/test';  
COPY 1  
postgres=# copy t1 from '/tmp/test';  
COPY 1  
postgres=# select * from t1;  
 id | info |                j                   
----+------+----------------------------------  
  1 | test | {"c1": 1, "c2": 2, "c3": "text"}  
(1 row)  
  
postgres=# select * from t2;  
 id | info | c1 | c2 |  c3    
----+------+----+----+------  
  1 | test |  1 |  2 | text  
(1 row)  

trigger 转换 例子3

删除规则

postgres=# drop rule r1 on t1;  
DROP RULE  

创建触发器函数

postgres=# create or replace function tg() returns trigger as $$  
postgres$# declare  
postgres$# begin  
postgres$#   insert into t2 values (NEW.ID, NEW.INFO, ((NEW.J)->>'c1')::int, ((NEW.j)->>'c2')::int, (NEW.j)->>'c3');  
postgres$#   return null;  
postgres$# end;  
postgres$# $$ language plpgsql strict;  
CREATE FUNCTION  

创建before触发器

postgres=# create trigger tg before insert on t1 for each row execute procedure tg();  
CREATE TRIGGER  

清除数据,重新插入,insert和copy都支持转换了。

postgres=# truncate t1;  
TRUNCATE TABLE  
postgres=# truncate t2;  
TRUNCATE TABLE  
postgres=# copy t1 from '/tmp/test';  
COPY 0  
postgres=# insert into t1 values (1,'test',jsonb '{"c1":1, "c2":2, "c3":"text"}');  
INSERT 0 0  
postgres=# select * from t2;  
 id | info | c1 | c2 |  c3    
----+------+----+----+------  
  1 | test |  1 |  2 | text  
  1 | test |  1 |  2 | text  
(2 rows)  
  
postgres=# select * from t1;  
 id | info | j   
----+------+---  
(0 rows)  

cool !!!

对于update, delete操作,方法类似,创建update, delete的rule即可。

小结

如果不需要支持COPY,用rule就可以了。

如果需要支持copy和insert, 请使用触发器。

最后,你可以将数据按原始格式输入,查询时,使用UDF转换,如果要创建索引,可以使用表达式索引。例如数组类型,某些输入是不规范的逗号分隔的多值,而PG的格式是{a,b,c}这样的格式。

postgres=# select regexp_split_to_array('a,b,c,d,e', ',');
 regexp_split_to_array 
-----------------------
 {a,b,c,d,e}
(1 row)

通过这个函数就可以方便的将多值字符串转换成数组,创建数组的函数了。

Flag Counter

digoal’s 大量PostgreSQL文章入口