Skip to content
ClickHouse Docs
ClickHouse DocsClickHouse Docs
49.6kGet Started

ODBC dictionary source

View as MarkdownEdit this pageReport an issueSet up docs MCP

You can use this method to connect any database that has an ODBC driver.

Example of settings:

SOURCE(ODBC(
    db 'DatabaseName'
    table 'SchemaName.TableName'
    connection_string 'DSN=some_parameters'
    invalidate_query 'SQL_QUERY'
    query 'SELECT id, value_1, value_2 FROM db_name.table_name'
))

Setting fields:

Setting Description
db Name of the database. Omit it if the database name is set in the <connection_string> parameters.
table Name of the table and schema if exists.
connection_string Connection string.
invalidate_query Query for checking the dictionary status. Optional. Read more in the section Refreshing dictionary data using LIFETIME.
background_reconnect Reconnect to replica in background if connection fails. Optional.
query The custom query. Optional.

ClickHouse receives quoting symbols from ODBC-driver and quote all settings in queries to driver, so it’s necessary to set table name accordingly to table name case in database.

If you have a problems with encodings when using Oracle, see the corresponding FAQ item.

Known Vulnerability of the ODBC Dictionary Functionality

Example of insecure use

Let’s configure unixODBC for PostgreSQL. Content of /etc/odbc.ini:

[gregtest]
Driver = /usr/lib/psqlodbca.so
Servername = localhost
PORT = 5432
DATABASE = test_db
#OPTION = 3
USERNAME = test
PASSWORD = test

If you then make a query such as

SELECT * FROM odbc('DSN=gregtest;Servername=some-server.com', 'test_db');

ODBC driver will send values of USERNAME and PASSWORD from odbc.ini to some-server.com.

Example of Connecting Postgresql

Ubuntu OS.

Installing unixODBC and the ODBC driver for PostgreSQL:

$ sudo apt-get install -y unixodbc odbcinst odbc-postgresql

Configuring /etc/odbc.ini (or ~/.odbc.ini if you signed in under a user that runs ClickHouse):

    [DEFAULT]
    Driver = myconnection

    [myconnection]
    Description         = PostgreSQL connection to my_db
    Driver              = PostgreSQL Unicode
    Database            = my_db
    Servername          = 127.0.0.1
    UserName            = username
    Password            = password
    Port                = 5432
    Protocol            = 9.3
    ReadOnly            = No
    RowVersioning       = No
    ShowSystemTables    = No
    ConnSettings        =

The dictionary configuration in ClickHouse:

CREATE DICTIONARY table_name (
    id UInt64,
    some_column UInt64 DEFAULT 0
)
PRIMARY KEY id
SOURCE(ODBC(connection_string 'DSN=myconnection' table 'postgresql_table'))
LAYOUT(HASHED())
LIFETIME(MIN 300 MAX 360)

You may need to edit odbc.ini to specify the full path to the library with the driver DRIVER=/usr/local/lib/psqlodbcw.so.

Example of Connecting MS SQL Server

Ubuntu OS.

Installing the ODBC driver for connecting to MS SQL:

$ sudo apt-get install tdsodbc freetds-bin sqsh

Configuring the driver:

    $ cat /etc/freetds/freetds.conf
    ...

    [MSSQL]
    host = 192.168.56.101
    port = 1433
    tds version = 7.0
    client charset = UTF-8

    # test TDS connection
    $ sqsh -S MSSQL -D database -U user -P password

    $ cat /etc/odbcinst.ini

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

    $ cat /etc/odbc.ini
    # $ cat ~/.odbc.ini # if you signed in under a user that runs ClickHouse

    [MSSQL]
    Description     = FreeTDS
    Driver          = FreeTDS
    Servername      = MSSQL
    Database        = test
    UID             = test
    PWD             = test
    Port            = 1433

    # (optional) test ODBC connection (to use isql-tool install the [unixodbc](https://packages.debian.org/sid/unixodbc)-package)
    $ isql -v MSSQL "user" "password"

Remarks:

  • to determine the earliest TDS version that is supported by a particular SQL Server version, refer to the product documentation or look at MS-TDS Product Behavior

Configuring the dictionary in ClickHouse:

CREATE DICTIONARY test (
    k UInt64,
    s String DEFAULT ''
)
PRIMARY KEY k
SOURCE(ODBC(table 'dict' connection_string 'DSN=MSSQL;UID=test;PWD=test'))
LAYOUT(FLAT())
LIFETIME(MIN 300 MAX 360)
Navigation