ClickHouse 系列：ClickHouse 與外部資料源整合（PostgreSQL）

1321 字

7 分鐘

ClickHouse 系列：ClickHouse 與外部資料源整合（PostgreSQL）

2025-08-20

software development

ClickHouse

/

Database

/

鐵人賽

/

Distributed

在實際數據平台架構中，ClickHouse 通常不是唯一的資料庫，而是與其他資料源（如 MySQL、PostgreSQL、S3、Kafka 等）整合，扮演 高效查詢與分析層 的角色。

本篇將示範如何透過 PostgreSQL Table Engine 和 MaterializedPostgreSQL Database Engine (experimental)，讓 ClickHouse 直接查詢 PostgreSQL 資料，實現跨庫即時分析。

為什麼要整合 PostgreSQL？#

在許多應用場景中，PostgreSQL 作為 OLTP 系統 儲存業務資料（如交易、用戶、訂單），但在報表分析時遇到以下挑戰：

OLTP 查詢性能無法滿足大量聚合分析
避免 ETL 搬運延遲帶來的資料不一致
不想複製全量資料，只需要即時查詢部分資料

ClickHouse 的 PostgreSQL Table Engine 可直接連線 PostgreSQL，並以類似「外部表」的方式查詢資料，適合快速整合多方資料源。

PostgreSQL Table Engine — 即時雙向查詢與插入#

適用情境#

不需全量同步，只想即時查詢 PostgreSQL 資料
需要在 ClickHouse 直接插入資料回 PostgreSQL
資料量相對較小、即時性需求高

PostgreSQL 端設定#

允許網路連線
postgresql.conf
```
1
listen_addresses = '*'
```

建立使用者

1
CREATE ROLE clickhouse_user SUPERUSER LOGIN PASSWORD 'ClickHouse_123';

建立資料庫與表

1
CREATE DATABASE db_in_psg;
2

3
CREATE TABLE table1 (
4
    id integer primary key,
5
    column1 varchar(10)
6
);
7

8
INSERT INTO table1 VALUES (1, 'abc'), (2, 'def');

設定連線權限

1
host    db_in_psg  clickhouse_user  192.168.1.0/24  password

重新載入設定
Terminal window
```
1
pg_ctl reload
```

ClickHouse 端設定#

建立資料庫
```
1
CREATE DATABASE db_in_ch;
```

建立連線表

1
CREATE TABLE db_in_ch.table1
2
(
3
    id UInt64,
4
    column1 String
5
)
6
ENGINE = PostgreSQL(
7
    'postgres-host.domain.com:5432',
8
    'db_in_psg',
9
    'table1',
10
    'clickhouse_user',
11
    'ClickHouse_123'
12
);

測試查詢
```
1
SELECT * FROM db_in_ch.table1;
```
雙向測試
- 在 PostgreSQL 新增資料，ClickHouse 查得到
- 在 ClickHouse 新增資料，PostgreSQL 查得到

MaterializedPostgreSQL Database Engine — 持續資料同步（CDC）#

適用情境#

需要將 PostgreSQL 整個資料庫或多個表持續同步到 ClickHouse
資料更新頻率高
適合報表與即時分析

注意事項#

實驗功能，需啟用設定
不支援 ClickHouse 直接修改同步表（避免與 CDC 衝突）
適合用於 只讀分析 場景

PostgreSQL 端設定#

開啟複製功能

1
listen_addresses = '*'
2
max_replication_slots = 10
3
wal_level = logical

建立使用者與資料庫

1
CREATE ROLE clickhouse_user SUPERUSER LOGIN PASSWORD 'ClickHouse_123';
2
CREATE DATABASE db1;

建立表與資料

1
\connect db1
2
CREATE TABLE table1 (
3
    id integer primary key,
4
    column1 varchar(10)
5
);
6
INSERT INTO table1 VALUES (1, 'abc'), (2, 'def');

設定權限

1
host    db1  clickhouse_user  192.168.1.0/24  password

ClickHouse 端設定#

啟用實驗功能

1
SET allow_experimental_database_materialized_postgresql=1;

建立同步資料庫

1
CREATE DATABASE db1_postgres
2
ENGINE = MaterializedPostgreSQL(
3
    'postgres-host.domain.com:5432',
4
    'db1',
5
    'clickhouse_user',
6
    'ClickHouse_123'
7
)
8
SETTINGS materialized_postgresql_tables_list = 'table1';

驗證資料
```
1
SELECT * FROM db1_postgres.table1;
```
測試同步 在 PostgreSQL 新增資料，ClickHouse 會自動更新。

選擇策略建議#

特性	PostgreSQL Table Engine	MaterializedPostgreSQL
存取方式	即時查詢與寫入	持續複製（只讀）
適合資料量	小批量、查詢即時	大批量、長期分析
延遲	查詢即時（依 PostgreSQL 響應）	低延遲（CDC 同步）
使用限制	受限於 PostgreSQL 性能	實驗功能、不可寫入