作者
digoal
日期
2017-06-19
标签
PostgreSQL , ETL , 数据转换 , 实时转换 , 规则 , 触发器 , 结构化 , 非结构化
背景
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) ```
通过这个函数就可以方便的将多值字符串转换成数组，创建数组的函数了。
PostgreSQL 许愿链接
您的愿望将传达给PG kernel hacker、数据库厂商等, 帮助提高数据库产品质量和功能, 说不定下一个PG版本就有您提出的功能点. 针对非常好的提议，奖励限量版PG文化衫、纪念品、贴纸、PG热门书籍等，奖品丰富，快来许愿。开不开森.
9.9元购买3个月阿里云RDS PostgreSQL实例
PostgreSQL 解决方案集合
德哥 / digoal's github - 公益是一辈子的事.
