Skip to main content

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

· 3 min read


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


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: (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

Database and table created in MSSQL:

Screenshot 2024-01-01 at 8 25 50 PM

MSSQL Login User, sql_user:

Screenshot 2024-01-01 at 8 27 11 PM

Database membership roles for sql_user:

Screenshot 2024-01-01 at 8 27 35 PM

Database User with Login:

Screenshot 2024-01-01 at 8 35 34 PM

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:

Driver = FreeTDS
Server =

Create an odbcinst.ini file:

vim odbcinst.ini

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

Trace = Yes
TraceFile = /tmp/odbc.log

Description = FreeTDS
Driver = /usr/lib/aarch64-linux-gnu/odbc/
Setup = /usr/lib/x86_64-linux-gnu/odbc/
UsageCount = 1

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'
image: marsnet/clickhouse-odbc:23.10
container_name: clickhouse-odbc
hostname: clickhouse-host
- "9000:9000"
- "8123:8123"
- "9009:9009"
- ./odbc.ini:/etc/odbc.ini
- ./odbcinst.ini:/etc/odbcinst.ini
restart: always
soft: -1
hard: -1
soft: 262144
hard: 262144
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
87a400b803ce marsnet/clickhouse-odbc:23.10 "/" 57 minutes ago Up About a minute>8123/tcp,>9000/tcp,>9009/tcp clickhouse-odbc

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');

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

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

│ 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;

FROM table1_odbc_mssql

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

│ 1 │ abc │
│ 2 │ def │
│ 3 │ ghi │

3 rows in set. Elapsed: 0.218 sec.

For more information, please see: