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');

执行成功如下