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

Cuando los datos de su negocio contienen valores constantes o pueden separarse en tablas de hechos y dimensiones, utilice diccionarios en lugar de tablas de dimensiones para evitar operaciones JOIN y mejorar el rendimiento de las consultas. Cree, modifique y utilice diccionarios externos en Alibaba Cloud ClickHouse.

Requisitos previos

El origen de datos y el clúster de destino deben encontrarse en la misma región y VPC, con listas blancas de IP configuradas mutuamente. De lo contrario, resuelva primero los problemas de conectividad. How to resolve network connectivity issues between a target cluster and a data source.

Nota

Ejecute SELECT * FROM system.clusters; para consultar las direcciones IP de su clúster de Alibaba Cloud ClickHouse.

Para obtener información sobre cómo configurar una lista blanca de direcciones IP para ClickHouse, consulte Configure an IP Address Whitelist.

Notas de uso

No es posible utilizar la consola para gestionar diccionarios en clústeres de Alibaba Cloud ClickHouse que ejecuten la versión 21.8 o posterior. En su lugar, gestióneles mediante sentencias SQL como CREATE DICTIONARY.

Crear una configuración de diccionario

Inicie sesión en la consola de ApsaraDB for ClickHouse.
En la esquina superior izquierda de la página, seleccione la región donde se encuentra el clúster de destino.
En la página Clusters, haga clic en la pestaña Clusters of Community-compatible Edition y luego haga clic en el ID del clúster de destino.
En el panel de navegación izquierdo, haga clic en Dictionary Management.
En la esquina superior derecha de la página, haga clic en Add Dictionary Configuration.

En el cuadro de diálogo Add Dictionary Configuration, configure los parámetros.

La siguiente tabla enumera los principales parámetros del diccionario. Para la referencia completa de parámetros, consulte: Dictionaries.

Parámetro	Descripción
<name>	Nombre personalizado del diccionario. Debe ser único a nivel global.
<source>	Origen de datos para el diccionario. Orígenes compatibles: MySQL ClickHouse
<lifetime>	Frecuencia de actualización de los datos del diccionario, en segundos.
<layout>	Disposición en memoria para el diccionario. Hay siete tipos disponibles: Clave numérica flat hashed range_hashed cache Clave compuesta complex_key_hashed complex_key_cache ip_trie
<structure>	Estructura de datos del diccionario. Nota Solo puede agregar un nodo a la vez.

Después de configurar los parámetros, haga clic en OK. El diccionario se crea automáticamente.

Una vez creado, puede consultar, modificar o eliminar el diccionario desde la columna Actions.

DDL de diccionarios

Los clústeres que ejecutan la versión 20.8 o posterior admiten DDL de diccionarios para crear diccionarios externos directamente. Compruebe la versión de su clúster en la página Cluster Information de la consola.

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)

Los siguientes ejemplos crean diccionarios a partir de distintos orígenes de datos.

Origen: Alibaba Cloud ClickHouse

Prepare los datos.

Cree una tabla de origen.

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

Importe los datos de prueba.

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

Cree el diccionario.

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

El origen es otro clúster de Alibaba Cloud ClickHouse

Prepare los datos.

Cree una tabla de origen.

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

Importe los datos de prueba.

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

Cree el diccionario.

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 los datos en la instancia de ApsaraDB RDS for MySQL.

Cree una base de datos y una tabla de origen.

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

Importe los datos de prueba.

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

Cree el diccionario.

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

Consulta personalizada

Prepare los datos.

Cree una tabla de origen.

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

Importe los datos de prueba.

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

Cree el diccionario.

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

Uso de un diccionario

Consulta de metadatos del diccionario

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

Consulta de datos del diccionario

Utilice dictGet para recuperar datos de un diccionario. Para la referencia completa de sintaxis, consulte la documentación oficial de ClickHouse](https://clickhouse.com/docs/en/sql-reference/functions/ext-dict-functions/#dictget-dictgetordefault-dictgetornull).

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