Dictionary Sources

Syntax

Tip

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

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

Configuration file

<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

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

Configuration file

<source>
  <file>
    <path>/opt/dictionaries/os.tsv</path>
    <format>TabSeparated</format>
  </file>
  <settings>
#highlight-next-line
      <format_csv_allow_single_quotes>0</format_csv_allow_single_quotes>
  </settings>
</source>