Skip to content
ClickHouse Docs
ClickHouse DocsClickHouse Docs
49.6kGet Started

Collections nommées

View as MarkdownEdit this pageReport an issueSet up docs MCP

Les collections nommées permettent de stocker des ensembles de paires clé-valeur utilisés pour configurer des intégrations avec des sources externes. Vous pouvez utiliser des collections nommées avec des dictionnaires, des tables, des fonctions de table et le stockage d’objets.

Les collections nommées peuvent être configurées via du DDL ou dans des fichiers de configuration, et sont prises en compte au démarrage de ClickHouse. Elles simplifient la création d’objets et permettent de masquer les informations d’authentification aux utilisateurs sans accès administratif.

Les clés d’une collection nommée doivent correspondre aux noms des paramètres de la fonction, du moteur de table, de la base de données, etc. correspondants. Dans les exemples ci-dessous, un lien vers la liste des paramètres est fourni pour chaque type.

Les paramètres définis dans une collection nommée peuvent être surchargés en SQL, comme le montrent les exemples ci-dessous. Cette possibilité peut être limitée à l’aide des mots-clés [NOT] OVERRIDABLE et des attributs XML, et/ou de l’option de configuration allow_named_collection_override_by_default.

Stocker des collections nommées dans la base de données système

Exemple de DDL

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

Dans l’exemple ci-dessus :

  • key_1 peut toujours être surchargée.
  • key_2 ne peut jamais être surchargée.
  • url peut être surchargée ou non selon la valeur de allow_named_collection_override_by_default.

Permissions pour créer des collections nommées avec DDL

Pour gérer des collections nommées avec DDL, un utilisateur doit disposer du privilège named_collection_control. Ce privilège peut être attribué en ajoutant un fichier à /etc/clickhouse-server/users.d/. Cet exemple accorde à l’utilisateur default les privilèges access_management et 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>

Stockage des collections nommées

Les collections nommées peuvent être stockées soit sur un disk local, soit dans ZooKeeper/Keeper. Par défaut, le stockage local est utilisé. Elles peuvent également être stockées de manière chiffrée, avec les mêmes algorithmes que ceux utilisés pour le chiffrement du disque, aes_128_ctr étant utilisé par défaut.

Pour configurer le stockage des collections nommées, vous devez spécifier un type. Il peut s'agir de local ou de keeper/zookeeper. Pour le stockage chiffré, vous pouvez utiliser local_encrypted ou keeper_encrypted/zookeeper_encrypted.

Pour utiliser ZooKeeper/Keeper, vous devez également définir un path (chemin dans ZooKeeper/Keeper où les collections nommées seront stockées) dans la section named_collections_storage du fichier de configuration. L'exemple suivant utilise le chiffrement et 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>

Le paramètre de configuration facultatif update_timeout_ms vaut 5000 par défaut.

Vous pouvez vérifier le type de stockage actif via system.server_settings et getServerSetting :

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

La modification du type de stockage nécessite un redémarrage du serveur ; SYSTEM RELOAD CONFIG ne modifie pas le backend actif.

Stockage des collections nommées dans des fichiers de configuration

Exemple 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>

Dans l’exemple ci-dessus :

  • key_1 peut toujours être surchargée.
  • key_2 ne peut jamais être surchargée.
  • url peut être surchargée ou non selon la valeur de allow_named_collection_override_by_default.

Modification des collections nommées

Les collections nommées créées à l’aide de requêtes DDL peuvent être modifiées ou supprimées via des instructions DDL. Les collections nommées créées à partir de fichiers XML peuvent être gérées en modifiant ou en supprimant le fichier XML correspondant.

Modifier une collection nommée DDL

Modifiez ou ajoutez les clés key1 et key3 de la collection collection2 (cela ne modifiera pas la valeur de la propriété overridable pour ces clés) :

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

Modifiez ou ajoutez la clé key1 et autorisez sa redéfinition systématique :

