Dictionaries in ClickHouse: fast lookup without JOIN

Dictionaries in ClickHouse: Fast Lookup Without JOIN

1. Why Dictionaries Are Needed — The JOIN Problem with Reference Data

Let's go back to our online casino. You have a bets table that stores sport_id — a number from 1 to 20. But in reports, you need to show the sport name: "Football", "Hockey", "Tennis". This information usually lives in a separate reference table sports.

-- Slow query with JOIN
SELECT 
    b.user_id,
    s.name AS sport_name,
    sum(b.amount) AS total
FROM bets b
JOIN sports s ON b.sport_id = s.id
GROUP BY b.user_id, s.name;

For a billion rows in bets and 20 rows in sports, this JOIN will copy the reference table for each chunk of data. ClickHouse performs a broadcast join (sends the small table to all shards), which is fast but still consumes memory and CPU.

Dictionaries solve this problem differently. A dictionary is an in-memory reference table that lives inside ClickHouse. You can look up a key and get a value in microseconds without executing a JOIN.

Real-life analogy: A dictionary is like a cheat sheet for an exam. You have a list of 20 rows: "1 = football, 2 = hockey...". When you need to find the sport name by ID, you just glance at the cheat sheet (memory), instead of going to the library for a thick reference book (disk). It's thousands of times faster.

Why this matters in ClickHouse: ClickHouse stores data on disk, and reading even a small table via JOIN requires disk operations. A dictionary resides in memory (compressed and optimized), and accessing it is simply reading from RAM.

2. Dictionary Types — How to Choose the Structure

ClickHouse offers several dictionary types (LAYOUT) depending on:

• data size (how many keys),
• key type (simple or composite),
• whether range lookup is needed (e.g., exchange rate on a date).

Type	When to Use	Max Keys	Features
flat	Very small dictionaries (up to 500k keys)	500,000	Fastest, stored in an array. Key must be integer (UInt*).
hashed	Medium dictionaries (millions of keys)	Unlimited	Hash table. Suitable for any key type. Slightly slower than flat.
sparse_hashed	Very large (tens of millions)	Very many	Saves memory (does not store null values), but slightly slower.
range_hashed	Date ranges (exchange rate by date)	Unlimited	Key + range (start, end). Allows get(key, date) lookup.
complex_key_hashed	Composite key (e.g., market_id, selection_id)	Unlimited	Key is a tuple of multiple fields.
ip_trie	IP addresses (prefix lookup)	Up to 500k	For GeoIP: find country/city by IP.

How to choose:

• Fewer than 500k keys and key is integer → flat (maximum speed).
• More than 500k keys or key is not integer → hashed.
• Very many keys and many null values → sparse_hashed.
• Need date-based lookup → range_hashed.
• Composite key (multiple fields) → complex_key_hashed.

Analogy: flat is like a cabinet with numbered drawers (index = number). You go straight to drawer #17. hashed is like a library catalog where you first compute the shelf by hashing the author's last name. range_hashed is like an archive where you search for a document knowing the date.

3. Data Sources — Where the Dictionary Gets Data

A dictionary can be populated from various sources (SOURCE). ClickHouse periodically updates the dictionary from the source at a specified interval (LIFETIME).

Supported sources:

• CLICKHOUSE — another ClickHouse table
• MYSQL — MySQL table
• POSTGRESQL — PostgreSQL table
• HTTP — REST API (JSON or XML)
• FILE — local file (CSV, TSV)
• REDIS — Redis (key-value)
• MONGODB — MongoDB collection

Example with MySQL:

CREATE DICTIONARY currencies_dict
(
    code String,
    name String,
    rate Decimal(10,4)
)
PRIMARY KEY code
SOURCE(MYSQL(
    host 'mysql-host'
    port 3306
    user 'reader'
    password 'secret'
    db 'reference'
    table 'currencies'
))
LIFETIME(MIN 3600 MAX 7200)   -- update every 1-2 hours
LAYOUT(HASHED());

