system.dictionaries

[Edit this page](https://github.com/ClickHouse/ClickHouse/tree/master/docs/en/operations/system-tables/dictionaries.md)

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](/docs/operations/system-tables/overview#system-tables-in-clickhouse-cloud) for further details.

Contains information about [dictionaries](/docs/sql-reference/statements/create/dictionary).

Columns:

- `database` ([String](/docs/sql-reference/data-types)) — Name of the database containing the dictionary created by DDL query. Empty string for other dictionaries.
- `name` ([String](/docs/sql-reference/data-types)) — Dictionary name.
- `uuid` ([UUID](/docs/sql-reference/data-types)) — Dictionary UUID.
- `status` ([Enum8('NOT\_LOADED' = 0, 'LOADED' = 1, 'FAILED' = 2, 'LOADING' = 3, 'FAILED\_AND\_RELOADING' = 4, 'LOADED\_AND\_RELOADING' = 5, 'NOT\_EXIST' = 6)](/docs/sql-reference/data-types)) — 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](/docs/sql-reference/data-types)) — Path to the configuration file that describes the dictionary.
- `type` ([String](/docs/sql-reference/data-types)) — Type of a dictionary allocation. Storing Dictionaries in Memory.
- `key.names` ([Array(String)](/docs/sql-reference/data-types)) — Array of key names provided by the dictionary.
- `key.types` ([Array(String)](/docs/sql-reference/data-types)) — Corresponding array of key types provided by the dictionary.
- `attribute.names` ([Array(String)](/docs/sql-reference/data-types)) — Array of attribute names provided by the dictionary.
- `attribute.types` ([Array(String)](/docs/sql-reference/data-types)) — Corresponding array of attribute types provided by the dictionary.
- `bytes_allocated` ([UInt64](/docs/sql-reference/data-types)) — Amount of RAM allocated for the dictionary.
- `hierarchical_index_bytes_allocated` ([UInt64](/docs/sql-reference/data-types)) — Amount of RAM allocated for hierarchical index.
- `query_count` ([UInt64](/docs/sql-reference/data-types)) — Number of queries since the dictionary was loaded or since the last successful reboot.
- `hit_rate` ([Float64](/docs/sql-reference/data-types)) — For cache dictionaries, the percentage of uses for which the value was in the cache.
- `found_rate` ([Float64](/docs/sql-reference/data-types)) — The percentage of uses for which the value was found.
- `element_count` ([UInt64](/docs/sql-reference/data-types)) — Number of items stored in the dictionary.
- `load_factor` ([Float64](/docs/sql-reference/data-types)) — Percentage filled in the dictionary (for a hashed dictionary, the percentage filled in the hash table).
- `source` ([String](/docs/sql-reference/data-types)) — Text describing the data source for the dictionary.
- `lifetime_min` ([UInt64](/docs/sql-reference/data-types)) — 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](/docs/sql-reference/data-types)) — 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](/docs/sql-reference/data-types)) — Start time for loading the dictionary.
- `last_successful_update_time` ([DateTime](/docs/sql-reference/data-types)) — End time for loading or updating the dictionary. Helps to monitor some troubles with dictionary sources and investigate the causes.
- `error_count` ([UInt64](/docs/sql-reference/data-types)) — Number of errors since last successful loading. Helps to monitor some troubles with dictionary sources and investigate the causes.
- `loading_duration` ([Float32](/docs/sql-reference/data-types)) — Duration of a dictionary loading.
- `last_exception` ([String](/docs/sql-reference/data-types)) — Text of the error that occurs when creating or reloading the dictionary if the dictionary couldn't be created.
- `comment` ([String](/docs/sql-reference/data-types)) — 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
```

Was this page helpful?