Skip to main content
Nous recommandons aux utilisateurs de toujours créer leur propre schéma pour les logs et les traces pour les raisons suivantes :
  • Choisir une clé primaire - Les schémas par défaut utilisent un ORDER BY optimisé pour des modèles d’accès spécifiques. Il est peu probable que vos modèles d’accès y correspondent.
  • Extraire la structure - Vous pouvez souhaiter extraire de nouvelles colonnes à partir de colonnes existantes, par exemple la colonne Body. Cela peut se faire à l’aide de colonnes matérialisées (et de vues matérialisées dans les cas plus complexes). Cela nécessite des modifications du schéma.
  • Optimiser les Maps - Les schémas par défaut utilisent le type Map pour stocker les attributs. Ces colonnes permettent de stocker des métadonnées arbitraires. Bien qu’il s’agisse d’une capacité essentielle — les métadonnées des événements n’étant souvent pas définies à l’avance et ne pouvant donc pas être stockées autrement dans une base de données fortement typée comme ClickHouse — l’accès aux clés de map et à leurs valeurs est moins efficace que l’accès à une colonne classique. Pour y remédier, nous modifions le schéma afin que les clés de map les plus fréquemment consultées deviennent des colonnes de premier niveau ; voir “Extracting structure with SQL”. Cela nécessite une modification du schéma.
  • Simplifier l’accès aux clés de map - L’accès aux clés dans les maps nécessite une syntaxe plus verbeuse. Vous pouvez atténuer cela avec des alias. Voir “Using Aliases” pour simplifier les requêtes.
  • Index secondaires - Le schéma par défaut utilise des index secondaires pour accélérer l’accès aux Maps et les requêtes textuelles. Ils ne sont généralement pas nécessaires et consomment de l’espace disque supplémentaire. Ils peuvent être utilisés, mais doivent être testés pour vérifier qu’ils sont réellement utiles. Voir “Secondary / Data Skipping indices”.
  • Utiliser des codecs - Vous pouvez souhaiter personnaliser les codecs des colonnes si vous comprenez les caractéristiques des données attendues et disposez d’éléments montrant que cela améliore la compression.
Nous décrivons en détail chacun des cas d’usage ci-dessus dans la suite de cette section. Important : Bien que les utilisateurs soient encouragés à étendre et à modifier leur schéma pour obtenir une compression optimale et de bonnes performances de requête, ils devraient respecter autant que possible la convention de nommage du schéma OTel pour les colonnes principales. Le plugin Grafana ClickHouse suppose l’existence de certaines colonnes OTel de base pour faciliter la construction des requêtes, par exemple Timestamp et SeverityText. Les colonnes requises pour les logs et les traces sont documentées ici [1][2] et ici, respectivement. Vous pouvez choisir de modifier ces noms de colonnes en remplaçant les valeurs par défaut dans la configuration du plugin.
ClickStack est livré avec un schéma par défaut optimiséClickStack fournit des schémas prêts à l’emploi pour les logs, les traces et les métriques qui intègrent les dernières fonctionnalités de ClickHouse (text indexes pour la recherche en texte intégral et par clé de map, colonnes matérialisées et tableaux ALIAS pour le filtrage en lecture directe, recherches de lignes par numéro de bloc) et ont été benchmarkés pour offrir de solides performances prêtes à l’emploi pour les charges de travail de logs et de traces. Utilisez-les comme point de référence pour votre propre conception.

​
Extraction de structure avec SQL

Qu’il s’agisse d’ingérer des logs structurés ou non structurés, les utilisateurs ont souvent besoin de pouvoir :
  • Extraire des colonnes à partir de blobs de chaînes. Les requêtes sur ces colonnes seront plus rapides que l’utilisation d’opérations sur les chaînes au moment de la requête.
  • Extraire des clés de Map. Le schéma par défaut place les attributs arbitraires dans des colonnes de type Map. Ce type offre une capacité sans schéma, avec l’avantage que les utilisateurs n’ont pas besoin de prédéfinir les colonnes d’attributs lors de la définition des logs et des traces — ce qui est souvent impossible lors de la collecte de logs depuis Kubernetes si l’on veut s’assurer que les labels des pods sont conservés pour des recherches ultérieures. L’accès aux clés d’une Map et à leurs valeurs est plus lent que les requêtes sur des colonnes ClickHouse classiques. Il est donc souvent préférable d’extraire les clés des Map vers les colonnes de premier niveau de la table.
Considérez les requêtes suivantes : Supposons que nous souhaitions compter quels chemins d’URL reçoivent le plus de requêtes POST à partir des logs structurés. Le blob JSON est stocké dans la colonne Body sous forme de String. De plus, il peut aussi être stocké dans la colonne LogAttributes sous la forme d’un Map(String, String) si l’utilisateur a activé le json_parser dans le collector.
Si LogAttributes est disponible, voici la requête permettant de compter les chemins d’URL du site qui reçoivent le plus de requêtes POST :
Notez ici l’utilisation de la syntaxe map, par ex. LogAttributes['request_path'], ainsi que de la fonction path pour supprimer les paramètres de requête de l’URL. Si l’utilisateur n’a pas activé le parsing JSON dans le collector, LogAttributes sera vide, ce qui nous obligera à utiliser les fonctions JSON pour extraire les colonnes du Body de type String.
Privilégiez ClickHouse pour le parsingNous recommandons généralement aux utilisateurs d’effectuer le parsing JSON des logs structurés dans ClickHouse. Nous sommes convaincus que ClickHouse propose l’implémentation de parsing JSON la plus rapide. Cela dit, nous comprenons que vous puissiez vouloir envoyer les logs vers d’autres sources sans intégrer cette logique dans SQL.
Examinons maintenant le même cas pour les logs non structurés :
Une requête similaire sur des logs non structurés nécessite l’utilisation d’expressions régulières via la fonction extractAllGroupsVertical.
La complexité et le coût accrus des requêtes nécessaires pour analyser des logs non structurés (notez la différence de performance) expliquent pourquoi nous recommandons aux utilisateurs d’utiliser des logs structurés chaque fois que possible.
Pensez aux DictionariesLa requête ci-dessus შეიძლება être optimisée pour exploiter des Dictionaries d’expressions régulières. Voir Using Dictionaries pour plus de détails.
Ces deux cas d’usage peuvent être pris en charge avec ClickHouse en déplaçant la logique de la requête ci-dessus au moment de l’insert. Nous examinons ci-dessous plusieurs approches et indiquons dans quels cas chacune est appropriée.
OTel ou ClickHouse pour le traitement ?Vous pouvez également effectuer le traitement à l’aide des processors et operators de l’OTel collector, comme décrit ici. Dans la plupart des cas, vous constaterez que ClickHouse est nettement plus économe en ressources et plus rapide que les processors du collector. Le principal inconvénient d’effectuer tout le traitement des événements en SQL est le couplage de votre solution à ClickHouse. Par exemple, vous pouvez souhaiter envoyer les logs traités vers d’autres destinations depuis l’OTel collector, par ex. S3.

