基于Clickhouse-fdw 集成Clickhouse 与Postgresql的探索

1 Conda环境下搭建Clickhouse-fdw

由于 Clickhouse-fdw 依赖高版本的 GNU tool chain 需要使用 GCC 7+ 来编译, 并且依赖的一些第三方库的版本也比较高, 原生 CentOSrepo 不能满足依赖,所以用选用 Conda 环境来解决依赖并 build 生成动态库。

本质上 Clickhouse-fdw 是一个 postgresqlextension 动态库, 它与 Clickhouse 之间的连接是使用的 Clickhouse-odbc 库。

1.1 编译clickhouse-odbc

  1. 递归拉取 clichouse-odbcsource, --recursive 可以把项目下的 sub project 一并拉下来。

    git clone --recursive [email protected]:ClickHouse/clickhouse-odbc.git
  2. 编译Clickhouse-odbc, README里面使用的是devtoolset-8 为了解决高版本的GCC的问题,我这里用的Conda的环境,需要用conda 安装gcc 7以及其他包括cmake 14+在内的依赖包.

    cd clickhouse-odbc
    mkdir build
    cd build
    # Configuration options for the project can be specified in the next command in a form of '-Dopt=val'
    cmake -DCMAKE_BUILD_TYPE=RelWithDebInfo ..

    在第7部的时候会有报错,错误步骤是在编译 gtest 的测试 binary, 其实可以不用解决,因为在此错误之前 libclickhouseodbc.so 已经被编译出来了。

    (py3) [ec2-user@ip-172-31-46-209 driver]$ ldd libclickhouseodbc.so
        linux-vdso.so.1 (0x00007ffd111b4000)
        libpthread.so.0 => /lib64/libpthread.so.0 (0x00007f4fcd072000)
        libstdc++.so.6 => /home/ec2-user/miniconda3/envs/py3/lib/libstdc++.so.6 (0x00007f4fcd616000)
        libgcc_s.so.1 => /home/ec2-user/miniconda3/envs/py3/lib/libgcc_s.so.1 (0x00007f4fcd602000)
        libc.so.6 => /lib64/libc.so.6 (0x00007f4fcccc7000)
        librt.so.1 => /lib64/librt.so.1 (0x00007f4fccabf000)
        libodbcinst.so.2 => /home/ec2-user/miniconda3/envs/py3/lib/libodbcinst.so.2 (0x00007f4fcc8a4000)
        libicuuc.so.58 => /home/ec2-user/miniconda3/envs/py3/lib/libicuuc.so.58 (0x00007f4fcc6f1000)
        /lib64/ld-linux-x86-64.so.2 (0x00007f4fcd573000)
        libm.so.6 => /lib64/libm.so.6 (0x00007f4fcc3b1000)
        libdl.so.2 => /lib64/libdl.so.2 (0x00007f4fcc1ad000)
        libicudata.so.58 => /home/ec2-user/miniconda3/envs/py3/lib/./libicudata.so.58 (0x00007f4fca8ab000)
    

    查看是否有找不到的动态库地址。

1.2 编译clickhouse-fdw

clickhouse-fdw 依赖 postgresql-dev 的包,所以还是要先 yum postgresql-dev 相关的依赖,然后再用 Conda 安装 postgresql 11 的版本。 Conda 安装的 postgresql 没有写入 systemctl, 需要手动配置启动。 Conda 安装的 postgresql pg_config 输出的系统变量里"CC"的指向路径是有问题的。

pg_config
....
CC = /tmp/build/80754af9/postgresql-split_1545233302450/_build_env/bin/x86_64-conda_cos6-linux-gnu-cc

这里 CC 是只编译 postgresql 的编译器路径,系统里根本没有这个,需要修改 Makefile 里面强制指向 CC 到系统的 gcc 编译器,否则会报错。

