ClickHouse dictionary source

Example of settings:

DDL
Configuration file

SOURCE(CLICKHOUSE(
    host 'example01-01-1'
    port 9000
    user 'dict_reader'
    password 'dict_reader_password'
    db 'default'
    table 'ids'
    where 'id=10'
    secure 1
    query 'SELECT id, value_1, value_2 FROM default.ids'
));

<source>
    <clickhouse>
        <host>example01-01-1</host>
        <port>9000</port>
        <user>dict_reader</user>
        <password>dict_reader_password</password>
        <db>default</db>
        <table>ids</table>
        <where>id=10</where>
        <secure>1</secure>
        <query>SELECT id, value_1, value_2 FROM default.ids</query>
    </clickhouse>
</source>

Setting fields:

Setting	Description
`host`	The ClickHouse host. If it is a local host, the query is processed without any network activity. To improve fault tolerance, you can create a Distributed table and enter it in subsequent configurations.
`port`	The port on the ClickHouse server.
`user`	Name of the ClickHouse user.
`password`	Password of the ClickHouse user.
`db`	Name of the database.
`table`	Name of the table.
`where`	The selection criteria. Optional.
`invalidate_query`	Query for checking the dictionary status. Optional. Read more in the section Refreshing dictionary data using LIFETIME.
`secure`	Use SSL for connection.
`query`	The custom query. Optional.

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

In ClickHouse Cloud, when a user other than default creates the dictionary, the source must specify both user and password, and user cannot be default. Otherwise CREATE DICTIONARY fails with a BAD_ARGUMENTS error. Use a dedicated user that has SELECT on the source table.

SQL Reference

Data Types

Engines

Functions

Formats

Settings

System Tables

Data Lakes