All Products
Search

This Product

    ApsaraDB for ClickHouse:Data dictionaries

    Document Center

    ApsaraDB for ClickHouse:Data dictionaries

    Last Updated:Mar 28, 2023

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

    Background information

    ApsaraDB for ClickHouse provides built-in dictionaries and external dictionaries. Built-in dictionaries contain predefined content for ApsaraDB for ClickHouse. You can add external dictionaries from multiple custom data sources. Compared with built-in dictionaries, external dictionaries are more widely and flexibly used. This topic describes how to manage external dictionaries of ApsaraDB for ClickHouse. In data warehousing development and data analysis, when you need to store constant data in business fact tables and business data in business dimension tables, you can use data dictionaries instead of dimension tables to store data. This way, you do not need to execute a JOIN statement. This makes queries more efficient.

    Note

    You cannot manage data dictionaries for ApsaraDB for ClickHouse clusters of V21.8 or later in the console. You can use SQL statements to view and create a data dictionary. For more information, see CREATE DICTIONARY.

    Create and modify an external dictionary

    1. Log on to the ApsaraDB for ClickHouse console.

    2. In the upper-left corner, select the region where the cluster that you want to manage is deployed.

    3. On the Default Instances tab of the Clusters page, click the ID of the cluster that you want to manage.

    4. In the left-side navigation pane, click Dictionary Management to go to the Dictionary Management page.

    5. In the upper-right corner, click Add Dictionary Configuration.

    6. In the Add Dictionary Configuration dialog box that appears, edit the template and specify the dictionary content.

      Parameter

      Description

      <name>

      The name of the external dictionary that you want to create. The name 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 data is updated in the dictionary. Unit: seconds.

      <layout>

      The formats of data stored in memory. ApsaraDB for ClickHouse supports the following data formats for an external dictionary:

      • Numeric key

        • flat

        • hashed

        • range_hashed

        • cache

      • Composite key

        • complex_key_hashed

        • complex_key_cache

        • ip_trie

      <structure>

      The data structure of the dictionary.

      Note

      You can add only one node at a time.

      Note

      For more information about how to configure dictionary parameters, see Configuring a Dictionary.

    7. Click OK. The data dictionary is created.

      You can click a link in the Actions column of the created dictionary to view, modify, or delete the dictionary.

    Execute a Data Definition Language (DDL) statement to create an external dictionary table

    ApsaraDB for ClickHouse of V20.8 or later allows you to execute a DDL statement to create an external dictionary table. You can confirm the version number of an ApsaraDB for ClickHouse cluster in 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]
(
    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)

    Use an external dictionary

    • Query an external dictionary table

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

    • Query data in an external dictionary

      • Use the dictGet() function and specify the parameters in the function to query data in a dictionary. 

        dictGet('dict_name', 'attr_name', id_expr)

        You can also use the dictGetOrDefault function to query data in a dictionary.

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

    For more information about the dictGet() functions, see dictGet, dictGetOrDefault, dictGetOrNull.