Instruction INSERT INTO

Documentation de référence sur l’instruction INSERT INTO

Insère des données dans une table.

Syntaxe

INSERT INTO [TABLE] [db.]table [(c1, c2, c3)] [SETTINGS ...] VALUES (v11, v12, v13), (v21, v22, v23), ...

Vous pouvez spécifier une liste de colonnes à insérer en utilisant (c1, c2, c3). Vous pouvez également utiliser une expression avec un sélecteur de colonnes comme * et/ou des modificateurs comme APPLY, EXCEPT, REPLACE.

Par exemple, prenons la table suivante :

SHOW CREATE insert_select_testtable;

CREATE TABLE insert_select_testtable
(
    `a` Int8,
    `b` String,
    `c` Int8
)
ENGINE = MergeTree()
ORDER BY a

INSERT INTO insert_select_testtable (*) VALUES (1, 'a', 1) ;

Si vous voulez insérer des données dans toutes les colonnes, à l’exception de la colonne b, vous pouvez le faire à l’aide du mot-clé EXCEPT. En reprenant la syntaxe ci-dessus, veillez à insérer autant de valeurs (VALUES (v11, v13)) que de colonnes spécifiées ((c1, c3)) :

INSERT INTO insert_select_testtable (* EXCEPT(b)) Values (2, 2);

SELECT * FROM insert_select_testtable;

┌─a─┬─b─┬─c─┐
│ 2 │   │ 2 │
└───┴───┴───┘
┌─a─┬─b─┬─c─┐
│ 1 │ a │ 1 │
└───┴───┴───┘

Dans cet exemple, on voit que, dans la deuxième ligne insérée, les colonnes a et c sont remplies avec les valeurs transmises, et b avec sa valeur par défaut. Il est également possible d’utiliser le mot-clé DEFAULT pour insérer des valeurs par défaut :

INSERT INTO insert_select_testtable VALUES (1, DEFAULT, 1) ;

Si une liste de colonnes ne comprend pas toutes les colonnes existantes, les colonnes restantes sont renseignées avec :

• Les valeurs calculées à partir des expressions DEFAULT spécifiées dans la définition de la table.
• Des zéros et des chaînes vides si aucune expression DEFAULT n’est définie.

Les données peuvent être transmises à l’instruction INSERT dans n’importe quel format pris en charge par ClickHouse. Le format doit être explicitement spécifié dans la requête :

INSERT INTO [db.]table [(c1, c2, c3)] FORMAT format_name data_set

Par exemple, le format de requête ci-dessous est identique à la version de base de INSERT ... VALUES :

INSERT INTO [db.]table [(c1, c2, c3)] FORMAT Values (v11, v12, v13), (v21, v22, v23), ...

ClickHouse supprime tous les espaces ainsi qu’un saut de ligne (s’il y en a un) avant les données. Lors de la construction d’une requête, nous recommandons de placer les données sur une nouvelle ligne après les opérateurs de la requête, ce qui est important si les données commencent par des espaces.

Exemple :

INSERT INTO t FORMAT TabSeparated
11  Hello, world!
22  Qwerty

Vous pouvez insérer des données séparément de la requête en utilisant le client en ligne de commande ou l’interface HTTP.

Si vous souhaitez spécifier SETTINGS pour une requête INSERT, vous devez le faire avant la clause FORMAT, car tout ce qui suit FORMAT format_name est traité comme des données. Par exemple :

INSERT INTO table SETTINGS ... FORMAT format_name data_set

​

Contraintes

Si une table possède des contraintes, leurs expressions seront vérifiées pour chaque ligne de données insérées. Si l’une de ces contraintes n’est pas respectée — le serveur renverra une exception indiquant le nom et l’expression de la contrainte, et la requête sera interrompue.

​

Validation des types de données

ClickHouse valide les types de données autorisés (contrôlés par des paramètres comme enable_time_time64_type, allow_suspicious_low_cardinality_types, allow_suspicious_fixed_string_types, etc.) uniquement lors de la création d’une table (CREATE TABLE) et de la modification du schéma (ALTER TABLE), et non lors des opérations INSERT.

Cela signifie que si une table contenant un type de données non autorisé existe déjà, des données peuvent y être insérées même lorsque le paramètre correspondant est désactivé sur le serveur. C’est intentionnel : une fois qu’une table est créée, les insertions ne doivent pas être bloquées par des paramètres qui contrôlent la création des types.

Par exemple :

SET enable_time_time64_type = 1;

CREATE TABLE events
(
    `id` UInt64,
    `event_time` Time
)
ENGINE = MergeTree()
ORDER BY id;

SET enable_time_time64_type = 0;

