651
懶人改善生活之 - 數據入庫實時轉換
標簽
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支持枚舉類型。
那麼如何將數據從字符串平滑、實時的在入庫階段轉換為PostgreSQL支持的類型呢？
PostgreSQL支持trigger和rule，使用任意方法，都可以做到數據的平滑轉換。
rule 轉換 例子
數據來源是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)
cool !!!使用這種方法，我們不需要先入庫在轉換，也不需要在入庫前寫程序進行轉換。
最後更新：2017-06-19 19:31:45