SELECT

Documentation de la requête SELECT

Les requêtes SELECT servent à extraire des données. Par défaut, les données demandées sont renvoyées au client ; associées à INSERT INTO, elles peuvent être redirigées vers une autre table.

​

Syntaxe

[WITH expr_list(subquery)]
SELECT [DISTINCT [ON (column1, column2, ...)]] expr_list
[FROM [db.]table | (subquery) | table_function] [FINAL]
[SAMPLE sample_coeff]
[ARRAY JOIN ...]
[GLOBAL] [ANY|ALL|ASOF] [INNER|LEFT|RIGHT|FULL|CROSS] [OUTER|SEMI|ANTI] JOIN (subquery)|table [(alias1 [, alias2 ...])] (ON <expr_list>)|(USING <column_list>)
[PREWHERE expr]
[WHERE expr]
[GROUP BY expr_list] [WITH ROLLUP|WITH CUBE] [WITH TOTALS]
[HAVING expr]
[WINDOW window_expr_list]
[QUALIFY expr]
[ORDER BY expr_list] [WITH FILL] [FROM expr] [TO expr] [STEP expr] [INTERPOLATE [(expr_list)]]
[LIMIT [offset_value, ]n BY columns]
[LIMIT [n, ]m] [WITH TIES]
[LIMIT [n] AFTER start_expr [ALL] [UNTIL end_expr] | LIMIT [n] UNTIL end_expr]
[SETTINGS ...]
[UNION  ...]
[INTO OUTFILE filename [TRUNCATE] [COMPRESSION type [LEVEL level]] ]
[FORMAT format]

La liste d’expressions qui suit immédiatement SELECT est obligatoire ; toutes les autres clauses sont facultatives.

SELECT et ses clauses facultatives sont décrits dans des sections distinctes :

• clause WITH
• clause SELECT
• clause ALL
• clause DISTINCT
• clause FROM
• clause SAMPLE
• clause ARRAY JOIN
• clause JOIN
• clause PREWHERE
• clause WHERE
• clause GROUP BY
• clause HAVING
• clause WINDOW
• clause QUALIFY
• clause ORDER BY
• clause LIMIT BY
• clause LIMIT
• clause OFFSET
• clause UNION
• clause INTERSECT
• clause EXCEPT
• clause INTO OUTFILE
• clause FORMAT

Une requête peut également être écrite sous la forme d’une chaîne linéaire de transformations à l’aide d’opérateurs pipe :

FROM table
|> WHERE x > 1
|> AGGREGATE count() AS c GROUP BY y
|> ORDER BY c DESC

​

Clause SELECT

Les expressions spécifiées dans la clause SELECT sont calculées une fois toutes les opérations des clauses décrites ci-dessus terminées. Ces expressions se comportent comme si elles s’appliquaient à des lignes distinctes du résultat. Si des expressions de la clause SELECT contiennent des fonctions d’agrégation, ClickHouse traite alors les fonctions d’agrégation ainsi que les expressions utilisées comme arguments lors de l’agrégation GROUP BY.

Si vous souhaitez inclure toutes les colonnes dans le résultat, utilisez le symbole astérisque (*). Par exemple, SELECT * FROM ....

​

Sélection dynamique de colonnes

La sélection dynamique de colonnes (également appelée expression COLUMNS) vous permet de faire correspondre certaines colonnes d’un résultat à une expression régulière re2.

COLUMNS('regexp')

Par exemple, prenons la table :

CREATE TABLE default.col_names (aa Int8, ab Int8, bc Int8) ENGINE = TinyLog

La requête suivante sélectionne les données de toutes les colonnes dont le nom contient le symbole a.

SELECT COLUMNS('a') FROM col_names

┌─aa─┬─ab─┐
│  1 │  1 │
└────┴────┘

Les colonnes sélectionnées ne sont pas renvoyées dans l’ordre alphabétique.

Vous pouvez utiliser plusieurs expressions COLUMNS dans une requête et leur appliquer des fonctions.

Par exemple :

SELECT COLUMNS('a'), COLUMNS('c'), toTypeName(COLUMNS('c')) FROM col_names

┌─aa─┬─ab─┬─bc─┬─toTypeName(bc)─┐
│  1 │  1 │  1 │ Int8           │
└────┴────┴────┴────────────────┘