-- This works even though the setting is now disabled.
-- The table already exists, so inserts are not blocked.
INSERT INTO events VALUES (1, '14:30:25');

-- But creating a new table with the Time type will fail.
CREATE TABLE events_new
(
    `id` UInt64,
    `event_time` Time
)
ENGINE = MergeTree()
ORDER BY id; -- ERR: TYPE_TIME_TIME64_IS_NOT_ENABLED

Par conséquent, un client exécutant une version plus récente (où un paramètre est activé par défaut) peut insérer dans un serveur plus ancien (où ce paramètre est désactivé) des données utilisant des types de données non autorisés, à condition que la table cible comporte déjà les types de colonnes correspondants. La validation s’effectue au niveau DDL, et non au niveau DML.

​

Insertion des résultats de SELECT

Syntaxe

INSERT INTO [TABLE] [db.]table [(c1, c2, c3)] SELECT ...

Les colonnes sont mises en correspondance en fonction de leur position dans la clause SELECT. Cependant, leurs noms dans l’expression SELECT et dans la table de INSERT peuvent différer. Si nécessaire, une conversion de type est effectuée.

Aucun format de données, à l’exception du format Values, ne permet d’affecter à des valeurs des expressions telles que now(), 1 + 2, etc. Le format Values permet une utilisation limitée des expressions, mais cela n’est pas recommandé, car leur exécution repose alors sur un code inefficace.

Les autres requêtes de modification des parties de données ne sont pas prises en charge : UPDATE, DELETE, REPLACE, MERGE, UPSERT, INSERT UPDATE. Cependant, vous pouvez supprimer les anciennes données à l’aide de ALTER TABLE ... DROP PARTITION.

La clause FORMAT doit être spécifiée à la fin de la requête si la clause SELECT contient la fonction de table input().

Pour insérer une valeur par défaut au lieu de NULL dans une colonne avec un type de données non Nullable, activez le paramètre insert_null_as_default.

INSERT prend également en charge les CTE (common table expression). Par exemple, les deux instructions suivantes sont équivalentes :

INSERT INTO x WITH y AS (SELECT * FROM numbers(10)) SELECT * FROM y;
WITH y AS (SELECT * FROM numbers(10)) INSERT INTO x SELECT * FROM y;

​

Insertion de données depuis un fichier

Syntaxe

INSERT INTO [TABLE] [db.]table [(c1, c2, c3)] FROM INFILE file_name [COMPRESSION type] [SETTINGS ...] [FORMAT format_name]

Utilisez la syntaxe ci-dessus pour insérer des données à partir d’un ou plusieurs fichiers stockés du côté du client. file_name et type sont des littéraux de chaîne. Le format du fichier d’entrée doit être défini dans la clause FORMAT.

Les fichiers compressés sont pris en charge. Le type de compression est détecté à partir de l’extension du nom de fichier. Il peut aussi être spécifié explicitement dans une clause COMPRESSION. Les types pris en charge sont : 'none', 'gzip', 'deflate', 'br', 'xz', 'zstd', 'lz4', 'bz2'.

Cette fonctionnalité est disponible dans le client en ligne de commande et clickhouse-local.

Exemples

​

Fichier unique avec FROM INFILE

Exécutez les requêtes suivantes à l’aide du client en ligne de commande:

Query

echo 1,A > input.csv ; echo 2,B >> input.csv
clickhouse-client --query="CREATE TABLE table_from_file (id UInt32, text String) ENGINE=MergeTree() ORDER BY id;"
clickhouse-client --query="INSERT INTO table_from_file FROM INFILE 'input.csv' FORMAT CSV;"
clickhouse-client --query="SELECT * FROM table_from_file FORMAT PrettyCompact;"

Response

┌─id─┬─text─┐
│  1 │ A    │
│  2 │ B    │
└────┴──────┘

​

Plusieurs fichiers avec FROM INFILE à l’aide de globs

Cet exemple est très similaire au précédent, mais ici les insertions se font à partir de plusieurs fichiers à l’aide de FROM INFILE 'input_*.csv.

echo 1,A > input_1.csv ; echo 2,B > input_2.csv
clickhouse-client --query="CREATE TABLE infile_globs (id UInt32, text String) ENGINE=MergeTree() ORDER BY id;"
clickhouse-client --query="INSERT INTO infile_globs FROM INFILE 'input_*.csv' FORMAT CSV;"
clickhouse-client --query="SELECT * FROM infile_globs FORMAT PrettyCompact;"

En plus de sélectionner plusieurs fichiers avec *, vous pouvez utiliser des plages ({1,2} ou {1..9}) ainsi que d’autres substitutions de glob. Ces trois syntaxes fonctionneraient avec l’exemple ci-dessus :

