This topic describes how to create, modify, and use external dictionaries of ApsaraDB for ClickHouse.

Background information

The dictionaries of ApsaraDB for ClickHouse include internal dictionaries and external dictionaries. Internal dictionaries contain predefined content for ApsaraDB for ClickHouse. External dictionaries allow you to add multiple custom data sources. Compared with internal dictionaries, external dictionaries are more widely and flexibly used. In the process of data warehousing development and data analysis, you can store constant data in business fact tables and business data in business dimension tables. You can replace dimension tables with dictionaries to prevent the use of theJOIN statement and makes queries more efficient.

Add dictionary configurations

  1. Use an Alibaba Cloud account to log on to the ApsaraDB for ClickHouse console.
  2. In the upper-left corner of the page, select the region where the cluster for which you want to apply for a public endpoint is deployed.
  3. On the Clusters page, click the ID of the cluster.
  4. In the left-side navigation pane, click Dictionary Management. The dictionary management page appears.
  5. In the upper-right corner, click Add Dictionary Configuration.
  6. In the Add Dictionary Configuration panel, edit the template and specify the dictionary content.
    Parameter Description
    <name> The name of the custom dictionary, which must be globally unique.
    <source> The data source of the dictionary. ApsaraDB for ClickHouse supports the following data sources:
    • MySQL
    • ClickHouse
    <lifetime> The interval at which to update the data in the dictionary. Unit: seconds.
    <layout> The type of the data in the dictionary. External dictionaries support the following data types:
    • Numeric key
      • flat
      • hashed
      • range_hashed
      • cache
    • Composite key
      • complex_key_hashed
      • complex_key_cache
      • ip_trie
    <structure> The structure of the dictionary.
    Note Only one node can be added at a time.
  7. After the configurations are complete, click OK. The dictionary is created.

    After the dictionary is created, you can click an item in the Actions column that corresponds to the dictionary to view, modify, or delete the dictionary.

Use DDL to create an external dictionary table

ApsaraDB for ClickHouse versions later than V20.1 allow you to use Data Definition Language (DDL) statements to create external dictionary tables. To view the version number of an ApsaraDB for ClickHouse cluster, log on to the ApsaraDB for ClickHouse console. Click the cluster ID to go to the Cluster Information page. In the Cluster Properties section, you can view the version number.

CREATE DICTIONARY [IF NOT EXISTS] [db.]dictionary_name [ON CLUSTER cluster]
    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)

Use a dictionary

Query a dictionary table
FROM system.dictionaries
Query data from a dictionary
Combine the dictGet statement with a function to query data from a dictionary.
dictGet('dict_name', 'attr_name', id_expr)

You can also use the following function:

dictGetOrDefault('dict_name', 'attr_name', id_expr, default_value_expr)