如何建立、修改和使用ClickHouse外部擴充字典 - ApsaraDB for ClickHouse

在數倉開發和資料分析過程中，當業務中有常量資料或者業務資料可以劃分為業務事實表和業務維度資料表時，您可以通過使用資料字典來替代維度資料表實現，避免使用Join語句，從而提升查詢效率。本文為您介紹雲資料庫ClickHouse如何建立、修改和使用外部擴充字典。

前提條件

資料來源與目的地組群使用了相同的VPC，且在同一地區下，並將彼此的IP添加到了對方白名單中。如果不滿足此條件，請先解決網路問題。具體操作，請參見如何解決目的地組群與資料來源網路互連問題。

說明

您可以通過SELECT * FROM system.clusters;命令查看雲資料庫ClickHouse叢集的IP地址。

ClickHouse如何添加白名單，請參見設定白名單。

注意事項

21.8及以上版本的雲資料庫ClickHouse叢集不支援通過控制台管理資料字典。您可以通過SQL查看和建立資料字典。具體操作，請參見建立字典。

建立字典配置

登入雲資料庫ClickHouse控制台。
在頁面左上方，選擇目的地組群所在的地區。
在叢集列表頁面，選擇社區版執行個體列表，單擊目的地組群ID。
在左側導覽列，單擊字典管理，進入字典列表頁面。
單擊右上方新增字典配置。

在彈出的新增字典配置視窗編輯模板填寫字典內容。

此處只介紹字典配置的主要參數，更多參數，請參見Dictionaries。

參數	描述
<name>	自訂字典名稱，必須全域唯一。
<source>	配置字典的資料來源，表示字典中的資料來源。目前ClickHouse支援的資料來源為： MySQL ClickHouse
<lifetime>	字典中資料的更新頻率，單位：秒。
<layout>	記憶體中的資料格式類型，目前擴充字典共擁有7種類型。單數值key flat hashed range_hashed cache 複合key complex_key_hashed complex_key_cache ip_trie
<structure>	字典的資料結構。說明單次只允許添加一個節點。

填寫完畢後單擊確定，資料字典建立完成。
建立成功後，在資料字典對應的操作列，可進行查看、刪除和修改。

字典表DDL

雲資料庫ClickHouse叢集20.8之後的版本（您可以登入ClickHouse控制台-叢集資訊-查看版本號碼）增加了資料字典的DDL，您可以直接使用DDL語句建立擴充字典表。

CREATE DICTIONARY [IF NOT EXISTS] [db.]dictionary_name [ON CLUSTER cluster]
(
    key1 type1  [DEFAULT|EXPRESSION expr1] [HIERARCHICAL|INJECTIVE|IS_OBJECT_ID],
    key2 type2  [DEFAULT|EXPRESSION expr2] [HIERARCHICAL|INJECTIVE|IS_OBJECT_ID],
    attr1 type2 [DEFAULT|EXPRESSION expr3],
    attr2 type2 [DEFAULT|EXPRESSION expr4]
)
PRIMARY KEY key1, key2
SOURCE(SOURCE_NAME([param1 value1 ... paramN valueN]))
LAYOUT(LAYOUT_NAME([param_name param_value]))
LIFETIME([MIN val1] MAX val2)

建立樣本如下：

源端為當前雲資料庫ClickHouse

資料準備。

建立源表。

CREATE TABLE default.cities ( id UInt64, city_name String)ENGINE = Memory;

匯入測試資料。

INSERT INTO default.cities (id, city_name) VALUES(1, 'Beijing'),(2, 'Shanghai'),(3, 'Guangzhou'),(4, 'Shenzhen');

建立字典表。

CREATE DICTIONARY default.city_dict (
 id UInt64,
 city_name String
)
PRIMARY KEY id
SOURCE(CLICKHOUSE( USER 'username' PASSWORD 'password' TABLE 'cities' DB 'default' ))
LAYOUT(HASHED())
LIFETIME(MIN 300 MAX 360);

源端為其他雲資料庫ClickHouse

資料準備。

建立源表。

CREATE TABLE default.cities ( id UInt64, city_name String)ENGINE = Memory;

匯入測試資料。

INSERT INTO default.cities (id, city_name) VALUES(1, 'Beijing'),(2, 'Shanghai'),(3, 'Guangzhou'),(4, 'Shenzhen');

建立字典表。

CREATE DICTIONARY default.city_dict (
 id UInt64,
 city_name String
)
PRIMARY KEY id
SOURCE(CLICKHOUSE(
 HOST 'cc-xxx.clickhouse.ads.aliyuncs.com'
 PORT 3306
 USER 'username'
 PASSWORD 'password'
 DB 'default'
 TABLE 'cities'
))
LAYOUT(HASHED())
LIFETIME(MIN 300 MAX 360);

源端為RDS MySQL執行個體

RDS Mysql側資料準備。

建立資料庫與源表。

CREATE DATABASE testdb;
CREATE TABLE testdb.cities ( id INT PRIMARY KEY, city_name VARCHAR(255));

匯入測試資料。

INSERT INTO testdb.cities (id, city_name) VALUES(1, 'Beijing'),(2, 'Shanghai'),(3, 'Guangzhou'),(4, 'Shenzhen');

建立字典表。

CREATE DICTIONARY default.city_dict (
 id UInt64,
 city_name String
)
PRIMARY KEY id
SOURCE(MYSQL(
 HOST 'rm-xxx.mysql.rds.aliyuncs.com'
 PORT 3306
 USER 'username'
 PASSWORD 'password'
 DB 'testdb'
 TABLE 'cities'
))
LAYOUT(HASHED())
LIFETIME(MIN 300 MAX 360);

帶有query語句的字典表

資料準備。

建立源表。

CREATE TABLE default.cities ( id UInt64, city_name String)ENGINE = Memory;

匯入測試資料。

INSERT INTO default.cities (id, city_name) VALUES(1, 'Beijing'),(2, 'Shanghai'),(3, 'Guangzhou'),(4, 'Shenzhen');

建立字典表。

CREATE DICTIONARY default.my_dict
(
 id UInt64,
 city_name String
)
PRIMARY KEY id
SOURCE(CLICKHOUSE(
USER 'username' 
PASSWORD 'password' 
DB 'default' 
QUERY 'SELECT id, city_name FROM default.cities where id<2'
))
LAYOUT(HASHED())
LIFETIME(MIN 300 MAX 600);

資料字典的使用

字典表查詢

SELECT
    name,
    type,
    key,
    attribute.names,
    attribute.types,
    bytes_allocated,
    element_count,
    source
FROM system.dictionaries

查詢字典資料

使用dictGet函數擷取資料字典中的資料。dictGet函數的更多詳情，請參見ClickHouse官方文檔。

dictGet(<dict_name>, <attr_name>, <id_expr>)
dictGetOrDefault(<dict_name>, <attr_name>, <id_expr>, <default_value_expr>)