ALTER NAMED COLLECTION collection2 SET key1=4 OVERRIDABLE

Supprimez la clé key2 de collection2 :

ALTER NAMED COLLECTION collection2 DELETE key2

Modifiez ou ajoutez la clé key1 et supprimez la clé key3 dans la collection collection2 :

ALTER NAMED COLLECTION collection2 SET key1=4, DELETE key3

Pour forcer une clé à utiliser les paramètres par défaut de l’indicateur overridable, vous devez la supprimer, puis l’ajouter à nouveau.

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

Supprimez la collection nommée DDL collection2 :

DROP NAMED COLLECTION collection2

Collections nommées pour accéder à S3

Pour une description des paramètres, voir fonction de table S3.

Exemple 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/'

Exemple 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>

fonction s3() et exemples de collection nommée pour la table S3

Les deux exemples suivants utilisent la même collection nommée s3_mydata :

Fonction s3()

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

Table 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;
┌

Collection nommée pour accéder à une base de données MySQL

Pour une description des paramètres, voir mysql.

Exemple 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

Exemple 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>

Exemples de collection nommée pour la fonction mysql(), une table MySQL, une base de données MySQL et un dictionnaire

Les quatre exemples suivants utilisent la même collection nommée mymysql :

fonction mysql()

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

┌

Table MySQL

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

┌

Base de données MySQL

CREATE DATABASE mydatabase ENGINE = MySQL(mymysql);

SHOW TABLES FROM mydatabase;

┌

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

┌

Collections nommées pour accéder à une base de données PostgreSQL

Pour la description des paramètres, voir postgresql. Il existe également les alias suivants :

  • username pour user
  • db pour database.

Les paramètres du pool de connexions du moteur de table PostgreSQL (postgresql_connection_pool_size et les autres paramètres postgresql_*) peuvent également être stockés dans la collection ou être transmis sous forme de surcharges key = value. Ils s’appliquent au moteur de table PostgreSQL, à la fonction de table postgresql et au moteur de base de données PostgreSQL ; une clause SETTINGS explicite sur une table a préséance sur les valeurs de la collection.

Le paramètre addresses_expr est utilisé dans une collection à la place de host:port. Ce paramètre est facultatif, car d'autres le sont également : host, hostname, port. Le pseudo-code ci-dessous explique l'ordre de priorité :

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

Exemple de création :

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

Exemple de configuration :

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

Exemple d’utilisation de collections nommées avec la fonction postgresql

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

┌

Exemple d’utilisation de collections nommées avec une base de données avec moteur PostgreSQL

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

SELECT * FROM mypgtable;

┌

Exemple d’utilisation des collections nommées avec une base de données utilisant le moteur PostgreSQL

CREATE DATABASE mydatabase ENGINE = PostgreSQL(mypg);

SHOW TABLES FROM mydatabase

┌

Exemple d’utilisation de collections nommées avec un dictionnaire dont la source est 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);

┌

Collections nommées pour accéder à une base de données ClickHouse distante

Pour la description des paramètres, consultez remote.

Exemple de configuration :

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'est pas nécessaire pour la connexion grâce à remoteSecure, mais peut être utilisé pour les dictionnaires.

Exemple d’utilisation des collections nommées avec les fonctions remote/remoteSecure

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

Exemple d’utilisation de collections nommées avec un dictionnaire dont la source est 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);
┌

Collections nommées pour accéder à Kafka

Pour une description des paramètres, voir Kafka.

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

Exemple 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>

Exemple d’utilisation des collections nommées avec une table Kafka

Les deux exemples suivants utilisent la même collection nommée 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;

Collections nommées pour les sauvegardes

Pour une description des paramètres, voir Sauvegarde et restauration.

Exemple de DDL

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

Exemple 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>

Collections nommées pour accéder aux tables et dictionnaires MongoDB

Pour la description des paramètres, voir mongodb.

Exemple de DDL

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

Exemple 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>

Table MongoDB

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

┌

Dictionnaire 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