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
  }
}

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 JDBC表引擎(clickhouse-jdbc-bridge)
Clickhouse通过jdbc连接获取Mysql/Oracle数据

如何通过JDBC将SQLServer导入Clickhouse