1. The deployment clickhouse – JDBC – bridge

Clickhouse-jdbc-bridge is a bridge between ClickHouse and other data via JDBC. You can import data from other databases into Your Clickhouse or access other databases directly from your Clickhouse.

1.1 Deployment Environment

Ubuntu 16.04 64

1.2 Recompiling the Bridge

Git clone https://github.com/ClickHouse/clickhouse-jdbc-bridge.git CD clickhouse - JDBC - bridge MVN - Drevision = 2.0.0 packageCopy the code

1.3 Downloading the Microsoft JDBC driver

Place the downloaded driver in the Drivers subdirectory under the Clickhouse-JdbC-Bridge directory

The JDBC version is mSSQL-JDBC-8.4.1.jre14.jar

Note: The JDBC version must be consistent with the Java version in the Ubuntu environment

2. Configuration clickhouse – JDBC – bridge

CD target mkdir -p config/datasources # Create the config directory and the datasources subdirectory to store the configurationCopy the code

Create the Datasource configuration file in the datasources directory with the msjdbc.json file name as consistent as possible. Add the following configuration:

{" MSJDBC ": {"driverUrls": ["/clickhouse-jdbc-bridge/drivers/ mssqL-jdbC-8.4.1.jre14.jar "], "driverClassName": ["/clickhouse-jdbc-bridge/drivers/ mssqL-jdbC-8.4.1.jre14.jar "], "driverClassName": "Com. Microsoft. Essentially. JDBC. SQLServerDriver", "jdbcUrl" : "JDBC: essentially: / / 192.168.1.196:1433; databaseName=CERSv5;" , "username": "xxx", "password": "xxx" } }Copy the code

3. Run clickhouse – JDBC – bridge

CD target java-jar clickhouse- jdbC-bridge-2.0.0. jar # If run successfully, loaded data source information, such as MSJDBC, will appear.Copy the code

4. Use Clickhouse-JDBC-Bridge to migrate data

4.1 Create the table with DBeaver or Clickhouse-client first

CREATE TABLE test(id String, operateTime DateTime) ENGINE = MergeTree
PARTITION BY toYYYYMM(operateTime)
ORDER BY id
SETTINGS index_granularity = 8192;
Copy the code

4.2 Enter clickhouse-client and execute insert into statement

INSERT INTO test SELECT * FROM jdbc('msjdbc', 'SELECT * FROM dbo.test');
Copy the code

5. Official examples: