600
動態輸出(ToB海量日誌轉換業務) - 阿裏雲HybridDB for PostgreSQL最佳實踐
標簽
PostgreSQL , UDF , 動態格式 , format , JOIN , OSS外部表
背景
有一些業務需要將數據歸類動態的輸出，比如一些公共日誌服務，所有用戶的日誌都被統一的按格式記錄到一起，但是每個最終用戶關心的字段都不一樣，甚至每個用戶對數據轉換的需求都不一樣。
比如這個業務：
《日增量萬億+級 實時分析、數據規整 - 阿裏雲HybridDB for PostgreSQL最佳實踐》
一、需求
1、可以根據ToB的用戶的定義，輸出不同的格式。
2、每個ToB的用戶，寫入到一個文件或多個文件。
3、一個文件不能出現兩個用戶的內容。
其他需求見：
《日增量萬億+級 實時分析、數據規整 - 阿裏雲HybridDB for PostgreSQL最佳實踐》
二、架構設計
1、采用OSS存儲實時載入的海量公共日誌。
2、采用HybridDB for PostgreSQL或RDS PostgreSQL的OSS外部表接口，直接並行讀取OSS的文件。
3、通過HDB PG的窗口函數，按ToB ID充分發。
4、通過UDF，將公共日誌的格式，按ToB ID對應的UDF轉換為對應的格式。
5、將轉換後的數據，寫入OSS。自動按ToB ID切換，絕對保證每個ToB的用戶，寫入到一個文件或多個文件。一個文件不出現兩個用戶的內容。
以上功能是阿裏雲HybridDB for PostgreSQL或RDS PostgreSQL的獨有功能。
三、DEMO與性能
這裏介紹如何動態的轉換數據，其他內容請詳見案例：
《日增量萬億+級 實時分析、數據規整 - 阿裏雲HybridDB for PostgreSQL最佳實踐》
1、創建公共日誌表
create table t1 (tid int, c1 text, c2 text, c3 int, c4 timestamp, c5 numeric);
2、寫入一批測試數據
insert into t1 select random()*100, md5(random()::text), 'test', random()*10000, clock_timestamp(), random() from generate_series(1,1000000);
3、創建UDF元信息表，存儲每個ToB ID對應的UDF名字
create table t2(tid int, udf name);
4、創建UDF，需要定製格式的ToB ID，創建對應的UDF
create or replace function f1(t1) returns text as $$
select format('tid: %L , c2: %L , c4: %L', $1.tid, $1.c2, $1.c4);
$$ language sql strict;
create or replace function f2(t1) returns text as $$
declare
res text := format('%L , %L , %L, %L', $1.tid, upper($1.c2), $1.c4, $1.c3);
begin
return res;
end;
$$ language plpgsql strict;
5、創建動態UDF，根據輸入，動態調用對應的UDF
create or replace function ff(t1, name) returns text as $$
declare
sql text := format('select %I(%L)', $2, $1);
res text;
begin
execute sql into res;
return res;
end;
$$ language plpgsql strict;
6、寫入UDF映射，例如1-100的ID，使用F1進行轉換，0的ID使用F2進行轉換。
insert into t2 select generate_series(1,100), 'f1';
insert into t2 values (0, 'f2');
7、動態轉換查詢如下：
postgres=# select ff(t1, t2.udf) from t1 join t2 on (t1.tid=t2.tid) where t1.tid=0 limit 10;
ff
-----------------------------------------------------
'0' , 'TEST' , '2017-08-03 18:55:00.48512', '9478'
'0' , 'TEST' , '2017-08-03 18:55:00.486426', '9352'
'0' , 'TEST' , '2017-08-03 18:55:00.487297', '4026'
'0' , 'TEST' , '2017-08-03 18:55:00.488419', '736'
'0' , 'TEST' , '2017-08-03 18:55:00.491082', '4334'
'0' , 'TEST' , '2017-08-03 18:55:00.491097', '2394'
'0' , 'TEST' , '2017-08-03 18:55:00.491839', '2076'
'0' , 'TEST' , '2017-08-03 18:55:00.492648', '9935'
'0' , 'TEST' , '2017-08-03 18:55:00.493505', '383'
'0' , 'TEST' , '2017-08-03 18:55:00.493874', '8546'
(10 rows)
postgres=# select ff(t1, t2.udf) from t1 join t2 on (t1.tid=t2.tid) where t1.tid=1 limit 10;
ff
----------------------------------------------------------
tid: '1' , c2: 'TEST' , c4: '2017-08-03 18:55:00.484799'
tid: '1' , c2: 'TEST' , c4: '2017-08-03 18:55:00.485209'
tid: '1' , c2: 'TEST' , c4: '2017-08-03 18:55:00.485276'
tid: '1' , c2: 'TEST' , c4: '2017-08-03 18:55:00.485744'
tid: '1' , c2: 'TEST' , c4: '2017-08-03 18:55:00.48582'
tid: '1' , c2: 'TEST' , c4: '2017-08-03 18:55:00.485967'
tid: '1' , c2: 'TEST' , c4: '2017-08-03 18:55:00.486067'
tid: '1' , c2: 'TEST' , c4: '2017-08-03 18:55:00.486281'
tid: '1' , c2: 'TEST' , c4: '2017-08-03 18:55:00.486756'
tid: '1' , c2: 'TEST' , c4: '2017-08-03 18:55:00.487714'
(10 rows)
8、HDB PG中的強製充分發：
postgres=# select ff(t1, t2.udf) as ff, row_number() over (partition by t1.tid order by c4) from t1 join t2 on (t1.tid=t2.tid) where t1.tid=0 limit 10;
ff | row_number
-----------------------------------------------------+------------
'0' , 'TEST' , '2017-08-03 18:55:00.48512', '9478' | 1
'0' , 'TEST' , '2017-08-03 18:55:00.486426', '9352' | 2
'0' , 'TEST' , '2017-08-03 18:55:00.487297', '4026' | 3
'0' , 'TEST' , '2017-08-03 18:55:00.488419', '736' | 4
'0' , 'TEST' , '2017-08-03 18:55:00.491082', '4334' | 5
'0' , 'TEST' , '2017-08-03 18:55:00.491097', '2394' | 6
'0' , 'TEST' , '2017-08-03 18:55:00.491839', '2076' | 7
'0' , 'TEST' , '2017-08-03 18:55:00.492648', '9935' | 8
'0' , 'TEST' , '2017-08-03 18:55:00.493505', '383' | 9
'0' , 'TEST' , '2017-08-03 18:55:00.493874', '8546' | 10
(10 rows)
四、技術點
這裏隻談本文涉及的技術點。
1、UDF
PostgreSQL支持多種UDF語言（例如C，plpgsql, sql, plpython, pljava, plv8, ......），用戶通過UDF定義需要轉換的格式。
2、動態調用
用戶通過動態調用，可以動態的調用對應的UDF，在一個請求中生成不同的格式。
五、雲端產品
六、類似場景、案例
《日增量萬億+級 實時分析、數據規整 - 阿裏雲HybridDB for PostgreSQL最佳實踐》
七、小結
一些公共日誌服務，所有用戶的日誌都被統一的按格式記錄到一起，但是每個最終用戶關心的字段都不一樣，甚至每個用戶對數據轉換的需求都不一樣。
PostgreSQL支持多種UDF語言（例如C，plpgsql, sql, plpython, pljava, plv8, ......），用戶通過UDF定義需要轉換的格式。
用戶通過動態調用，可以動態的調用對應的UDF，在一個請求中生成不同的格式。
八、參考
《日增量萬億+級 實時分析、數據規整 - 阿裏雲HybridDB for PostgreSQL最佳實踐》
最後更新：2017-08-13 22:34:53