Skip to content
ClickHouse Docs
ClickHouse DocsClickHouse Docs
49.6kGet Started

Coleções nomeadas

View as MarkdownEdit this pageReport an issueSet up docs MCP

Coleções nomeadas oferecem uma forma de armazenar coleções de pares chave-valor para serem usadas na configuração de integrações com fontes externas. Você pode usar coleções nomeadas com dicionários, tabelas, funções de tabela e armazenamento de objetos.

Coleções nomeadas podem ser configuradas com DDL ou em arquivos de configuração e são aplicadas quando o ClickHouse é iniciado. Elas simplificam a criação de objetos e o ocultamento de credenciais de usuários sem acesso administrativo.

As chaves em uma coleção nomeada devem corresponder aos nomes dos parâmetros da função, motor de tabela, banco de dados etc. correspondentes. Nos exemplos abaixo, há um link para a lista de parâmetros de cada tipo.

Os parâmetros definidos em uma coleção nomeada podem ser sobrescritos em SQL, como mostrado nos exemplos abaixo. Essa capacidade pode ser limitada usando as palavras-chave [NOT] OVERRIDABLE, atributos XML e/ou a opção de configuração allow_named_collection_override_by_default.

Armazenar coleções nomeadas no banco de dados do sistema

Exemplo de DDL

CREATE NAMED COLLECTION name AS
key_1 = 'value' OVERRIDABLE,
key_2 = 'value2' NOT OVERRIDABLE,
url = 'https://connection.url/'

No exemplo acima:

  • key_1 sempre pode ser sobrescrita.
  • key_2 nunca pode ser sobrescrita.
  • url pode ou não ser sobrescrita, dependendo do valor de allow_named_collection_override_by_default.

Permissões para criar coleções nomeadas com DDL

Para gerenciar coleções nomeadas com DDL, um usuário deve ter o privilégio named_collection_control. Esse privilégio pode ser atribuído adicionando um arquivo a /etc/clickhouse-server/users.d/. O exemplo concede ao usuário default os privilégios access_management e named_collection_control:

/etc/clickhouse-server/users.d/user_default.xmlxml
<clickhouse>
  <users>
    <default>
      <password_sha256_hex>65e84be33532fb784c48129675f9eff3a682b27168c0ea744b2cf58ee02337c5</password_sha256_hex replace=true>
      <access_management>1</access_management>
      <named_collection_control>1</named_collection_control>
    </default>
  </users>
</clickhouse>

Armazenamento de coleções nomeadas

As coleções nomeadas podem ser armazenadas no disco local ou no ZooKeeper/Keeper. Por padrão, o armazenamento local é usado. Elas também podem ser armazenadas com criptografia usando os mesmos algoritmos usados na criptografia de disco, em que aes_128_ctr é usado por padrão.

Para configurar o armazenamento de coleções nomeadas, você precisa especificar um type. Ele pode ser local ou keeper/zookeeper. Para armazenamento criptografado, você pode usar local_encrypted ou keeper_encrypted/zookeeper_encrypted.

Para usar ZooKeeper/Keeper, também é necessário configurar um path (caminho no ZooKeeper/Keeper onde as coleções nomeadas serão armazenadas) na seção named_collections_storage do arquivo de configuração. O exemplo a seguir usa criptografia e ZooKeeper/Keeper:

<clickhouse>
  <named_collections_storage>
    <type>zookeeper_encrypted</type>
    <key_hex>bebec0cabebec0cabebec0cabebec0ca</key_hex>
    <algorithm>aes_128_ctr</algorithm>
    <path>/named_collections_path/</path>
    <update_timeout_ms>1000</update_timeout_ms>
  </named_collections_storage>
</clickhouse>

O parâmetro de configuração opcional update_timeout_ms é, por padrão, 5000.

Você pode verificar o tipo de armazenamento ativo por meio de system.server_settings e getServerSetting:

SELECT value, getServerSetting('named_collections_storage_type')
FROM system.server_settings
WHERE name = 'named_collections_storage.type';

Alterar o tipo de armazenamento exige reiniciar o servidor; SYSTEM RELOAD CONFIG não altera o backend ativo.

Armazenar coleções nomeadas em arquivos de configuração

Exemplo de XML

