Dictionary Sources

Syntax

If you are using a dictionary with ClickHouse Cloud please use the DDL query option to create your dictionaries, and create your dictionary as user default. Also, verify the list of supported dictionary sources in the Cloud Compatibility guide.

A dictionary can be connected to ClickHouse from many different sources. The source is configured in the source section for configuration file and using the SOURCE clause for DDL statement.

DDL
Configuration file

CREATE DICTIONARY dict_name (...)
...
SOURCE(SOURCE_TYPE(param1 val1 ... paramN valN)) -- Source configuration
...

<clickhouse>
  <dictionary>
    ...
    <source>
      <source_type>
        <!-- Source configuration -->
      </source_type>
    </source>
    ...
  </dictionary>
  ...
</clickhouse>

Supported dictionary sources

The following source types (SOURCE_TYPE/source_type) are available:

Local file
Executable File
Executable Pool
HTTP(S)
DBMS
- ODBC
- MySQL
- ClickHouse
- MongoDB
- Redis
- Cassandra
- PostgreSQL
- YTsaurus
YAMLRegExpTree
Null

For source types Local file, Executable file, HTTP(s), ClickHouse optional settings are available:

DDL
Configuration file

SOURCE(FILE(path './user_files/os.tsv' format 'TabSeparated'))
SETTINGS(format_csv_allow_single_quotes = 0)

<source>
  <file>
    <path>/opt/dictionaries/os.tsv</path>
    <format>TabSeparated</format>
  </file>
  <settings>
      <format_csv_allow_single_quotes>0</format_csv_allow_single_quotes>
  </settings>
</source>

Column order of a custom query

The ClickHouse, MySQL, PostgreSQL and ODBC sources accept a query field that replaces the query ClickHouse would otherwise build from table and where. A dictionary expects the columns of that result in a fixed order, which is not the order the attributes are written in the CREATE DICTIONARY statement:

the key column(s), in PRIMARY KEY order;
for a range_hashed or complex_key_range_hashed dictionary, the range_min column, then the range_max column;
the remaining attributes, in the order they are declared.

The ClickHouse source matches the result to that order by column name, so with it the order of the query does not matter as long as every column is named after the attribute it fills. The MySQL, PostgreSQL and ODBC sources take the columns by position, so with those a query whose columns are in any other order loads values into the wrong attributes. Depending on the types involved, the dictionary either fails to load with a conversion error or stores wrong values, so write the query in the order above:

CREATE DICTIONARY contracts (
    meter_no String,
    id Int64,
    contract_time Date,
    end_date Date
)
PRIMARY KEY meter_no
-- The key comes first, then the range columns, then the remaining attributes.
SOURCE(POSTGRESQL(... query 'SELECT meter_no, contract_time, end_date, id FROM contracts'))
LAYOUT(RANGE_HASHED())
RANGE(MIN contract_time MAX end_date)
LIFETIME(300)

SQL Reference

Data Types

Engines

Functions

Formats

Settings

System Tables

Data Lakes

Syntax

Supported dictionary sources

Column order of a custom query

​Syntax

​Supported dictionary sources

​Column order of a custom query

Syntax

Supported dictionary sources

Column order of a custom query