INSERT INTO infile_globs FROM INFILE 'input_*.csv' FORMAT CSV;
INSERT INTO infile_globs FROM INFILE 'input_{1,2}.csv' FORMAT CSV;
INSERT INTO infile_globs FROM INFILE 'input_?.csv' FORMAT CSV;

​

Insertion à l’aide d’une fonction de table

Des données peuvent être insérées dans des tables référencées à l’aide de fonctions de table.

Syntaxe

INSERT INTO [TABLE] FUNCTION table_func ...

Exemple

La fonction de table remote est utilisée dans les requêtes suivantes :

Query

CREATE TABLE simple_table (id UInt32, text String) ENGINE=MergeTree() ORDER BY id;
INSERT INTO TABLE FUNCTION remote('localhost', default.simple_table)
    VALUES (100, 'inserted via remote()');
SELECT * FROM simple_table;

Response

┌──id─┬─text──────────────────┐
│ 100 │ inserted via remote() │
└─────┴───────────────────────┘

​

Insertion dans ClickHouse Cloud

Par défaut, les services ClickHouse Cloud disposent de plusieurs répliques pour assurer la haute disponibilité. Lorsque vous vous connectez à un service, une connexion est établie avec l’une de ces répliques.

Une fois l’INSERT effectué avec succès, les données sont écrites dans le stockage sous-jacent. Cependant, les répliques peuvent mettre un certain temps à recevoir ces mises à jour. Par conséquent, si vous utilisez une autre connexion qui exécute une requête SELECT sur l’une de ces autres répliques, les données mises à jour peuvent ne pas encore y être visibles.

Il est possible d’utiliser select_sequential_consistency pour forcer la réplique à recevoir les dernières mises à jour. Voici un exemple de requête SELECT utilisant ce paramètre :

SELECT .... SETTINGS select_sequential_consistency = 1;

Notez que l’utilisation de select_sequential_consistency augmentera la charge sur ClickHouse Keeper (utilisé en interne par ClickHouse Cloud) et peut entraîner un ralentissement des performances en fonction de la charge du service. Nous vous recommandons de ne pas activer ce paramètre, sauf si nécessaire. L’approche recommandée consiste à effectuer les lectures/écritures dans la même session ou à utiliser un pilote client qui utilise le protocole natif (et prend donc en charge les connexions persistantes).

​

Insertion dans une configuration répliquée

Dans une configuration répliquée, les données ne seront visibles sur les autres répliques qu’après leur réplication. Les données commencent à être répliquées (téléchargées sur les autres répliques) immédiatement après un INSERT. Cela diffère de ClickHouse Cloud, où les données sont immédiatement écrites sur le stockage partagé et où les répliques s’abonnent aux modifications des métadonnées.

Notez que, dans les configurations répliquées, les INSERTs peuvent parfois prendre un certain temps (de l’ordre d’une seconde), car ils nécessitent un commit dans ClickHouse Keeper pour le consensus distribué. L’utilisation de S3 pour le stockage ajoute également de la latence.

​

Considérations relatives aux performances

INSERT trie les données d’entrée selon la clé primaire et les répartit en partitions selon la clé de partition. Si vous insérez des données dans plusieurs partitions à la fois, cela peut réduire considérablement les performances de la requête INSERT. Pour éviter cela :

• Ajoutez les données par lots suffisamment volumineux, par exemple 100 000 lignes à la fois.
• Regroupez les données par clé de partition avant de les envoyer à ClickHouse.

Les performances ne diminueront pas si :

• Les données sont ajoutées en temps réel.
• Vous envoyez des données généralement triées par horodatage.

​

Insertions asynchrones

Il est possible d’insérer des données de manière asynchrone au moyen d’insertions petites mais fréquentes. Les données issues de ces insertions sont regroupées en lots, puis insérées en toute sécurité dans une table. Pour utiliser les insertions asynchrones, activez le paramètre async_insert.

L’utilisation de async_insert ou du moteur de table Buffer ajoute une mise en mémoire tampon supplémentaire.

​

Insertions volumineuses ou de longue durée

Lorsque vous insérez de grands volumes de données, ClickHouse optimise les performances d’écriture grâce à un processus appelé « squashing ». De petits blocs de données insérées en mémoire sont fusionnés puis compactés en blocs plus volumineux avant d’être écrits sur disque. Le squashing réduit le surcoût associé à chaque opération d’écriture. Au cours de ce processus, les données insérées deviennent disponibles pour les requêtes une fois que ClickHouse a terminé d’écrire chaque bloc de max_insert_block_size lignes.

Voir aussi

• async_insert
• wait_for_async_insert
• wait_for_async_insert_timeout
• async_insert_max_data_size
• async_insert_busy_timeout_ms
• async_insert_stale_timeout_ms