Fonte de dicionário ODBC

Configure uma conexão ODBC como fonte de dicionário no ClickHouse.

Você pode usar este método para se conectar a qualquer banco de dados que tenha um driver ODBC.

Exemplo de configurações:

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

Arquivo de configuração

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

Campos de configuração:

Configuração	Descrição
db	Nome do banco de dados. Omita-o se o nome do banco de dados estiver definido nos parâmetros de <connection_string>.
table	Nome da tabela e do esquema, se existir.
connection_string	String de conexão.
invalidate_query	Consulta para verificar o status do dicionário. Opcional. Leia mais na seção Atualização de dados de dicionário usando LIFETIME.
background_reconnect	Reconecta à réplica em segundo plano se a conexão falhar. Opcional.
query	Consulta personalizada. Opcional.

Os campos table e query não podem ser usados juntos. Além disso, um dos campos table ou query deve ser declarado.

O ClickHouse recebe os caracteres de aspas do driver ODBC e adiciona aspas a todas as configurações nas consultas enviadas ao driver, portanto é necessário definir o nome da tabela de acordo com o uso de maiúsculas e minúsculas no banco de dados.

Se você tiver problemas de codificação ao usar Oracle, consulte o item correspondente no FAQ.

​

Vulnerabilidade conhecida da funcionalidade de Dicionário ODBC

Ao se conectar ao banco de dados por meio do parâmetro de conexão Servername do driver ODBC, ele pode ser substituído. Nesse caso, os valores de USERNAME e PASSWORD de odbc.ini são enviados ao servidor remoto e podem ser comprometidos.

Exemplo de uso inseguro

Vamos configurar o unixODBC para PostgreSQL. Conteúdo de /etc/odbc.ini:

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

Se você fizer uma consulta como

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

O ODBC Driver enviará os valores de USERNAME e PASSWORD do odbc.ini para some-server.com.

​

Exemplo de conexão com o PostgreSQL

Sistema operacional Ubuntu.

Instalação do unixODBC e do driver ODBC para PostgreSQL:

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

Configurando o /etc/odbc.ini (ou ~/.odbc.ini se você fez login como um usuário que executa o 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        =

A configuração do dicionário no 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)

Arquivo de configuração

<clickhouse>
    <dictionary>
        <name>table_name</name>
        <source>
            <odbc>
                <!-- Você pode especificar os seguintes parâmetros na 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>

Talvez seja necessário editar o odbc.ini para especificar o caminho completo da biblioteca com o driver DRIVER=/usr/local/lib/psqlodbcw.so.

​

Exemplo de conexão com o MS SQL Server

Sistema operacional: Ubuntu.

Instalação do driver ODBC para conexão ao MS SQL:

$ sudo apt-get install tdsodbc freetds-bin sqsh

Configurando o driver:

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

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

    # testar conexão TDS
    $ 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 # se você estiver conectado com um usuário que executa o ClickHouse

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

    # (opcional) testar conexão ODBC (para usar o isql-tool, instale o pacote [unixodbc](https://packages.debian.org/sid/unixodbc))
    $ isql -v MSSQL "user" "password"

Observações:

• para determinar a versão mais antiga do TDS com suporte em uma versão específica do SQL Server, consulte a documentação do produto ou veja MS-TDS Product Behavior

Configurando o dicionário no 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)

Arquivo de configuração

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