Dictionaries

src/Dictionaries/ implements external dictionaries — in-memory key→value (or key→tuple) lookups that can be queried with dictGet* or via Dictionary table engines. They are independent of MergeTree and serve as the canonical way to enrich queries with reference data.

Capabilities

• Source the data from a long list of backends.
• Cache the data in one of several layouts tuned for different use cases.
• Support hierarchical lookups (dictGetHierarchy, dictIsIn).
• Support range and polygon dictionaries for geo and time-windowed lookups.
• Support complex (multi-column) keys.
• Reload on a schedule, on file mtime change, or on demand.

Layouts (IDictionary subclasses)

Layout	Use case	File
flat	Small dense integer keys (UInt64 indexed array).	FlatDictionary.cpp
hashed	Generic. The default.	HashedDictionary.cpp
sparse_hashed	Hashed with a memory-saving sparse map.	HashedDictionary.cpp
complex_key_hashed	Multi-column key.	HashedDictionary.cpp
range_hashed	Lookup by (key, range_value).	RangeHashedDictionary.cpp
complex_key_range_hashed	Multi-column key + range.	RangeHashedDictionary.cpp
cache / complex_key_cache	LRU cache; loads keys on demand.	CacheDictionary.cpp
ssd_cache / complex_key_ssd_cache	LRU cache with disk overflow.	SSDCacheDictionary.cpp
direct / complex_key_direct	No caching; queries the source on every lookup.	DirectDictionary.cpp
polygon	2D point-in-polygon lookups.	PolygonDictionary*.cpp
regexp_tree	Pattern matching on URLs / user agents.	RegExpTreeDictionary.cpp
ip_trie	IPv4/IPv6 prefix lookups.	IPAddressDictionary.cpp

Sources (IDictionarySource subclasses)

Source	Backend	File
clickhouse	Another ClickHouse table (local or remote).	ClickHouseDictionarySource.cpp
executable / executable_pool	A child process.	ExecutableDictionarySource.cpp, ExecutablePoolDictionarySource.cpp
file	Local file (TSV/CSV/JSONEachRow/Native).	FileDictionarySource.cpp
http / https	HTTP request.	HTTPDictionarySource.cpp
mysql / postgresql / mongodb / redis / cassandra / mssql / clickhouse_dictionary	External databases.	MySQLDictionarySource.cpp, PostgreSQLDictionarySource.cpp, …
xdbc (ODBC)	Generic ODBC bridge.	XDBCDictionarySource.cpp
library	Shared library loaded by clickhouse-library-bridge.	LibraryDictionarySource.cpp

Sources are pluggable: write a class implementing IDictionarySource and register it. Sources usually run a SELECT against the backend, returning a Block of rows that the layout consumes.

Lookup interface

Functions in src/Functions/FunctionsExternalDictionaries.cpp provide the SQL surface:

• dictGet('dict_name', 'attr', key) — typed lookup.
• dictGetOrDefault(...), dictGetOrNull(...) — null/default handling.
• dictGetHierarchy('dict_name', key) — walk the hierarchy upward.
• dictIsIn('dict_name', child, parent) — hierarchy membership.
• dictHas('dict_name', key) — existence test.
• dictGetUInt64, dictGetString, etc. — typed wrappers.

The optimizer can fold sequences of dictGet calls and predicate-pushdown for some sources. DirectJoin (in src/Interpreters/DirectJoin.cpp) lets the planner do a direct hash-table-style join against a dictionary instead of building a temporary one.

Configuration

Two configuration paths:

1. XML/YAML under <dictionaries_config> and per-file in dictionaries/.
2. SQL via CREATE DICTIONARY ... SOURCE(...) LAYOUT(...) LIFETIME(...) PRIMARY KEY .... Stored alongside other access entities; replicates via ReplicatedAccessStorage.

ExternalDictionariesLoader.cpp and ExternalLoader.cpp (in src/Interpreters/) own the lifecycle: scan configs, instantiate sources, apply the layout, watch for mtime/lifetime expiry, expose system.dictionaries.

Reloading

Dictionaries support:

• A LIFETIME(MIN m MAX n) window — the loader picks a refresh time uniformly between m and n.
• SYSTEM RELOAD DICTIONARY <name> — force reload.
• SYSTEM RELOAD DICTIONARIES — reload all.
• <update_field> — incremental reload (only rows newer than the last reload).

Dictionary table engine

StorageDictionary (in src/Storages/StorageDictionary.cpp) presents a dictionary as a regular table. Useful for browsing, joining, or backing materialized views.

Bridges

For ODBC and shared-library dictionary sources, ClickHouse runs out-of-process helpers:

• clickhouse-odbc-bridge (a small daemon in the same binary).
• clickhouse-library-bridge (loads .so plugins for custom sources).

The clients live under src/BridgeHelper/; the daemon code is shared with the main binary.

Entry points for modification

• New layout → subclass IDictionary and add a registration call.
• New source → subclass IDictionarySource and a factory in DictionarySourceFactory.cpp.
• New dictGet* variant → extend FunctionsExternalDictionaries.cpp.

Related pages

• Other engines — StorageDictionary.
• Functions — dictGet*.
• Interpreters — ExternalDictionariesLoader.