如何通过JDBC将mysql导入Clickhouse
1. 部署clickhouse-jdbc-bridge
clickhouse-jdbc-bridge是通过jdbc将Clickhouse与其他数据沟通的桥梁,可将其他数据库数据导入clickhouse中,也可以直接在clickhouse中访问其他数据库。
1.2 重新编译bridge
git clone https://github.com/ClickHouse/clickhouse-jdbc-bridge.git
cd clickhouse-jdbc-bridge
mvn -Drevision=2.0.0 package
1.3 下载Microsoft JDBC驱动
把下载的驱动放到clickhouse-jdbc-bridge目录下的drivers子目录
本次JDBC版本为:mssql-jdbc-8.4.1.jre14.jar
注意:jdbc版本需与Ubuntu环境中的java版本一致
2. 配置clickhouse-jdbc-bridge
cd target
mkdir -p config/datasources #创建config目录及datasources子目录用于存放配置
在datasources目录下创建数据源配置文件,msjdbc.json 文件名尽量与配置中的Datasource名一致,加入以下配置:
{
"$schema": "../datasource.jschema",
"msjdbc": {
"aliases": [
"self"
],
"driverUrls": [
"/server/hbase/mysql-connector-java-8.0.27.jar"
],
"driverClassName": "com.mysql.jdbc.Driver",
"jdbcUrl": "jdbc:mysql://127.0.0.1:3306/test?compress=false",
"username": "root",
"password": "root",
"maximumPoolSize": 5
}
}
3. 运行clickhouse-jdbc-bridge
cd target
java -jar clickhouse-jdbc-bridge-2.0.0.jar
#如果成功运行,会出现已加载的数据源信息,如前面的msjdbc。
4. 使用clickhouse-jdbc-bridge迁移数据
4.1 先用DBeaver或clickhouse-client创建表
CREATE TABLE test(id String, operateTime DateTime) ENGINE = MergeTree
PARTITION BY toYYYYMM(operateTime)
ORDER BY id
SETTINGS index_granularity = 8192;
4.2 进入clickhouse-client 执行insert into语句
INSERT INTO test SELECT * FROM jdbc('msjdbc', 'SELECT * FROM dbo.test');
#创建可视化连接 数据是读的mysql
CREATE TABLE default.test2
(
`id` Int32,
`one` String,
`two` String
)
ENGINE = JDBC('msjdbc', 'test', 'test')
#创建ck 库
CREATE TABLE test(id Int32, one String,two String) ENGINE = MergeTree
ORDER BY id
SETTINGS index_granularity = 8192;
#同步数据
INSERT INTO test SELECT * FROM jdbc('msjdbc', 'SELECT * FROM test.test');