À mesure que la popularité de ClickHouse a augmenté au fil des années, de plus en plus de personnes connaissent les fonctionnalités principales de ClickHouse comme ses performances incroyables, son taux de compression élevé ou les énormes capacités de lecture et d’écriture de presque tous les formats de données existants.

Mais ClickHouse possède également de nombreux joyaux cachés, qui peuvent vous aider dans votre travail quotidien, et que beaucoup de gens ignorent. Même ainsi, la plupart d’entre eux sont bien documentés dans la documentation officielle, si vous ne savez pas que vous les cherchez, vous ne les trouverez pas.

Dans cet article de blog, je mettrai en évidence certaines de mes petites fonctionnalités préférées que davantage de personnes devraient connaître.

Sélectionnez les modificateurs

Dans un SGBD basé sur des colonnes comme ClickHouse, des requêtes comme SELECT * FROM table devrait généralement être évitée. C’est du moins vrai lorsqu’il s’agit de requêtes régulières émises par votre application. Le travail quotidien typique d’un administrateur de base de données ou d’un développeur de bases de données inclut cependant souvent ce type de requêtes, sinon il faudrait énormément de temps pour taper manuellement toutes les colonnes X d’une table.

Mais que se passe-t-il si vous souhaitez exécuter de nombreuses opérations sur certaines colonnes ? Imaginez que vous ayez le tableau suivant :

Unset

CREATE TABLE customer (
customerId UInt64,
custom_num_1 UInt64,
custom_num_2 UInt64,
...
custom_num_50 UInt64,
custom_string_1 String,
custom_string_2 String,
...
custom_string_50 String
);

Lors de l’optimisation de votre tableau, vous pourriez être intéressé par la longueur moyenne des colonnes String de votre tableau, ainsi que par la valeur maximale de vos colonnes numériques. Si vous souhaitez écrire manuellement une requête pour collecter ces informations, vous devrez écrire la fonction correcte pour 100 colonnes de cette façon.

Dans certaines bases de données (comme MySQL mais aussi ClickHouse) vous pouvez utiliser le INFORMATION_SCHEMA.COLUMNS table pour construire votre requête. Cela peut être pratique pour de nombreux administrateurs de base de données, car ils y sont peut-être déjà habitués, mais ClickHouse offre un moyen encore plus rapide d’atteindre votre objectif : Sélectionnez les modificateurs

En utilisant une combinaison de modificateurs, notre tâche se résume à une simple requête :

Unset

SELECT

       COLUMNS('.*num.*') APPLY max,

       COLUMNS('.*string.*') APPLY length APPLY max

FROM customer

Nous utilisons le COLONNES modificateur pour appliquer une expression régulière aux noms de colonnes pour obtenir uniquement les colonnes contenant des noms num ou string, et sur toutes ces colonnes, nous appliquons la fonction maximum s’il figurait dans l’ensemble des colonnes numériques, ou si nous appliquons d’abord la fonction longueur et ensuite la fonction maximum. Cela nous donne les résultats souhaités et prend beaucoup moins de temps que de créer une requête via le schéma d’information ou d’écrire manuellement 100 colonnes.

Données de test manuelles dans différents formats

Souvent, lorsque je souhaite aider les clients ou les utilisateurs de ClickHouse sur le canal ouvert Slack, il existe des cas où vous devez fournir des exemples dans un tableau de test. Parfois, écrire la définition d’une telle table de test et la remplir avec des données de test peut même représenter plus de code que ce que vous souhaitez afficher en premier lieu.

Comme vous avez pu le voir dans la section précédente, le tableau que j’ai utilisé pour expliquer les modificateurs de sélection est plus long que le code réel que je souhaite présenter (et il a déjà été abrégé).

Mais il y a une possibilité dans ClickHouse de travailler directement sur les données, comme si elles se trouvaient dans un tableau, qui est le fonction de table de formatage

