ON CLUSTER, qui est décrite séparément.
Formes de syntaxe
Avec un schéma explicite
table_name dans la base de données db ou dans la base de données courante si db n’est pas défini, avec la structure spécifiée entre crochets et le moteur engine.
La structure de la table est une liste de descriptions de colonnes, d’index secondaires, de projections et de contraintes. Si la clé primaire est prise en charge par le moteur, elle sera indiquée comme paramètre du moteur de table.
Dans le cas le plus simple, une description de colonne est de la forme name type. Exemple : RegionID UInt32.
Des expressions peuvent également être définies pour les valeurs par défaut (voir ci-dessous).
Si nécessaire, la clé primaire peut être spécifiée, avec une ou plusieurs expressions de clé.
Des commentaires peuvent être ajoutés aux colonnes et à la table.
Avec le schéma d’une table existante
Avec le schéma et les données d’une table existante
db.table lui sont attachées. En d’autres termes, les données de db.table sont clonées dans db2.table_clone lors de sa création. Cette requête est équivalente à ce qui suit :
db.table) sera utilisé.
À partir d’une fonction de table
À partir d’une requête SELECT
SELECT, avec le moteur engine, et la remplit avec les données issues de SELECT. Vous pouvez également spécifier explicitement la définition des colonnes.
Si la table existe déjà et que IF NOT EXISTS est spécifié, la requête n’aura aucun effet.
D’autres clauses peuvent apparaître après la clause ENGINE dans la requête. Consultez la documentation détaillée sur la création de tables dans les descriptions des moteurs de table.
Exemple
Query
Response
Modificateurs NULL ou NOT NULL
NULL et NOT NULL placés après le type de données dans une définition de colonne permettent ou non que celui-ci soit Nullable.
Si le type n’est pas Nullable et que NULL est spécifié, il sera traité comme Nullable ; si NOT NULL est spécifié, ce ne sera pas le cas. Par exemple, INT NULL équivaut à Nullable(INT). Si le type est Nullable et que les modificateurs NULL ou NOT NULL sont spécifiés, une exception sera levée.
Voir aussi le paramètre data_type_default_nullable.
Valeurs par défaut
DEFAULT expr, MATERIALIZED expr ou ALIAS expr. Exemple : URLDomain String DEFAULT domain(URL).
L’expression expr est facultative. Si elle est omise, le type de colonne doit être indiqué explicitement et la valeur par défaut sera 0 pour les colonnes numériques, '' (la chaîne vide) pour les colonnes de type chaîne, [] (le tableau vide) pour les colonnes de type tableau, 1970-01-01 pour les colonnes de type date, ou NULL pour les colonnes Nullable.
Le type de colonne d’une colonne avec valeur par défaut peut être omis ; dans ce cas, il est déduit du type de expr. Par exemple, le type de la colonne EventDate DEFAULT toDate(EventTime) sera Date.
Si un type de données et une expression de valeur par défaut sont tous deux spécifiés, une fonction implicite de transtypage est insérée pour convertir l’expression dans le type spécifié. Exemple : Hits UInt32 DEFAULT 0 est représenté en interne sous la forme Hits UInt32 DEFAULT toUInt32(0).
Une expression de valeur par défaut expr peut faire référence à des colonnes de table quelconques et à des constantes. ClickHouse vérifie que les modifications de la structure de la table n’introduisent pas de boucles dans le calcul de l’expression. Pour INSERT, il vérifie que les expressions peuvent être résolues, c’est-à-dire que toutes les colonnes à partir desquelles elles peuvent être calculées ont bien été fournies.
DEFAULT
DEFAULT expr
Valeur par défaut standard. Si la valeur d’une telle colonne n’est pas spécifiée dans une requête INSERT, elle est calculée à partir de expr.
Exemple :
MATERIALIZED
MATERIALIZED expr
Expression matérialisée. Les valeurs de ces colonnes sont automatiquement calculées d’après l’expression matérialisée spécifiée lors de l’insertion des lignes. Il n’est pas possible de spécifier explicitement des valeurs lors des INSERT.
De plus, les colonnes avec une valeur par défaut de ce type ne sont pas incluses dans le résultat de SELECT *. Cela permet de préserver l’invariant selon lequel le résultat d’un SELECT * peut toujours être réinséré dans la table à l’aide de INSERT. Ce comportement peut être désactivé avec le paramètre asterisk_include_materialized_columns.
Exemple :
EPHEMERAL
EPHEMERAL [expr]
Colonne éphémère. Les colonnes de ce type ne sont pas stockées dans la table et il n’est pas possible d’effectuer un SELECT dessus. La seule utilité des colonnes éphémères est de servir à construire les expressions de valeur par défaut d’autres colonnes.
Un INSERT sans colonnes explicitement spécifiées ignorera les colonnes de ce type. Cela permet de préserver l’invariant selon lequel le résultat d’un SELECT * peut toujours être réinséré dans la table à l’aide de INSERT.
Exemple :
ALIAS
ALIAS expr
Colonnes calculées (synonyme). Les colonnes de ce type ne sont pas stockées dans la table et il n’est pas possible d’y INSERT des valeurs.
Lorsque des requêtes SELECT font explicitement référence à des colonnes de ce type, la valeur est calculée au moment de la requête à partir de expr. Par défaut, SELECT * exclut les colonnes ALIAS. Ce comportement peut être désactivé avec le paramètre asterisk_include_alias_columns.
Lorsque vous utilisez la requête ALTER pour ajouter de nouvelles colonnes, les anciennes données de ces colonnes ne sont pas écrites. À la place, lors de la lecture d’anciennes données qui n’ont pas de valeurs pour les nouvelles colonnes, les expressions sont calculées à la volée par défaut. Cependant, si l’évaluation des expressions nécessite d’autres colonnes qui ne sont pas indiquées dans la requête, ces colonnes seront également lues, mais uniquement pour les blocs de données qui en ont besoin.
Si vous ajoutez une nouvelle colonne à une table mais modifiez ensuite son expression par défaut, les valeurs utilisées pour les anciennes données changeront (pour les données dont les valeurs n’ont pas été stockées sur le disque). Notez que lors de l’exécution des fusions en arrière-plan, les données des colonnes absentes dans l’une des parties en cours de fusion sont écrites dans la partie fusionnée.
Il n’est pas possible de définir des valeurs par défaut pour les éléments des structures de données imbriquées.
Vous pouvez définir une clé primaire lors de la création d’une table. La clé primaire peut être définie de deux façons :
- Dans la liste des colonnes
- Hors de la liste des colonnes
Contraintes
CONSTRAINT
boolean_expr_1 peut être n’importe quelle expression booléenne. Si des contraintes sont définies pour la table, chacune d’elles sera vérifiée pour chaque ligne de la requête INSERT. Si une contrainte n’est pas respectée, le serveur renverra une exception indiquant le nom de la contrainte et l’expression vérifiée.
L’ajout d’un grand nombre de contraintes peut nuire aux performances des requêtes INSERT volumineuses.
Les contraintes existantes dans toutes les tables peuvent être consultées dans la table system.constraints.
ASSUME
ASSUME est utilisée pour définir une CONSTRAINT sur une table, supposée être vraie. Cette contrainte peut ensuite être utilisée par l’optimiseur pour améliorer les performances des requêtes SQL.
Prenez cet exemple où ASSUME CONSTRAINT est utilisé lors de la création de la table users_a :
ASSUME CONSTRAINT sert à indiquer que la fonction length(name) est toujours égale à la valeur de la colonne name_len. Cela signifie que chaque fois que length(name) est appelée dans une requête, ClickHouse peut la remplacer par name_len, ce qui devrait être plus rapide, car cela évite d’appeler la fonction length().
Ensuite, lors de l’exécution de la requête SELECT name FROM users_a WHERE length(name) < 5;, ClickHouse peut l’optimiser en SELECT name FROM users_a WHERE name_len < 5; grâce à ASSUME CONSTRAINT. La requête peut ainsi s’exécuter plus rapidement, car il n’est plus nécessaire de calculer la longueur de name pour chaque ligne.
ASSUME CONSTRAINT ne fait pas respecter la contrainte ; il informe simplement l’optimiseur que la contrainte est supposée vraie. Si la contrainte n’est pas réellement vraie, les résultats des requêtes peuvent être incorrects. Par conséquent, vous ne devez utiliser ASSUME CONSTRAINT que si vous êtes sûr que la contrainte est vraie.
Expression TTL
Codecs de compression des colonnes
lz4 dans la version autogérée, et zstd dans ClickHouse Cloud.
Pour la famille de moteurs MergeTree, vous pouvez modifier la méthode de compression par défaut dans la section compression de la configuration du serveur.
Vous pouvez également définir la méthode de compression pour chaque colonne dans la requête CREATE TABLE.
Default peut être spécifié pour faire référence à la compression par défaut, qui peut dépendre de différents paramètres (et des propriétés des données) au moment de l’exécution.
Exemple : value UInt64 CODEC(Default) — identique à l’absence de spécification de codec.
Vous pouvez également supprimer le CODEC actuel de la colonne et utiliser la compression par défaut définie dans config.xml :
CODEC(Delta, Default).
La compression est prise en charge pour les moteurs de table suivants :
- Famille MergeTree. Prend en charge les codecs de compression des colonnes ainsi que la sélection de la méthode de compression par défaut via les paramètres compression.
- Famille Log. Utilise par défaut la méthode de compression
lz4et prend en charge les codecs de compression des colonnes. - Set. Prend uniquement en charge la compression par défaut.
- Join. Prend uniquement en charge la compression par défaut.
Codecs d’usage général
NONE
NONE — Aucune compression.
LZ4
LZ4 — Algorithme de compression de données sans perte utilisé par défaut. Utilise la compression rapide LZ4.
LZ4HC
LZ4HC[(level)] — algorithme LZ4 HC (forte compression) avec niveau configurable. Niveau par défaut : 9. Le paramètre level <= 0 applique le niveau par défaut. Niveaux possibles : [1, 12]. Plage de niveaux recommandée : [4, 9].
ZSTD
ZSTD[(level)] — algorithme de compression ZSTD avec un level configurable. Niveaux possibles : [1, 22]. Niveau par défaut : 1.
Des niveaux de compression élevés sont utiles dans des scénarios asymétriques, par exemple lorsqu’on compresse une fois puis qu’on décompresse à plusieurs reprises. Des niveaux plus élevés offrent une meilleure compression, au prix d’une utilisation du CPU plus importante.
Obsolète : ZSTD_QAT
Obsolète : DEFLATE_QPL
Codecs spécialisés
Delta
Delta(delta_bytes) — Méthode de compression dans laquelle les valeurs brutes sont remplacées par la différence entre deux valeurs adjacentes, à l’exception de la première, qui reste inchangée. delta_bytes correspond à la taille maximale des valeurs brutes ; la valeur par défaut est sizeof(type). Le fait de spécifier delta_bytes comme argument est obsolète et sa prise en charge sera supprimée dans une prochaine release. Delta est un codec de préparation des données, c’est-à-dire qu’il ne peut pas être utilisé seul.
DoubleDelta
DoubleDelta(bytes_size) — Calcule le delta des deltas et l’écrit sous forme binaire compacte. bytes_size a une signification similaire à delta_bytes dans le codec Delta. La spécification de bytes_size comme argument est obsolète et sa prise en charge sera supprimée dans une prochaine version. Les taux de compression optimaux sont obtenus pour des séquences monotones avec un pas constant, comme les séries temporelles. Peut être utilisé avec n’importe quel type numérique. Implémente l’algorithme utilisé dans Gorilla TSDB, en l’étendant pour prendre en charge les types 64 bits. Utilise 1 bit supplémentaire pour les deltas 32 bits : des préfixes sur 5 bits au lieu de préfixes sur 4 bits. Pour plus d’informations, voir Compressing Time Stamps dans Gorilla: A Fast, Scalable, In-Memory Time Series Database. DoubleDelta est un codec de préparation des données, c’est-à-dire qu’il ne peut pas être utilisé seul.
GCD
GCD() - - Calcule le plus grand commun diviseur (PGCD) des valeurs de la colonne, puis divise chaque valeur par ce PGCD. Peut être utilisé avec des colonnes d’entiers, de nombres décimaux et de date/heure. Ce codec est particulièrement adapté aux colonnes dont les valeurs varient (augmentent ou diminuent) par multiples du PGCD, par exemple 24, 28, 16, 24, 8, 24 (PGCD = 4). GCD est un codec de préparation des données, c’est-à-dire qu’il ne peut pas être utilisé seul.
Gorilla
Gorilla(bytes_size) — Calcule le XOR entre la valeur en virgule flottante courante et la précédente, puis l’écrit sous forme binaire compacte. Plus la différence entre des valeurs consécutives est faible, c.-à-d. plus les valeurs de la série évoluent lentement, meilleur est le taux de compression. Implémente l’algorithme utilisé dans Gorilla TSDB, avec une extension pour prendre en charge les types sur 64 bits. Valeurs possibles pour bytes_size : 1, 2, 4, 8 ; la valeur par défaut est sizeof(type) si elle est égale à 1, 2, 4 ou 8. Dans tous les autres cas, elle vaut 1. Pour plus d’informations, voir la section 4.1 de Gorilla: A Fast, Scalable, In-Memory Time Series Database.
ALP
ALP() — Compression adaptative sans perte pour les données à virgule flottante, basée sur la mise à l’échelle décimale. ALP tente de représenter chaque valeur sous la forme d’un entier mis à l’échelle exact à l’aide de puissances de 10, puis compresse les entiers obtenus avec Frame-of-Reference et le compactage de bits. Les valeurs qui ne peuvent pas être représentées exactement sont stockées comme exceptions brutes. Fonctionne particulièrement bien pour les nombres provenant de valeurs décimales (par ex., mesures, devises). Prend en charge Float32 et Float64. Pour plus de détails, voir ALP: Adaptive lossless floating-point compression.
Ce codec est expérimental et nécessite
SET allow_experimental_codecs = 1 pour être utilisé.FPC
FPC(level, float_size) - Prédit de façon répétée la valeur en virgule flottante suivante dans la séquence à l’aide du meilleur de deux prédicteurs, puis applique un XOR entre la valeur réelle et la valeur prédite, avant de compresser le résultat en supprimant les zéros de tête. À l’instar de Gorilla, ce codec est efficace pour stocker une série de valeurs en virgule flottante qui évoluent lentement. Pour les valeurs 64 bits (double), FPC est plus rapide que Gorilla ; pour les valeurs 32 bits, les performances peuvent varier. Valeurs possibles de level : 1-28, la valeur par défaut est 12. Valeurs possibles de float_size : 4, 8, la valeur par défaut est sizeof(type) si le type est Float. Dans tous les autres cas, elle est de 4. Pour une description détaillée de l’algorithme, voir High Throughput Compression of Double-Precision Floating-Point Data.
T64
T64 — Méthode de compression qui tronque les bits de poids fort inutilisés des valeurs dans les types de données entiers (y compris Enum, Date et DateTime). À chaque étape de son algorithme, le codec prend un bloc de 64 valeurs, les dispose dans une matrice de 64x64 bits, la transpose, tronque les bits inutilisés des valeurs et renvoie la partie restante sous forme de séquence. Les bits inutilisés sont ceux qui ne varient pas entre les valeurs minimale et maximale de l’ensemble de la partie de données pour laquelle la compression est utilisée.
Les codecs DoubleDelta et Gorilla sont utilisés dans Gorilla TSDB comme éléments de son algorithme de compression. L’approche Gorilla est efficace dans les cas où l’on a une séquence de valeurs qui évoluent lentement avec leurs horodatages. Les horodatages sont compressés efficacement par le codec DoubleDelta, et les valeurs sont compressées efficacement par le codec Gorilla. Par exemple, pour obtenir une table stockée efficacement, vous pouvez la créer avec la configuration suivante :
Codecs de chiffrement
AES_128_GCM_SIV
CODEC('AES-128-GCM-SIV') — Chiffre les données avec AES-128 en mode GCM-SIV selon la RFC 8452.
AES-256-GCM-SIV
CODEC('AES-256-GCM-SIV') — Chiffre les données avec AES-256 en mode GCM-SIV.
Ces codecs utilisent un nonce fixe, le chiffrement est donc déterministe. Ils sont ainsi compatibles avec les moteurs de déduplication tels que ReplicatedMergeTree, mais cela présente une faiblesse : lorsqu’un même bloc de données est chiffré deux fois, le texte chiffré obtenu est exactement identique. Un adversaire capable de lire le disque peut donc constater cette équivalence (sans pour autant en connaître le contenu).
La plupart des moteurs, y compris la famille “*MergeTree”, créent des fichiers d’index sur le disque sans appliquer de codecs. Cela signifie que du texte en clair apparaîtra sur le disque si une colonne chiffrée est indexée.
Si vous exécutez une requête SELECT mentionnant une valeur spécifique dans une colonne chiffrée (par exemple dans sa clause WHERE), cette valeur peut apparaître dans system.query_log. Vous pouvez envisager de désactiver la journalisation.
Si une compression doit être appliquée, elle doit être indiquée explicitement. Sinon, seul le chiffrement sera appliqué aux données.
Tables temporaires
Veuillez noter que les tables temporaires ne sont pas répliquées. Par conséquent, il n’y a aucune garantie que les données insérées dans une table temporaire soient disponibles sur d’autres répliques. Le principal cas d’utilisation des tables temporaires consiste à interroger de petits jeux de données externes ou à effectuer des jointures avec ceux-ci au cours d’une même session.
- Les tables temporaires disparaissent à la fin de la session, y compris si la connexion est perdue.
- Une table temporaire utilise le moteur de table Memory lorsque aucun moteur n’est spécifié, et elle peut utiliser n’importe quel moteur de table sauf Replicated et
KeeperMap. - Il n’est pas possible de spécifier de DB pour une table temporaire. Elle est créée en dehors des bases de données.
- Il est impossible de créer une table temporaire avec une requête DDL distribuée sur tous les serveurs du cluster (à l’aide de
ON CLUSTER) : cette table n’existe que dans la session en cours. - Si une table temporaire porte le même nom qu’une autre et qu’une requête spécifie le nom de la table sans préciser la DB, la table temporaire sera utilisée.
- Pour le traitement distribué des requêtes, les tables temporaires utilisant le moteur Memory dans une requête sont transmises aux serveurs distants.
(GLOBAL) IN distribué. Pour plus d’informations, voir les sections appropriées
Il est possible d’utiliser des tables avec ENGINE = Memory à la place des tables temporaires.
REPLACE TABLE
REPLACE vous permet de mettre à jour une table de façon atomique.
Cette instruction est prise en charge par les moteurs de base de données
Atomic et Replicated,
qui sont les moteurs de base de données par défaut de ClickHouse et de ClickHouse Cloud, respectivement.SELECT qui ne récupère pas les données indésirables,
puis supprimer l’ancienne table et renommer la nouvelle.
Cette approche est illustrée dans l’exemple ci-dessous :
REPLACE (à condition d’utiliser les moteurs de base de données par défaut) pour obtenir le même résultat :
Syntaxe
Toutes les formes de syntaxe de l’instruction
CREATE s’appliquent également à cette instruction. L’utilisation de REPLACE sur une table inexistante entraîne une erreur.Exemples :
- Local
- Cloud
Prenons la table suivante :Nous pouvons utiliser l’instruction Ou nous pouvons utiliser l’instruction
REPLACE pour effacer toutes les données :REPLACE pour modifier la structure de la table :Clause COMMENT
La clause
COMMENT doit être spécifiée après toute clause propre au stockage, telle que PARTITION BY, ORDER BY et les SETTINGS propres au stockage.Après la clause COMMENT, seuls les SETTINGS propres aux requêtes (comme max_threads, etc.) seront analysés, et non les paramètres liés au stockage.Cela signifie que l’ordre correct des clauses est le suivant :ENGINE- clauses de stockage
COMMENT- paramètres de requête (le cas échéant)
Query
Response