作者:騰訊雲流計算 Oceanus 團隊
流計算 Oceanus 簡介
流計算 Oceanus 是大資料產品生態體系的實時化分析利器,是基於 Apache Flink 構建的具備一站開發、無縫連線、亞秒延時、低廉成本、安全穩定等特點的企業級實時大資料分析平臺。流計算 Oceanus 以實現企業資料價值最大化為目標,加速企業實時化數字化的建設程式。
本文將向您詳細介紹如何獲取 PostgreSQL 表資料,並使用字串函式進行轉換,最後將資料輸出到 ClickHouse 中。
操作視訊
前置準備
建立流計算 Oceanus 叢集
進入流計算 Oceanus 控制檯 [1],點選左側【叢集管理】,點選左上方【建立叢集】,具體可參考流計算 Oceanus 官方文件 建立獨享叢集 [2]。
建立 PostgreSQL 例項
進入 PostgreSQL 控制檯 [3],點選左上角【新建】建立例項,具體參考 建立 PostgreSQL 例項 [4]。
資料準備:
進入例項資料庫,建立 test1 表,並手動插入資料。
-- 建表語句
create table public.test1 (
id INT,
str_one VARCHAR(50),
str_two VARCHAR(50),
str_thr VARCHAR(50),
PRIMARY key(id)
);
-- 插入語句
INSERT INTO public.test1 VALUES (1, 'hello world', 'b', 'Oceanus-1');
INSERT INTO public.test1 VALUES (2, 'good job', 'c', 'Oceanus-2');
INSERT INTO public.test1 VALUES (3, 'hello oceanus', 'd', 'Oceanus-3');
筆者這裡使用 DBeaver 進行外網連線,更多連線方式參考官網文件 連線 PostgreSQL 例項 [5]
建立 ClickHouse 叢集
進入 ClickHouse 控制檯 [6],點選左上角【新建叢集】,完成 ClickHouse 叢集建立,具體可參考 ClickHouse 快速入門 [7]。建立 ClickHouse 表: 登陸 ClickHouse 叢集(登入方式參考 ClickHouse 快速入門 [7]),並建表。
CREATE TABLE default.pg_to_ck on cluster default_cluster (
id Int8,
str_one String,
str_two String,
str_thr String,
Sign Int8 )
ENGINE = ReplicatedCollapsingMergeTree('/clickhouse/tables/{layer}-{shard}/default/pg_to_ck', '{replica}',Sign)
ORDER BY (id);
注:流計算 Oceanus 叢集、PostgreSQL 例項、ClickHouse 叢集需在同一 VPC 下。
流計算 Oceanus 作業
1. 建立 Source
-- PostgreSQL CDC Source。
CREATE TABLE PostgreSourceTable (
id INT,
str_one VARCHAR,
str_two VARCHAR,
str_thr VARCHAR,
PRIMARY KEY (id) NOT ENFORCED -- 如果要同步的資料庫表定義了主鍵, 則這裡也需要定義
) WITH (
'connector' = 'postgres-cdc', -- 必須為 'postgres-cdc'
'hostname' = '10.0.0.236', -- 資料庫的 IP
'port' = '5432', -- 資料庫的訪問埠
'username' = 'root', -- 資料庫訪問使用的使用者名稱(需要提供 REPLICATION 許可權, 日誌級別必須大於等於 logical, 且設定後需要重啟例項)
'password' = 'xxxxxxxxxxx', -- 資料庫訪問使用的密碼
'database-name' = 'postgres', -- 需要同步的資料庫名
'schema-name' = 'public', -- 需要同步的資料庫模式 (Schema)
'table-name' = 'test1' -- 需要同步的資料表名
);
2. 建立 Sink
-- ClickHouse Sink (不完全支援upsert,詳見說明文件)。配合 flink-connector-clickhouse 使用。
CREATE TABLE clickhouse_sink (
id INT,
str_one VARCHAR,
str_two VARCHAR,
str_thr VARCHAR,
PRIMARY KEY (id) NOT ENFORCED -- 如果要同步的資料庫表定義了主鍵, 則這裡也需要定義
) WITH (
'connector' = 'clickhouse', -- connector 型別為 clickhouse
'url' = 'clickhouse://10.0.0.178:8123', -- 指定資料庫連結 url
'database-name' = 'default', -- 需要寫入的 clickhouse 庫名
'table-name' = 'pg_to_ck', -- 需要寫入的 clickhouse 表名
'table.collapsing.field' = 'Sign' -- 採用 CollapsingMergeTree 引擎的 clickhouse 表,Collapsing 列欄位的名稱
);
3. 編寫業務 SQL
INSERT INTO clickhouse_sink
SELECT
id,
--INITCAP:將 str_one 中的單詞轉為大寫開頭,例如 INITCAP('i have a dream') 返回 'I Have A Dream'。
INITCAP(str_one) AS str_one,
--TO_BASE64:將 string 表示的字串編碼為 Base64 字串。
TO_BASE64(str_two) AS str_two,
--REPLACE:將 string1 字串中所有的 string2 替換為 string3。例如 REPLACE('banana', 'a', 'A') 返回 'bAnAnA'。
REPLACE(str_thr,'Oceanus','Hello Oceanus') AS str_thr
FROM PostgreSourceTable;
這裡我們使用 Flink 1.13 叢集,舊版 Flink 叢集需選擇相應的內建 Connector
總結
-
使用 Postgres-CDC 聯結器:
-
用於同步的 Postgres 使用者至少需要開啟 REPLICATION、LOGIN、SCHEMA、DATABASE、SELECT 許可權。可以進入 PostgreSQL 資料庫進行授權操作。
CREATE ROLE debezium_user REPLICATION LOGIN;
GRANT USAGE ON DATABASE database_name TO debezium_user;
GRANT USAGE ON SCHEMA schema_name TO debezium_user;
GRANT SELECT ON scheam_name.table_name, scheam_name.table_name TO debezium_user;
-
日誌級別必須大於等於 logical, 且設定後需要重啟例項。進入資料庫例項,單擊【引數設定】,單擊【WAL】,修改【wal_level】的【引數執行值】為 "logical"。修改成功後點選右上角【重啟】。
-
更多字串操作函式請參考流計算 Oceanus 官方文件 字串函式[8]。
參考連結
[1] 流計算 Oceanus 控制檯:https://console.cloud.tencent.com/oceanus/overview
[2] 建立獨享叢集:https://cloud.tencent.com/document/product/849/48298
[3] PostgreSQL 控制檯:https://console.cloud.tencent.com/postgres/index
[4] 建立 PostgreSQL 例項:https://cloud.tencent.com/document/product/409/56961
[5] 連線 PostgreSQL 例項:https://cloud.tencent.com/document/product/409/40429
[6] ClickHouse 控制檯:https://console.cloud.tencent.com/cdwch?region=ap-guangzhou
[7] ClickHouse 快速入門:https://cloud.tencent.com/document/product/1299/49824
[8] 流計算 Oceanus 字串函式:https://cloud.tencent.com/document/product/849/18073
關注“騰訊雲大資料”公眾號,技術交流、最新活動、服務專享一站 Get~