Skip to main content
Skip to main content
Edit this page

system.dictionaries

Querying in ClickHouse Cloud

The data in this system table is held locally on each node in ClickHouse Cloud. Obtaining a complete view of all data, therefore, requires the clusterAllReplicas function. See here for further details.

Contains information about dictionaries.

Columns:

  • database (String) — Name of the database containing the dictionary created by DDL query. Empty string for other dictionaries.
  • name (String) — Dictionary name.
  • uuid (UUID) — Dictionary UUID.
  • status (Enum8('NOT_LOADED' = 0, 'LOADED' = 1, 'FAILED' = 2, 'LOADING' = 3, 'FAILED_AND_RELOADING' = 4, 'LOADED_AND_RELOADING' = 5, 'NOT_EXIST' = 6)) — Dictionary status.Possible values:
    • NOT_LOADED — Dictionary was not loaded because it was not used
    • LOADED — Dictionary loaded successfully
    • FAILED — Unable to load the dictionary as a result of an error
    • LOADING — Dictionary is loading now
    • LOADED_AND_RELOADING — Dictionary is loaded successfully
    • and is being reloaded right now (frequent reasons: SYSTEM RELOAD DICTIONARY query —
    • timeout —
    • dictionary config has changed) —
    • FAILED_AND_RELOADING — Could not load the dictionary as a result of an error and is loading now.
  • origin (String) — Path to the configuration file that describes the dictionary.
  • type (String) — Type of a dictionary allocation. Storing Dictionaries in Memory.
  • key.names (Array(String)) — Array of key names provided by the dictionary.
  • key.types (Array(String)) — Corresponding array of key types provided by the dictionary.
  • attribute.names (Array(String)) — Array of attribute names provided by the dictionary.
  • attribute.types (Array(String)) — Corresponding array of attribute types provided by the dictionary.
  • bytes_allocated (UInt64) — Amount of RAM allocated for the dictionary.
  • hierarchical_index_bytes_allocated (UInt64) — Amount of RAM allocated for hierarchical index.
  • query_count (UInt64) — Number of queries since the dictionary was loaded or since the last successful reboot.
  • hit_rate (Float64) — For cache dictionaries, the percentage of uses for which the value was in the cache.
  • found_rate (Float64) — The percentage of uses for which the value was found.
  • element_count (UInt64) — Number of items stored in the dictionary.
  • load_factor (Float64) — Percentage filled in the dictionary (for a hashed dictionary, the percentage filled in the hash table).
  • source (String) — Text describing the data source for the dictionary.
  • lifetime_min (UInt64) — Minimum lifetime of the dictionary in memory, after which ClickHouse tries to reload the dictionary (if invalidate_query is set, then only if it has changed). Set in seconds.
  • lifetime_max (UInt64) — Maximum lifetime of the dictionary in memory, after which ClickHouse tries to reload the dictionary (if invalidate_query is set, then only if it has changed). Set in seconds.
  • loading_start_time (DateTime) — Start time for loading the dictionary.
  • last_successful_update_time (DateTime) — End time for loading or updating the dictionary. Helps to monitor some troubles with dictionary sources and investigate the causes.
  • error_count (UInt64) — Number of errors since last successful loading. Helps to monitor some troubles with dictionary sources and investigate the causes.
  • loading_duration (Float32) — Duration of a dictionary loading.
  • last_exception (String) — Text of the error that occurs when creating or reloading the dictionary if the dictionary couldn't be created.
  • comment (String) — Text of the comment to dictionary.

Example

Configure the dictionary:

CREATE DICTIONARY dictionary_with_comment
(
    id UInt64,
    value String
)
PRIMARY KEY id
SOURCE(CLICKHOUSE(HOST 'localhost' PORT tcpPort() TABLE 'source_table'))
LAYOUT(FLAT())
LIFETIME(MIN 0 MAX 1000)
COMMENT 'The temporary dictionary';

Make sure that the dictionary is loaded.

SELECT * FROM system.dictionaries LIMIT 1 FORMAT Vertical;

Row 1:
──────
database:                    default
name:                        dictionary_with_comment
uuid:                        4654d460-0d03-433a-8654-d4600d03d33a
status:                      NOT_LOADED
origin:                      4654d460-0d03-433a-8654-d4600d03d33a
type:
key.names:                   ['id']
key.types:                   ['UInt64']
attribute.names:             ['value']
attribute.types:             ['String']
bytes_allocated:             0
query_count:                 0
hit_rate:                    0
found_rate:                  0
element_count:               0
load_factor:                 0
source:
lifetime_min:                0
lifetime_max:                0
loading_start_time:          1970-01-01 00:00:00
last_successful_update_time: 1970-01-01 00:00:00
loading_duration:            0
last_exception:
comment:                     The temporary dictionary