CREATE DICTIONARY

A dictionary is a mapping (key -> attributes) that is convenient for various types of reference lists. ClickHouse supports special functions for working with dictionaries that can be used in queries. It is easier and more efficient to use dictionaries with functions than a JOIN with reference tables.

Dictionaries can be created in two ways:

With a DDL query (recommended)
With a configuration file

Creating a dictionary with a DDL query

Supported in ClickHouse Cloud

Dictionaries can be created with DDL queries. This is the recommended method because with DDL created dictionaries:

No additional records are added to server configuration files.
Dictionaries can be used like first-class entities such as tables or views.
Data can be read directly, using familiar SELECT syntax rather than dictionary table functions. Note that when accessing a dictionary directly via a SELECT statement, cached dictionary will return only cached data, while for a non-cached dictionary it will return all the data that it stores.
Dictionaries can be easily renamed.

Syntax

CREATE [OR REPLACE] DICTIONARY [IF NOT EXISTS] [db.]dictionary_name [ON CLUSTER cluster]
(
    key1  type1  [DEFAULT | EXPRESSION expr1] [IS_OBJECT_ID],
    key2  type2  [DEFAULT | EXPRESSION expr2],
    attr1 type2  [DEFAULT | EXPRESSION expr3] [HIERARCHICAL|INJECTIVE],
    attr2 type2  [DEFAULT | EXPRESSION expr4] [HIERARCHICAL|INJECTIVE]
)
PRIMARY KEY key1, key2
SOURCE(SOURCE_NAME([param1 value1 ... paramN valueN]))
LAYOUT(LAYOUT_NAME([param_name param_value]))
LIFETIME({MIN min_val MAX max_val | max_val})
SETTINGS(setting_name = setting_value, setting_name = setting_value, ...)
COMMENT 'Comment'

Clause	Description
Attributes	Dictionary attributes are specified similarly to table columns. The only required property is the type, all others may have default values.
PRIMARY KEY	Defines the key column(s) for dictionary lookups. Depending on the layout, one or more attributes can be specified as keys.
`SOURCE`	Defines the data source for the dictionary (e.g. ClickHouse table, HTTP, PostgreSQL).
`LAYOUT`	Controls how the dictionary is stored in memory (e.g. `FLAT`, `HASHED`, `CACHE`).
`LIFETIME`	Sets the refresh interval for the dictionary.
`ON CLUSTER`	Creates the dictionary on a cluster. Optional.
`SETTINGS`	Additional dictionary settings. Optional.
`COMMENT`	Adds a text comment to the dictionary. Optional.

Creating a dictionary with a configuration file

Not supported in ClickHouse Cloud

The dictionary configuration file has the following format:

<clickhouse>
    <comment>An optional element with any content. Ignored by the ClickHouse server.</comment>

    <!--Optional element. File name with substitutions-->
    <include_from>/etc/clickhouse-server/substitutions.xml</include_from>

    <dictionary>
        <!-- Dictionary configuration. -->
        <!-- There can be any number of dictionary sections in a configuration file. -->
    </dictionary>

</clickhouse>

You can configure any number of dictionaries in the same file.

Layouts — How dictionaries are stored in memory
Sources — Connecting to data sources
Lifetime — Automatic refresh configuration
Attributes — Key and attribute configuration
Embedded Dictionaries — Built-in geobase dictionaries
system.dictionaries — System table with dictionary information

Creating a dictionary with a DDL query

Syntax

Creating a dictionary with a configuration file

Related content