pgspider clickhouse fdw 试用
昨天有构建过一个clickhouse 的fdw docker 镜像，今天测试下
环境使用docker-compose 运行，clickhouse 同样也是
环境准备
- docker-compose 环境
version: "3"
services:
server:
image: yandex/clickhouse-server
ports:
- "8123:8123"
- "9000:9000"
- "9009:9009"
ulimits:
nproc: 65535
nofile:
soft: 262144
hard: 262144
client:
image: yandex/clickhouse-client
command: ['--host', 'server']
pgspider:
image: dalongrong/pgspider:clickhouse
ports:
- "5432:5432"
environment:
- "POSTGRES_PASSWORD=dalong"
- 启动服务
docker-compose up -d
- 初始化clickhouse 数据
创建表：
CREATE TABLE wikistat
(
project String,
subproject String,
hits UInt64,
size UInt64
) ENGINE = Log;
导入数据：
serverhost=192.168.0.103 // replace with your hostid
docker run -i yandex/clickhouse-client --format_csv_delimiter="|" --host ${serverhost} --query="INSERT INTO default.wikistat FORMAT CSV" < ./data/info.csv
数据内容
aa|File:Sleeping_lion.jpg|1|8030
aa|Main_Page|1|78261
aa|Special:Statistics|1|20493
aa|Special:WhatLinksHere/File:Crystal_Clear_app_email.png|1|5412
aa|Special:WhatLinksHere/File:Wikipedia-logo-fr.png|1|5370
aa|Steward_requests/Bot_status|1|4733
aa|Translation_teams/ru|1|4718
aa|User:%E5%8F%B8%E5%BE%92%E4%BC%AF%E9%A2%9C|2|20096
aa|User:149.62.201.0/24|1|4802
aa|User:191.101.30.0/24|1|4806
aa|User:5.172.192.0/21|1|4800
aa|User:62.76.7.25|1|4788
- 效果
使用clickhouse fdw
使用已经构建好，同时push dockerhub 的
- 启用扩展
CREATE EXTENSION clickhousedb_fdw;
- 创建外部server
CREATE SERVER myserver FOREIGN DATA WRAPPER clickhousedb_fdw OPTIONS(dbname 'default', driver '/opt/clickhousedb/libclickhouseodbc.so', host 'server');
- 创建用户映射
CREATE USER MAPPING FOR postgres SERVER myserver;
- 创建表映射
CREATE FOREIGN TABLE wikistat (
project text,
subproject text,
hits int,
size int
) SERVER myserver OPTIONS (table_name 'wikistat');
- 查询
select * from wikistat;
- 效果
说明
clickhouse 的fdw 是一个不错的工具，但是对于clickhouse 的操作，当前还不是很强大
参考资料
https://github.com/Percona-Lab/clickhousedb_fdw
https://hub.docker.com/repository/docker/dalongrong/pgspider
https://github.com/rongfengliang/pgspider-docker
https://github.com/rongfengliang/clickhouse-fdw-docker-learning
