PostgreSQL 使用 clickhousedb_fdw 访问 ClickHouse
作者:杨杰
简介
PostgreSQL FDW 是一种外部访问接口,它可以被用来访问存储在外部的数据,这些数据可以是外部的 PG 数据库,也可以 mysql、ClickHouse 等数据库。
ClickHouse 是一款快速的开源 OLAP 数据库管理系统,它是面向列的,允许使用 SQL 查询实时生成分析报告。
clickhouse_fdw 是一个开源的外部数据包装器(FDW)用于访问 ClickHouse 列存数据库。
目前有以下两款 clickhouse_fdw:
https://github.com/adjust/clickhouse_fdw
一直持续不断的有提交,目前支持 PostgreSQL 11-13
https://github.com/Percona-Lab/clickhousedb_fdw
之前有一年时间没有动静,最近一段时间刚从 adjust/clickhouse_fdw merge 了一下,目前也支持 PostgreSQL 11-13。
本文就以 adjust/clickhouse_fdw 为例。
安装
libcurl >= 7.43.0
yum install libcurl-devel libuuid-devel
git clone https://github.com/adjust/clickhouse_fdw.git
cd clickhouse_fdw
mkdir build && cd build
cmake ..
make && make install
使用
CH 端:
生成测试表及数据,这里我们使用 CH 官网提供的 Star Schema Benchmark
https://clickhouse.tech/docs/en/getting-started/example-datasets/star-schema/#star-schema-benchmark
模拟数据量:5 张数据表,数据主要集中在 lineorder*表,单表 9000w rows 左右、22G 存储。
[root@vm101 ansible]# clickhouse client
ClickHouse client version 20.8.9.6.
Connecting to localhost:9000 as user default.
Connected to ClickHouse server version 20.8.9 revision 54438.
vm101 :) show tables;
SHOW TABLES
┌─name───────────┐
│ customer │
│ lineorder │
│ lineorder_flat │
│ part │
│ supplier │
└────────────────┘
5 rows in set. Elapsed: 0.004 sec.
vm101 :) select count(*) from lineorder_flat;
SELECT count(*)
FROM lineorder_flat
┌──count()─┐
│ 89987373 │
└──────────┘
1 rows in set. Elapsed: 0.005 sec.
[root@vm101 ansible]# du -sh /clickhouse/data/default/lineorder_flat/
22G /clickhouse/data/default/lineorder_flat/
PG 端:
创建 FDW 插件
postgres=# create extension clickhouse_fdw ;
CREATE EXTENSION
postgres=# \dew
List of foreign-data wrappers
Name | Owner | Handler | Validator
----------------+----------+--------------------------+----------------------------
clickhousefdw | postgres | clickhousedbfdw_handler | clickhousedb_fdw_validator
(1 row)
创建 CH 外部服务器
postgres=# CREATE SERVER clickhousesvr FOREIGN DATA WRAPPER clickhousefdw
OPTIONS(host '10.0.0.101', port '9000', dbname 'default', driver 'binary');
CREATE SERVER
postgres=# \des
List of foreign servers
Name | Owner | Foreign-data wrapper
----------------+----------+----------------------
clickhousesvr | postgres | clickhousefdw
(1 row)
创建用户映射
postgres=# CREATE USER MAPPING FOR CURRENTUSER SERVER clickhousesvr
OPTIONS (user 'default', password '');
CREATE USER MAPPING
postgres=# \deu
List of user mappings
Server | User name
----------------+-----------
clickhouse_svr | postgres
(1 row)
创建外部表
postgres=# IMPORT FOREIGN SCHEMA "default" FROM SERVER clickhouse_svr INTO public;
IMPORT FOREIGN SCHEMA
postgres=# \det
List of foreign tables
Schema | Table | Server
--------+----------------+----------------
public | customer | clickhouse_svr
public | lineorder | clickhouse_svr
public | lineorderflat | clickhousesvr
public | part | clickhouse_svr
public | supplier | clickhouse_svr
(5 rows)
查询
postgres=# select count(*) from lineorder_flat ;
count
----------
89987373
(1 row)
postgres=# select "LOORDERKEY","CNAME" from lineorder_flat limit 5;
LOORDERKEY | CNAME
-------------+--------------------
3271 | Customer#000099173
3271 | Customer#000099173
3271 | Customer#000099173
3271 | Customer#000099173
5607 | Customer#000273061
(5 rows)
需要注意的是 CH 是区分大小写的以及一些函数兼容问题,上面的示例也有展示。
测试 SQL 直接使用 CH SSB 提供的 13 条 SQL,SQL 基本类似,选一条做下测试,运行时间基本是一致的。
CH:
vm101 :) SELECT
:-] toYear(LO_ORDERDATE) AS year,
:-] C_NATION,
:-] sum(LOREVENUE - LOSUPPLYCOST) AS profit
:-] FROM lineorder_flat
:-] WHERE CREGION = 'AMERICA' AND SREGION = 'AMERICA' AND (PMFGR = 'MFGR#1' OR PMFGR = 'MFGR#2')
:-] GROUP BY
:-] year,
:-] C_NATION
:-] ORDER BY
:-] year ASC,
:-] C_NATION ASC;
SELECT
toYear(LO_ORDERDATE) AS year,
C_NATION,
sum(LOREVENUE - LOSUPPLYCOST) AS profit
FROM lineorder_flat
WHERE (CREGION = 'AMERICA') AND (SREGION = 'AMERICA') AND ((PMFGR = 'MFGR#1') OR (PMFGR = 'MFGR#2'))
GROUP BY
year,
C_NATION
ORDER BY
year ASC,
C_NATION ASC
┌─year─┬─C_NATION──────┬───────profit─┐
│ 1992 │ ARGENTINA │ 157402521853 │
...
│ 1998 │ UNITED STATES │ 89854580268 │
└──────┴───────────────┴──────────────┘
35 rows in set. Elapsed: 0.195 sec. Processed 89.99 million rows, 1.26 GB (460.70 million rows/s., 6.46 GB/s.)
PG:
postgres=# SELECT
datepart('year', "LOORDERDATE") AS year,
"C_NATION",
sum("LOREVENUE" - "LOSUPPLYCOST") AS profit
FROM lineorder_flat
WHERE "CREGION" = 'AMERICA' AND "SREGION" = 'AMERICA' AND ("PMFGR" = 'MFGR#1' OR "PMFGR" = 'MFGR#2')
GROUP BY
year,
"C_NATION"
ORDER BY
year ASC,
"C_NATION" ASC;
year | C_NATION | profit
------+---------------+--------------
1992 | ARGENTINA | 157402521853
...
1998 | UNITED STATES | 89854580268
(35 rows)
Time: 195.102 ms
相关
https://github.com/adjust/clickhouse_fdw
https://github.com/Percona-Lab/clickhousedb_fdw
https://github.com/ClickHouse/ClickHouse
https://clickhouse.tech/docs/en/getting-started/example-datasets/star-schema/
了解更多 PostgreSQL 技术干货、热点文集、行业动态、新闻资讯、精彩活动,请访问中国 PostgreSQL 社区网站:www.postgresqlchina.com
版权声明: 本文为 InfoQ 作者【PostgreSQLChina】的原创文章。
原文链接:【http://xie.infoq.cn/article/9b207a0538a0b64f58829e0af】。
本文遵守【CC-BY 4.0】协议,转载请保留原文出处及本版权声明。
评论