PostgreSQL uses clickhousedb_ FDW access Clickhouse

author ： Yang jie

brief introduction

PostgreSQL FDW It's an external access interface , It can be used to access data stored externally , The data can be external PG database , It's fine too mysql、ClickHouse Such as the database .

ClickHouse It's a fast open source OLAP Database management system , It's column oriented , Allow to use SQL Query and generate analysis report in real time .

clickhouse_fdw Is an open source external data wrapper (FDW) Used to access the ClickHouse Column storage database .

Now there are two clickhouse_fdw：
https://github.com/adjust/clickhouse_fdw

There's a constant flow of submissions , At present, we support PostgreSQL 11-13
https://github.com/Percona-Lab/clickhousedb_fdw

There was no movement for a year before , Recently, I just came from adjust/clickhouse_fdw merge For a moment , Currently supported PostgreSQL 11-13.

This article is based on adjust/clickhouse_fdw For example .

install

# 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

Use

CH End ：
Generate test tables and data , Here we use CH Provided by the official website Star Schema Benchmark

https://clickhouse.tech/docs/en/getting-started/example-datasets/star-schema/#star-schema-benchmark

Analog data volume ：5 Data sheets , The data is mainly concentrated in lineorder* surface , Single table 9000w rows about 、22G Storage .

[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 End ：
establish FDW plug-in unit

postgres=# create extension clickhouse_fdw ;
CREATE EXTENSION
postgres=# \dew
List of foreign-data wrappers
Name | Owner | Handler | Validator 
----------------+----------+--------------------------+----------------------------
clickhouse_fdw | postgres | clickhousedb_fdw_handler | clickhousedb_fdw_validator
(1 row)

establish CH External servers

postgres=# CREATE SERVER clickhouse_svr FOREIGN DATA WRAPPER clickhouse_fdw 
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 
----------------+----------+----------------------
clickhouse_svr | postgres | clickhouse_fdw
(1 row)

Create user mapping

postgres=# CREATE USER MAPPING FOR CURRENT_USER SERVER clickhouse_svr 
OPTIONS (user 'default', password '');
CREATE USER MAPPING
postgres=# \deu
List of user mappings
Server | User name 
----------------+-----------
clickhouse_svr | postgres
(1 row)

Create an external table

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 | lineorder_flat | clickhouse_svr
public | part | clickhouse_svr
public | supplier | clickhouse_svr
(5 rows)

Inquire about

postgres=# select count(*) from lineorder_flat ;
count 
----------
89987373
(1 row)

postgres=# select "LO_ORDERKEY","C_NAME" from lineorder_flat limit 5;
LO_ORDERKEY | C_NAME 
-------------+--------------------
3271 | Customer#000099173
3271 | Customer#000099173
3271 | Customer#000099173
3271 | Customer#000099173
5607 | Customer#000273061
(5 rows)

It should be noted that CH It's case sensitive and some function compatibility issues , The above example also shows .
test SQL Use it directly CH SSB Provided 13 strip SQL,SQL similar , Choose one to test , The running time is basically the same .

CH:

vm101 :) SELECT
:-] toYear(LO_ORDERDATE) AS year,
:-] C_NATION,
:-] sum(LO_REVENUE - LO_SUPPLYCOST) AS profit
:-] FROM lineorder_flat
:-] WHERE C_REGION = 'AMERICA' AND S_REGION = 'AMERICA' AND (P_MFGR = 'MFGR#1' OR P_MFGR = 'MFGR#2')
:-] GROUP BY
:-] year,
:-] C_NATION
:-] ORDER BY
:-] year ASC,
:-] C_NATION ASC;

SELECT 
toYear(LO_ORDERDATE) AS year,
C_NATION,
sum(LO_REVENUE - LO_SUPPLYCOST) AS profit
FROM lineorder_flat
WHERE (C_REGION = 'AMERICA') AND (S_REGION = 'AMERICA') AND ((P_MFGR = 'MFGR#1') OR (P_MFGR = '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
date_part('year', "LO_ORDERDATE") AS year,
"C_NATION",
sum("LO_REVENUE" - "LO_SUPPLYCOST") AS profit
FROM lineorder_flat
WHERE "C_REGION" = 'AMERICA' AND "S_REGION" = 'AMERICA' AND ("P_MFGR" = 'MFGR#1' OR "P_MFGR" = '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

relevant

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/

Learn more about PostgreSQL Technical dry cargo 、 Hot papers 、 Industry trends 、 News and information 、 Wonderful activities , Please visit China PostgreSQL Community websites ：www.postgresqlchina.com