Yandex Managed Service for ClickHouse® integration with external Microsoft SQL Server database via ClickHouse® JDBC Bridge

With ClickHouse® JDBC Bridge, you can:

• Query data from an external Microsoft SQL Server database table using the JDBC table function.
• Use the JDBC table engine to create tables in ClickHouse® which reference a table in an external Microsoft SQL Server database.

Get your cloud readyGet your cloud ready

Sign up for Yandex Cloud and create a billing account:

1. Navigate to the management console and log in to Yandex Cloud or create a new account.
2. On the Yandex Cloud Billing page, make sure you have a billing account linked and it has the ACTIVE or TRIAL_ACTIVE status. If you do not have a billing account, create one and link a cloud to it.

If you have an active billing account, you can navigate to the cloud page to create or select a folder for your infrastructure.

Learn more about clouds and folders.

Required paid resourcesRequired paid resources

The solution support costs include:

• Fee for a Managed Service for ClickHouse® cluster: using computing resources allocated to hosts (including ZooKeeper hosts) and disk space (see Managed Service for ClickHouse® pricing).
• NAT gateway fee if public access is not enabled for cluster hosts (see Virtual Private Cloud pricing).
• Fee for using public IP addresses if public access is enabled for cluster hosts (see Virtual Private Cloud pricing).

Set up your infrastructureSet up your infrastructure

1. Create a security group and configure it.

   Also add a rule for outgoing traffic:

   • Port range: 0-65535
   • Protocol: TCP
   • Source: CIDR
   • CIDR blocks: 0.0.0.0/0

   This rule allows all outgoing traffic, enabling ClickHouse® JDBC Bridge to connect to external databases including Microsoft SQL Server.

2. Create a Managed Service for ClickHouse® cluster.

   When creating a cluster, specify the security group prepared earlier.

   Under DBMS settings, click Settings and add the jdbcBridge option with the following parameters:

   • Host: IP address of your Microsoft SQL Server database installation.
   • Port: 9019.

3. If you do not use public access, create a NAT gateway for the subnet you want to create your Managed Service for ClickHouse® cluster in.

Prepare the external Microsoft SQL Server databasePrepare the external Microsoft SQL Server database

1. Make sure your external Microsoft SQL Server database installation allows connection via ports 9019 and 1433.

2. Connect to your external Microsoft SQL Server database installation and download the JDBC driver into the /opt/drivers directory:

   sudo mkdir -p /opt/drivers && \
   curl -s https://repo1.maven.org/maven2/com/microsoft/sqlserver/mssql-jdbc/ | grep -oP '(?<=href=")[^"]+(?=/")' | grep 'jre8$' | grep -v 'preview' | sort -V | tail -n1 | xargs -I{} bash -c 'ver="{}"; file=$(curl -s https://repo1.maven.org/maven2/com/microsoft/sqlserver/mssql-jdbc/$ver/ | grep -oP "(?<=href=\")[^\"]+\.jar" | grep -vE "javadoc|sources" | head -n1); sudo curl -o /opt/drivers/$file https://repo1.maven.org/maven2/com/microsoft/sqlserver/mssql-jdbc/$ver/$file'
   

3. Install Docker Engine.

4. Run ClickHouse® JDBC Bridge:

   docker run -d --name jdbc_bridge --network host -v /opt/drivers:/app/drivers clickhouse/jdbc-bridge
   

   If your container is already running, restart it to load the new drivers to ClickHouse® JDBC Bridge:

   docker container restart jdbc_bridge
   

5. Create a file named init.sql with the following contents:

   CREATE LOGIN jdbc_user WITH PASSWORD = '<user_password>';
   GO
   CREATE DATABASE mydb;
   GO
   USE mydb;
   GO
   CREATE USER jdbc_user FOR LOGIN jdbc_user;
   GO
   ALTER ROLE db_owner ADD MEMBER jdbc_user;
   GO
   CREATE SCHEMA jdbc_schema AUTHORIZATION jdbc_user;
   GO
   ALTER USER jdbc_user WITH DEFAULT_SCHEMA = jdbc_schema;
   GO
   

   Where <user_password> must be at at least eight characters long and contain at least three of these four character types:

   • Uppercase letters
   • Lowercase letters
   • Numbers
   • Special characters

6. Execute the init.sql script via sqlcmd as the SA admin user:

   sqlcmd -S <Microsoft_SQL_Server_host> -U SA -P '<administrator_password>' -i init.sql
   

   Where:

   • <Microsoft_SQL_Server_host>: IP address of your Microsoft SQL Server installation.
   • <administrator_password>: SA admin account password.

   This script creates:

   • mydb database
   • jdbc_user
   • jdbc_schema

7. Connect as the new jdbc_user user:

   sqlcmd -S <Microsoft_SQL_Server_host> -U jdbc_user -P '<user_password>' -i init.sql
   

8. Create a set of test data:

   CREATE TABLE Employees (
       Id INT PRIMARY KEY IDENTITY(1,1),
       Name NVARCHAR(100),
       Position NVARCHAR(100),
       Salary DECIMAL(10,2)
   );
   GO
   
   INSERT INTO Employees (Name, Position, Salary) VALUES
   ('Alice Johnson', 'Developer', 75000),
   ('Bob Smith', 'Manager', 90000),
   ('Charlie Rose', 'Analyst', 65000);
   GO
   
   SELECT * FROM Employees;
   GO
   

Query data using the JDBC table functionQuery data using the JDBC table function

1. Connect to the Managed Service for ClickHouse® cluster.

2. Send a query to the external Microsoft SQL Server database using the JDBC table function:

   SELECT * FROM jdbc('jdbc:sqlserver://<Microsoft_SQL_Server_DB_host>:1433;databaseName=mydb;user=jdbc_user;password=<user_password>;encrypt=false;', 'jdbc_schema', 'Employees')
   

   Where:

   • <user_password>: jdbc_user user password.
   • <Microsoft_SQL_Server_DB_host>: IP address of your Microsoft SQL Server database installation.

   If the query is successful, you will get data from the external Microsoft SQL Server database.

Create a table using the JDBC table engineCreate a table using the JDBC table engine

With the JDBC table engine, you can query data via SELECT. To use the JDBC table engine:

1. Connect to the Managed Service for ClickHouse® cluster.

2. Create a table with the JDBC table engine based on the table from the external Microsoft SQL Server database.

   CREATE TABLE mssql_employees
   (
       Id Int32,
       Name String,
       Position String,
       Salary Decimal(10, 2)
   )
   ENGINE = JDBC(
       'jdbc:sqlserver://<Microsoft_SQL_Server_DB_host>:1433;databaseName=mydb;user=jdbc_user;password=<user_password>;encrypt=false;',
       'jdbc_schema',
       'Employees'
   );
   

   You can also create a table by specifying only some of the fields.

3. Check the result:

   SELECT * FROM mssql_employees;
   

   If the table was created correctly, the output will return data from the external Microsoft SQL Server database table.

Delete the resources you createdDelete the resources you created

Some resources are not free of charge. To avoid paying for them, delete the resources you no longer need:

• Delete the Managed Service for ClickHouse® cluster.
• Delete the NAT gateway.

ClickHouse® is a registered trademark of ClickHouse, Inc.