Day 63: Advanced ClickHouse® Dictionaries – Integrating External Data Sources for Faster Lookups

Introduction

Modern analytics platforms rarely store all business data in one place. Customer information may reside in PostgreSQL, product catalogs in MySQL, pricing information behind REST APIs, and configuration data inside Redis. Meanwhile, ClickHouse® serves as the analytical engine that powers dashboards and reports.

Synchronizing all this reference data into ClickHouse® tables isn't always practical. It increases storage requirements, complicates ETL pipelines, and creates additional maintenance overhead.

ClickHouse® Dictionaries solve this challenge by providing an efficient mechanism for retrieving external reference data during query execution. Instead of relying on expensive JOIN operations, dictionaries perform ultra-fast key-value lookups, making analytical queries both simpler and faster.

In this guide, you'll learn how ClickHouse® Dictionaries work, supported data sources, dictionary layouts, refresh strategies, performance considerations, and best practices for production deployments.

---

What Are ClickHouse® Dictionaries?

A ClickHouse® Dictionary is an optimized in-memory lookup structure that retrieves values based on one or more keys.

Unlike database tables that are designed for scanning millions of rows, dictionaries focus on instant key-based access.

Consider a traditional JOIN:

SELECT
    o.order_id,
    c.customer_name
FROM orders o
JOIN customers c
ON o.customer_id = c.customer_id;

Using a dictionary, the same lookup becomes:

SELECT
    order_id,
    dictGet(
        'customer_dictionary',
        'customer_name',
        customer_id
    ) AS customer_name
FROM orders;

Instead of joining large datasets, ClickHouse® performs a direct lookup against an optimized dictionary, reducing execution time for many analytical workloads.

---

Why Use External Dictionaries?

Many organizations separate operational databases from analytical systems.

For example:

• Customer records stored in PostgreSQL
• Product information stored in MySQL
• Exchange rates retrieved from REST APIs
• Configuration values stored in Redis
• Shared lookup tables maintained in another ClickHouse® cluster

Without dictionaries, this data typically needs to be copied into ClickHouse®, creating duplicate storage and additional synchronization logic.

External dictionaries eliminate that complexity by allowing ClickHouse® to retrieve reference data directly from its source while caching results for high-speed access.

Key benefits include:

• Faster lookup performance
• Reduced dependency on JOIN operations
• Less duplicate storage
• Cleaner SQL queries
• Centralized management of reference data
• Better analytical performance

---

Supported External Data Sources

One of the biggest strengths of ClickHouse® Dictionaries is their flexibility.

They support numerous external systems, including:

• ClickHouse® tables
• PostgreSQL
• MySQL
• Redis
• HTTP and HTTPS endpoints
• Executable scripts
• ODBC-compatible databases
• MongoDB (through supported integrations)

This broad compatibility allows organizations to integrate existing infrastructure without redesigning their data architecture.

---

How Dictionaries Work

The lookup process is straightforward.

Application Query
        │
        ▼
ClickHouse Query Engine
        │
        ▼
Dictionary
        │
        ▼
Memory Cache
        │
        ▼
External Data Source
(PostgreSQL / MySQL / Redis / HTTP)

During execution:

1. The query requests a value from a dictionary.
2. ClickHouse® checks whether the value already exists in memory.
3. If available, the result is returned immediately.
4. If the cache requires refreshing, ClickHouse® reloads data from the external source according to the configured refresh interval.

Because frequently accessed data remains cached, repeated lookups avoid unnecessary network requests.

---

Creating an External Dictionary

Creating a dictionary is straightforward.

Example using PostgreSQL:

CREATE DICTIONARY customer_dictionary
(
    customer_id UInt64,
    customer_name String DEFAULT 'Unknown Customer',
    country String DEFAULT 'Unknown Country'
)
PRIMARY KEY customer_id
SOURCE(
    POSTGRESQL(
        HOST 'postgres'
        PORT 5432
        USER 'user'
        PASSWORD 'password'
        DB 'sales'
        TABLE 'customers'
    )
)
LAYOUT(HASHED())
LIFETIME(MIN 300 MAX 600);

This configuration specifies:

• PostgreSQL as the source
• customer_id as the lookup key
• Hashed layout for fast access
• Automatic refresh every 5–10 minutes