/etc/clickhouse-server/config.d/named_collections.xmlxml
<clickhouse>
     <named_collections>
        <name>
            <key_1 overridable="true">value</key_1>
            <key_2 overridable="false">value_2</key_2>
            <url>https://connection.url/</url>
        </name>
     </named_collections>
</clickhouse>

No exemplo acima:

  • key_1 sempre pode ser sobrescrito.
  • key_2 nunca pode ser sobrescrito.
  • url pode ou não ser sobrescrito, dependendo do valor de allow_named_collection_override_by_default.

Modificando coleções nomeadas

Coleções nomeadas criadas com instruções DDL podem ser alteradas ou removidas com DDL. Coleções nomeadas criadas com arquivos XML podem ser gerenciadas editando ou excluindo o arquivo XML correspondente.

Alterar uma coleção nomeada DDL

Altere ou adicione as chaves key1 e key3 da coleção nomeada collection2 (isso não alterará o valor da flag overridable para essas chaves):

ALTER NAMED COLLECTION collection2 SET key1=4, key3='value3'

Altere ou adicione a chave key1 e permita que ela seja sempre sobrescrita:

ALTER NAMED COLLECTION collection2 SET key1=4 OVERRIDABLE

Remova a chave key2 de collection2:

ALTER NAMED COLLECTION collection2 DELETE key2

Altere ou adicione a chave key1 e exclua a chave key3 da coleção collection2:

ALTER NAMED COLLECTION collection2 SET key1=4, DELETE key3

Para forçar uma chave a usar as configurações padrão da flag overridable, é necessário remover a chave e adicioná-la novamente.

ALTER NAMED COLLECTION collection2 DELETE key1;
ALTER NAMED COLLECTION collection2 SET key1=4;

Exclua a coleção nomeada collection2:

DROP NAMED COLLECTION collection2

Coleções nomeadas para acessar o S3

Para ver a descrição dos parâmetros, consulte a função de tabela S3.

Exemplo de DDL

CREATE NAMED COLLECTION s3_mydata AS
access_key_id = 'AKIAIOSFODNN7EXAMPLE',
secret_access_key = 'wJalrXUtnFEMI/K7MDENG/bPxRfiCYEXAMPLEKEY',
format = 'CSV',
url = 'https://s3.us-east-1.amazonaws.com/yourbucket/mydata/'

Exemplo em XML

<clickhouse>
    <named_collections>
        <s3_mydata>
            <access_key_id>AKIAIOSFODNN7EXAMPLE</access_key_id>
            <secret_access_key>wJalrXUtnFEMI/K7MDENG/bPxRfiCYEXAMPLEKEY</secret_access_key>
            <format>CSV</format>
            <url>https://s3.us-east-1.amazonaws.com/yourbucket/mydata/</url>
        </s3_mydata>
    </named_collections>
</clickhouse>

Exemplos de coleção nomeada para a função s3() e a tabela S3

Ambos os exemplos a seguir usam a mesma coleção nomeada s3_mydata:

função s3()

INSERT INTO FUNCTION s3(s3_mydata, filename = 'test_file.tsv.gz',
   format = 'TSV', structure = 'number UInt64', compression_method = 'gzip')
SELECT * FROM numbers(10000);

Tabela S3

CREATE TABLE s3_engine_table (number Int64)
ENGINE=S3(s3_mydata, url='https://s3.us-east-1.amazonaws.com/yourbucket/mydata/test_file.tsv.gz', format = 'TSV')
SETTINGS input_format_with_names_use_header = 0;

SELECT * FROM s3_engine_table LIMIT 3;
┌

Coleções nomeadas para acessar um banco de dados MySQL

Consulte a descrição dos parâmetros em mysql.

Exemplo de DDL

CREATE NAMED COLLECTION mymysql AS
user = 'myuser',
password = 'mypass',
host = '127.0.0.1',
port = 3306,
database = 'test',
connection_pool_size = 8,
replace_query = 1

Exemplo de XML

<clickhouse>
    <named_collections>
        <mymysql>
            <user>myuser</user>
            <password>mypass</password>
            <host>127.0.0.1</host>
            <port>3306</port>
            <database>test</database>
            <connection_pool_size>8</connection_pool_size>
            <replace_query>1</replace_query>
        </mymysql>
    </named_collections>
