How to create, modify, and use external dictionaries in Alibaba Cloud ClickHouse - ApsaraDB for ClickHouse: Central de Documentação do Alibaba Cloud

Pré-requisitos

A source de dados e o cluster de destino devem estar na mesma região e VPC, com permissão mútua nas listas de permissões de IP. Caso contrário, resolva os problemas de conectividade primeiro. {{XREF_0}}.

Nota

Execute SELECT * FROM system.clusters; para visualizar os endereços IP do seu cluster Alibaba Cloud ClickHouse.

Para saber como configurar uma lista de permissões de endereços IP para o ClickHouse, consulte {{XREF_1}}.

Observações de uso

Não é possível usar o console para gerenciar dicionários em clusters do Alibaba Cloud ClickHouse com versão 21.8 ou superior. Em vez disso, gerencie-os por meio de instruções SQL, como CREATE DICTIONARY.

Criar uma configuração de dicionário

Faça login no console do ApsaraDB for ClickHouse.
No canto superior esquerdo da página, selecione a região onde o cluster de destino está localizado.
Na página Clusters, clique na aba Clusters of Community-compatible Edition e, em seguida, clique no ID do cluster desejado.
No painel de navegação à esquerda, clique em Dictionary Management.
No canto superior direito da página, clique em Add Dictionary Configuration.

Na caixa de diálogo Add Dictionary Configuration, configure os parâmetros.

A tabela a seguir lista os principais parâmetros do dicionário. Para referência completa dos parâmetros: Dictionaries.

Parâmetro	Descrição
<name>	Nome personalizado do dicionário. Deve ser globalmente único.
<source>	Source de dados para o dicionário. Sources suportadas: MySQL ClickHouse
<lifetime>	Frequência de atualização dos dados do dicionário, em segundos.
<layout>	Layout em memória para o dicionário. Sete tipos disponíveis: Chave numérica flat hashed range_hashed cache Chave composta complex_key_hashed complex_key_cache ip_trie
<structure>	Estrutura de dados do dicionário. Nota É possível adicionar apenas um nó por vez.

Após configurar os parâmetros, clique em OK. O dicionário será criado.

Após a criação, visualize, modifique ou exclua o dicionário na coluna Actions.

DDL de dicionário

Clusters executando a versão 20.8 ou superior suportam DDL de dicionário para criar dicionários externos diretamente. Verifique a versão do seu cluster na página Cluster Information no 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)

Os exemplos a seguir criam dicionários a partir de diferentes sources de dados.

Source: Alibaba Cloud ClickHouse

Prepare os dados.

Crie uma tabela de source.

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

Importe os dados de teste.

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

Crie o dicionário.

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 é outro Alibaba Cloud ClickHouse

Prepare os dados.

Crie uma tabela de source.

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

Importe os dados de teste.

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

Crie o dicionário.

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 os dados na instância ApsaraDB RDS for MySQL.

Crie um banco de dados e uma tabela de source.

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

Importe os dados de teste.

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

Crie o dicionário.

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 os dados.

Crie uma tabela de source.

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

Importe os dados de teste.

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

Crie o dicionário.

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

Usar um dicionário

Consultar metadados do dicionário

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

Consultar dados do dicionário

Utilize dictGet para recuperar dados de um dicionário. Referência completa da sintaxe: documentação oficial do ClickHouse.

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