​
Colonnes matérialisées

Les colonnes matérialisées constituent la solution la plus simple pour extraire une structure à partir d’autres colonnes. Les valeurs de ces colonnes sont toujours calculées au moment de l’insertion et ne peuvent pas être spécifiées dans des requêtes INSERT.
SurcoûtLes colonnes matérialisées entraînent un surcoût de stockage supplémentaire, car les valeurs sont extraites vers de nouvelles colonnes sur disque au moment de l’insertion.
Les colonnes matérialisées prennent en charge n’importe quelle expression ClickHouse et peuvent tirer parti de toutes les fonctions analytiques pour le traitement des chaînes (y compris les expressions régulières et la recherche) et les URL, effectuer des conversions de type, extraire des valeurs depuis JSON ou réaliser des opérations mathématiques. Nous recommandons les colonnes matérialisées pour les traitements de base. Elles sont particulièrement utiles pour extraire des valeurs depuis des maps, les promouvoir en colonnes de premier niveau et effectuer des conversions de type. Elles sont souvent les plus utiles dans des schémas très simples ou en complément de vues matérialisées. Considérez le schéma suivant pour des logs dont le JSON a été extrait dans la colonne LogAttributes par le collector :
Le schéma équivalent pour extraire des données à l’aide des fonctions JSON à partir d’un Body de type String se trouve ici. Nos trois colonnes matérialisées extraient la page demandée, le type de requête et le domaine du référent. Elles accèdent aux clés de la map et appliquent des fonctions à leurs valeurs. La requête suivante est nettement plus rapide :
Par défaut, les colonnes matérialisées ne sont pas renvoyées dans un 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 d’INSERT. Ce comportement peut être désactivé en définissant asterisk_include_materialized_columns=1 et peut être activé dans Grafana (voir Additional Settings -> Custom Settings dans la configuration de la source de données).

​
Vues matérialisées

Les vues matérialisées offrent un moyen plus puissant d’appliquer un filtrage SQL et des transformations aux logs et aux traces. Les vues matérialisées permettent de déplacer le coût du calcul du moment de l’exécution de la requête vers le moment de l’insertion. Une vue matérialisée ClickHouse n’est en réalité qu’un trigger qui exécute une requête sur des blocs de données lorsqu’ils sont insérés dans une table. Les résultats de cette requête sont insérés dans une seconde table « cible ».
Mises à jour en temps réelLes vues matérialisées dans ClickHouse sont mises à jour en temps réel à mesure que les données arrivent dans la table sur laquelle elles reposent, et fonctionnent davantage comme des index continuellement mis à jour. À l’inverse, dans d’autres bases de données, les vues matérialisées sont généralement des instantanés statiques d’une requête qui doivent être actualisés (comme les vues matérialisées actualisables de ClickHouse).
La requête associée à la vue matérialisée peut théoriquement être n’importe quelle requête, y compris une agrégation, même s’il existe des limitations avec les jointures. Pour les charges de travail de transformation et de filtrage requises pour les logs et les traces, vous pouvez considérer que toute instruction SELECT est possible. Gardez à l’esprit que la requête n’est qu’un trigger qui s’exécute sur les lignes insérées dans une table (la table source), les résultats étant envoyés vers une nouvelle table (la table cible). Afin d’éviter de persister les données deux fois (dans les tables source et cible), nous pouvons remplacer le moteur de la table source par un Null table engine, tout en conservant le schéma d’origine. Nos OTel collectors continueront à envoyer les données vers cette table. Par exemple, pour les logs, la table otel_logs devient :
Le moteur de table Null est une optimisation puissante ; considérez-le comme /dev/null. Cette table ne stocke aucune donnée, mais toutes les vues matérialisées attachées sont tout de même exécutées sur les lignes insérées avant que celles-ci ne soient ignorées. Considérez la requête suivante. Elle transforme nos lignes dans un format que nous souhaitons conserver, en extrayant toutes les colonnes de LogAttributes (nous supposons que cela a été défini par le collector à l’aide de l’opérateur json_parser), en définissant SeverityText et SeverityNumber (sur la base de quelques conditions simples et de la définition de ces colonnes). Dans ce cas, nous sélectionnons également uniquement les colonnes dont nous savons qu’elles seront renseignées, en ignorant des colonnes telles que TraceId, SpanId et TraceFlags.
Nous extrayons également la colonne Body ci-dessus — au cas où des attributs supplémentaires seraient ajoutés ultérieurement sans être extraits par notre SQL. Cette colonne devrait bien se compresser dans ClickHouse et sera rarement consultée, sans impact sur les performances des requêtes. Enfin, nous réduisons le Timestamp en DateTime (pour économiser de l’espace — voir « Optimisation des types ») via un cast.
Expressions conditionnellesNotez l’utilisation des expressions conditionnelles ci-dessus pour extraire SeverityText et SeverityNumber. Elles sont extrêmement utiles pour formuler des conditions complexes et vérifier si des valeurs sont définies dans des maps ; ici, nous supposons naïvement que toutes les clés existent dans LogAttributes. Nous recommandons aux utilisateurs de bien s’y familiariser : elles sont très utiles pour le parsing des logs, en complément des fonctions de gestion des valeurs NULL !
Nous avons besoin d’une table pour recevoir ces résultats. La table cible ci-dessous correspond à la requête ci-dessus :
Les types sélectionnés ici reposent sur les optimisations décrites dans “Optimisation des types”.
Notez à quel point nous avons profondément modifié notre schéma. En pratique, vous aurez probablement aussi des colonnes de traces à conserver, ainsi que la colonne ResourceAttributes (qui contient généralement des métadonnées Kubernetes). Grafana peut exploiter les colonnes de traces pour créer des liens entre les logs et les traces - voir “Utiliser Grafana”.
Ci-dessous, nous créons une vue matérialisée otel_logs_mv, qui exécute l’instruction SELECT ci-dessus pour la table otel_logs et envoie les résultats vers otel_logs_v2.
Ce qui précède est illustré ci-dessous : Si nous redémarrons maintenant la config du collector utilisée dans “Exporter vers ClickHouse”, les données apparaîtront dans otel_logs_v2 au format souhaité. Notez l’utilisation de fonctions typées d’extraction JSON.
Une vue matérialisée équivalente, reposant sur l’extraction de colonnes depuis la colonne Body à l’aide de fonctions JSON, est présentée ci-dessous :

