title_wip

How to set up ClickHouse on Docker with ODBC to connect to a Microsoft SQL Server (MSSQL) database


## Question[​](#question "Direct link to Question")

How do I set up ClickHouse with a Docker image to connect to Microsoft SQL Server?

## Answer[​](#answer "Direct link to Answer")

***Notes on this example***

- Uses the ClickHouse Docker Ubuntu image
- Uses the FreeTDS Driver
- Uses MSSQL Server 2012R2
- Windows hostname for this example is `MARSDB2.marsnet2.local` at IP: `192.168.1.133` (update with your hostname and/or IP)
- MSSQL Instance name `MARSDB2`
- MSSQL Login and datbase users are `sql_user`

### Example setup in MSSQL for testing ###[​](#example-setup-in-mssql-for-testing- "Direct link to Example setup in MSSQL for testing ###")

Database and table created in MSSQL:

MSSQL Login User, `sql_user`:

Database membership roles for `sql_user`:

Database User with Login:

### Configuring ClickHouse with ODBC[​](#configuring-clickhouse-with-odbc "Direct link to Configuring ClickHouse with ODBC")

Create a working directory:

```
mkdir ch-odbc-mssql
cd ch-odbc-mssql
```

Create an `odbc.ini` file:

```
vim odbc.ini
```

Add the following entries to update the name of the DSN and IP:

```
[marsdb2_mssql]
Driver = FreeTDS
Server = 192.168.1.133
```

Create an `odbcinst.ini` file:

```
vim odbcinst.ini
```

Add the following entries (trace is optional but helps with debugging):

```
[ODBC]
Trace = Yes
TraceFile = /tmp/odbc.log

[FreeTDS]
Description = FreeTDS
Driver = /usr/lib/aarch64-linux-gnu/odbc/libtdsodbc.so
Setup = /usr/lib/x86_64-linux-gnu/odbc/libtdsS.so
UsageCount = 1
```

### Configure a Dockerfile to download the image and add the TDS and required ODBC libraries[​](#configure-a-dockerfile-to-download-the-image-and-add-the-tds-and-required-odbc-libraries "Direct link to Configure a Dockerfile to download the image and add the TDS and required ODBC libraries")

Create the Dockerfile:

```
vim Dockerfile
```

Add the contents of the Dockerfile:

```
FROM clickhouse/clickhouse-server:23.10

# Install the ODBC driver

RUN apt-get update && apt-get install -y --no-install-recommends unixodbc \
    && apt-get install -y freetds-bin freetds-common freetds-dev libct4 libsybdb5 \
	&& apt-get install tdsodbc
```

Build the new docker image:

```
docker build . -t marsnet/clickhouse-odbc:23.10
```

Create a `docker-compose.yml` file:

```
vim docker-compose.yml
```

Add the following contents to the YAML:

```
version: '3.7'
services:
  clickhouse:
    image: marsnet/clickhouse-odbc:23.10
    container_name: clickhouse-odbc
    hostname: clickhouse-host
    ports:
      - "9000:9000"
      - "8123:8123"
      - "9009:9009"
    volumes:
      - ./odbc.ini:/etc/odbc.ini
      - ./odbcinst.ini:/etc/odbcinst.ini
    restart: always
    ulimits:
      memlock:
        soft: -1
        hard: -1
      nofile:
        soft: 262144
        hard: 262144
    deploy:
      resources:
        limits:
          memory: 4g
```

Start the container:

```
docker compose up --detach
```

After you start the container, you should see something like this:

```
ch-odbc-mssql % docker compose up --detach
[+] Running 1/1
 ✔ Container clickhouse-odbc  Started
```

Check to ensure the container is running:

```
ch-odbc-mssql % docker ps
CONTAINER ID   IMAGE                           COMMAND            CREATED          STATUS              PORTS                                                                    NAMES
87a400b803ce   marsnet/clickhouse-odbc:23.10   "/entrypoint.sh"   57 minutes ago   Up About a minute   0.0.0.0:8123->8123/tcp, 0.0.0.0:9000->9000/tcp, 0.0.0.0:9009->9009/tcp   clickhouse-odbc
```

### Test ODBC connection[​](#test-odbc-connection "Direct link to Test ODBC connection")

Login with the ClickHouse client:

```
./clickhouse client
```

Test the `SELECT` using the `odbc` table function to the remote MSSQL Database table:

```
clickhouse-host :) SELECT * from odbc('DSN=marsdb2_mssql;port=1433;Uid=sql_user;Pwd=ClickHouse123;Database=db1', 'table1');

SELECT *
FROM odbc('DSN=marsdb2_mssql;port=1433;Uid=sql_user;Pwd=ClickHouse123;Database=db1', 'table1')

Query id: 23494da2-6e12-4ade-95fa-372a0420cac1

┌─id─┬─column1─┐
│  1 │ abc     │
│  2 │ def     │
│  3 │ ghi     │
└────┴─────────┘

3 rows in set. Elapsed: 0.188 sec.
```

You can also create a remote table using the `odbc` table engine:

```
CREATE TABLE table1_odbc_mssql
(
    `id` Int32,
    `column1` String
)
ENGINE = ODBC('DSN=marsdb2_mssql;port=1433;Uid=sql_user;Pwd=ClickHouse123;Database=db1', 'dbo', 'table1')
```

Use a `SELECT` query to test the new remote table:

```
clickhouse-host :) select * from table1_odbc_mssql;

SELECT *
FROM table1_odbc_mssql

Query id: 94724368-485d-4364-ae58-a435a225c37d

┌─id─┬─column1─┐
│  1 │ abc     │
│  2 │ def     │
│  3 │ ghi     │
└────┴─────────┘

3 rows in set. Elapsed: 0.218 sec.
```

For more information, please see:

- <https://hub.docker.com/_/clickhouse>
- <https://clickhouse.com/docs/engines/table-engines/integrations/odbc>
- <https://github.com/ClickHouse/clickhouse-odbc>

May 29, 2024 · 4 min read