ODBC dictionary source - Clickhouse

import Tabs from '@theme/Tabs'; import TabItem from '@theme/TabItem';

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

Example of settings:

sql

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

</TabItem> <TabItem value="xml" label="Configuration file">

xml

<source>
    <odbc>
        <db>DatabaseName</db>
        <table>ShemaName.TableName</table>
        <connection_string>DSN=some_parameters</connection_string>
        <invalidate_query>SQL_QUERY</invalidate_query>
        <query>SELECT id, value_1, value_2 FROM ShemaName.TableName</query>
    </odbc>
</source>

</TabItem> </Tabs>

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.

:::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 {#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:

text

[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

sql

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 {#example-of-connecting-postgresql}

Ubuntu OS.

Installing unixODBC and the ODBC driver for PostgreSQL:

bash

$ 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):

text

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

sql

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)

</TabItem> <TabItem value="xml" label="Configuration file">

xml

<clickhouse>
    <dictionary>
        <name>table_name</name>
        <source>
            <odbc>
                <!-- You can specify the following parameters in connection_string: -->
                <!-- DSN=myconnection;UID=username;PWD=password;HOST=127.0.0.1;PORT=5432;DATABASE=my_db -->
                <connection_string>DSN=myconnection</connection_string>
                <table>postgresql_table</table>
            </odbc>
        </source>
        <lifetime>
            <min>300</min>
            <max>360</max>
        </lifetime>
        <layout>
            <hashed/>
        </layout>
        <structure>
            <id>
                <name>id</name>
            </id>
            <attribute>
                <name>some_column</name>
                <type>UInt64</type>
                <null_value>0</null_value>
            </attribute>
        </structure>
    </dictionary>
</clickhouse>

</TabItem> </Tabs>

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 {#example-of-connecting-ms-sql-server}

Ubuntu OS.

Installing the ODBC driver for connecting to MS SQL:

bash

$ sudo apt-get install tdsodbc freetds-bin sqsh

Configuring the driver:

bash

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

sql

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)

</TabItem> <TabItem value="xml" label="Configuration file">

xml

<clickhouse>
    <dictionary>
        <name>test</name>
        <source>
            <odbc>
                <table>dict</table>
                <connection_string>DSN=MSSQL;UID=test;PWD=test</connection_string>
            </odbc>
        </source>

        <lifetime>
            <min>300</min>
            <max>360</max>
        </lifetime>

        <layout>
            <flat />
        </layout>

        <structure>
            <id>
                <name>k</name>
            </id>
            <attribute>
                <name>s</name>
                <type>String</type>
                <null_value></null_value>
            </attribute>
        </structure>
    </dictionary>
</clickhouse>

</TabItem> </Tabs>