Avec cette fonction, vous pouvez spécifier le format que vous souhaitez utiliser (comme JSONEachRow) puis saisir directement les données, au lieu de les insérer d’abord dans un tableau :

Unset

SELECT *
FROM FORMAT(JSONEachRow, '{"customerId":1,"type":1,"custom_num1":4711}n{"customerId":2, "type":2,"custom_ips":["127.0.0.1","127.0.0.2"]}')

+-customerId-+-type-+-custom_num1-+-custom_ips----------------+
| 1 | 1 | 4711 | [] |
| 2 | 2 | NULL | ['127.0.0.1','127.0.0.2'] |
+------------+------+-------------+---------------------------+

Vous pouvez voir qu’il génère un jeu de résultats avec deux lignes, et même des types complexes comme les tableaux sont réalisables. Et vous pouvez utiliser une grande variété de formats de données.

Générer des séries avec Nombres fonction de table

Certains SGBD ont la possibilité de générer des séquences de nombres en utilisant des fonctions de table. ClickHouse ne fait pas exception à cet égard, à condition que le Nombres() fonction de table pour ces égards. Mais saviez-vous que vous pouvez facilement générer date séquences ou temps des séquences avec cette fonction de table également ?

ClickHouse permet des fonctions arithmétiques simples sur Date et DateHeure types de données, vous permettant de générer facilement des séquences de dates ou d’horodatages :

Unset

SELECT
number,
now() - number AS previousTimes,
toDate(now()) + number AS nextDays
FROM numbers(10)
+-number-+-------previousTimes-+---nextDays-+
|  0 | 2024-04-25 13:04:30 | 2024-04-25 |
|  1 | 2024-04-25 13:04:29 | 2024-04-26 |
|  2 | 2024-04-25 13:04:28 | 2024-04-27 |
|  3 | 2024-04-25 13:04:27 | 2024-04-28 |
|  4 | 2024-04-25 13:04:26 | 2024-04-29 |
|  5 | 2024-04-25 13:04:25 | 2024-04-30 |
|  6 | 2024-04-25 13:04:24 | 2024-05-01 |
|  7 | 2024-04-25 13:04:23 | 2024-05-02 |
|  8 | 2024-04-25 13:04:22 | 2024-05-03 |
|  9 | 2024-04-25 13:04:21 | 2024-05-04 |
+--------+---------------------+------------+

En appliquant la multiplication, vous pouvez également introduire des étapes, etc.

Comme le type de données final des colonnes sera toujours Date ou DateHeure vous pouvez être sûr que seules les dates valides sont générées

Formatage des données dans des colonnes personnalisées

Parfois, il est nécessaire de formater partiellement vos données lors de la sortie. Par exemple si vous souhaitez insérer des données dans un service de streaming comme Kafka. En règle générale, vous avez besoin de certaines colonnes en tant que colonnes directes, mais vous combinez également les données d’autres colonnes dans une colonne de charge utile, dans un format donné (généralement JSON).

Bien sûr, vous pouvez également faire beaucoup de ces choses dans d’autres SGBD, en utilisant la concaténation de chaînes et en créant votre JSON manuellement, ou en utilisant des fonctions JSON spécifiques comme versJSONString ou créez manuellement des objets JSON, etc.

Mais ClickHouse vous couvre également ici, en vous offrant la fonction formatRowNoNewline(). Cette fonction vous permet de formater une quantité arbitraire de colonnes dans tous les formats de sortie possibles proposés par ClickHouse.

Et bien sûr, vous pouvez également utiliser Select Modifiers pour spécifier les colonnes à formater :

Unset

SELECT
customerId,
formatRowNoNewline('JSONEachRow', COLUMNS('.*num.*')) AS payload
FROM customer
LIMIT 10

