ClickHouse JDBC表引擎(clickhouse-jdbc-bridge)

ClickHouse允许使用jdbc连接到远程数据库(PostgreSQL,SQLite,H2,Mysql,ClickHouse等)进行数据查询
这种引擎clickhouse自己无法完成,需要另外一个服务的支持:clickhouse-jdbc-bridge.它的作用是接收ClickHouse的http请求,然后去远程查询结果,之后再反馈给ClickHouse.
要使用这个引擎,通常会碰到各种各样的坑,希望我这篇文章能帮助大部分人避免一些坑.
jdbc-bridge服务需要自己打包部署,或者百度别人打包好的jar包.
jdbc-bridge 的github地址:clickhouse-jdbc-bridge
会java的话,从Git上面拉下来以后在idea或者eclipse中执行mvn的clean packge打包完成后到打包目录里面找到clickhouse-jdbc-bridge-1.0.jar(版本号可能不一样),这个就是我们要额外启动的服务
通过rz命令将jar包上传到服务器.
上面的步骤不会可以私信我,我发jar包给你.
此时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-1.0.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 ; │
└────┴────────────┘

访问成功,注意如果我们搭建的是虚拟机,必须要保证有充足的内存,如果内存少于1个g,访问jdbc引擎会报错(我在实际中遇到的问题)

下面说一下上面–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 ./
另外,其实访问远程clickhouse其实是要使用Remote引擎.

另外及其有可能会碰到这个错误:

Code: 33. DB::Exception: Received from localhost:9000. DB::Exception: Cannot read all data. 
Bytes read: 7. Bytes expected: 122.: While executing JDBC. 

这个错误是因为创建JDBC表时,错误的指定了数据类型导致的,上面给的官网的类型对应是有问题的,只能用来参考,因为我mysql中id列类型为tinyint,我创建jdbc表时指定id为Int8是会报上面的错误,但是指定Int16就可以正常访问,

所以建议初次搭建练习时,使用jdbc表函数先访问,可以避免类型转换的错
jdbc表函数中的参数与JDBC引擎中的参数是一样的.

node01.hadoop.com :) select * from jdbc('jdbc:mysql://node03:3306?user=root&password=123456', 'test', 'test');

┌─id─┬─name─────┐
│  1 │ zhangsan │
└────┴──────────┘

1 rows in set. Elapsed: 0.060 sec. 

总结:

1

 DB::Exception: jdbc-bridge is not running. Please, start it manually. 

如果碰到这个错误,先看bidge是否启动,如果启动了,再看bridge启动日志是否报错,如果以上均没有,请查看clickhosue-server 中的 <jdbc_bridge>配置,端口与ip是否对应.
2
如果报内存的错误,请保证运行内存在1g以上
3

 No suitable driver found for jdbc:mysql://node03:3306user=root&password=123456

先查看url是否正确.指定驱动包就使用--driver-path ./,所有除了clickhouse的驱动包都放置到clickhouse-jdbc-bridge-1.0.jar同级目录下.

4

Cannot read all data. 
Bytes read: 7. Bytes expected: 122.: While executing JDBC. 

请先使用jdbc函数访问,再排查类型转换的问题.