tencent cloud

Data Dictionary
最終更新日:2025-03-31 14:55:26
Data Dictionary
最終更新日: 2025-03-31 14:55:26

Feature Overview

ClickHouse offers internal dictionaries and external dictionaries. The former predefines the content and the latter offers various definitions from external data sources. You can use special functions along with a dictionary for query, which is simpler and more efficient than the combination of JOIN and a reference table. This document describes how to use external dictionaries in the console.

Notes

1. A data dictionary maintained in the console will take effect on all ClickHouse nodes.
2. A data dictionary added in the console can only be maintained (modified and deleted) in the console.
3. A data dictionary added using SQL commands or other backend methods cannot be maintained (modified and deleted) in the console.
4. A dictionary table created in the console will be added to the Cloud Data Warehouse global database cdwch_{instanceId}_dictionary_database by default.
Note:
Do not maintain a dictionary via both the console and other means (such as SQL commands and other backend means). Otherwise, content may be overwritten or lost (such as due to accidental deletion).

Directions in Console

1. Log in to the Tencent Cloud TCHouse-C console, select the target cluster in Cluster List, and click the Data Dictionaries tab on the cluster details page.
2. View the list of existing external dictionaries on the current page.

3. Click Create Dictionary in the top-left corner, set the fields as prompted, and click OK to create a dictionary.
Parameter
Description
Reference
Dictionary Name
Enter a custom external dictionary name, which can contain 2–16 lowercase letters, digits, and underscores and must start with a letter and end with a letter or digit.
-
Data Source
Select the type of data source for the external dictionary. Currently, the Cloud Data Warehouse console supports the MySQLClickHouse data source.
Data Source Connection
Configure data source information to verify the connectivity of the configured external source. Configuration items include:

HOST: IP address or domain name. Currently, only VPC connections are supported.
TCP PORT: TCP port
USER: external source account
PASSWORD: password of the external source account
-
Source Table Information
Select the databases and tables of the dependent external source and enter `WHERE` (table filter condition) and `INVALIDATEQUERY` (for querying/checking the dictionary status, with only the updated data extracted).
Data Structure
Set the primary key and general fields of the external dictionary, including:

PRIMARY_KEY: single or composite primary key.
COLUME_NAME: field type. Currently, the console supports the following types: UInt8, UInt16, UInt32, UInt64, Int8, Int16, Int32, Int64, Float32, Float64, Date, DateTime, UUID, and String.
DEFAULT_VALUE: default value for empty fields
EXPRESSION: expression to describe fields (if applicable)
IS_HIERARCHICAL: indicates the support for hierarchy. The default value is `false`.
S_INJECTIVE: indicates the inline mapping "id -> attribute". The default value is `false`.
Storage Format
Select the type of memory layout for the external dictionary, including:

FLAT: stores the entire dictionary in memory as a flat array, which is suitable for a single primary key.
HASHED: stores the entire dictionary in memory as a hash table, which is suitable for a single primary key.
RANGE_HASHED: stores the entire dictionary in memory as a hash table. It comes with an ordered array of ranges and corresponding values, which is suitable for a single primary key. You need to set the fields to represent the ranges.
CACHE: stores the entire dictionary in a cache with a certain number of cells, which is suitable for a single primary key. You need to set the cache size.
COMPLEX_KEY_HASHED: similar to HASHED and suitable for composite primary keys.
COMPLEX_KEY_CACHE: similar to CACHE and suitable for composite primary keys. You need to set the cache size.
Update Interval
Set the frequency of updating data in the dictionary. The unit is s.
3. After the external dictionary is created, you can view, modify, and delete it in the list.

Managing External Dictionary via SQL

1. Create a dictionary.
CREATE DICTIONARY [IF NOT EXISTS] [db.]dictionary_name [ON CLUSTER cluster]
(
 key1 type1  [DEFAULT|EXPRESSION expr1] [IS_OBJECT_ID],
 key2 type2  [DEFAULT|EXPRESSION expr2],
 attr1 type2 [DEFAULT|EXPRESSION expr3] [HIERARCHICAL|INJECTIVE],
 attr2 type2 [DEFAULT|EXPRESSION expr4] [HIERARCHICAL|INJECTIVE]
)
PRIMARY KEY key1, key2
SOURCE(SOURCE_NAME([param1 value1 ... paramN valueN]))
LAYOUT(LAYOUT_NAME([param_name param_value])``)
LIFETIME({MIN min_val MAX max_val | max_val})
2. View the dictionary.
SELECT * FROM system.dictionaries
3. Delete the dictionary.
DROP DICTIONARY <database name>.<dictionary name>
For more information on dictionary SQL, see CREATE DICTIONARY.

Using External Dictionary

You can query some types of dictionaries using general SELECT:
SELECT * FROM <database name>.<dictionary name>
You can also query them using dictionary functions:
dictGet('dict_name', attr_names, id_expr)
dictGetOrDefault('dict_name', attr_names, id_expr, default_value_expr)
dictGetOrNull('dict_name', attr_name, id_expr)
For more information on dictionary functions, see Functions for Working with External Dictionaries.
この記事はお役に立ちましたか？
営業担当者に お問い合わせ いただくか チケットを提出 してサポートを求めることができます。
はい
いいえ

フィードバック