system.dictionaries

Contains information about external dictionaries.

Columns:

  • database (String) — Name of the database containing the dictionary created by DDL query. Empty string for other dictionaries.
  • name (String) — Dictionary name.
  • status (Enum8) — 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 — Key type: Numeric Key (UInt64) or Сomposite key (String) — form “(type 1, type 2, …, type n)”.
  • attribute.names (Array(String)) — Array of attribute names provided by the dictionary.
  • attribute.types (Array(String)) — Corresponding array of attribute types that are provided by the dictionary.
  • bytes_allocated (UInt64) — Amount of RAM allocated for the dictionary.
  • 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.
  • 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 external sources and investigate 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.

Example

Configure the dictionary.

  1. CREATE DICTIONARY dictdb.dict
  2. (
  3.     `key` Int64 DEFAULT -1,
  4.     `value_default` String DEFAULT 'world',
  5.     `value_expression` String DEFAULT 'xxx' EXPRESSION 'toString(127 * 172)'
  6. )
  7. PRIMARY KEY key
  8. SOURCE(CLICKHOUSE(HOST 'localhost' PORT 9000 USER 'default' TABLE 'dicttbl' DB 'dictdb'))
  9. LIFETIME(MIN 0 MAX 1)
  10. LAYOUT(FLAT())

Make sure that the dictionary is loaded.

  1. SELECT * FROM system.dictionaries
  1. ┌─database─┬─name─┬─status─┬─origin──────┬─type─┬─key────┬─attribute.names──────────────────────┬─attribute.types─────┬─bytes_allocated─┬─query_count─┬─hit_rate─┬─element_count─┬───────────load_factor─┬─source─────────────────────┬─lifetime_min─┬─lifetime_max─┬──loading_start_time─┌──last_successful_update_time─┬──────loading_duration─┬─last_exception─┐
  2. │ dictdb   │ dict │ LOADED │ dictdb.dict │ Flat │ UInt64 │ ['value_default','value_expression'] │ ['String','String'] │           74032 │           0 │        1 │             1 │ 0.0004887585532746823 │ ClickHouse: dictdb.dicttbl │            0 │            1 │ 2020-03-04 04:17:34 │   2020-03-04 04:30:34        │                 0.002 │                │
  3. └──────────┴──────┴────────┴─────────────┴──────┴────────┴──────────────────────────────────────┴─────────────────────┴─────────────────┴─────────────┴──────────┴───────────────┴───────────────────────┴────────────────────────────┴──────────────┴──────────────┴─────────────────────┴──────────────────────────────┘───────────────────────┴────────────────┘

Original article