+-customerId-+-payload------------------------------------------------------------------------------+
|      20 | {"custom_num_1":"4503644724578621668","custom_num_2":"156","custom_num_50":"32624"}  |
|    111 | {"custom_num_1":"9395348731023764754","custom_num_2":"4","custom_num_50":"8919"} |
|    187 | {"custom_num_1":"4410745110154105282","custom_num_2":"67","custom_num_50":"19015"}   |
|    231 | {"custom_num_1":"8206799308850873985","custom_num_2":"151","custom_num_50":"43260"}  |
|    262 | {"custom_num_1":"14904510309594397590","custom_num_2":"83","custom_num_50":"2802"}   |
|    375 | {"custom_num_1":"14468162691130275987","custom_num_2":"13","custom_num_50":"6910"}   |
|    388 | {"custom_num_1":"15191470301382236130","custom_num_2":"110","custom_num_50":"39490"} |
|    434 | {"custom_num_1":"11648353314185268442","custom_num_2":"211","custom_num_50":"52725"} |
|    439 | {"custom_num_1":"8430391284904487000","custom_num_2":"31","custom_num_50":"43376"}   |
|    468 | {"custom_num_1":"11265143749859112447","custom_num_2":"41","custom_num_50":"58748"}  |
+------------+--------------------------------------------------------------------------------------+

Interroger l’ensemble du cluster

Parfois, interroger un seul nœud ne suffit pas. Imaginez le cas où vous recherchez des requêtes qui durent plus longtemps qu’un seuil spécifique. Vous pouvez obtenir les informations dans ClickHouse système.query_log table, mais vous devrez vérifier tous les hôtes séparément.

Mais encore une fois, ClickHouse a ce qu’il vous faut. Il y a une fonction de table clusterAllReplicas qui permet d’exécuter une requête sur tous les nœuds d’un cluster et de vous donner le résultat combiné, comme s’il s’agissait d’une table locale :

Unset

SELECT
user,
substring(query, 1, 15) AS query_part,
query_duration_ms
FROM clusterAllReplicas('mytestcluster', system, query_log)
WHERE query_duration_ms > 50
LIMIT 3

+-user----+-query_part------+-query_duration_ms-+
| default | INSERT INTO col |                52 |
| default | INSERT INTO col |                55 |
| default | INSERT INTO col |                51 |
+---------+-----------------+-------------------+

Bonus : travailler avec AggregateStates

Travailler avec des vues matérialisées et des possibilités de Fonctions d’agrégation pourrait remplir plusieurs articles de blog à lui seul (par exemple celui-ci sur impact sur les performances des vues matérialisées). C’est pourquoi je souhaite seulement mentionner brièvement certaines fonctionnalités que tout le monde ne connaît pas, mais qui pourraient s’avérer utiles.

Supposons que nous ayons les 2 tableaux suivants, qui comptent simplement clients uniques par heure ou jour:

Unset

CREATE TABLE customers_hourly (
eventDate Date,
eventHour UInt8,
uniqueCustomers AggregateFunction(uniq, UInt64)
) ENGINE=AggregatingMergeTree
ORDER BY (eventDate, eventHour);

CREATE TABLE customers_daily (
eventDate Date,
uniqueCustomers AggregateFunction(uniq, UInt64)
) ENGINE=AggregatingMergeTree
ORDER BY (eventDate);

Initialiser l’agrégation

Remplir ces tableaux est assez simple avec les vues matérialisées. Mais que se passe-t-il si vous souhaitez insérer manuellement une ligne. Par exemple, à des fins de test, vous souhaitez insérer le test customerId 4711 à 3 heures différentes d’une même journée.

Comme uniqueCustomers est une AggregationFunction, vous ne pouvez pas insérer directement le customerId dans cette colonne, donc quelque chose comme ceci ne fonctionne pas :

Unset

INSERT INTO customers_hourly
SELECT eventDate,eventHour,customerId
FROM FORMAT(JSONEachRow,
$${"customerId":4711,"eventDate":"2024-04-26","eventHour":1}
{"customerId":4711,"eventDate":"2024-04-26","eventHour":2}
{"customerId":4711,"eventDate":"2024-04-26","eventHour":3}$$)

Bien entendu, il est possible de générer un état d’agrégation en utilisant les fonctions window :

