ClickHouse JDBC Bridge 的安装及连接sqlserver
用于ClickHouse的JDBC链接桥。 它充当无状态代理,将查询从ClickHouse传递到外部数据源。 使用此扩展,您可以跨多个数据源实时在ClickHouse上运行分布式查询,从而以某种方式简化了用于数据仓库,监视和完整性检查等的数据管道的构建过程。
安装环境:
[root@node220 ~]# cat /etc/redhat-release
CentOS Linux release 7.6.1810 (Core)
[root@node220 ~]# uname -a
Linux node220 3.10.0-957.el7.x86_64 #1 SMP Thu Nov 8 23:39:32 UTC 2018 x86_64 x86_64 x86_64 GNU/Linux
[root@node220 ~]#
SQLSERVER: 2012 Enterprise
jdbc软件:clickhouse-jdbc-bridge-2.0.2-1.noarch
SQLSERVER驱动:mssql-jdbc-8.4.1.jre8.jar
获取数据流程:
clickhouse-server -> jdbc -> SQLSERVER2012
RHCE 7.9
软件安装:
CentOS/RHEL
yum install -y wget
wget https://github.com/ClickHouse/clickhouse-jdbc-bridge/releases/download/v2.0.2/clickhouse-jdbc-bridge-2.0.2-1.noarch.rpm
yum localinstall -y clickhouse-jdbc-bridge-2.0.2-1.noarch.rpm
ClickHouse JDBC Bridge安装完成之后,即可使用它连接其他的数据库,如MySQL,SQL server等
以SQL server举例:
在/etc/clickhouse-jdbc-bridge目录下新建drivers文件夹,将SQL server驱动jar包放入其中
SQL server驱动jar包:
https://github.com/microsoft/mssql-jdbc/releases/download/v8.4.1/mssql-jdbc-8.4.1.jre8.jar
在/etc/clickhouse-jdbc-bridge/config/datasource目录下,新建msjdbc.json文件
[root@node220 datasources]# pwd
/etc/clickhouse-jdbc-bridge/config/datasources
[root@node220 datasources]# ls
datasource.json.example msjdbc.json script.json
配置文件如下:
cat msjdbc.json
{
"$schema": "../datasource.jschema",
"ch-server": {
"aliases": [
"self"
],
"driverUrls": [
"https://repo1.maven.org/maven2/ru/yandex/clickhouse/clickhouse-jdbc/0.2.5/clickhouse-jdbc-0.2.5-shaded.jar"
],
"driverClassName": "ru.yandex.clickhouse.ClickHouseDriver",
"jdbcUrl": "jdbc:clickhouse://ch-server:8123/system?compress=false",
"username": "default",
"password": "",
"maximumPoolSize": 5
},
"msjdbc": {
"driverUrls": [
"/etc/clickhouse-jdbc-bridge/drivers/mssql-jdbc-8.4.1.jre8.jar"
],
"driverClassName": "com.microsoft.sqlserver.jdbc.SQLServerDriver",
"jdbcUrl": "jdbc:sqlserver://172.16.11.115:1433;DatabaseName=test",
"username": "sa",
"password": "rscpass"
}
}
启动服务:
[root@node220 ~]# nohup /usr/local/bin/clickhouse-jdbc-bridge &
如果在连接过程中,出现连接错误的情况,请打开sqlserver配置管理器,查看
开启sqlserver的tcp/ip连接
在sqlserver mangement网络服务中,开启tcp/ip的端口,主要是IP0,IP1,ipall
测试:
在clickhouse-client中查询SQLSERVER远程数据库中的数据:
SELECT * FROM jdbc('msjdbc', 'SELECT * FROM dbo.z_user_favorite');
方法一:使用jdbc表引擎创建clickhouse数据表,这种方式当SQLSERVER关机时,无法查询到数据
CREATE TABLE z_user_favorite (id Int64,store_id Int64,goods_id Int64,user_id Int64,create_time Int64,type Int64,visitor_id Int64 ) ENGINE = JDBC('jdbc:sqlserver://172.16.11.115:1433;DatabaseName=test;user=sa;password=rscpass', '', 'z_user_favorite')
方法二:创建单独复制表:当SQLSERVER关闭时,可以查询到本地的数据
CREATE TABLE z_user_favorite (id Int64,store_id Int64,goods_id Int64,user_id Int64,create_time Int64,type Int64,visitor_id Int64 ) ENGINE = MergeTree ORDER BY id SETTINGS index_granularity = 8192;
插入数据:
INSERT INTO z_user_favorite SELECT * FROM jdbc('msjdbc', 'SELECT * FROM dbo.z_user_favorite');
官方查询案例:
![分割线](/images/fengexian.gif)
![感谢打赏](/images/weixin.jpg)
![江西数库信息技术有限公司](/images/shukuinfo.jpg)