Skip to main content
Skip to main content
Edit this page

ODBC dictionary source

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:

SettingDescription
dbName of the database. Omit it if the database name is set in the <connection_string> parameters.
tableName of the table and schema if exists.
connection_stringConnection string.
invalidate_queryQuery for checking the dictionary status. Optional. Read more in the section Refreshing dictionary data using LIFETIME.
background_reconnectReconnect to replica in background if connection fails. Optional.
queryThe custom query. Optional.
Note

The table and query fields cannot be used together. And either one of the table or query fields must be declared.

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​

Note

When connecting to the database through the ODBC driver connection parameter Servername can be substituted. In this case values of USERNAME and PASSWORD from odbc.ini are sent to the remote server and can be compromised.

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)