写点什么

PostgreSQL 使用 clickhousedb_fdw 访问 ClickHouse

发布于: 2021 年 01 月 27 日

作者:杨杰


简介

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


发布于: 2021 年 01 月 27 日阅读数: 47
用户头像

开源是一种商业模式适合于中国 2020.10.31 加入

官方公众号:开源软件联盟PostgreSQL分会 官方网站:postgresqlchina.com 官方交流社区:pgfans.cn 官方资源社区:postgreshub.cn

评论

发布
暂无评论
PostgreSQL使用clickhousedb_fdw访问ClickHouse