PostgreSQL使用clickhouse_fdw访问ClickHouse

Postgres

postgres版本：16（测试可用）
docker 安装

插件安装

clickhouse_fdw: https://github.com/ildus/clickhouse_fdw

安装命令

git clone git@github.com:ildus/clickhouse_fdw.git
cd clickhouse_fdw
mkdir build && cd build
cmake ..
make && make install

deb12换源

cat > /etc/apt/sources.list << EOF
deb http://mirrors.tuna.tsinghua.edu.cn/debian/ bookworm main contrib non-free non-free-firmware
deb-src http://mirrors.tuna.tsinghua.edu.cn/debian/ bookworm main contrib non-free non-free-firmware

deb http://mirrors.tuna.tsinghua.edu.cn/debian/ bookworm-updates main contrib non-free non-free-firmware
deb-src http://mirrors.tuna.tsinghua.edu.cn/debian/ bookworm-updates main contrib non-free non-free-firmware

deb http://mirrors.tuna.tsinghua.edu.cn/debian/ bookworm-backports main contrib non-free non-free-firmware
deb-src http://mirrors.tuna.tsinghua.edu.cn/debian/ bookworm-backports main contrib non-free non-free-firmware

deb http://mirrors.tuna.tsinghua.edu.cn/debian-security bookworm-security main contrib non-free non-free-firmware
deb-src http://mirrors.tuna.tsinghua.edu.cn/debian-security bookworm-security main contrib non-free non-free-firmware
EOF

apt update

安装编译器

docker镜像里没有必要的编译工具，make 执行不了
make 过程中陆陆续续提示缺少依赖，按提示安装包即可
在这里插入图片描述

大概安装了以下包，仅供参考

apt-get install devscripts build-essential cmake unixodbc-dev postgresql-server-dev-16 pkg-config libcurl libcurl4-openssl-dev uuid-dev

最终，make install 会把编译好的插件发送到指定位置，直接连接pg加载即可

加载插件

CREATE EXTENSION clickhouse_fdw;  -- 加载插件

导入外部表

CREATE SERVER clickhouse_svr FOREIGN DATA WRAPPER clickhouse_fdw OPTIONS(host '192.168.30.101', port '8123', dbname 'my_ch_db', driver 'http');  -- 连接clickhouse

CREATE USER MAPPING FOR CURRENT_USER SERVER clickhouse_svr OPTIONS (user 'default', password '');  -- 用户映射，clickhouse的连接密码
IMPORT FOREIGN SCHEMA "my_ch_db" FROM SERVER clickhouse_svr INTO public;  -- 把clickhouse模式导入pg

注意IMPORT SCHEMA会加载clickhouse库名<my_ch_db>下的所有表到postgres的public模式
可以在pg的外表中查看到
在这里插入图片描述

删除外部表

drop FOREIGN table if exists "soc_filtered"; -- 删除所有外部表
drop user mapping FOR CURRENT_USER server clickhouse_svr;  -- 删除用户映射
drop server clickhouse_svr;  -- 删除链接