Alibaba Cloud ClickHouse で外部辞書を作成、変更、使用する方法 - ApsaraDB for ClickHouse

ビジネスデータに定数データが含まれている場合、またはファクトテーブルとディメンションテーブルに分離できる場合は、ディメンションテーブルの代わりに辞書を使用して JOIN を回避し、クエリパフォーマンスを向上させることができます。 Alibaba Cloud ClickHouse では、外部辞書を作成、変更、使用できます。

前提条件

データソースとターゲットクラスターは、同じリージョンおよび VPC 内に存在し、相互に IP アドレスホワイトリストに登録されている必要があります。そうでない場合は、まず接続の問題を解決してください。ターゲットクラスターとデータソース間のネットワーク接続の問題を解決する方法。

説明

SELECT * FROM system.clusters; を実行して、Alibaba Cloud ClickHouse クラスターの IP アドレスを確認します。

ClickHouse の IP アドレスホワイトリストの設定方法については、「IP アドレスホワイトリストの設定」をご参照ください。

注意事項

バージョン 21.8 以降を実行している Alibaba Cloud ClickHouse クラスターの場合、コンソールを使用して辞書を管理することはできません。代わりに、CREATE DICTIONARY などの SQL ステートメントを使用して管理してください。

辞書設定の作成

ApsaraDB for ClickHouse コンソールにログインします。
ページの左上隅で、ターゲットクラスターが配置されているリージョンを選択します。
クラスターリスト ページで、Community Edition インスタンスのリスト タブをクリックして、対象クラスターの ID をクリックします。
左側のナビゲーションペインで、辞書管理 をクリックします。
ページの右上隅で、辞書構成を追加する をクリックします。

辞書構成を追加する ダイアログボックスで、パラメーターを設定します。

次の表に、主要な辞書パラメーターを示します。パラメーターの詳細については、「Dictionaries」をご参照ください。

パラメーター	説明
<name>	カスタム辞書名。グローバルに一意である必要があります。
<source>	辞書のデータソース。サポートされているソース： MySQL ClickHouse
<lifetime>	辞書データの更新頻度 (秒単位)。
<layout>	辞書のメモリ内レイアウト。 7 つのタイプが利用可能です。数値キー flat hashed range_hashed cache 複合キー complex_key_hashed complex_key_cache ip_trie
<structure>	辞書のデータ構造。説明一度に追加できる属性は 1 つのみです。

パラメーターを設定してを決定をクリックすると、辞書が作成されます。

作成後、**[操作]** 列で辞書を表示、変更、または削除できます。

辞書 DDL

バージョン 20.8 以降を実行しているクラスターは、辞書 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)

次の例では、さまざまなデータソースから辞書を作成します。

ソース： Alibaba Cloud 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);

ソースは別の Alibaba Cloud 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 9000
 USER 'username'
 PASSWORD 'password'
 DB 'default'
 TABLE 'cities'
))
LAYOUT(HASHED())
LIFETIME(MIN 300 MAX 360);

ApsaraDB RDS for MySQL

ApsaraDB RDS for 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);

カスタムクエリ

データを準備します。

ソーステーブルを作成します。

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 を使用して辞書からデータを取得します。構文の詳細については、「ClickHouse 公式ドキュメント」をご参照ください。

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