背景
ck可以用过clickhouse-jdbc-bridge技术来直接访问各数据库,包括oracle
规划
jdbc-bridge是面向客户端的技术,相当于服务端
安装配置
需要准备的文件
clickhouse-jdbc-bridge
https://github.com/ClickHouse/clickhouse-jdbc-bridge
理论上需要下载源码然后用maven打包,但提供了打包好的,可以推测用的是maven的shade插件
oracle的驱动
在官网https://mvnrepository.com下载Oracle对应的驱动包ojdbc8-12.2.0.1.jar放置到/data/clickhouse-jdbc-bridge/drivers目录下。
ojdbc8-12.2.0.1.jar 可用于11g
创建目录
注意,jdbc-bridge对目录有要求,相对位置是固定的,上级目录随意。
└── clickhouse-jdbc-bridge
├── clickhouse-jdbc-bridge-2.1.0-shaded.jar
├── config
│ └── datasources
│ └── oracle.json
└── drivers
└── ojdbc8-12.2.0.1.jar
sudo mkdir -p /opt/clickhouse/clickhouse-jdbc-bridge
sudo chown -R bigdata:bigdata /opt/clickhouse/clickhouse-jdbc-bridge
注意,这里权限可以不用clickhouse用户,别的用户也可以
cd /opt/clickhouse/clickhouse-jdbc-bridge
mkdir -p config/datasources
mkdir drivers
sudo mkdir -p /opt/clickhouse/clickhouse-jdbc-bridge;sudo mkdir -p /opt/clickhouse/clickhouse-jdbc-bridge/config/datasources;sudo mkdir -p /opt/clickhouse/clickhouse-jdbc-bridge/drivers;sudo chown -R bigdata:bigdata /opt/clickhouse/clickhouse-jdbc-bridge
最终的目录结构
/opt
└── clickhouse
└── clickhouse-jdbc-bridge
├── clickhouse-jdbc-bridge-2.1.0-shaded.jar
├── config
│ └── datasources
│ └── oracle.json
└── drivers
└── ojdbc8-12.2.0.1.jar
配置文件
cd /opt/clickhouse/clickhouse-jdbc-bridge/config/datasources
注意:
- oracle.json和里面的根节点oracle名字必须一样
- jdbcUrl中,注意gid和库,如果是库,要用/,如果是gid,要用:,弄混了会报错
- connectionTestQuery一定要为空串,不然有可能出问题,oracle好像不支持这么测试
- :后面可以没有空格
- connectTimeout ,socketTimeout 2个参数都不能用,用了启动不会报错,但连的时候会报没有source
- serverTimezone这个参数也不行,用了启动不会报错,但连的时候会报没有source,要用"timezone":“GTM+8”
vim oracle.json
{
"oracle": {
"driverUrls": [
"/opt/clickhouse/clickhouse-jdbc-bridge/drivers/ojdbc8-12.2.0.1.jar"
],
"driverClassName": "oracle.jdbc.driver.OracleDriver",
"jdbcUrl": "jdbc:oracle:thin:@xx.xxx.0.70:1521/ods",
"username": "账号",
"password": "密码",
"connectionTestQuery": "",
"timezone":"GTM+8"
}
}
cd /opt/clickhouse/clickhouse-jdbc-bridge/;nohup java -jar clickhouse-jdbc-bridge-2.1.0-shaded.jar &
tail -f nohup.out
jdbc相关配置
以jdbc url配置时:
jdbc:mysql://59.110.137.112:3306/answer?useUnicode=true&characterEncoding=utf8
&zeroDateTimeBehavior=convertToNull&useSSL=true&serverTimezone=GMT%2B8
&nullCatalogMeansCurrent=true
以配置文件或者代码配置时:
复制
设置时区
设置时区时,添加serverTimezone参数,比如serverTimezone=GMT%2B8(URL 中 %2B表示+号,GMT%2B8即为GMT+8)。
GMT+8 表示东八区,北京时间
Asia/Shanghai 表示上海时间,也是东八区
ck的配置
到/etc/clickhouse-server目录下的config.xml文件,找到以下代码,移除注释,并修改host为执行clickhouse-jdbc-bridge-2.10.0-shaded.jar的ip地址:
<jdbc_bridge>
<host>localhost</host>
<port>9019</port>
</jdbc_bridge>
使用
启动
nohup java -jar clickhouse-jdbc-bridge-2.1.0-shaded.jar &
tail -f nohup.out
重启ck
systemctl restart clickhouse-server
systemctl restart clickhouse-server2
验证
select * from jdbc('','show datasources') --查看外部数据源
select * from jdbc('oracle','SELECT (TRUNC (SYSDATE+1) - SYSDATE) * 24 * 60 * 60 num_of_sec_left FROM DUAL') --运行一个测试sql