</clickhouse>

Exemplos de coleção nomeada para a função mysql(), tabela MySQL, banco de dados MySQL e Dicionário

Os quatro exemplos a seguir usam a mesma coleção nomeada mymysql:

função mysql()

SELECT count() FROM mysql(mymysql, table = 'test');

┌

Tabela MySQL

CREATE TABLE mytable(A Int64) ENGINE = MySQL(mymysql, table = 'test', connection_pool_size=3, replace_query=0);
SELECT count() FROM mytable;

┌

Banco de dados MySQL

CREATE DATABASE mydatabase ENGINE = MySQL(mymysql);

SHOW TABLES FROM mydatabase;

┌

Dicionário do MySQL

CREATE DICTIONARY dict (A Int64, B String)
PRIMARY KEY A
SOURCE(MYSQL(NAME mymysql TABLE 'source'))
LIFETIME(MIN 1 MAX 2)
LAYOUT(HASHED());

SELECT dictGet('dict', 'B', 2);

┌

Coleções nomeadas para acessar o banco de dados PostgreSQL

Para ver a descrição dos parâmetros, consulte postgresql. Além disso, há os seguintes aliases:

  • username para user
  • db para database.

As configurações do pool de conexões do motor de tabela PostgreSQL (postgresql_connection_pool_size e as outras configurações postgresql_*) também podem ser armazenadas na coleção ou passadas como sobrescritas key = value. Elas se aplicam ao motor de tabela PostgreSQL, à função de tabela postgresql e ao motor de banco de dados PostgreSQL; uma cláusula SETTINGS explícita em uma tabela tem precedência sobre os valores da coleção.

O parâmetro addresses_expr é usado em uma coleção no lugar de host:port. Esse parâmetro é opcional, porque há outros parâmetros opcionais: host, hostname, port. O pseudocódigo a seguir explica a prioridade:

CASE
    WHEN collection['addresses_expr'] != '' THEN collection['addresses_expr']
    WHEN collection['host'] != ''           THEN collection['host'] || ':' || if(collection['port'] != '', collection['port'], '5432')
    WHEN collection['hostname'] != ''       THEN collection['hostname'] || ':' || if(collection['port'] != '', collection['port'], '5432')
END

Exemplo de criação:

CREATE NAMED COLLECTION mypg AS
user = 'pguser',
password = 'jw8s0F4',
host = '127.0.0.1',
port = 5432,
database = 'test',
schema = 'test_schema'

Exemplo de configuração:

<clickhouse>
    <named_collections>
        <mypg>
            <user>pguser</user>
            <password>jw8s0F4</password>
            <host>127.0.0.1</host>
            <port>5432</port>
            <database>test</database>
            <schema>test_schema</schema>
        </mypg>
    </named_collections>
</clickhouse>

Exemplo de uso de coleções nomeadas com a função postgresql

SELECT * FROM postgresql(mypg, table = 'test');

┌

Exemplo de uso de coleções nomeadas com banco de dados com mecanismo PostgreSQL

CREATE TABLE mypgtable (a Int64) ENGINE = PostgreSQL(mypg, table = 'test', schema = 'public');

SELECT * FROM mypgtable;

┌

Exemplo de uso de coleções nomeadas com um banco de dados com engine PostgreSQL

CREATE DATABASE mydatabase ENGINE = PostgreSQL(mypg);

SHOW TABLES FROM mydatabase

┌

Exemplo de uso de coleção nomeada com um Dicionário de fonte POSTGRESQL

CREATE DICTIONARY dict (a Int64, b String)
PRIMARY KEY a
SOURCE(POSTGRESQL(NAME mypg TABLE test))
LIFETIME(MIN 1 MAX 2)
LAYOUT(HASHED());

SELECT dictGet('dict', 'b', 2);

┌

Coleções nomeadas para acessar um banco de dados remoto do ClickHouse

Consulte a descrição dos parâmetros em remote.

Exemplo de configuração:

CREATE NAMED COLLECTION remote1 AS
host = 'remote_host',
port = 9000,
database = 'system',
user = 'foo',
password = 'secret',
secure = 1
<clickhouse>
    <named_collections>
        <remote1>
            <host>remote_host</host>
            <port>9000</port>
            <database>system</database>
            <user>foo</user>
            <password>secret</password>
            <secure>1</secure>
        </remote1>
    </named_collections>