Chaque colonne renvoyée par l’expression COLUMNS est passée à la fonction comme argument distinct. Vous pouvez également passer d’autres arguments à la fonction si elle les accepte. Soyez prudent lorsque vous utilisez des fonctions. Si une fonction n’accepte pas le nombre d’arguments que vous lui avez passés, ClickHouse lève une exception.

Par exemple :

SELECT COLUMNS('a') + COLUMNS('c') FROM col_names

Received exception from server (version 19.14.1):
Code: 42. DB::Exception: Received from localhost:9000. DB::Exception: Number of arguments for function plus does not match: passed 3, should be 2.

Dans cet exemple, COLUMNS('a') renvoie deux colonnes : aa et ab. COLUMNS('c') renvoie la colonne bc. L’opérateur + ne peut pas être appliqué à 3 arguments. ClickHouse génère donc une exception avec le message approprié.

Les colonnes correspondant à l’expression COLUMNS peuvent avoir des types de données différents. Si COLUMNS ne correspond à aucune colonne et qu’il s’agit de la seule expression dans SELECT, ClickHouse génère une exception.

​

Sélectionner des colonnes avec LIKE ou ILIKE

Vous pouvez également sélectionner des colonnes en faisant correspondre leurs noms à un motif après *, en utilisant LIKE (sensible à la casse) ou ILIKE (insensible à la casse) :

SELECT * ILIKE 'a%' FROM col_names

┌─aa─┬─ab─┐
│  1 │  1 │
└────┴────┘

Les motifs LIKE et ILIKE suivent la sémantique de LIKE, et non celle des expressions régulières. Le caractère % correspond à n’importe quelle séquence de caractères, le caractère _ correspond à un seul caractère, et \ sert à échapper %, _ et \. La seule différence entre les deux est que LIKE fait correspondre les noms de colonnes en respectant la casse, tandis que ILIKE est insensible à la casse. Par exemple :

SELECT * ILIKE 'a_' FROM col_names

La requête sélectionne les colonnes dont le nom comporte deux caractères et commence par a, comme aa et ab.

* LIKE et * ILIKE prennent également en charge les astérisques qualifiés et les transformateurs de colonnes :

SELECT t.* ILIKE 'a%' EXCEPT (ab) FROM col_names AS t

┌─aa─┐
│  1 │
└────┘

​

Astérisque

Vous pouvez mettre un astérisque à n’importe quel endroit d’une requête à la place d’une expression. Lors de l’analyse de la requête, l’astérisque est remplacé par la liste de toutes les colonnes de la table (à l’exclusion des colonnes MATERIALIZED et ALIAS). Il n’existe que quelques cas où l’utilisation d’un astérisque se justifie :

• Lors de la création d’un dump de table.
• Pour les tables qui ne contiennent que quelques colonnes, comme les tables système.
• Pour obtenir des informations sur les colonnes d’une table. Dans ce cas, définissez LIMIT 1. Mais il est préférable d’utiliser la requête DESC TABLE.
• Lorsqu’un filtrage important s’applique à un petit nombre de colonnes via PREWHERE.
• Dans les sous-requêtes (car les colonnes qui ne sont pas nécessaires à la requête externe sont exclues des sous-requêtes).

Dans tous les autres cas, nous ne recommandons pas d’utiliser l’astérisque, car cela ne vous donne que les inconvénients d’un SGBD colonnaire, sans ses avantages. En d’autres termes, l’utilisation de l’astérisque n’est pas recommandée.

​

Valeurs extrêmes

En plus des résultats, vous pouvez également obtenir les valeurs minimales et maximales des colonnes de résultat. Pour ce faire, définissez le paramètre extremes sur 1. Les minima et maxima sont calculés pour les types numériques, les dates et les dates avec heure. Pour les autres colonnes, les valeurs par défaut sont affichées.

Deux lignes supplémentaires sont calculées : l’une pour les minima et l’autre pour les maxima. Ces deux lignes supplémentaires sont affichées dans les formats XML, JSON*, TabSeparated*, CSV*, Vertical, Template et Pretty*, séparément des autres lignes. Elles ne sont pas affichées dans les autres formats.

Dans les formats JSON* et XML, les valeurs extrêmes sont affichées dans un champ extremes distinct. Dans les formats TabSeparated*, CSV* et Vertical, la ligne apparaît après le résultat principal, et après totals s’il est présent. Elle est précédée d’une ligne vide (après les autres données). Dans les formats Pretty*, la ligne est affichée sous la forme d’une table distincte après le résultat principal, et après totals s’il est présent. Dans le format Template, les valeurs extrêmes sont affichées conformément au modèle spécifié.