Unset

INSERT INTO customers_hourly
SELECT eventDate,eventHour,
uniqState(toUInt64(assumeNotNull(customerId))) OVER ()
FROM FORMAT(JSONEachRow,
$${"customerId":4711,"eventDate":"2024-04-26","eventHour":1}
{"customerId":4711,"eventDate":"2024-04-26","eventHour":2}
{"customerId":4711,"eventDate":"2024-04-26","eventHour":3}$$)

Mais si vous avez plusieurs arguments dans la fonction qui pourraient être difficiles à lire, vous pouvez également simplement utiliser le initializeAggregation fonction à insérer directement dans le tableau :

Unset

INSERT INTO customers_hourly
SELECT eventDate,eventHour,
initializeAggregation('uniqState',toUInt64(assumeNotNull(customerId)))
FROM FORMAT(JSONEachRow,
$${"customerId":4711,"eventDate":"2024-04-26","eventHour":1}
{"customerId":4711,"eventDate":"2024-04-26","eventHour":2}
{"customerId":4711,"eventDate":"2024-04-26","eventHour":3}$$)

Finaliser l’agrégation

Maintenant que nous savons comment écrire directement dans une fonction d’agrégation, comment pouvons-nous en lire ? Bien sûr, nous pouvons utiliser uniqMerge pour obtenir le résultat final, mais c’est une fonction d’agrégation, nous devons donc faire PAR GROUPE etc pour obtenir le résultat final. Et si nous voulons voir les résultats intermédiaires que nous avons ajoutés dans le tableau, nous devrons également utiliser à nouveau une fenêtre pour éviter la réduction des lignes. Ou nous utilisons simplement finalizeAgrégation pour faire plus simple :

Unset

SELECT
       eventDate,
       eventHour,
       finalizeAggregation(uniqueCustomers)
FROM customers_hourly
┌──eventDate─┬─eventHour─┬─finalizeAggregation(uniqueCustomers)─┐
1. │ 2024-04-26 │         1 │                                    1 │
2. │ 2024-04-26 │         2 │                                    1 │
3. │ 2024-04-26 │         3 │                                    1 │
└────────────┴───────────┴──────────────────────────────────────┘
┌──eventDate─┬─eventHour─┬─finalizeAggregation(uniqueCustomers)─┐
4. │ 2024-04-26 │         1 │                                    1 │
5. │ 2024-04-26 │         2 │                                    1 │
6. │ 2024-04-26 │         3 │                                    1 │
└────────────┴───────────┴──────────────────────────────────────┘

Fusionner l’état

Comment passer de l’agrégation horaire à l’agrégation journalière ? Dans les cas où vous pourriez simplement résumer les résultats du tableau horaire, c’est bien sûr assez facile à réaliser. Malheureusement, avec la fonction uniq, vous ne pouvez pas simplement résumer les résultats intermédiaires, car le même utilisateur aurait pu être actif pendant plusieurs heures, le résultat quotidien n’est donc pas la somme de tous les résultats horaires. J’ai vu des clients recalculer simplement la table quotidienne à partir de la table de base, au lieu de simplement continuer l’agrégation en utilisant uniqMergeState.:

Unset

INSERT INTO customers_daily
SELECT eventDate, uniqMergeState(uniqueCustomers)
FROM customers_hourly
GROUP BY eventDate

La logique est aussi simple qu’il y paraît. Il fusionnera les résultats intermédiaires, mais au lieu de donner le résultat fusionné, il donnera le nouvel état interne, étant donc stocké dans la table d’agrégation quotidienne.

Ce sont 5 (+3 bonus) petites fonctionnalités que je pense être bonnes à connaître. Si vous avez d’autres fonctionnalités ou sujets intéressants sur lesquels vous souhaitez en savoir plus, n’hésitez pas à me contacter via slack ou linked.in

L’Open Source vit de sa communauté !

Crédit image en vedette : Kevin Ku/Unsplash