Source de dictionnaire ODBC

Configurer une connexion ODBC comme source de dictionnaire dans ClickHouse.

Vous pouvez utiliser cette méthode pour vous connecter à n’importe quelle base de données disposant d’un pilote ODBC.

Exemple de paramètres :

DDL

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

Fichier de configuration

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

Champs des paramètres :

Paramètre	Description
db	Nom de la base de données. Omettez-le si le nom de la base de données est défini dans les paramètres de <connection_string>.
table	Nom de la table et du schéma, s’il existe.
connection_string	Chaîne de connexion.
invalidate_query	Requête permettant de vérifier l’état du dictionnaire. Facultatif. Pour en savoir plus, consultez la section Refreshing dictionary data using LIFETIME.
background_reconnect	Se reconnecte à la réplique en arrière-plan si la connexion échoue. Facultatif.
query	Requête personnalisée. Facultatif.

Les champs table et query ne peuvent pas être utilisés ensemble. De plus, l’un des deux champs table ou query doit obligatoirement être déclaré.

ClickHouse reçoit les caractères de guillemet du pilote ODBC et place tous les paramètres entre guillemets dans les requêtes envoyées au pilote ; il est donc nécessaire de définir le nom de la table en respectant sa casse dans la base de données.

Si vous rencontrez des problèmes d’encodage lors de l’utilisation d’Oracle, consultez l’article correspondant de la FAQ.

​

Vulnérabilité connue de la fonctionnalité des dictionnaires ODBC

Lors de la connexion à la base de données via le pilote ODBC, le paramètre de connexion Servername peut être remplacé. Dans ce cas, les valeurs de USERNAME et PASSWORD de odbc.ini sont envoyées au serveur distant et peuvent être compromises.

Exemple d’utilisation non sécurisée

Configurons unixODBC pour PostgreSQL. Contenu de /etc/odbc.ini :

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

Si vous exécutez ensuite une requête telle que

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

Le pilote ODBC enverra les valeurs de USERNAME et PASSWORD contenues dans odbc.ini à some-server.com.

​

Exemple de connexion à PostgreSQL

Système d’exploitation Ubuntu.

Installation de unixODBC et du pilote ODBC pour PostgreSQL :

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

Configuration de /etc/odbc.ini (ou ~/.odbc.ini si vous êtes connecté avec l’utilisateur qui exécute 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        =

La configuration du dictionnaire dans ClickHouse :

DDL

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)

Fichier de configuration

<clickhouse>
    <dictionary>
        <name>table_name</name>
        <source>
            <odbc>
                <!-- Vous pouvez spécifier les paramètres suivants dans 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>

Vous devrez peut-être modifier odbc.ini afin d’indiquer le chemin complet vers la bibliothèque avec le pilote DRIVER=/usr/local/lib/psqlodbcw.so.

​

Exemple de connexion à MS SQL Server

Sous Ubuntu.

Installation du pilote ODBC pour la connexion à MS SQL :

$ sudo apt-get install tdsodbc freetds-bin sqsh

Configuration du 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"

Remarques :

• pour déterminer la plus ancienne version de TDS prise en charge par une version donnée de SQL Server, consultez la documentation du produit ou reportez-vous à MS-TDS Product Behavior

Configuration du dictionnaire dans ClickHouse :

DDL

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)

Fichier de configuration

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