​
Attention aux types

Les vues matérialisées ci-dessus reposent sur un transtypage implicite, en particulier lors de l’utilisation de la map LogAttributes. ClickHouse convertit souvent de manière transparente la valeur extraite vers le type de la table cible, ce qui réduit la quantité de syntaxe nécessaire. Nous recommandons toutefois aux utilisateurs de toujours tester leurs vues en utilisant l’instruction SELECT de la vue avec une instruction INSERT INTO vers une table cible utilisant le même schéma. Cela devrait permettre de confirmer que les types sont correctement gérés. Portez une attention particulière aux cas suivants :
  • Si une clé n’existe pas dans une map, une chaîne vide sera renvoyée. Pour les valeurs numériques, vous devrez la convertir en une valeur appropriée. Cela peut être fait avec des fonctions conditionnelles, par ex. if(LogAttributes['status'] = ", 200, LogAttributes['status']), ou avec des fonctions de transtypage si des valeurs par défaut sont acceptables, par ex. toUInt8OrDefault(LogAttributes['status'] )
  • Certains types ne sont pas toujours convertis ; par ex., les représentations textuelles de valeurs numériques ne seront pas converties en valeurs d’enum.
  • Les fonctions d’extraction JSON renvoient les valeurs par défaut de leur type si aucune valeur n’est trouvée. Assurez-vous que ces valeurs sont pertinentes !
Évitez NullableÉvitez d’utiliser Nullable dans ClickHouse pour les données d’observabilité. Il est rarement nécessaire, pour les logs et les traces, de distinguer une valeur vide d’une valeur NULL. Cette fonctionnalité entraîne un surcoût de stockage et aura un impact négatif sur les performances des requêtes. Voir ici pour plus de détails.

​
Choisir une clé primaire (de tri)

Une fois les colonnes souhaitées extraites, vous pouvez commencer à optimiser votre clé de tri/primaire. Quelques règles simples peuvent vous aider à choisir une clé de tri. Les points suivants peuvent parfois se contredire ; examinez-les donc dans cet ordre. Ce processus vous permettra d’identifier plusieurs clés possibles, 4 à 5 étant généralement suffisantes :
  1. Sélectionnez des colonnes qui correspondent à vos filtres les plus courants et à vos habitudes d’accès. Si vous commencez généralement vos investigations d’observabilité en filtrant sur une colonne donnée, par exemple le nom du pod, cette colonne sera fréquemment utilisée dans les clauses WHERE. Priorisez son inclusion dans votre clé plutôt que celle de colonnes moins souvent utilisées.
  2. Privilégiez les colonnes qui permettent d’exclure une grande part du nombre total de lignes lorsqu’un filtre est appliqué, afin de réduire la quantité de données à lire. Les noms de service et les codes d’état sont souvent de bons candidats — dans le second cas, uniquement si vous filtrez sur des valeurs qui excluent la majorité des lignes. Par exemple, filtrer sur des codes 200 correspondra, dans la plupart des systèmes, à la majorité des lignes, contrairement aux erreurs 500, qui ne représenteront qu’un petit sous-ensemble.
  3. Privilégiez les colonnes susceptibles d’être fortement corrélées à d’autres colonnes de la table. Cela permet aussi de garantir que ces valeurs soient stockées de façon contiguë, ce qui améliore la compression.
  4. Les opérations GROUP BY et ORDER BY sur les colonnes de la clé de tri peuvent être plus économes en mémoire.

Une fois le sous-ensemble de colonnes de la clé de tri identifié, celles-ci doivent être déclarées dans un ordre précis. Cet ordre peut fortement influencer à la fois l’efficacité du filtrage sur les colonnes de clé secondaires dans les requêtes et le taux de compression des fichiers de données de la table. En règle générale, il est préférable d’ordonner les clés par cardinalité croissante. Il faut toutefois tenir compte du fait que le filtrage sur les colonnes situées plus loin dans la clé de tri sera moins efficace que sur celles placées plus tôt dans le tuple. Trouvez le bon équilibre entre ces comportements et tenez compte de vos habitudes d’accès. Surtout, testez différentes variantes. Pour mieux comprendre les clés de tri et savoir comment les optimiser, nous recommandons cet article.
La structure avant toutNous vous recommandons de définir vos clés de tri une fois vos logs structurés. N’utilisez ni les clés des maps d’attributs ni des expressions d’extraction JSON pour la clé de tri. Assurez-vous que vos clés de tri figurent comme colonnes de premier niveau dans votre table.

​
Utiliser les maps

Les exemples précédents montrent comment utiliser la syntaxe de map map['key'] pour accéder aux valeurs des colonnes Map(String, String). Outre l’utilisation de la notation map pour accéder aux clés imbriquées, ClickHouse propose des fonctions map spécialisées pour filtrer ou sélectionner ces colonnes. Par exemple, la requête suivante permet d’identifier toutes les clés uniques présentes dans la colonne LogAttributes à l’aide de la fonction mapKeys, suivie de la fonction groupArrayDistinctArray (un combinateur).
Évitez les pointsNous ne recommandons pas d’utiliser des points dans les noms de colonnes Map et pourrions abandonner cette possibilité à l’avenir. Utilisez plutôt un _.

​
Utilisation des alias

Interroger des types Map est plus lent qu’interroger des colonnes normales - voir “Accélérer les requêtes”. De plus, la syntaxe est plus complexe, ce qui peut rendre leur écriture fastidieuse. Pour remédier à ce problème, nous recommandons d’utiliser des colonnes Alias. Les colonnes ALIAS sont calculées au moment de la requête et ne sont pas stockées dans la table. Il est donc impossible d’INSERT une valeur dans une colonne de ce type. Les alias permettent de faire référence à des clés de Map, de simplifier la syntaxe et d’exposer de manière transparente les entrées d’une Map comme des colonnes normales. Prenons l’exemple suivant :
Nous avons plusieurs colonnes matérialisées et une colonne ALIAS, RemoteAddr, qui accède à la map LogAttributes. Nous pouvons désormais interroger via cette colonne les valeurs de LogAttributes['remote_addr'], ce qui simplifie notre requête, c.-à-d.
De plus, l’ajout de ALIAS via la commande ALTER TABLE est trivial. Ces colonnes sont immédiatement disponibles, par exemple
Les alias sont exclus par défautPar défaut, SELECT * exclut les colonnes ALIAS. Ce comportement peut être désactivé en définissant asterisk_include_alias_columns=1.

​
Optimisation des types

Les bonnes pratiques générales de ClickHouse pour l’optimisation des types s’appliquent également à ce cas d’usage ClickHouse.

​
Utilisation des codecs

En plus des optimisations de type, vous pouvez suivre les bonnes pratiques générales relatives aux codecs lorsque vous cherchez à optimiser la compression pour les schémas d’observabilité de ClickHouse. De manière générale, le codec ZSTD est particulièrement bien adapté aux jeux de données de logs et de traces. Augmenter le niveau de compression par rapport à sa valeur par défaut de 1 peut améliorer la compression. Cela doit toutefois être testé, car des valeurs plus élevées entraînent une surcharge CPU plus importante au moment de l’insert. En pratique, nous constatons généralement peu de gain à augmenter cette valeur. En outre, les timestamps, bien qu’ils bénéficient de l’encodage delta en termes de compression, peuvent dégrader les performances des query si cette colonne est utilisée dans la clé primaire/de tri. Nous recommandons aux utilisateurs d’évaluer le compromis entre compression et performances des query.

​
Utilisation des dictionnaires

Les dictionnaires sont une fonctionnalité clé de ClickHouse : ils fournissent une représentation clé-valeur en mémoire de données provenant de diverses sources, optimisée pour des requêtes de recherche à très faible latence. Cela s’avère utile dans divers scénarios, qu’il s’agisse d’enrichir à la volée les données ingérées sans ralentir le processus d’ingestion, ou d’améliorer les performances des requêtes de manière générale, en particulier pour les jointures. Même si les jointures sont rarement nécessaires dans les cas d’usage d’observabilité, les dictionnaires restent utiles pour l’enrichissement, aussi bien à l’insertion qu’au moment de la requête. Nous donnons ci-dessous des exemples des deux approches.
Accélérer les jointuresLes utilisateurs qui souhaitent accélérer les jointures avec des dictionnaires trouveront plus de détails ici.

​
À l’insertion vs au moment de la requête

Les dictionnaires peuvent être utilisés pour enrichir des jeux de données au moment de la requête ou à l’insertion. Chacune de ces approches présente ses avantages et ses inconvénients. En résumé :
  • À l’insertion - Cette approche est généralement adaptée si la valeur d’enrichissement ne change pas et provient d’une source externe pouvant servir à alimenter le dictionnaire. Dans ce cas, enrichir la ligne à l’insertion évite une recherche dans le dictionnaire au moment de la requête. Cela a toutefois un coût en termes de performances d’insertion, ainsi qu’un surcoût de stockage, car les valeurs enrichies seront stockées dans des colonnes.
  • Au moment de la requête - Si les valeurs d’un dictionnaire changent fréquemment, les recherches au moment de la requête sont souvent plus adaptées. Cela évite d’avoir à mettre à jour les colonnes (et à réécrire les données) si les valeurs associées changent. Cette souplesse se paie toutefois par le coût d’une recherche au moment de la requête. Ce coût est généralement sensible lorsqu’une recherche est nécessaire pour un grand nombre de lignes, par exemple lorsqu’une recherche dans le dictionnaire est utilisée dans une clause de filtre. Pour l’enrichissement des résultats, c’est-à-dire dans le SELECT, ce surcoût est généralement négligeable.
Nous recommandons aux utilisateurs de se familiariser avec les bases des dictionnaires. Les dictionnaires fournissent une table de correspondance en mémoire à partir de laquelle des valeurs peuvent être récupérées à l’aide de fonctions spécialisées. Pour des exemples simples d’enrichissement, consultez le guide sur les dictionnaires ici. Ci-dessous, nous nous concentrons sur des tâches courantes d’enrichissement pour l’observabilité.

​
Utilisation des dictionnaires IP

L’enrichissement géographique des logs et des traces avec des valeurs de latitude et de longitude à partir d’adresses IP est un besoin courant en observabilité. Nous pouvons y parvenir à l’aide d’un dictionnaire structuré ip_trie. Nous utilisons le jeu de données DB-IP à l’échelle des villes, mis à disposition publiquement par DB-IP.com dans le cadre de la licence CC BY 4.0. D’après le README, les données sont structurées comme suit :
Avec cette structure, commençons par jeter un coup d’œil aux données à l’aide de la fonction de table url() :
Pour nous simplifier la vie, utilisons le moteur de table URL() pour créer une table ClickHouse avec nos noms de champ et vérifier le nombre total de lignes :
Comme notre dictionnaire ip_trie exige que les plages d’adresses IP soient exprimées au format CIDR, nous devons transformer ip_range_start et ip_range_end. Le CIDR correspondant à chaque plage peut être calculé simplement avec la requête suivante :
Cette requête fait beaucoup de choses. Pour celles et ceux que cela intéresse, lisez cette excellente explication. Sinon, retenez simplement qu’elle calcule un CIDR pour une plage d’adresses IP.
Dans notre cas, nous n’aurons besoin que de la plage d’adresses IP, du code pays et des coordonnées. Créons donc une nouvelle table et insérons nos données Geo IP :
Afin d’effectuer des recherches IP à faible latence dans ClickHouse, nous exploiterons des dictionnaires pour stocker en mémoire la correspondance clé -> attributs de nos données Geo IP. ClickHouse fournit une structure de dictionnaire ip_trie pour associer nos préfixes réseau (blocs CIDR) à des coordonnées et des codes pays. La requête suivante définit un dictionnaire utilisant ce layout et la table ci-dessus comme source.
Nous pouvons sélectionner des lignes dans le dictionnaire et confirmer que ce jeu de données est disponible pour les opérations de recherche :
Actualisation périodiqueLes dictionnaires ClickHouse sont actualisés périodiquement en fonction des données de la table sous-jacente et de la clause lifetime utilisée ci-dessus. Pour mettre à jour notre dictionnaire Geo IP afin qu’il reflète les dernières modifications du jeu de données DB-IP, il suffit de réinsérer les données de la table distante geoip_url dans notre table geoip, avec les transformations appliquées.
Maintenant que nous avons chargé les données Geo IP dans notre dictionnaire ip_trie (qui, commodément, porte lui aussi le nom ip_trie), nous pouvons l’utiliser pour la géolocalisation d’adresses IP. Pour cela, utilisez la fonction dictGet() comme suit :
Remarquez ici la rapidité de récupération. Cela nous permet d’enrichir les logs. Dans ce cas, nous choisissons d’effectuer un enrichissement au moment de la requête. En revenant à notre jeu de données de logs d’origine, nous pouvons utiliser ce qui précède pour agréger nos logs par pays. Ce qui suit suppose que nous utilisons le schéma issu de notre vue matérialisée précédente, qui inclut une colonne RemoteAddress extraite.
Comme la correspondance entre une adresse IP et un lieu géographique peut changer, les utilisateurs voudront probablement savoir d’où provenait la requête au moment où elle a été effectuée, et non quelle est la localisation géographique actuelle de cette même adresse. C’est pourquoi un enrichissement au moment de l’indexation est probablement préférable ici. Cela peut se faire à l’aide de colonnes matérialisées, comme indiqué ci-dessous, ou dans le select d’une vue matérialisée :
Mise à jour périodiqueLes utilisateurs souhaiteront probablement que le dictionnaire d’enrichissement d’IP soit mis à jour périodiquement à partir des nouvelles données. Cela peut se faire à l’aide de la clause LIFETIME du dictionnaire, qui entraînera son rechargement périodique depuis la table sous-jacente. Pour mettre à jour la table sous-jacente, voir “Vues matérialisées actualisables”.
Les pays et coordonnées ci-dessus offrent des possibilités de visualisation allant au-delà du regroupement et du filtrage par pays. Pour vous en inspirer, voir “Visualisation des données géographiques”.

​
Utilisation des dictionnaires Regex (analyse des user-agents)

L’analyse des chaînes user-agent est un cas classique d’utilisation des expressions régulières, ainsi qu’un besoin fréquent dans les jeux de données basés sur les logs et les traces. ClickHouse permet d’analyser efficacement les user-agents à l’aide de dictionnaires arborescents d’expressions régulières. Les dictionnaires arborescents d’expressions régulières sont définis dans ClickHouse open-source à l’aide du type de source de dictionnaire YAMLRegExpTree, qui indique le path vers un fichier YAML contenant l’arbre d’expressions régulières. Si vous souhaitez fournir votre propre dictionnaire d’expressions régulières, vous trouverez ici les détails sur la structure requise. Ci-dessous, nous nous concentrons sur l’analyse des user-agents à l’aide de uap-core et chargeons notre dictionnaire pour le format CSV pris en charge. Cette approche est compatible avec OSS et ClickHouse Cloud.
Dans les exemples ci-dessous, nous utilisons des snapshots des expressions régulières uap-core les plus récentes pour l’analyse des user-agents, datant de juin 2024. Le fichier le plus récent, mis à jour occasionnellement, est disponible ici. Vous pouvez suivre les étapes ici pour charger le fichier CSV utilisé ci-dessous.
Créez les tables Memory suivantes. Elles contiennent nos expressions régulières pour analyser les appareils, les navigateurs et les systèmes d’exploitation.
Ces tables peuvent être alimentées à partir des fichiers CSV suivants, hébergés publiquement, à l’aide de la fonction de table url :
Une fois nos tables en mémoire remplies, nous pouvons charger nos dictionnaires d’expressions régulières. Notez que nous devons définir les valeurs clés comme colonnes : ce seront les attributs que nous pourrons extraire du user agent.
Avec ces dictionnaires chargés, nous pouvons utiliser un user-agent d’exemple et tester nos nouvelles fonctionnalités d’extraction à partir de dictionnaires :
Comme les règles de traitement des user agents changent rarement et que le dictionnaire n’a besoin d’être mis à jour qu’avec l’arrivée de nouveaux navigateurs, systèmes d’exploitation et appareils, il est judicieux d’effectuer cette extraction au moment de l’insertion. Nous pouvons effectuer ce travail soit à l’aide d’une colonne matérialisée, soit à l’aide d’une vue matérialisée. Ci-dessous, nous modifions la vue matérialisée utilisée précédemment :
Cela nous oblige à modifier le schéma de la table cible otel_logs_v2 :
Après avoir redémarré le collector et ingéré des logs structurés en suivant les étapes documentées précédemment, nous pouvons interroger les colonnes Device, Browser et Os que nous venons d’extraire.
Tuples pour les structures complexesNotez l’utilisation de Tuples pour ces colonnes de user agent. Les Tuples sont recommandés pour les structures complexes dont la hiérarchie est connue à l’avance. Les sous-colonnes offrent les mêmes performances que les colonnes classiques (contrairement aux clés de Map) tout en permettant des types hétérogènes.

​
Pour aller plus loin

Pour plus d’exemples et d’informations sur les dictionnaires, nous recommandons les articles suivants :

​
Accélérer les requêtes

ClickHouse prend en charge plusieurs techniques pour améliorer les performances des requêtes. Les éléments suivants ne doivent être envisagés qu’après avoir choisi une clé primaire/de tri adaptée afin d’optimiser les modes d’accès les plus courants et de maximiser la compression. C’est généralement ce qui a le plus d’impact sur les performances pour le moins d’efforts.

​
Utilisation des vues matérialisées (incrémental) pour les agrégations

Dans les sections précédentes, nous avons vu comment utiliser les vues matérialisées pour transformer et filtrer les données. Elles peuvent toutefois aussi servir à précalculer des agrégations au moment de l’insertion et à en stocker le résultat. Ce résultat peut ensuite être mis à jour avec celui des insertions suivantes, ce qui permet en pratique de précalculer une agrégation à l’insertion. L’idée principale ici est que les résultats constituent souvent une représentation plus compacte des données d’origine (une esquisse partielle dans le cas des agrégations). Associés à une requête plus simple pour lire les résultats depuis la table cible, les temps de requête seront plus courts que si le même calcul était effectué sur les données d’origine. Considérez la requête suivante, dans laquelle nous calculons le trafic total par heure à l’aide de nos logs structurés :
On peut imaginer qu’il s’agit d’un graphique en courbes courant que les utilisateurs créent dans Grafana. Cette requête est certes très rapide — l’ensemble de données ne contient que 10 millions de lignes, et ClickHouse est rapide ! Cependant, si l’on passe à des milliards, voire des milliers de milliards de lignes, l’idéal serait de conserver ce niveau de performance pour cette requête.
Cette requête serait 10x plus rapide si nous utilisions la table otel_logs_v2, issue de notre vue matérialisée précédente, qui extrait la clé size de la map LogAttributes. Nous utilisons ici les données brutes uniquement à des fins d’illustration et recommandons d’utiliser la vue précédente s’il s’agit d’une requête courante.
Nous avons besoin d’une table pour recevoir les résultats si nous voulons effectuer ce calcul à l’insertion à l’aide d’une vue matérialisée. Cette table ne doit conserver qu’une seule ligne par heure. Si une mise à jour est reçue pour une heure existante, les autres colonnes doivent être fusionnées avec la ligne correspondant à cette heure. Pour que cette fusion d’états incrémentaux ait lieu, des états partiels doivent être stockés pour les autres colonnes. Cela nécessite un type de moteur spécial dans ClickHouse : le SummingMergeTree. Celui-ci remplace toutes les lignes ayant la même clé de tri par une seule ligne contenant les valeurs additionnées des colonnes numériques. La table suivante fusionnera toutes les lignes ayant la même date, en additionnant toutes les colonnes numériques.
Pour illustrer notre vue matérialisée, supposons que notre table bytes_per_hour soit vide et n’ait encore reçu aucune donnée. Notre vue matérialisée exécute le SELECT ci-dessus sur les données insérées dans otel_logs (cette opération s’effectue sur des blocs d’une taille configurée), puis envoie les résultats vers bytes_per_hour. La syntaxe est présentée ci-dessous :
La clause TO est essentielle ici, car elle indique où les résultats seront envoyés, c’est-à-dire dans bytes_per_hour. Si nous redémarrons notre OTel Collector et renvoyons les logs, la table bytes_per_hour sera alimentée de manière incrémentale avec le résultat de la requête ci-dessus. Une fois l’opération terminée, nous pouvons vérifier la taille de bytes_per_hour : nous devrions avoir 1 ligne par heure :
Nous avons ici réduit efficacement le nombre de lignes de 10m (dans otel_logs) à 113 en stockant le résultat de notre requête. L’idée clé est que, si de nouveaux logs sont insérés dans la table otel_logs, de nouvelles valeurs seront envoyées à bytes_per_hour pour l’heure correspondante, où elles seront automatiquement fusionnées de manière asynchrone en arrière-plan. En ne conservant qu’une seule ligne par heure, bytes_per_hour restera donc toujours à la fois compacte et à jour. Comme la fusion des lignes est asynchrone, il peut y avoir plus d’une ligne par heure au moment où un utilisateur exécute une requête. Pour garantir que toutes les lignes en attente soient fusionnées au moment de la requête, nous avons deux options :
  • Utiliser le modificateur FINAL sur le nom de la table (ce que nous avons fait pour la requête de comptage ci-dessus).
  • Agréger selon la clé d’ordre utilisée dans notre table finale, c.-à-d. Timestamp, et sommer les métriques.
En général, la deuxième option est plus efficace et plus flexible (la table peut être utilisée à d’autres fins), mais la première peut être plus simple pour certaines requêtes. Nous montrons les deux ci-dessous :
Cela a permis d’accélérer notre requête de 0,6 s à 0,008 s, soit plus de 75 fois plus rapide !
Ces gains peuvent être encore plus importants sur des jeux de données plus volumineux et avec des requêtes plus complexes. Voir ici pour des exemples.

​
Un exemple plus complexe

L’exemple ci-dessus agrège un simple comptage par heure à l’aide de SummingMergeTree. Pour des statistiques plus élaborées que de simples sommes, il faut utiliser un autre moteur de table cible : AggregatingMergeTree. Supposons que nous voulions calculer le nombre d’adresses IP uniques (ou d’utilisateurs uniques) par jour. Voici la requête correspondante :
Pour conserver un décompte de cardinalité afin de permettre une mise à jour incrémentielle, AggregatingMergeTree est nécessaire.
Pour que ClickHouse sache que des états d’agrégation seront stockés, nous définissons la colonne UniqueUsers avec le type AggregateFunction, en précisant la fonction à l’origine des états partiels (uniq) ainsi que le type de la colonne source (IPv4). Comme avec le SummingMergeTree, les lignes ayant la même valeur de clé ORDER BY seront fusionnées (Hour dans l’exemple ci-dessus). La vue matérialisée associée utilise la requête précédente :
Remarquez que nous ajoutons le suffixe State à la fin de nos fonctions d’agrégation. Cela garantit que l’état d’agrégation de la fonction est renvoyé à la place du résultat final. Cet état contiendra des informations supplémentaires lui permettant de fusionner avec d’autres états. Une fois les données rechargées, à la suite d’un redémarrage du collector, nous pouvons confirmer que 113 lignes sont présentes dans la table unique_visitors_per_hour.
Notre requête finale doit faire appel au suffixe Merge pour nos fonctions (car les colonnes stockent des états d’agrégation partiels) :
Notez que nous utilisons ici GROUP BY plutôt que FINAL.

​
Utilisation des vues matérialisées (incrémentales) pour des recherches rapides

Vous devez tenir compte des modes d’accès lorsque vous choisissez la clé de tri ClickHouse, en y incluant les colonnes fréquemment utilisées dans les clauses de filtre et d’agrégation. Cela peut être contraignant dans les cas d’usage d’observabilité, où les utilisateurs ont des modes d’accès plus variés qui ne peuvent pas être ramenés à un seul ensemble de colonnes. Cet aspect est particulièrement bien illustré par un exemple intégré aux schémas OTel par défaut. Considérez le schéma par défaut pour les traces :
Ce schéma est optimisé pour le filtrage sur ServiceName, SpanName et Timestamp. En tracing, les utilisateurs doivent aussi pouvoir effectuer des recherches à partir d’un TraceId spécifique et récupérer les spans associés à la trace correspondante. Bien que cet élément figure dans la clé de tri, sa position en fin de clé signifie que le filtrage ne sera pas aussi efficace et qu’il faudra probablement analyser des volumes importants de données pour récupérer une seule trace. L’OTel collector installe également une vue matérialisée et la table associée pour répondre à ce problème. La table et la vue sont présentées ci-dessous :
La vue garantit en pratique que la table otel_traces_trace_id_ts contient l’horodatage minimal et maximal de la trace. Cette table, triée par TraceId, permet de récupérer efficacement ces horodatages. Ces plages d’horodatage peuvent ensuite être utilisées pour interroger la table principale otel_traces. Plus précisément, lorsque Grafana récupère une trace à partir de son identifiant, il utilise la requête suivante :
La CTE identifie ici les horodatages minimal et maximal de l’ID de trace ae9226c78d1d360601e6383928e4d22d, avant de les utiliser pour filtrer la table principale otel_traces afin de récupérer les spans associés. Cette même approche peut être appliquée à des schémas d’accès similaires. Nous présentons un exemple semblable dans la section Modélisation des données ici.

​
Utilisation des projections

Les projections ClickHouse vous permettent de définir plusieurs clauses ORDER BY pour une table. Dans les sections précédentes, nous avons vu comment les vues matérialisées peuvent être utilisées dans ClickHouse pour précalculer des agrégations, transformer des lignes et optimiser les requêtes d’observability selon différents schémas d’accès. Nous avons présenté un exemple dans lequel la vue matérialisée envoie des lignes vers une table cible avec une clé de tri différente de celle de la table d’origine qui reçoit les insertions, afin d’optimiser les recherches par ID de trace. Les projections peuvent être utilisées pour résoudre le même problème, en permettant à l’utilisateur d’optimiser les requêtes sur une colonne qui ne fait pas partie de la clé primaire. En théorie, cette possibilité peut être utilisée pour fournir plusieurs clés de tri pour une table, avec un inconvénient majeur : la duplication des données. Plus précisément, les données devront être écrites dans l’ordre de la clé primaire principale, en plus de l’ordre spécifié pour chaque projection. Cela ralentira les insertions et consommera davantage d’espace disque.
Projections vs vues matérialiséesLes projections offrent bon nombre des mêmes possibilités que les vues matérialisées, mais doivent être utilisées avec parcimonie, ces dernières étant souvent à privilégier. Vous devez en comprendre les inconvénients et savoir dans quels cas elles sont appropriées. Par exemple, même si les projections peuvent être utilisées pour précalculer des agrégations, nous recommandons d’utiliser des vues matérialisées pour cela.
Considérez la requête suivante, qui filtre notre table otel_logs_v2 sur les codes d’erreur 500. Il s’agit probablement d’un schéma d’accès courant pour la journalisation, les utilisateurs souhaitant filtrer par codes d’erreur :
Utilisez Null pour mesurer les performancesNous n’affichons pas les résultats ici en utilisant FORMAT Null. Cela force la lecture de tous les résultats sans les renvoyer, ce qui évite qu’une requête se termine prématurément à cause d’une LIMIT. Cela sert uniquement à montrer le temps nécessaire pour parcourir l’ensemble des 10m lignes.
La requête ci-dessus nécessite un balayage linéaire avec la clé de tri choisie (ServiceName, Timestamp). Bien que nous puissions ajouter Status à la fin de la clé de tri, ce qui améliorerait les performances de la requête ci-dessus, nous pouvons aussi ajouter une projection.
Notez qu’il faut d’abord créer la projection, puis la matérialiser. Cette dernière commande entraîne le stockage des données deux fois sur le disque, selon deux ordres différents. La projection peut également être définie lors de la création des données, comme indiqué ci-dessous, et sera ensuite maintenue automatiquement à mesure que des données sont insérées.
Il est important de noter que, si la projection est créée via un ALTER, sa création est asynchrone lorsque la commande MATERIALIZE PROJECTION est exécutée. Vous pouvez suivre l’avancement de cette opération avec la query suivante, en attendant que is_done=1.
Si nous répétons la requête ci-dessus, nous pouvons constater que les performances se sont considérablement améliorées, au prix d’un espace de stockage supplémentaire (voir “Mesurer la taille de la table et la compression” pour savoir comment procéder à cette mesure).
Dans l’exemple ci-dessus, nous indiquons dans la projection les colonnes utilisées dans la requête précédente. Cela signifie que seules les colonnes spécifiées seront stockées sur disque dans le cadre de la projection, triées par Status. Si, à l’inverse, nous utilisions SELECT * ici, toutes les colonnes seraient stockées. Bien que cela permette à davantage de requêtes (utilisant n’importe quel sous-ensemble de colonnes) de tirer parti de la projection, cela entraînerait un stockage supplémentaire. Pour mesurer l’espace disque et la compression, consultez “Mesurer la taille et la compression d’une table”.

​
Index secondaires / data skipping indices

Aussi bien réglée que soit la clé primaire dans ClickHouse, certaines requêtes nécessiteront inévitablement un parcours complet de la table. Même si cela peut être atténué à l’aide de vues matérialisées (et, pour certaines requêtes, de projections), celles-ci demandent une maintenance supplémentaire et supposent que les utilisateurs sachent qu’elles existent pour pouvoir en tirer parti. Là où les bases de données relationnelles traditionnelles résolvent ce problème avec des index secondaires, ceux-ci sont inefficaces dans des bases de données orientées colonnes comme ClickHouse. À la place, ClickHouse utilise des index « Skip », qui peuvent améliorer considérablement les performances des requêtes en permettant à la base de données d’ignorer de gros segments de données sans valeur correspondante. Les schémas OTel par défaut utilisent des index secondaires pour tenter d’accélérer l’accès aux maps. Bien que nous les jugions généralement peu efficaces et ne recommandions pas de les reprendre dans votre schéma personnalisé, les skipping indices peuvent malgré tout être utiles. Vous devriez lire et comprendre le guide des index secondaires avant d’essayer de les appliquer. En règle générale, ils sont efficaces lorsqu’il existe une forte corrélation entre la clé primaire et la colonne/expression non primaire ciblée, et que les utilisateurs recherchent des valeurs rares, c’est-à-dire des valeurs qui n’apparaissent pas dans beaucoup de granules.

​
Index de texte pour la recherche en texte intégral

ClickHouse fournit un index de texte spécialisé pour la recherche en texte intégral. Cet index crée un index inversé sur des données textuelles tokenisées, ce qui permet d’exécuter rapidement des requêtes de recherche basées sur des tokens. Les index de texte sont disponibles à partir de la version 26.2 de ClickHouse. Ils peuvent être définis sur les types de colonnes suivants dans les tables MergeTree : String, FixedString, Array(String), Array(FixedString) et Map (via les fonctions map mapKeys et mapValues). Un index de texte nécessite un argument tokenizer dans sa définition. Il est également possible de spécifier une fonction de prétraitement afin de transformer la chaîne d’entrée avant la tokenisation. Les fonctions recommandées pour rechercher dans l’index sont : hasAnyTokens et hasAllTokens. Certaines fonctions traditionnelles de recherche dans les chaînes sont également automatiquement optimisées lorsqu’un index de texte est présent. Consultez la documentation pour plus de détails ainsi que la liste des fonctions prises en charge ici et ici. Dans les exemples ci-dessous, nous utilisons un jeu de données de logs structurés.
Nous pouvons également utiliser hasAnyTokens sans index de texte, mais la requête devra effectuer un parcours complet, donc lent, de la colonne Body :

​
Ajout d’un index de texte

Un index de texte sur la colonne Body peut être ajouté lors de la création de la table :
ou ajoutée ultérieurement à l’aide de ALTER TABLE :
Si nous exécutons à nouveau la même requête SELECT, elle utilisera l’index de texte pour effectuer la recherche. Le volume de données lues passe de gigaoctets à mégaoctets et les performances s’améliorent d’environ 45 fois.

​
Utiliser un préprocesseur

Dans ce jeu de données, la colonne Body contient une chaîne au format JSON avec plusieurs paires clé-valeur (par ex. msg, id, ctx, attr, etc.). Supposons que nous souhaitions rechercher uniquement dans le champ msg. Au lieu d’indexer l’ensemble de la chaîne JSON, nous pouvons définir un préprocesseur afin d’extraire uniquement la valeur msg avant la tokenisation. Par exemple :
Dans cet exemple, le préprocesseur :
  • réduit la quantité de texte tokenisé et indexé,
  • diminue la taille de l’index,
  • réduit la probabilité de faux positifs,
  • et améliore les performances des requêtes.
Par rapport à l’index non prétraité, les performances sont multipliées par environ 2. L’utilisation d’un préprocesseur réduit également la taille de l’index, qui passe de plusieurs gigaoctets à quelques centaines de kilo-octets, soit environ 0,01 % de sa taille d’origine
**Autres index pour la recherche de texte Vous trouverez plus de détails sur les index secondaires de saut ici.

​
Extraction depuis des maps

Le type Map est très répandu dans les schémas OTel. Ce type exige que les valeurs et les clés aient le même type, ce qui est suffisant pour des métadonnées telles que les labels Kubernetes. Gardez à l’esprit que, lorsque vous interrogez une sous-clé d’un type Map, l’intégralité de la colonne parente est chargée. Si la map contient de nombreuses clés, cela peut entraîner un surcoût important pour la requête, car il faut lire davantage de données sur le disque que si la clé existait en tant que colonne. Si vous interrogez fréquemment une clé spécifique, envisagez de la déplacer dans sa propre colonne dédiée au niveau racine. Il s’agit généralement d’une tâche effectuée en réponse à des schémas d’accès courants après le déploiement, et qui peut être difficile à anticiper avant la mise en production. Voir “Gérer les modifications de schéma” pour savoir comment modifier votre schéma après le déploiement.

​
Mesurer la taille d’une table et la compression

L’une des principales raisons pour lesquelles ClickHouse est utilisé pour l’Observability est la compression. En plus de réduire considérablement les coûts de stockage, moins de données sur disque signifie moins d’I/O, ainsi que des requêtes et des insertions plus rapides. La réduction des I/O compense largement la charge supplémentaire liée à tout algorithme de compression du point de vue du CPU. Améliorer la compression des données doit donc être la première priorité pour garantir la rapidité des requêtes ClickHouse. Vous trouverez ici plus de détails sur la mesure de la compression.
Dernière modification le 23 juillet 2026