Dictionary layouts

Dictionary layout types

There are a variety of ways to store dictionaries in memory, each with CPU and RAM-usage trade-offs.

Layout	Description
flat	Stores data in flat arrays indexed by key. Fastest layout, but keys must be `UInt64` and bounded by `max_array_size`.
hashed	Stores data in a hash table. No key size limit, supports any number of elements.
sparse_hashed	Like `hashed`, but trades CPU for lower memory usage.
complex_key_hashed	Like `hashed`, for composite keys.
complex_key_sparse_hashed	Like `sparse_hashed`, for composite keys.
hashed_array	Attributes stored in arrays with a hash table mapping keys to array indices. Memory-efficient for many attributes.
complex_key_hashed_array	Like `hashed_array`, for composite keys.
range_hashed	Hash table with ordered ranges. Supports lookups by key + date/time range.
complex_key_range_hashed	Like `range_hashed`, for composite keys.
cache	Fixed-size in-memory cache. Only frequently accessed keys are stored.
complex_key_cache	Like `cache`, for composite keys.
ssd_cache	Like `cache`, but stores data on SSD with an in-memory index.
complex_key_ssd_cache	Like `ssd_cache`, for composite keys.
direct	No in-memory storage — queries the source directly for each request.
complex_key_direct	Like `direct`, for composite keys.
ip_trie	Trie structure for fast IP prefix lookups (CIDR-based).
hierarchical	Support for hierarchies with a numeric key describing parent-child relationships.
polygon	Efficient point-in-polygon lookups (reverse geocoding).
regexp_tree	Matches keys against a tree of regular expressions.

Recommended layoutsflat, hashed, and complex_key_hashed provide the best query performance. Caching layouts are not recommended due to potentially poor performance and difficulty tuning parameters — see cache for details.

Cache-based layouts are unsuitable for reliable insert-time enrichment because their warmed entries are local to each server and are lost when a server restarts or is replaced. ClickHouse Cloud autoscaling and managed node replacement make this condition more likely. Use a fully loaded layout instead; see Cache dictionaries in ingestion paths.

Specify dictionary layout

Use CREATE DICTIONARY to create dictionaries.When creating a dictionary that reads from a ClickHouse table, include a dedicated user’s username and password in the SOURCE clause. That user needs SELECT permission on the source table. See the ClickHouse dictionary source example.Verify the list of supported dictionary sources in the Cloud Compatibility guide.

You can configure a dictionary layout with the LAYOUT clause (for DDL) or the layout setting for configuration file definitions.

DDL
Configuration file

CREATE DICTIONARY (...)
...
LAYOUT(LAYOUT_TYPE(param value)) -- layout settings
...

<clickhouse>
    <dictionary>
        ...
        <layout>
            <layout_type>
                <!-- layout settings -->
            </layout_type>
        </layout>
        ...
    </dictionary>
</clickhouse>

See also CREATE DICTIONARY for the full DDL syntax. Dictionaries without word complex-key* in a layout have a key with UInt64 type, complex-key* dictionaries have a composite key (complex, with arbitrary types). Numeric key example (column key_column has UInt64 type):

DDL
Configuration file

CREATE DICTIONARY dict_name (
    key_column UInt64,
    ...
)
PRIMARY KEY key_column

<structure>
    <id>
        <name>key_column</name>
    </id>
    ...
</structure>

Composite key example (key has one element with String type):

DDL
Configuration file

CREATE DICTIONARY dict_name (
    country_code String,
    ...
)
PRIMARY KEY country_code

<structure>
    <key>
        <attribute>
            <name>country_code</name>
            <type>String</type>
        </attribute>
    </key>
    ...
</structure>

Improve dictionary performance

There are several ways to improve dictionary performance:

Call the function for working with the dictionary after GROUP BY.
Mark attributes to extract as injective. An attribute is called injective if different keys correspond to different attribute values. So when GROUP BY uses a function that fetches an attribute value by the key, this function is automatically taken out of GROUP BY.

ClickHouse generates an exception for errors with dictionaries. Examples of errors can be:

The dictionary being accessed could not be loaded.
Error querying a cached dictionary.

You can view the list of dictionaries and their statuses in the system.dictionaries table.

SQL Reference

Data Types

Engines

Functions

Formats

Settings

System Tables

Data Lakes

Dictionary layout types

Specify dictionary layout

Improve dictionary performance

​Dictionary layout types

​Specify dictionary layout

​Improve dictionary performance

Dictionary layout types

Specify dictionary layout

Improve dictionary performance