Dictionaries in ClickHouse

A dictionary is a key-value lookup table that ClickHouse keeps in memory and refreshes from a source on a schedule. It exists for one very common shape of query: a big fact table that needs a few attributes from a small dimension table (user to plan, product to category, URL to site section).

You could JOIN. A dictionary is usually better for this, for three reasons: the lookup is a hash probe in memory instead of building a right-hand hash table on every query; the call sits in the select list, so it reads like a function; and the same lookup works inside DEFAULT expressions, materialized views and ORDER BY/PARTITION BY clauses where a join cannot go.

A first dictionary

Set up the demo data:

CREATE TABLE events (event_time DateTime, user_id UInt64, event_type LowCardinality(String),
                     url String, duration_ms UInt32, country LowCardinality(String))
ENGINE = MergeTree ORDER BY (event_type, event_time);

INSERT INTO events
SELECT toDateTime('2026-01-01 00:00:00') + number, number % 1000,
       ['view', 'click', 'buy'][number % 3 + 1], concat('/p/', toString(number % 50)),
       number % 5000, ['US', 'DE', 'TR', 'JP'][number % 4 + 1]
FROM numbers(1000000);

CREATE TABLE users (user_id UInt64, plan LowCardinality(String), signup_country LowCardinality(String))
ENGINE = MergeTree ORDER BY user_id;

INSERT INTO users
SELECT number, ['free', 'pro', 'team'][number % 3 + 1], ['US', 'DE', 'TR', 'JP'][number % 4 + 1]
FROM numbers(1000);

Now the dictionary:

CREATE DICTIONARY users_dict
(
    user_id        UInt64,
    plan           String,
    signup_country String
)
PRIMARY KEY user_id
SOURCE(CLICKHOUSE(TABLE 'users' DB 'default'))
LIFETIME(MIN 0 MAX 300)
LAYOUT(HASHED());

Four clauses define it: the key (PRIMARY KEY), where the data comes from (SOURCE), how often to refresh (LIFETIME) and how to hold it in memory (LAYOUT). Attributes are the other columns.

Read from it with dictGet(dictionary, attribute, key). The key must have exactly the key’s type, so cast literals:

SELECT dictGet('users_dict', 'plan', toUInt64(7)) AS plan;

pro

The companions you will use daily:

-- a default instead of an empty value for missing keys
SELECT dictGetOrDefault('users_dict', 'plan', toUInt64(99999), 'unknown');   -- unknown

-- membership test
SELECT dictHas('users_dict', toUInt64(5)), dictHas('users_dict', toUInt64(5000));   -- 1  0

A missing key with plain dictGet returns the attribute type’s default (empty string, 0), which is easy to misread as real data. Use dictGetOrDefault or dictHas when absence is meaningful.

Use it in the aggregation, over the large table:

SELECT dictGet('users_dict', 'plan', user_id) AS plan, count() AS events
FROM events
GROUP BY plan
ORDER BY plan;

free  334000
pro   333000
team  333000

Sources

CLICKHOUSE(...) reads another ClickHouse table. Dictionaries can also be fed from a query, a file, an HTTP endpoint, or another database (MySQL, PostgreSQL, …). The query form is handy when the dimension needs shaping first:

CREATE DICTIONARY pages_dict (url String, section String)
PRIMARY KEY url
SOURCE(CLICKHOUSE(QUERY 'SELECT concat(''/p/'', toString(number)) AS url,
                                if(number < 10, ''docs'', ''shop'') AS section
                         FROM numbers(50)'))
LIFETIME(0)
LAYOUT(COMPLEX_KEY_HASHED());

SELECT dictGet('pages_dict', 'section', tuple('/p/3'));    -- docs
SELECT dictGet('pages_dict', 'section', tuple('/p/30'));   -- shop

Credentials belong in the config or named collections, not inline in the DDL; the first example reaches the same server and needs none.

Layouts: how it is stored

The layout is a memory/speed trade-off.

Layout	Key	Use when
FLAT	UInt64, small dense range	ids that are almost sequential; fastest, array-backed
HASHED	UInt64	general default for integer keys
COMPLEX_KEY_HASHED	anything, including strings or tuples	string or composite keys (key passed as tuple(...))
RANGE_HASHED	key plus a date range	slowly changing attributes (“plan as of date”)
CACHE / SSD_CACHE	key	dimension too large to hold entirely; loads on demand
DIRECT	key	no caching at all; every lookup hits the source

Composite and string keys are passed as a tuple: dictGet('pages_dict', 'section', tuple(url)).

Refreshing

LIFETIME(MIN 0 MAX 300) tells ClickHouse to reload the dictionary at a random time between 0 and 300 seconds after the last load (the randomness avoids all replicas hitting the source simultaneously). LIFETIME(0) means never refresh automatically. Force a reload with:

SYSTEM RELOAD DICTIONARY users_dict;

For a ClickHouse table source, the server is smart enough to skip the reload when the source has not changed (it checks the table’s modification state).

Looking at what is loaded

SELECT name, status, element_count, bytes_allocated, loading_duration, last_exception
FROM system.dictionaries;

status should be LOADED; a dictionary that fails to load stores the error in last_exception, and every dictGet on it fails until it is fixed. bytes_allocated is how much memory it holds, which is the number to watch before you load a large dimension.

A dictionary can also be queried like a table (SELECT * FROM users_dict), and used as a default value:

CREATE TABLE users_with_plan
(
    user_id UInt64,
    plan String DEFAULT dictGet('users_dict', 'plan', user_id)
)
ENGINE = MergeTree ORDER BY user_id;

INSERT INTO users_with_plan (user_id) VALUES (3), (4);
-- 3 free / 4 pro

Be careful with that last pattern: the value is materialised at insert time, so it does not change when the dictionary does.

Dictionary or JOIN?

Use a dictionary when the right side is small (fits in memory comfortably), key-addressed, and you want the lookup in an expression. Use a JOIN when you need multiple matches per key, non-equality conditions, or when the right side is big and changes all the time.