Les valeurs extrêmes sont calculées sur les lignes avant LIMIT, mais après LIMIT BY. Toutefois, lors de l’utilisation de LIMIT offset, size, les lignes avant offset sont incluses dans extremes. Une plage LIMIT ... AFTER ... UNTIL se comporte ici comme LIMIT : les valeurs extrêmes sont calculées sur les lignes lues avant l’application de la plage. Dans les requêtes en flux, le résultat peut également inclure un petit nombre de lignes ayant passé LIMIT.

​

Remarques

Vous pouvez utiliser des synonymes (alias AS) dans n’importe quelle partie d’une requête.

Les clauses GROUP BY, ORDER BY et LIMIT BY peuvent accepter des arguments positionnels. Pour activer cette fonctionnalité, activez le paramètre enable_positional_arguments. Ainsi, par exemple, ORDER BY 1,2 triera les lignes de la table d’abord selon la première colonne, puis selon la deuxième.

​

Détails d’implémentation

Si la requête omet les clauses DISTINCT, GROUP BY et ORDER BY, ainsi que les sous-requêtes IN et JOIN, elle sera entièrement traitée en flux, avec une consommation de RAM en O(1). Sinon, la requête peut consommer beaucoup de RAM si les limites appropriées ne sont pas définies :

• max_memory_usage
• max_rows_to_group_by
• max_rows_to_sort
• max_rows_in_distinct
• max_bytes_in_distinct
• max_rows_in_set
• max_bytes_in_set
• max_rows_in_join
• max_bytes_in_join
• max_bytes_before_external_sort
• max_bytes_ratio_before_external_sort
• max_bytes_before_external_group_by
• max_bytes_ratio_before_external_group_by

Pour plus d’informations, consultez la section « Paramètres ». Il est possible d’utiliser le tri externe (en enregistrant des tables temporaires sur un disque) et l’agrégation externe.

​

Modificateurs de SELECT

Vous pouvez utiliser les modificateurs suivants dans les requêtes SELECT.

Modifier	Description
APPLY	Permet d’invoquer une fonction pour chaque ligne renvoyée par une expression de table externe d’une requête.
EXCEPT	Spécifie le nom d’une ou de plusieurs colonnes à exclure du résultat. Tous les noms de colonnes correspondants sont omis de la sortie.
REPLACE	Spécifie un ou plusieurs alias d’expression. Chaque alias doit correspondre au nom d’une colonne de l’instruction SELECT *. Dans la liste des colonnes de sortie, la colonne correspondant à l’alias est remplacée par l’expression de ce REPLACE. Ce modificateur ne change ni les noms ni l’ordre des colonnes. Il peut toutefois modifier la valeur et son type.

​

Combinaisons de modificateurs

Vous pouvez utiliser chaque modificateur séparément ou les combiner.

Exemples :

Utiliser plusieurs fois le même modificateur.

SELECT COLUMNS('[jk]') APPLY(toString) APPLY(length) APPLY(max) FROM columns_transformers;

┌─max(length(toString(j)))─┬─max(length(toString(k)))─┐
│                        2 │                        3 │
└──────────────────────────┴──────────────────────────┘

Utilisation de plusieurs modificateurs dans une même requête.

SELECT * REPLACE(i + 1 AS i) EXCEPT (j) APPLY(sum) from columns_transformers;

┌─sum(plus(i, 1))─┬─sum(k)─┐
│             222 │    347 │
└─────────────────┴────────┘

​

SETTINGS dans la requête SELECT

Vous pouvez spécifier les paramètres nécessaires directement dans la requête SELECT. La valeur du paramètre s’applique uniquement à cette requête, puis est réinitialisée à sa valeur par défaut ou à sa valeur précédente une fois la requête exécutée.

Pour découvrir d’autres façons de définir des paramètres, voir ici.

Pour les paramètres booléens définis sur true, vous pouvez utiliser une syntaxe abrégée en omettant l’affectation de valeur. Lorsque seul le nom du paramètre est spécifié, il est automatiquement défini sur 1 (true).

Exemple

SELECT * FROM some_table SETTINGS optimize_read_in_order=1, cast_keep_nullable=1;