ClickHouse Dictionaries
Overview
ClickHouse Dictionaries are in-memory key-value stores designed for fast point lookups. They are significantly faster than JOINs for repeated lookups of static or slowly-changing reference data. Moose's OlapDictionary class wraps ClickHouse CREATE DICTIONARY and manages the full lifecycle — creation, updates via CREATE OR REPLACE DICTIONARY, and teardown.
When to use a Dictionary
Enriching events with reference data (country codes, product categories, user tiers)
Replacing repeated JOINs in Materialized Views with a single dictGet() call
Low-latency lookups from external systems (MySQL, PostgreSQL, HTTP, Redis)
Slowly-changing dimension tables that should be cached in memory
ClickHouse 22.4+ required
Dictionaries require ClickHouse 22.4 or later. The CREATE OR REPLACE DICTIONARY syntax used for updates requires ClickHouse 22.9+.
Basic Usage
Define a dictionary over an existing OlapTable or View. The primaryKey specifies which column(s) to use as the lookup key. Use a HASHED layout for single integer (UInt64) keys, and COMPLEX_KEY_HASHED for multi-column or string keys.
import { OlapDictionary, OlapTable, UInt64 } from "@514labs/moose-lib"; // Source table (already defined elsewhere)import { ProductsTable } from "./Products"; // The T interface describes the dictionary attributes.// Column names and types are inferred from T by the Moose compiler plugin.interface ProductLookup { productId: UInt64; // primary key — UInt64 integer → use HASHED layout productName: string; // attribute category: string; // attribute priceLevel: number; // attribute} export const productDict = new OlapDictionary<ProductLookup>("dict_products", { sourceTable: ProductsTable, primaryKey: ["productId"], layout: { type: "HASHED" }, lifetime: 3600, // reload from ClickHouse every hour});Using a Dictionary in a Materialized View
Once defined, use dictGet helpers to reference the dictionary in a Materialized View SELECT:
import { MaterializedView, sql } from "@514labs/moose-lib";import { eventsTable } from "./Events";import { productDict } from "./productDict"; interface EnrichedEvent { eventId: string; productId: number; productName: string; category: string;} export const enrichedEventsMV = new MaterializedView<EnrichedEvent>({ tableName: "EnrichedEvents", materializedViewName: "EnrichedEvents_MV", orderByFields: ["eventId"], selectStatement: sql.statement` SELECT ${eventsTable.columns.eventId}, ${eventsTable.columns.productId}, ${productDict.get("productName", "productId")} AS productName, ${productDict.get("category", "productId")} AS category FROM ${eventsTable} `, selectTables: [eventsTable],});Layout Types
The layout controls how the dictionary is stored in memory. Choose based on your key type and access pattern.
| Layout | Key type | Best for |
|---|---|---|
FLAT | UInt64 (0–500 000) | Small sequential integer keys |
HASHED | UInt64 | General-purpose integer keys |
SPARSE_HASHED | UInt64 | Memory-constrained environments (~3× less RAM) |
HASHED_ARRAY | UInt64 | High-concurrency multi-threaded reads |
COMPLEX_KEY_HASHED | String / multi-column | String keys or composite keys |
COMPLEX_KEY_SPARSE_HASHED | String / multi-column | Memory-constrained string keys |
CACHE | UInt64 | LRU cache — only loads accessed keys |
COMPLEX_KEY_CACHE | String / multi-column | LRU cache with complex keys |
DIRECT | UInt64 | No caching — reads source on every lookup |
IP_TRIE | IPv4/IPv6 | Longest-prefix match for IP ranges |
HASHED vs COMPLEX_KEY_HASHED
Use HASHED for single UInt64 numeric keys. Use COMPLEX_KEY_HASHED when
your primary key is a string, or when you need a composite (multi-column) key.
COMPLEX_KEY_* layouts support one or more columns of any type in primaryKey.
Lifetime
The lifetime controls how often ClickHouse reloads the dictionary from its source.
// Static — never reloads (use for immutable data)lifetime: 0 // Reload every 5 minuteslifetime: 300 // Reload with jitter: between 5 and 6 minutes (avoids thundering herd)lifetime: { min: 300, max: 360 }Source Types
Local ClickHouse Table (recommended)
Point sourceTable at an OlapTable or View on the same ClickHouse instance. Moose automatically tracks the dependency for correct DDL ordering.
export const myDict = new OlapDictionary<MyLookup>("dict_my_lookup", { sourceTable: MyTable, // OlapTable or View primaryKey: ["id"], layout: { type: "HASHED" }, lifetime: 3600,});SQL Query Source
Use sourceQuery + sourceTables to define the dictionary from an arbitrary SQL query. This lets you pre-filter or join data before loading it into the dictionary.
import { sql } from "@514labs/moose-lib"; export const myDict = new OlapDictionary<ActiveProductLookup>("dict_active_products", { sourceQuery: sql` SELECT product_id, product_name, category FROM products WHERE is_active = 1 `, sourceTables: [ProductsTable], // required for dependency tracking primaryKey: ["product_id"], layout: { type: "HASHED" }, lifetime: 600,});External Sources
Connect to MySQL, PostgreSQL, HTTP endpoints, Redis, MongoDB, or S3. Use mooseRuntimeEnv.get() for credentials — never hardcode secrets.
import { mooseRuntimeEnv } from "@514labs/moose-lib"; export const myDict = new OlapDictionary<ProductLookup>("dict_mysql_products", { externalSource: { type: "mysql", host: mooseRuntimeEnv.get("MYSQL_HOST"), port: 3306, user: mooseRuntimeEnv.get("MYSQL_USER"), password: mooseRuntimeEnv.get("MYSQL_PASSWORD"), db: "mydb", table: "products", }, primaryKey: ["product_id"], layout: { type: "HASHED" }, lifetime: 300,});Lifecycle Management
OlapDictionary supports the same lifeCycle options as OlapTable:
LifeCycle.FULLY_MANAGED(default) — Moose creates, updates, and drops the dictionary.LifeCycle.DELETION_PROTECTED— Moose can create but will not drop.LifeCycle.EXTERNALLY_MANAGED— Moose never touches this dictionary (use for dictionaries managed outside Moose).
import { OlapDictionary, LifeCycle } from "@514labs/moose-lib"; export const legacyDict = new OlapDictionary<LegacyLookup>("dict_legacy", { sourceTable: LegacyTable, primaryKey: ["id"], layout: { type: "HASHED" }, lifetime: 0, lifeCycle: LifeCycle.EXTERNALLY_MANAGED, // Moose will never touch this});SQL Helper Methods
OlapDictionary provides typed helpers for generating dictGet, dictGetOrDefault, and dictHas SQL expressions.
// dictGet — raises error if key not foundproductDict.get("productName", "product_id")// → dictGet('local.dict_products', 'productName', `product_id`) // dictGetOrDefault — returns default if key not foundproductDict.getOrDefault("category", "Unknown", "product_id")// → dictGetOrDefault('local.dict_products', 'category', `product_id`, 'Unknown') // dictHas — returns 1 if key exists, 0 otherwiseproductDict.has("product_id")// → dictHas('local.dict_products', `product_id`)