Skip to content
ClickHouse Docs
ClickHouse DocsClickHouse Docs
49.6kGet Started

Dictionary layouts

View as MarkdownEdit this pageReport an issueSet up docs MCP

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.

Specify dictionary layout

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

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

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

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.

Navigation