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

With ClickHouse® JDBC Bridge, you can:

• Query a table in an external Microsoft SQL Server database using the JDBC table function.
• Create ClickHouse® tables linked to corresponding tables in an external Microsoft SQL Server database, using the JDBC table engine.

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 create or select a folder for your infrastructure on the cloud page.

Learn more about clouds and folders here.

Required paid resourcesRequired paid resources

• Managed Service for ClickHouse® cluster: use of computing resources allocated to hosts, storage and backup size (see Managed Service for ClickHouse® pricing).
• NAT gateway: hourly use of the gateway and its outgoing traffic if public access is not enabled for cluster hosts (see Virtual Private Cloud pricing).
• Public IP addresses if public access is enabled for cluster hosts (see Virtual Private Cloud pricing).

Prepare your infrastructurePrepare your infrastructure

1. Create a security group and configure it.

   Add the following egress rule:

   • Port range: 0-65535.
   • Protocol: TCP.
   • Source: Address range.
   • IPv4 CIDR: 0.0.0.0/0.

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

2. Create a Managed Service for ClickHouse® cluster.

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

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

   • Host: Microsoft SQL Server IP address.
   • Port: 9019.

3. If you do not plan to enable public access to your Managed Service for ClickHouse® cluster, create a NAT gateway for the subnet where your cluster will reside.

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

1. Make sure the host where your Microsoft SQL Server database is installed allows connection on ports 9019 and 1433.

2. Connect to your Microsoft SQL Server host 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 ClickHouse® JDBC Bridge container is already running, restart it to load the new drivers:

   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
   

   The <user_password> must be at at least eight characters long and contain at least three of the following four character types:

   • Uppercase letters
   • Lowercase letters
   • Numbers
   • Special characters

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

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

   Where:

   • <Microsoft_SQL_Server_host>: Microsoft SQL Server IP address.
   • <administrator_password>: SA password.

   The script will create the following entities:

   • mydb database
   • jdbc_user user
   • jdbc_schema schema

7. Connect using the jdbc_user account:

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

8. Create a test dataset:

   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 the data using the JDBC table functionQuery the 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 password.
   • <Microsoft_SQL_Server_DB_host>: Microsoft SQL Server IP address.

   If successful, the query will return 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 using the SELECT statement. To use the JDBC table engine:

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

2. Create a table with the JDBC table engine that links to an external Microsoft SQL Server table.

   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 create a table with only a subset of columns defined.

3. Check the result:

   SELECT * FROM mssql_employees;
   

   If created successfully, querying the table will return data from the external Microsoft SQL Server database.

Delete the resources you createdDelete the resources you created

Some resources are not free of charge. Delete the resources you no longer need to avoid paying for them:

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

ClickHouse® is a registered trademark of ClickHouse, Inc.