Select from MySql to Clickhouse

The ClickHouse JDBC Bridge allows ClickHouse to access data from any external data source for which a JDBC driver is available.

• You can use the ClickHouse JDBC Bridge for both reads and writes.
• You can use in parallel for multiple external data sources.
• You can run distributed queries on ClickHouse across multiple external and internal data sources in real time.

PREREQUISITES

You have access to a machine that has:

1. a Unix shell and internet access
2. wget installed
3. a current version of Java (e.g. OpenJDK Version >= 17) installed
4. a current version of MySQL (e.g. MySQL Version >=8) installed and running
5. a current version of ClickHouse installed and running

Configure MySQL

First of all, we need a table with some datas and user with enough privileges in MySql. That is why we are going to create database and table and user, respectively.

el2@m:$ sudo mysql
# Create a database in MySQL
mysql> CREATE DATABASE db1;
# Create a table
mysql> CREATE TABLE db1.table1 (
        id INT,
        column1 VARCHAR(255)
       );
# Insert sample rows
mysql> INSERT INTO db1.table1 
        (id, column1) 
       VALUES 
        (1, 'abc'),
        (2, 'def'),
        (3, 'ghi');
# Create a user to connect from ClickHouse
mysql> CREATE USER 'mysql_clickhouse'@'%' IDENTIFIED BY 'Password123!';
#  Grant privileges as needed. (For demonstration purposes, the mysql_clickhouse user is granted admin prvileges.)
mysql> GRANT ALL PRIVILEGES ON *.* TO 'mysql_clickhouse'@'%';

Configure ClickHouse JDBC Bridge

Create a local folder where we will later install the ClickHouse JDBC Bridge into (feel free to name the folder anything you like and put it anywhere you like):

el2@m:$ mkdir ~/clickhouse-jdbc-bridge
el2@m:$ cd ~/clickhouse-jdbc-bridge
# Now we download the current version of the ClickHouse JDBC Bridge into that folder:
el2@m:$ wget…