Dictionary layouts

Dictionary layout types for storing dictionaries in memory

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).

Recommended layouts

flat, 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.

Specify dictionary layout

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.

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

DDL

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

Configuration file

<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

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

Configuration file

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

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

DDL

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

Configuration file

<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.