Clickhouse通过jdbc连接获取Mysql/Oracle数据(clickhouse-jdbc-bridge组件)
Clickhouse通过jdbc连接获取Mysql/Oracle数据
引言
Clickhouse连接获取Mysql/Oracle数据的方式主要为jdbc和odbc两种。
jdbc：通过jdbc的方式来连接到外部数据库，为实现这连接，需要借助clickhouse-jdbc-bridge这一组件
clickhouse-jdbc-bridge
clickhouse-jdbc-bridge主要充当无状态代理，主要作用为将查询从clickhouse传递到外部数据。通过这一扩展，可在clickhouse上实时跨多个数据源运行分布式查询，从而简化为数据仓储、监控和完整性检查等构件数据管道的过程。
jdbc使用步骤
下载jar 地址： https://github.com/ClickHouse/clickhouse-jdbc-bridge/releases/tag/v2.0.6
要使用这个引擎,通常会碰到各种各样的坑,希望我这篇文章能帮助大部分人避免一些坑.
jdbc-bridge服务需要自己打包部署,或者百度别人打包好的jar包.
jdbc-bridge 的github地址:clickhouse-jdbc-bridge
会java的话,从Git上面拉下来以后在idea或者eclipse中执行mvn的clean packge打包完成后到打包目录里面找到clickhouse-jdbc-bridge-2.0.4-SNAPSHOT-shaded(版本号可能不一样),这个就是我们要额外启动的服务
此时clickhouse-jdbc-bridge服务已经准备好.下面添加clickhouse-server的配置
vim /etc/clickhouse-server/config.xml
<jdbc_bridge>
<host>127.0.0.1</host>
<port>9019</port>
</jdbc_bridge>
上面的配置指的是当前的Clickhouse要连接的bridge的地址与端口,127.0.0.1代表当前服务器ip,9019是brigde的服务端口.
配置好后,看bridge服务启动的参数.
java -jar clickhouse-jdbc-bridge-2.0.4-SNAPSHOT.jar --help
显示如下
Options:
--daemon
Run as daemon
Default: false
--datasources
File, containing specifications for connections
--driver-path
Path to directory, containing JDBC drivers
--err-log-path
Where to redirect STDERR
--help
Show help message
--http-port
Port to listen on
Default: 9019
--http-timeout
A timeout for dealing with database
Default: 1800
--listen-host
Host to listen on
Default: localhost
--log-level
Log level
Default: DEBUG
--log-path
Where to write logs
从最主要的开始说
--driver-path 这里指定一个存放jdbc驱动包的目录,如mysql-connector-java-5.1.38.jar注意不能放clickhouse的驱动包,至于为什么后面说
--listen-host不指定的话默认就是当前服务器地址,要与上面我们添加到clickhouse-server的config.xml的配置一致.
--http-port 不指定的话默认就是9019,与我们config.xml中的配置一致,如果不一样,需要指定为config.xml中配置的端口,不然clickhouse会找不到bridge服务
--datasources 用来配置我们jdbc引擎里面的url参数,这样就不需要创建jdbc引擎中强指定url(因为此url要指定账号跟密码),采用这种方式会更加安全与方便.
至于其他的log配置请自行根据情况配置.
JDBC引擎的创建表方式:
CREATE TABLE default.test
(
`id` Int8,
`name` String
)
ENGINE = JDBC('jdbc:clickhouse://node02:8123?user=default&password=', 'mydatabase', 'test')
JDBC中的参数第一个为url,url可以省略jdbc:,第二个参数为数据库,第三个为表名
基于上面我的示例参数,下面的都可以:
//url中不指定数据库
ENGINE = JDBC('jdbc:clickhouse://node02:8123?user=default&password=', 'mydatabase', 'test')
//省略`jdbc:`
ENGINE = JDBC('clickhouse://node02:8123?user=default&password=', 'mydatabase', 'test')
//url中指定数据库,第二个参数可以不填
ENGINE = JDBC('jdbc:clickhouse://node02:8123/mydatabase?user=default&password=', '', 'test')
//url中指定数据库,第二个参数也指定数据库
ENGINE = JDBC('jdbc:clickhouse://node02:8123/mydatabase?user=default&password=', 'mydatabase', 'test')
//url中指定数据库,第二个参数也指定数据库,以第二个参数中的数据库为准
ENGINE = JDBC('jdbc:clickhouse://node02:8123/default?user=default&password=', 'mydatabase', 'test')
还有另外一种指定url的方式是结合brige中的参数–datasources读取配置文件中的url
这种方式需要提前编写配置文件如:
vim datasource-config.txt (后缀名不重要)
datasource.myclickhouse=clickhouse://node02:8123/default?user=default&password=
启动brige服务时候指定–datasources 文件路径/datasource-config.txt
此时JDBC参数可以改为如下:
JDBC('datasource://myclickhouse', 'mydatabase', 'test')
参数说完了,在上面创建表时指定了id与name列,列的个数可以少,但是名字一定要一致,至于类型参考以下(只能参考,虽然是官网给的,但是有坑)
上面的都了解了以后,我们启动服务
java -jar clickhouse-jdbc-bridge-1.0.jar
然后创建表:
CREATE TABLE default.test
(
`id` Int8,
`name` String
)
ENGINE = JDBC('clickhouse://node02:8123/?user=default&password=', 'mydatabase', 'test')
select * from test;
┌─id─┬─name───────┐
│ 1 │ zhangsan ; │
└────┴────────────┘
下面说一下上面–driver-path中遗留的一个问题.我们这里是访问的clickhouse-jdbc,但是如果要访问mysql,是会报找不到driver的错误.同级目录下创建一个dirvers目录,用来存放驱动包,把mysql驱动包放到drivers目录后,启动bridge服务指定该目录:
java -jar clickhouse-jdbc-bridge-1.0.jar --datasources ./datasource-config.txt --driver-path ./drivers
这样就可以找到mysql的driver了,但是如果按照上面的方式启动,访问clickhouse时候,又会报找不到驱动包,然后把clickouse驱动包放到这个目录下,服务会启动不起来(报不能实例化ClickHouseDriver).
如果既要访问远程clickhouse又要访问其他的jdbc,可以将其他的驱动包放到clickhouse-jdbc-bridge-1.0.jar同一级目录下(还是不能有clickhouse驱动包),然后指定driver目录为当前目录
java -jar clickhouse-jdbc-bridge-1.0.jar --datasources ./datasource-config.txt --driver-path ./
所以综上我踩过的坑给大家建议,所有除了clickhouse的驱动包都放置到clickhouse-jdbc-bridge-1.0.jar同级目录下.指定驱动包为当前目录–driver-path ./
jdbc使用步骤
把clickhouse-jdbc-bridge插件 上传到服务器
导入jdbc mysql/oracle依赖
mkdir drivers
# rz上传mysql/oracle jar包
mysql-connector-java-5.1.34.jar oracle-jdbc-14.jar
3. 配置json文件（也可不配置json）
# 进入目标文档
cd /etc/clickhouse-jdbc-bridge/config/datasources
vi mysql.json
明：
mysql-server为这个bridge的别名；
driverUrl为存放mysql连接jar的地址
driverClassName、jdbcUrl、username、password自行修改
{
"$schema": "../datasource.jschema",
"mysql-server": {
"aliases": [
"self"
],
"driverUrls": [
"/etc/clickhouse-jdbc-bridge/drivers/mysql-connector-java-5.1.34.jar"
],
"driverClassName": "com.mysql.jdbc.Driver",
"jdbcUrl": "jdbc:mysql://0.0.0.0.:3306/test?compress=false",
"username": "root",
"password": "root",
"maximumPoolSize": 5
}
}
oracle.json如下
{
"$schema": "../datasource.jschema",
"oracle-server": {
"aliases": [
"oracle"
],
"driverUrls": [
"/etc/clickhouse-jdbc-bridge/drivers/ojdbc6-11.2.0.3.jar"
],
"driverClassName": "oracle.jdbc.driver.OracleDriver",
"jdbcUrl": "jdbc:oracle:thin:@0.0.0.0:1521:test",
"username": "root",
"password": "root",
"maximumPoolSize": 5
}
}
clickhouse Json 如下
{
"$schema": "../datasource.jschema",
"ckjdbc": {
"aliases": [
"self"
],
"driverUrls": [
"/server/hbase/drivers/phoenix-server-hbase-2.1-5.1.2.jar"
],
"driverClassName": "org.apache.phoenix.jdbc.PhoenixDriver",
"jdbcUrl": "jdbc:phoenix:101.34.236.169:2181:/hbase",
"username": "",
"password": "",
"maximumPoolSize": 5
}
}
4. 启动clickhouse-jdbc-bridge
[root@VM-16-8-centos hbase]# java -jar clickhouse-jdbc-bridge-2.0.4-SNAPSHOT.jar
同步mysql到 clickhouse
mysql同步语句如下
CREATE TABLE default.test
(
`id` Int32,
`one` String,
`two` String
)
ENGINE = JDBC('jdbc:mysql:127.0.0.1:3306/?user=root&password=root', 'test', 'test')
clickhouse的同步语句
CREATE TABLE default.test2
(
`ID` Int32,
`NAME` String
)
ENGINE = JDBC('ckjdbc', 'WYS', 'TEST')
#创建ck 库
CREATE TABLE test3(id Int32, NAME String) ENGINE = MergeTree
ORDER BY id
SETTINGS index_granularity = 8192;
#同步数据
INSERT INTO test3 SELECT * FROM jdbc('ckjdbc', 'SELECT * FROM WYS.TEST');
执行成功如下
