Skip to main content

Dictionaries

Tutorial

If you are getting started with Dictionaries in ClickHouse we have a tutorial that covers that topic. Take a look here.

You can add your own dictionaries from various data sources. The source for a dictionary can be a ClickHouse table, a local text or executable file, an HTTP(s) resource, or another DBMS. For more information, see “Dictionary Sources”.

ClickHouse:

  • Fully or partially stores dictionaries in RAM.
  • Periodically updates dictionaries and dynamically loads missing values. In other words, dictionaries can be loaded dynamically.
  • Allows creating dictionaries with xml files or DDL queries.

The configuration of dictionaries can be located in one or more xml-files. The path to the configuration is specified in the dictionaries_config parameter.

Dictionaries can be loaded at server startup or at first use, depending on the dictionaries_lazy_load setting.

The dictionaries system table contains information about dictionaries configured at server. For each dictionary you can find there:

  • Status of the dictionary.
  • Configuration parameters.
  • Metrics like amount of RAM allocated for the dictionary or a number of queries since the dictionary was successfully loaded.
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.

Creating a dictionary with a DDL query

Dictionaries can be created with DDL queries, and this is the recommended method because with DDL created dictionaries:

  • No additional records are added to server configuration files
  • The dictionaries can be worked with as first-class entities, like tables or views
  • Data can be read directly, using familiar SELECT rather than dictionary table functions
  • The dictionaries can be easily renamed

Creating a dictionary with a configuration file

note

Creating a dictionary with a configuration file is not applicable to ClickHouse Cloud. Please use DDL (see above), and create your dictionary as user default.

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/metrika.xml</include_from>


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

</clickhouse>

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

note

You can convert values for a small dictionary by describing it in a SELECT query (see the transform function). This functionality is not related to dictionaries.

See Also