cache dictionary layout

The cached dictionary layout type is stores the dictionary in a cache that has a fixed number of cells. These cells contain frequently used elements. The dictionary key has the UInt64 type. When searching for a dictionary, the cache is searched first. For each block of data, all keys that are not found in the cache or are outdated are requested from the source using SELECT attrs... FROM db.table WHERE id IN (k1, k2, ...). The received data is then written to the cache. That applies to looking a key up - dictGet and the other dictionary functions. Reading the dictionary as a table with SELECT ... FROM <dictionary> is different: because a cache keeps no record of which keys exist, the read enumerates only the cells that happen to be resident in the cache at that moment and that hold a value, and a WHERE on the key is an ordinary filter over those cells, not a list of keys to fetch. A key that is not in the cache cannot be discovered this way, no matter what the WHERE says. A key that was looked up but was not found at the source is not visible either: the cache remembers the miss as a default cell, and a table read skips default cells. Resident cells are not free of the source either: an expired cell is read through the same path as dictGet, so it is re-requested from the source - synchronously, or asynchronously if allow_read_expired_keys is enabled.

CREATE DICTIONARY cache_dict (id UInt64, data String) PRIMARY KEY id
SOURCE(CLICKHOUSE(TABLE 'cache_src')) LIFETIME(MIN 0 MAX 900) LAYOUT(CACHE(SIZE_IN_CELLS 1000));

-- nothing is cached yet, so nothing comes back and the source is not queried
SELECT count() FROM cache_dict WHERE id IN (1, 2, 3);
0

-- looking the keys up populates the cache
SELECT dictGet('cache_dict', 'data', toUInt64(number + 1)) FROM numbers(3);

-- and now the same read sees them
SELECT count() FROM cache_dict WHERE id IN (1, 2, 3);
3

So a cache dictionary is meant to be used through the dictionary functions. If you need a lookup of arbitrary keys to always reach the source, use dictGet with the direct layout, which queries the source on every lookup and caches nothing. Note that a table read of a direct dictionary is not a keyed fetch either: SELECT ... FROM <dictionary> WHERE key IN (...) loads the whole source and filters afterwards, because ClickHouse does not push the key filter into the dictionary. To read a dictionary as a table, use a layout that holds all of it, such as flat or hashed. If keys are not found in dictionary, then update cache task is created and added into update queue. Update queue properties can be controlled with settings max_update_queue_size, update_queue_push_timeout_milliseconds, query_wait_timeout_milliseconds, max_threads_for_updates. For cache dictionaries, the expiration lifetime of data in the cache can be set. If more time than lifetime has passed since loading the data in a cell, the cell’s value is not used and key becomes expired. The key is re-requested the next time it needs to be used. This behaviour can be configured with setting allow_read_expired_keys. This is the least effective of all the ways to store dictionaries. The speed of the cache depends strongly on correct settings and the usage scenario. A cache type dictionary performs well only when the hit rates are high enough (recommended 99% and higher). You can view the average hit rate in the system.dictionaries table. If setting allow_read_expired_keys is set to 1, by default 0. Then dictionary can support asynchronous updates. If a client requests keys and all of them are in cache, but some of them are expired, then dictionary will return expired keys for a client and request them asynchronously from the source. To improve cache performance, use a subquery with LIMIT, and call the function with the dictionary externally. All types of sources are supported. Example of settings:

DDL
Configuration file

LAYOUT(CACHE(SIZE_IN_CELLS 1000000000))

<layout>
    <cache>
        <!-- The size of the cache, in number of cells. Rounded up to a power of two. -->
        <size_in_cells>1000000000</size_in_cells>
        <!-- Allows to read expired keys. -->
        <allow_read_expired_keys>0</allow_read_expired_keys>
        <!-- Max size of update queue. -->
        <max_update_queue_size>100000</max_update_queue_size>
        <!-- Max timeout in milliseconds for push update task into queue. -->
        <update_queue_push_timeout_milliseconds>10</update_queue_push_timeout_milliseconds>
        <!-- Max wait timeout in milliseconds for update task to complete. -->
        <query_wait_timeout_milliseconds>60000</query_wait_timeout_milliseconds>
        <!-- Max threads for cache dictionary update. -->
        <max_threads_for_updates>4</max_threads_for_updates>
    </cache>
</layout>

Set a large enough cache size. You need to experiment to select the number of cells:

Set some value.
Run queries until the cache is completely full.
Assess memory consumption using the system.dictionaries table.
Increase or decrease the number of cells until the required memory consumption is reached.

ClickHouse is not recommended as a source for this layout. Dictionary lookups require random point reads, which are not the access pattern ClickHouse is optimized for.

SQL Reference

Data Types

Engines

Functions

Formats

Interfaces

Settings

System Tables

Data Lakes