[Yandex Cloud documentation](../../index.md) > [Tutorials](../index.md) > [Building a data platform](index.md) > Yandex Managed Service for ClickHouse® integration with Microsoft SQL Server via ClickHouse® JDBC Bridge

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

With [ClickHouse® JDBC Bridge](https://clickhouse.com/docs/enen/integrations/jdbc/jdbc-with-clickhouse), you can:

* [Query a table](#jdbc-table-function) in an external Microsoft SQL Server database using the [JDBC table function](https://clickhouse.com/docs/enen/sql-reference/table-functions/jdbc).
* [Create ClickHouse® tables](#jdbc-table-engine) linked to corresponding tables in an external Microsoft SQL Server database, using the [JDBC table engine](https://clickhouse.com/docs/enen/engines/table-engines/integrations/jdbc).

## Get your cloud ready {#before-begin}

Sign up for Yandex Cloud and create a [billing account](../../billing/concepts/billing-account.md):
1. Navigate to the [management console](https://console.yandex.cloud) and log in to Yandex Cloud or create a new account.
1. On the **[Yandex Cloud Billing](https://center.yandex.cloud/billing/accounts)** page, make sure you have a billing account linked and it has the `ACTIVE` or `TRIAL_ACTIVE` [status](../../billing/concepts/billing-account-statuses.md). If you do not have a billing account, [create one](../../billing/quickstart/index.md) and [link](../../billing/operations/pin-cloud.md) a cloud to it.

If you have an active billing account, you can create or select a [folder](../../resource-manager/concepts/resources-hierarchy.md#folder) for your infrastructure on the [cloud page](https://console.yandex.cloud/cloud).

[Learn more about clouds and folders here](../../resource-manager/concepts/resources-hierarchy.md).


### Required paid resources {#paid-resources}

The solution support costs include:

* Fee for a Managed Service for ClickHouse® cluster: use of computing resources allocated to hosts (including ZooKeeper hosts) and disk space (see [Managed Service for ClickHouse® pricing](../../managed-clickhouse/pricing.md)).
* Fee for the NAT gateway if public access is not enabled for cluster hosts (see [Virtual Private Cloud pricing](../../vpc/pricing.md)).
* Fee for public IP addresses if public access is enabled for cluster hosts (see [Virtual Private Cloud pricing](../../vpc/pricing.md)).


## Set up the infrastructure {#deploy-infrastructure}

1. [Create a security group](../../vpc/operations/security-group-create.md) and [configure it](../../managed-clickhouse/operations/connect/index.md#configuring-security-groups).

    Add the following egress rule:

    * **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 such as Microsoft SQL Server.

1. [Create a Managed Service for ClickHouse® cluster](../../managed-clickhouse/operations/cluster-create.md).

    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`.

1. If you do not plan to enable public access to your Managed Service for ClickHouse® cluster, create a [NAT gateway](../../vpc/operations/create-nat-gateway.md) for the subnet where your cluster will reside.

## Prepare the external Microsoft SQL Server database {#prepare-source}

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

1. Connect to your Microsoft SQL Server host and download the [JDBC driver](https://mvnrepository.com/artifact/com.microsoft.sqlserver/mssql-jdbc) into the `/opt/drivers` directory:

    ```bash
    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'
    ```

1. Install [Docker Engine](https://docs.docker.com/engine/install/).

1. Run ClickHouse® JDBC Bridge:

    ```bash
    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:

    ```bash
    docker container restart jdbc_bridge
    ```

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

    ```sql
    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

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

    ```bash
    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

1. Connect using the `jdbc_user` account:

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

1. Create a test dataset:

    ```sql
    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 function {#jdbc-table-function}

1. [Connect to the Managed Service for ClickHouse® cluster](../../managed-clickhouse/operations/connect/index.md).

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

    ```sql
    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 engine {#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](../../managed-clickhouse/operations/connect/index.md).

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

    ```sql
    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.

1. Check the result:

    ```sql
    SELECT * FROM mssql_employees;
    ```

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

## Delete the resources you created {#clear-out}

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](../../managed-clickhouse/operations/cluster-delete.md).
* [Delete the NAT gateway](../../vpc/operations/delete-nat-gateway.md).

_ClickHouse® is a registered trademark of [ClickHouse, Inc](https://clickhouse.com)._