Why this is convenient: Your currency reference can be updated once an hour from an external MySQL database maintained by the finance department. ClickHouse pulls the changes automatically; you don't need to write an ETL script.

4. Creating a Dictionary from a ClickHouse Table — Step by Step

The most common scenario: you already have a reference table in ClickHouse and want to turn it into a dictionary for fast lookups.

Step 1: Create the reference table (if it doesn't exist)

CREATE TABLE sports
(
    id       UInt32,          -- sport ID (1, 2, 3...)
    name     String,          -- 'Football', 'Hockey', 'Tennis'
    category String           -- 'team', 'individual', 'esports'
)
ENGINE = MergeTree()
ORDER BY id;

-- Populate with data
INSERT INTO sports VALUES (1, 'Football', 'team'), (2, 'Hockey', 'team'), (3, 'Tennis', 'individual');

Step 2: Create a dictionary on top of this table

CREATE DICTIONARY sports_dict
(
    id       UInt32,          -- key column
    name     String,          -- value to retrieve
    category String           -- another value
)
PRIMARY KEY id                -- lookup key
SOURCE(CLICKHOUSE(
    host 'localhost'
    port 9000
    user 'default'
    password ''
    db 'default'
    table 'sports'
))
LIFETIME(MIN 300 MAX 600)     -- update every 5-10 minutes
LAYOUT(HASHED());              -- for our 20 records, flat is also fine, but hashed works too

Breaking down the parameters:

• PRIMARY KEY id — the column used for lookup. Must be unique.
• SOURCE(CLICKHOUSE(...)) — data source. You can specify any host, not just localhost.
• LIFETIME(MIN 300 MAX 600) — the dictionary will be fully reloaded every 5–10 minutes. MIN and MAX are used for randomization to prevent all dictionaries on all servers from updating simultaneously.
• LAYOUT(HASHED()) — in-memory structure. For 20 records, flat is better, but we keep hashed as an example.

What happens after creation: ClickHouse reads the entire sports table, loads it into memory as a hash table. Now you can use dictGet for fast access.

5. Using Dictionaries in Queries — dictGet and Friends

The real magic begins in SELECT. Instead of JOIN sports, you use dictionary functions.

dictGet — the main function

-- Get sport name by sport_id
SELECT 
    user_id,
    sport_id,
    dictGet('sports_dict', 'name', sport_id) AS sport_name,
    amount
FROM bets
LIMIT 10;

Syntax: dictGet('dictionary_name', 'value_column', key)

dictGetOrDefault — with a default value

-- If sport_id not found, return 'Unknown'
SELECT 
    user_id,
    sport_id,
    dictGetOrDefault('sports_dict', 'name', sport_id, 'Unknown') AS sport_name
FROM bets;

dictHas — check if a key exists

-- Find bets with invalid sport_id
SELECT DISTINCT sport_id
FROM bets
WHERE dictHas('sports_dict', sport_id) = 0;   -- returns sport_ids not in the dictionary

Full example with aggregation

-- Top 5 sports by total bet amount without JOIN!
SELECT 
    dictGet('sports_dict', 'name', sport_id) AS sport_name,
    sum(amount) AS total_amount,
    count() AS bet_count
FROM bets
WHERE created_at >= today() - 7
GROUP BY sport_id
ORDER BY total_amount DESC
LIMIT 5;

Why this is faster than JOIN: No disk reads, no distribution of the reference table across shards, no hashing at query time. The dictionary is already in memory on each ClickHouse node.

6. Complex Keys — dictGet with Tuple

When the key consists of multiple fields (e.g., market_id + selection_id), use LAYOUT(COMPLEX_KEY_HASHED()) and pass the key as a tuple.

Creating a dictionary with a composite key:

-- Odds dictionary: (market_id, selection_id) → odds value
CREATE DICTIONARY odds_dict
(
    market_id    UInt32,
    selection_id UInt32,
    odds_value   Decimal(10,3)
)
PRIMARY KEY (market_id, selection_id)   -- composite key!
SOURCE(CLICKHOUSE(
    table 'odds_reference'
))
LIFETIME(MIN 60 MAX 120)
LAYOUT(COMPLEX_KEY_HASHED());            -- must be complex_key!

Usage in queries:

-- Get odds for a specific market and outcome
SELECT 
    bet_id,
    market_id,
    selection_id,
    dictGet('odds_dict', 'odds_value', tuple(market_id, selection_id)) AS odds
FROM bets;

What is a tuple? A tuple is simply a group of values wrapped in parentheses. tuple(market_id, selection_id) creates a key like (100, 5).

7. Range Dictionaries — for Historical Data (Exchange Rate on a Date)

Imagine you have historical exchange rates that change daily. For each bet in euros, you need the rate on the bet's date.

Source table (e.g., in MySQL):

currency	start_date	end_date	rate
EUR	2025-01-01	2025-01-31	1.05
EUR	2025-02-01	2025-02-28	1.08
EUR	2025-03-01	2099-12-31	1.10

Create a range dictionary:

CREATE DICTIONARY eur_rates_dict
(
    currency   String,
    start_date Date,
    end_date   Date,
    rate       Decimal(10,4)
)
PRIMARY KEY currency
SOURCE(CLICKHOUSE(table 'eur_rates'))
LIFETIME(MIN 3600 MAX 7200)
LAYOUT(RANGE_HASHED())                     -- special type
RANGE(MIN start_date MAX end_date);        -- specify range columns

Usage:

-- For each EUR bet, get the rate on the bet date
SELECT 
    bet_id,
    amount_eur,
    created_at,
    dictGet('eur_rates_dict', 'rate', tuple(currency, created_at)) AS rate
FROM bets
WHERE currency = 'EUR';

ClickHouse automatically finds the record where created_at falls between start_date and end_date for the given currency.

Analogy: It's like a calendar of price changes. You say, "Give me the rate for March 15," and the dictionary checks its calendar: March 15 falls in the interval March 1 – March 31, rate 1.10.

8. Monitoring Dictionaries — system.dictionaries

To understand what's happening with dictionaries, there's the system table system.dictionaries.

SELECT *
FROM system.dictionaries
WHERE name = 'sports_dict';

Useful columns:

Column	What it shows
status	LOADED — loaded, LOADING — loading, FAILED — error
origin	Source (ClickHouse, MySQL...)
type	Type (flat, hashed, range_hashed...)
key	Key type
attribute.names	Available columns
bytes_allocated	Memory usage (bytes)
query_count	Number of lookups
hit_rate	Hit rate (higher is better)
load_factor	How full the dictionary is (for hashed)
creation_time	When loaded
last_exception	If status FAILED, the error is here

Memory monitoring:

SELECT 
    name,
    formatReadableSize(bytes_allocated) AS memory,
    query_count,
    hit_rate
FROM system.dictionaries
WHERE status = 'LOADED'
ORDER BY bytes_allocated DESC;

If a dictionary takes gigabytes, you might have chosen the wrong LAYOUT (e.g., hashed instead of sparse_hashed).

9. Hot Reload — SYSTEM RELOAD DICTIONARY

Dictionaries update automatically according to LIFETIME. But sometimes you need to force an update:

• You just fixed data in the source and don't want to wait 10 minutes.
• The dictionary failed (e.g., source was unavailable) and you fixed the issue.

-- Reload a specific dictionary
SYSTEM RELOAD DICTIONARY sports_dict;

-- Reload all dictionaries
SYSTEM RELOAD DICTIONARIES;

What happens: ClickHouse re-reads the source (e.g., the sports table) and replaces the dictionary content in memory. During reload, queries using dictGet will wait (or return old data, depending on version). For critical systems, perform reloads at night.

How to verify the dictionary loaded correctly:

SELECT status, last_exception 
FROM system.dictionaries 
WHERE name = 'sports_dict';

If status is LOADED, everything is fine. If FAILED, check last_exception.

10. Example Architecture: All Reference Dictionaries for a Betting Platform

Imagine a complete betting platform architecture. You have dozens of reference dictionaries constantly used in queries to enrich data.

Dictionaries to create:

-- 1. Sports (20 records, FLAT)
CREATE DICTIONARY sports_dict (id UInt32, name String, category String)
PRIMARY KEY id
SOURCE(CLICKHOUSE(table 'sports'))
LIFETIME(3600) LAYOUT(FLAT());

-- 2. Leagues/Championships (10k records, HASHED)
CREATE DICTIONARY leagues_dict (id UInt32, name String, sport_id UInt32, country_id UInt32)
PRIMARY KEY id
SOURCE(CLICKHOUSE(table 'leagues'))
LIFETIME(3600) LAYOUT(HASHED());

-- 3. Countries (200 records, FLAT)
CREATE DICTIONARY countries_dict (id UInt32, name String, code String)
PRIMARY KEY id
SOURCE(CLICKHOUSE(table 'countries'))
LIFETIME(86400) LAYOUT(FLAT());   -- rarely change, update once a day

-- 4. Currencies with historical rates (RANGE)
CREATE DICTIONARY exchange_rates_dict (currency String, start_date Date, end_date Date, rate Decimal(10,4))
PRIMARY KEY currency
SOURCE(CLICKHOUSE(table 'exchange_rates'))
LIFETIME(3600) LAYOUT(RANGE_HASHED()) RANGE(MIN start_date MAX end_date);

-- 5. Commissions by country and bet type (COMPLEX_KEY)
CREATE DICTIONARY commission_dict (country_id UInt32, bet_type String, commission Decimal(5,2))
PRIMARY KEY (country_id, bet_type)
SOURCE(CLICKHOUSE(table 'commissions'))
LIFETIME(7200) LAYOUT(COMPLEX_KEY_HASHED());

Usage in a single query:

SELECT 
    b.user_id,
    dictGet('sports_dict', 'name', b.sport_id) AS sport_name,
    dictGet('leagues_dict', 'name', b.league_id) AS league_name,
    dictGet('countries_dict', 'name', dictGet('leagues_dict', 'country_id', b.league_id)) AS country_name,
    b.amount_eur * dictGet('exchange_rates_dict', 'rate', tuple('EUR', toDate(b.created_at))) AS amount_usd,
    dictGet('commission_dict', 'commission', tuple(dictGet('leagues_dict', 'country_id', b.league_id), 'prematch')) AS commission
FROM bets b
WHERE b.created_at >= today() - 7;

Advantages of this approach:

• Speed: No JOINs, only direct memory lookups.
• Readability: Code is clearer — you can immediately see which dictionaries are used.
• Manageability: Updating a reference (e.g., commission for Spain) happens in one place, not in ETL scripts.
• Memory efficiency: Dictionaries are stored compressed, often taking less space than a denormalized column in a table.

What happens if you don't use dictionaries? You either denormalize data (repeat the sport name in every bet row — multiplying data volume by 10+ times) or perform a JOIN on every aggregation (which is slow and painful on billions of rows).

What's Next

Now you know everything about dictionaries. Next topics:

• Updating dictionaries via HTTP — how to pull data from an external API.
• Using dictionaries in materialized views — for pre-enriching data.
• Dictionary clustering — how dictionaries behave in a ClickHouse cluster (Distributed).

Bottom line: Dictionaries are an indispensable tool for working with reference data in ClickHouse. They turn slow JOINs with small tables into lightning-fast memory lookups. The rule is simple: if the reference doesn't change more than once a minute and its size allows storing it in RAM — make it a dictionary. Your queries will thank you.

---

← Previous: TTL in ClickHouse: Automatic Data Lifecycle Management
→ Next: Special ClickHouse Engines: When MergeTree Doesn't Fit

— Editorial Team