(py3) [ec2-user@ip-172-31-46-209 clickhousedb_fdw]$ make USE_PGXS=1
rm -rf libclickhouse-1.0.so lib/*.o
make -f lib/Makefile
make[1]: 进入目录“/home/ec2-user/workspaces/clickhousedb_fdw”
g++ -Wall -Wpointer-arith -O2 -fPIC -O0 -g3 -Wno-unused-variable -fPIC -Wall -I. -Ilib   -c -o lib/clickhouse-client.o lib/clickhouse-client.cpp
g++ -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -Wno-format-truncation -march=nocona -mtune=haswell -ftree-vectorize -fPIC -fstack-protector-strong -fno-plt -O2 -pipe -I/home/ec2-user/miniconda3/envs/py3/include -fdebug-prefix-map==/usr/local/src/conda/- -fdebug-prefix-map==/usr/local/src/conda-prefix -fPIC lib/clickhouse-client.o -o libclickhouse-1.0.so -L/home/ec2-user/miniconda3/envs/py3/lib -L. -lclickhouse-1.0 -lodbc -lodbcinst -Wl,-O2 -Wl,--sort-common -Wl,--as-needed -Wl,-z,relro -Wl,-z,now -Wl,--disable-new-dtags -Wl,-rpath,/home/ec2-user/miniconda3/envs/py3/lib -L/home/ec2-user/miniconda3/envs/py3/lib  -L/home/ec2-user/miniconda3/envs/py3/lib -Wl,--as-needed -Wl,-rpath,'/home/ec2-user/miniconda3/envs/py3/lib',--disable-new-dtags -fPIC -shared -ldl -lstdc++ -L -lodbc
make[1]: 放弃循环依赖 test <- 755 test 。 g++ -o0 -g3 -wno-unused-variable -iinclude lib example odbc_test.o -o -ldl -l. -lclickhouse-1.0 -lodbc -lodbcinst make[1]: 离开目录“ home ec2-user workspaces clickhousedb_fdw” make -f makefile 进入目录“ 放弃循环依赖 <- 对“all”无需做任何事。 usr bin install -c -m libclickhouse-1.0.so miniconda3 envs py3 < pre>

最后运行 make USE_PGXS=1 install 把对应的动态库放到制定位置。

2 配置 postgresqlclickhousedb-fdw 连接 Clickhouse

先配置 clickhouse 表并导入数据

CREATE DATABASE test_database;

USE test_database;

CREATE TABLE tax_bills_nyc
(
bbl Int64,
owner_name String,
address String,
tax_class String,
tax_rate String,
emv Float64,
tbea Float64,
bav Float64,
tba String,
property_tax String,
condonumber String,
condo String,
insertion_date DateTime MATERIALIZED now()
)
ENGINE = MergeTree PARTITION BY tax_class ORDER BY (owner_name)

curl -X GET 'http://taxbills.nyc/tax_bills_june15_bbls.csv'  \
| clickhouse-client --input_format_allow_errors_num=10 \
--query="INSERT INTO test_database.tax_bills_nyc FORMAT CSV"

配置启动 postgresql,首先要初始化 postgresq

initdb pdatta

这个时候会以 ec2-user 用户创建一个 pdata 的目录,里面会有生成 postsql 的默认配置。 修改 pdata 目录下的两个文件 postgresql.confpg_hba.conf , 第一个文件修改 listernling portbind ip 改为 0.0.0.0, 第二个文件修改 trustip, 这样就可以从外部访问 postgresql.

pg_ctl start -D pdata -s -w -t 300

如果需要停止服务可以运行

pg_ctl stop -D pdata -s -m fast

创建 clickhouse-fdw extension

CREATE EXTENSION clickhousedb_fdw;

创建 SERVER, 这里需要指定 clickhouse 的地址为 host, 以及之前编译的 clickhouseodbc.so 地址作为 driver 的地址, dbname 也是 clickhouse 上的 =dbname=。

CREATE SERVER clickhouse_svr FOREIGN DATA WRAPPER clickhousedb_fdw OPTIONS(dbname 'test_db', driver '/home/ec2-user/workspaces/clickhouse-odbc/build/driver/libclickhouseodbc.so', host '172.31.37.147');

创建user mapping

CREATE USER MAPPING FOR CURRENT_USER SERVER clickhouse_svr;

创建外表, 并且表名需要跟 clickshouse 的表名一致。

CREATE FOREIGN TABLE tax_bills_nyc
(
bbl int8,
owner_name text,
address text,
tax_class text,
tax_rate text,
emv Float,
tbea Float,
bav Float,
tba text,
property_tax text,
condonumber text,
condo text,
insertion_date Time
) SERVER clickhouse_svr;

查询数据 clickhouse_fwd_query_results.png

Date: 2020-08-13

Author: shawn-win11

Created: 2022-09-24 Sat 15:03

Validate