Coleções nomeadas

Documentação sobre coleções nomeadas

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 criadas por DDL podem ser habilitadas em alguns serviços do ClickHouse Cloud. Entre em contato com o Suporte para confirmar a disponibilidade. Coleções nomeadas definidas em arquivos de configuração não estão disponíveis porque os usuários não podem modificar arquivos de configuração do servidor no ClickHouse Cloud.

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.

Se a substituição for permitida, pode ser possível que usuários sem acesso administrativo descubram as credenciais que você está tentando ocultar. Se você estiver usando coleções nomeadas com esse propósito, deverá desabilitar allow_named_collection_override_by_default (que vem habilitada por padrão).

​

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.xml

<clickhouse>
  <users>
    <default>
      <password_sha256_hex>65e84be33532fb784c48129675f9eff3a682b27168c0ea744b2cf58ee02337c5</password_sha256_hex replace=true>
      <access_management>1</access_management>
      <named_collection_control>1</named_collection_control>
      <!-- Uncomment to also let this user see the values stored in named collections. -->
      <!-- <show_named_collections_secrets>1</show_named_collections_secrets> -->
    </default>
  </users>
</clickhouse>

No exemplo acima, o valor password_sha256_hex é a representação hexadecimal do hash SHA256 da senha. Esta configuração do usuário default tem o atributo replace=true, pois a configuração padrão já define uma password em texto simples, e não é possível ter, ao mesmo tempo, uma password em texto simples e uma password_sha256_hex definidas para um usuário.

named_collection_control permite que o usuário crie, altere e remova coleções nomeadas, mas não que leia os valores já armazenados nelas. Esse é um privilégio separado, SHOW NAMED COLLECTIONS SECRETS, habilitado pela configuração show_named_collections_secrets. Um usuário só pode conceder os privilégios que ele mesmo possui, portanto, enquanto show_named_collections_secrets estiver desabilitado, o usuário não terá o conjunto completo de privilégios e GRANT ALL ON *.* TO another_user WITH GRANT OPTION será rejeitado com (Missing permissions: SHOW NAMED COLLECTIONS SECRETS ON *).

​

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.xml

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

O primeiro argumento da função s3() acima é o nome da coleção, s3_mydata. Sem coleções nomeadas, o ID da chave de acesso, a chave secreta, o formato e a URL teriam de ser informados em cada chamada à função s3().

​

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;
┌─number─┐
│      0 │
│      1 │
│      2 │
└────────┘

​

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

┌─count()─┐
│       3 │
└─────────┘

A coleção nomeada não define o parâmetro table, então ele é informado na chamada da função como 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;

┌─count()─┐
│       3 │
└─────────┘

A DDL substitui a configuração connection_pool_size da coleção nomeada.

​

Banco de dados MySQL

CREATE DATABASE mydatabase ENGINE = MySQL(mymysql);

SHOW TABLES FROM mydatabase;

┌─name───┐
│ source │
│ test   │
└────────┘

​

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

┌─dictGet('dict', 'B', 2)─┐
│ two                     │
└─────────────────────────┘

​

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

┌─a─┬─b───┐
│ 2 │ two │
│ 1 │ one │
└───┴─────┘
SELECT * FROM postgresql(mypg, table = 'test', schema = 'public');

┌─a─┐
│ 1 │
│ 2 │
│ 3 │
└───┘

​

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;

┌─a─┐
│ 1 │
│ 2 │
│ 3 │
└───┘

O PostgreSQL copia os dados da coleção nomeada quando a tabela é criada. Alterações na coleção não afetam as tabelas existentes.

​

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

┌─name─┐
│ test │
└──────┘

​

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

┌─dictGet('dict', 'b', 2)─┐
│ two                     │
└─────────────────────────┘

​

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);
┌─dummy─┐
│     0 │
└───────┘

SELECT * FROM remote(remote1, database = merge(system, '^one'));
┌─dummy─┐
│     0 │
└───────┘

INSERT INTO FUNCTION remote(remote1, database = default, table = test) VALUES (1,'a');

SELECT * FROM remote(remote1, database = default, table = test);
┌─a─┬─b─┐
│ 1 │ a │
└───┴───┘

​

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);
┌─dictGet('dict', 'b', 1)─┐
│ a                       │
└─────────────────────────┘

​

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>

​

Autenticação OAUTHBEARER/OIDC

As opções de OAUTHBEARER são configurações estendidas do librdkafka e, portanto, devem ficar aninhadas em <kafka> dentro de cada coleção nomeada. As chaves kafka_* de nível superior configuram o motor de tabela Kafka. O ClickHouse converte os sublinhados dos nomes dos elementos XML aninhados em pontos antes de repassá-los ao librdkafka.

O exemplo a seguir configura credenciais de cliente OAuth para um espaço de nomes do Azure Event Hubs:

/etc/clickhouse-server/config.d/named_collections.xml

<clickhouse>
    <named_collections>
        <eventhub_one>
            <!-- Kafka table-engine settings -->
            <kafka_broker_list>domain.one.servicebus.windows.net:9093</kafka_broker_list>
            <kafka_topic_list>my-topic</kafka_topic_list>
            <kafka_group_name>my-group</kafka_group_name>
            <kafka_format>JSONEachRow</kafka_format>
            <kafka_security_protocol>sasl_ssl</kafka_security_protocol>
            <kafka_sasl_mechanism>OAUTHBEARER</kafka_sasl_mechanism>

            <!-- Extended librdkafka settings -->
            <kafka>
                <sasl_oauthbearer_method>oidc</sasl_oauthbearer_method>
                <sasl_oauthbearer_client_id from_env="EVENTHUB_CLIENT_ID"/>
                <sasl_oauthbearer_client_secret from_env="EVENTHUB_CLIENT_SECRET"/>
                <sasl_oauthbearer_token_endpoint_url>https://login.microsoftonline.com/TENANT_ID/oauth2/v2.0/token</sasl_oauthbearer_token_endpoint_url>
                <sasl_oauthbearer_scope>https://domain.one.servicebus.windows.net/.default</sasl_oauthbearer_scope>
            </kafka>
        </eventhub_one>
    </named_collections>
</clickhouse>

Quando ENGINE = Kafka(eventhub_one) usa uma coleção nomeada, o ClickHouse não mescla a configuração global <kafka> do servidor. Crie uma coleção nomeada por namespace do Event Hubs sempre que o endereço do broker ou o escopo OAuth for diferente, e inclua nessa coleção nomeada todas as propriedades OIDC obrigatórias.

Os atributos from_env deste exemplo se aplicam a coleções nomeadas definidas em XML e leem seus valores do ambiente do processo do servidor ClickHouse. Consulte substituição por variáveis de ambiente para mais detalhes sobre implantação e valores padrão, e criptografar e ocultar a configuração para proteger segredos em arquivos de configuração pré-processados.

​

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;

┌─count()─┐
│       2 │
└─────────┘

O DDL substitui a configuração de opções da coleção nomeada.

​

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

┌─dictGet('dict', 'b', 2)─┐
│ two                     │
└─────────────────────────┘

A coleção nomeada define my_collection como nome da coleção. Na chamada da função, esse valor é substituído por collection = 'my_dict' para selecionar outra coleção.