</clickhouse>

secure não é necessário na conexão por causa de remoteSecure, mas pode ser usado para dicionários.

Exemplo de uso de coleções nomeadas com as funções remote/remoteSecure

SELECT * FROM remote(remote1, table = one);
┌

Exemplo de uso de coleções nomeadas com um dicionário com origem no ClickHouse

CREATE DICTIONARY dict(a Int64, b String)
PRIMARY KEY a
SOURCE(CLICKHOUSE(NAME remote1 TABLE test DB default))
LIFETIME(MIN 1 MAX 2)
LAYOUT(HASHED());

SELECT dictGet('dict', 'b', 1);
┌

Coleções nomeadas para acessar o Kafka

Veja a descrição dos parâmetros em Kafka.

Exemplo de DDL

CREATE NAMED COLLECTION my_kafka_cluster AS
kafka_broker_list = 'localhost:9092',
kafka_topic_list = 'kafka_topic',
kafka_group_name = 'consumer_group',
kafka_format = 'JSONEachRow',
kafka_max_block_size = '1048576';

Exemplo em XML

<clickhouse>
    <named_collections>
        <my_kafka_cluster>
            <kafka_broker_list>localhost:9092</kafka_broker_list>
            <kafka_topic_list>kafka_topic</kafka_topic_list>
            <kafka_group_name>consumer_group</kafka_group_name>
            <kafka_format>JSONEachRow</kafka_format>
            <kafka_max_block_size>1048576</kafka_max_block_size>
        </my_kafka_cluster>
    </named_collections>
</clickhouse>

Exemplo de uso de coleções nomeadas com uma tabela do Kafka

Ambos os exemplos a seguir usam a mesma coleção nomeada my_kafka_cluster:

CREATE TABLE queue
(
    timestamp UInt64,
    level String,
    message String
)
ENGINE = Kafka(my_kafka_cluster)

CREATE TABLE queue
(
    timestamp UInt64,
    level String,
    message String
)
ENGINE = Kafka(my_kafka_cluster)
SETTINGS kafka_num_consumers = 4,
         kafka_thread_per_consumer = 1;

Coleções nomeadas para backups

Para a descrição dos parâmetros, consulte Backup e restauração.

Exemplo de DDL

BACKUP TABLE default.test to S3(named_collection_s3_backups, 'directory')

Exemplo de XML

<clickhouse>
    <named_collections>
        <named_collection_s3_backups>
            <url>https://my-s3-bucket.s3.amazonaws.com/backup-S3/</url>
            <access_key_id>ABC123</access_key_id>
            <secret_access_key>Abc+123</secret_access_key>
        </named_collection_s3_backups>
    </named_collections>
</clickhouse>

Coleções nomeadas para acessar Tabela e Dicionário do MongoDB

Para obter a descrição dos parâmetros, consulte mongodb.

Exemplo de DDL

CREATE NAMED COLLECTION mymongo AS
user = '',
password = '',
host = '127.0.0.1',
port = 27017,
database = 'test',
collection = 'my_collection',
options = 'connectTimeoutMS=10000'

Exemplo em XML

<clickhouse>
    <named_collections>
        <mymongo>
            <user></user>
            <password></password>
            <host>127.0.0.1</host>
            <port>27017</port>
            <database>test</database>
            <collection>my_collection</collection>
            <options>connectTimeoutMS=10000</options>
        </mymongo>
    </named_collections>
</clickhouse>

tabela do MongoDB

CREATE TABLE mytable(log_type VARCHAR, host VARCHAR, command VARCHAR) ENGINE = MongoDB(mymongo, options='connectTimeoutMS=10000&compressors=zstd')
SELECT count() FROM mytable;

┌

Dicionário MongoDB

CREATE DICTIONARY dict
(
    `a` Int64,
    `b` String
)
PRIMARY KEY a
SOURCE(MONGODB(NAME mymongo COLLECTION my_dict))
LIFETIME(MIN 1 MAX 2)
LAYOUT(HASHED())

SELECT dictGet('dict', 'b', 2);

┌
Navigation