Different source types require different configuration options, but the overall structure remains similar.

---

Dictionary Functions

ClickHouse® provides several built-in functions for working with dictionaries.

dictGet()

Returns a value associated with a key.

SELECT
    customer_id,
    dictGet(
        'customer_dictionary',
        'customer_name',
        customer_id
    ) AS customer_name
FROM orders;

---

dictHas()

Checks whether a key exists.

SELECT
    customer_id,
    dictHas(
        'customer_dictionary',
        customer_id
    )
FROM orders;

---

dictGetOrDefault()

Returns a fallback value when a key cannot be found.

SELECT
    dictGetOrDefault(
        'customer_dictionary',
        'country',
        customer_id,
        'Unknown'
    );

Alternatively, default values can be defined directly in the dictionary schema using the DEFAULT keyword, allowing standard dictGet() calls to automatically return fallback values.

---

Choosing the Right Dictionary Layout

Dictionary layout directly affects lookup speed, memory consumption, and scalability.

HASHED

Ideal for:

• Small and medium datasets
• Unique keys
• Maximum lookup performance

---

CACHE

Best suited for:

• Very large datasets
• On-demand loading
• Lower memory usage

---

COMPLEX_KEY_HASHED

Designed for composite keys involving multiple columns.

Useful when lookups depend on more than one attribute.

---

RANGE_HASHED

Optimized for range-based lookups.

Typical use cases include:

• Historical pricing
• Effective dates
• Time intervals
• Versioned records

Selecting the appropriate layout ensures optimal performance while minimizing resource usage.

---

Refresh Strategies

External data changes over time.

ClickHouse® controls refresh behavior using the LIFETIME setting.

Example:

LIFETIME(MIN 300 MAX 600)

This allows dictionaries to refresh approximately every five to ten minutes.

Choosing the right refresh interval involves balancing:

• Data freshness
• Query performance
• Memory usage
• Load on external systems

Static reference data generally requires less frequent updates, while dynamic datasets may need shorter refresh intervals.

---

Performance Considerations

Although dictionaries are extremely efficient, they aren't a replacement for every JOIN.

Dictionaries perform best when:

• Reference datasets are relatively small
• Queries require frequent key-based lookups
• Data changes infrequently
• Low-latency access is essential

Traditional JOINs remain the better option when:

• Combining large transactional tables
• Performing complex relational analysis
• Relationships change frequently
• Multiple large datasets must be processed together

Understanding these differences helps you choose the right tool for each workload.

---

Best Practices

To maximize dictionary performance:

• Use dictionaries for lookup and dimension data.
• Select layouts based on dataset size and access patterns.
• Configure refresh intervals appropriate for business requirements.
• Monitor memory usage regularly.
• Avoid loading excessively large datasets entirely into memory.
• Benchmark dictionary performance under realistic workloads.
• Keep dictionary schemas synchronized with source systems.

Following these practices ensures reliable and efficient operation in production.

---

Common Mistakes to Avoid

Using Dictionaries for Large Fact Tables

Dictionaries are intended for reference data, not billions of transactional records.

---

Refreshing Too Frequently

Very short refresh intervals can overload external systems without delivering significant business value.

---

Selecting the Wrong Layout

Using a hashed layout for massive datasets or cache layouts for heavily accessed small datasets can reduce performance.

---

Ignoring Memory Usage

High-speed layouts consume memory. Always evaluate available system resources before choosing a layout.

---

When Should You Use Dictionaries?

Use dictionaries for:

• Customer information
• Product metadata
• Country and region lookups
• Configuration settings
• Currency exchange rates
• Small reference datasets

Use JOINs for:

• Large transactional datasets
• Complex analytical queries
• Multi-table aggregations
• Frequently changing relationships

Choosing the right approach leads to simpler queries and better overall performance.

---

Conclusion

ClickHouse® Dictionaries are one of the platform's most powerful optimization features for analytical workloads.

By connecting directly to external systems such as PostgreSQL, MySQL, Redis, HTTP services, and other ClickHouse® clusters, dictionaries enable high-speed lookups without the cost of traditional JOIN operations.

When combined with the appropriate layout, sensible refresh intervals, and thoughtful data modeling, they can significantly reduce query latency while simplifying SQL queries and lowering storage duplication.

As your analytics environment