How to create, modify, and use external dictionaries in Alibaba Cloud ClickHouse - ApsaraDB for ClickHouse

When your business data contains constant data or can be separated into fact and dimension tables, use dictionaries instead of dimension tables to avoid JOINs and improve query performance. Create, modify, and use external dictionaries in Alibaba Cloud ClickHouse.

Prerequisites

The data source and target cluster must be in the same region and VPC with mutual IP whitelisting. If not, resolve connectivity issues first. How to resolve network connectivity issues between a target cluster and a data source.

Note

Run SELECT * FROM system.clusters; to view the IP addresses of your Alibaba Cloud ClickHouse cluster.

To learn how to configure an IP address whitelist for ClickHouse, see Configure an IP Address Whitelist.

Usage notes

You cannot use the console to manage dictionaries for Alibaba Cloud ClickHouse clusters running version 21.8 or later. Instead, manage them with SQL statements such as CREATE DICTIONARY.

Create a dictionary configuration

Log on to the ApsaraDB for ClickHouse console.
In the upper-left corner of the page, select the region where the target cluster is located.
On the Clusters page, click the Clusters of Community-compatible Edition tab, and then click the ID of your target cluster.
In the left-side navigation pane, click Dictionary Management.
In the upper-right corner of the page, click Add Dictionary Configuration.

In the Add Dictionary Configuration dialog box, configure the parameters.

The following table lists the main dictionary parameters. Full parameter reference: Dictionaries.

Parameter	Description
<name>	Custom dictionary name. Must be globally unique.
<source>	Data source for the dictionary. Supported sources: MySQL ClickHouse
<lifetime>	The update frequency of the dictionary data, in seconds.
<layout>	In-memory layout for the dictionary. Seven types available: Numeric key flat hashed range_hashed cache Composite key complex_key_hashed complex_key_cache ip_trie
<structure>	The dictionary's data structure. Note You can add only one node at a time.

After configuring the parameters, click OK. The dictionary is created.

After creation, view, modify, or delete the dictionary from the Actions column.

Dictionary DDL

Clusters running version 20.8 or later support dictionary DDL to create external dictionaries directly. Check your cluster version on the Cluster Information page in the console.

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)

The following examples create dictionaries from different data sources.

Source: Alibaba Cloud ClickHouse

Prepare the data.

Create a source table.

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

Import test data.

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

Create the dictionary.

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);

Source is another Alibaba Cloud ClickHouse

Prepare the data.

Create a source table.

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

Import test data.

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

Create the dictionary.

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);

ApsaraDB RDS for MySQL

Prepare the data in the ApsaraDB RDS for MySQL instance.

Create a database and a source table.

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

Import test data.

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

Create the dictionary.

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);

Custom query

Prepare the data.

Create a source table.

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

Import test data.

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

Create the dictionary.

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);

Use a dictionary

Query dictionary metadata

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

Query dictionary data

Use dictGet to retrieve data from a dictionary. Full syntax reference: official ClickHouse documentation.

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