BD ClickHouse pour les gens, ou Technologies extraterrestres

Alexey Lizunov, responsable du centre de compétences pour les canaux de service à distance au sein de la direction des technologies de l'information de MKB

En alternative à la pile ELK (ElasticSearch, Logstash, Kibana), nous menons des recherches sur l'utilisation de la base de données ClickHouse comme stockage de données pour les logs.

Dans cet article, nous aimerions partager notre expérience de l'utilisation de la base de données ClickHouse ainsi que les résultats préliminaires de notre phase pilote. Il convient de noter immédiatement que les résultats sont impressionnants.

Ensuite, nous décrirons plus en détail comment notre système est configuré et de quels composants il se compose. Mais pour l'instant, nous aimerions parler un peu de cette base de données dans son ensemble et pourquoi elle mérite d'être remarquée. La base de données ClickHouse est une base de données analytique en colonnes à haute performance développée par Yandex. Elle est utilisée dans les services de Yandex, étant à l'origine le principal stockage de données pour Yandex.Metrica. C'est un système open-source, gratuit. En tant que développeur, j'ai toujours été curieux de savoir comment cela était réalisé, car ils traitent des volumes de données fantastiquement grands. L'interface utilisateur de Metrica est très flexible et fonctionne rapidement. Lors de mes premières interactions avec cette base de données, l'impression était : « Enfin ! C'est fait « pour les gens » ! Depuis le processus d'installation jusqu'à l'envoi de requêtes.

Cette base de données a un seuil d'entrée très bas. Même un développeur de niveau moyen peut installer cette base de données et commencer à l'utiliser en quelques minutes. Tout fonctionne parfaitement. Même les personnes qui ne connaissent pas bien Linux peuvent s'en sortir rapidement avec l'installation et effectuer des opérations simples. Auparavant, lorsqu'on parlait de Big Data, Hadoop, Google BigTable, HDFS, un développeur ordinaire pouvait imaginer que cela concernait des téraoctets, des pétaoctets, et que les réglages et le développement de ces systèmes étaient réservés à des personnes aux compétences extraordinaires. Avec l'apparition de la base de données ClickHouse, nous avons obtenu un outil simple et compréhensible permettant de résoudre des tâches auparavant inaccessibles. Il suffit d'une machine assez ordinaire et de cinq minutes pour l'installer. En d'autres termes, nous avons une base de données comme MySql, mais seulement pour stocker des milliards d'enregistrements ! Une sorte de superarchive avec un langage SQL. C'est comme si l'on avait remis aux gens des armes d'extraterrestres.

À propos de notre système de collecte de logs

Pour collecter des informations, nous utilisons des fichiers journaux IIS d'applications web au format standard (nous travaillons également actuellement sur le parsing des journaux applicatifs, mais notre principal objectif lors de la phase pilote est la collecte des journaux IIS).

Nous n'avons pas pu nous passer complètement de la pile ELK pour diverses raisons, et nous continuons à utiliser les composants LogStash et Filebeat, qui ont bien fait leurs preuves et fonctionnent de manière fiable et prévisible.

Le schéma général de journalisation est présenté dans l'illustration ci-dessous :

Une particularité de l'insertion de données dans la base de données ClickHouse est l'insertion peu fréquente (une fois par seconde) de grands volumes de données. C'est, à en juger par les premières expériences de travail avec ClickHouse, la partie la plus 'problématique' à laquelle on est confronté : le schéma devient un peu plus complexe.
Ici, un plugin pour LogStash a beaucoup aidé, qui insère directement les données dans ClickHouse. Ce composant est déployé sur le même serveur que la base de données elle-même. Bien que ce ne soit généralement pas recommandé, d'un point de vue pratique, pour éviter de multiplier les serveurs, il est actuellement déployé sur le même serveur. Nous n'avons observé aucun plantage ni conflit de ressources avec la base de données. De plus, il est important de noter que le plugin dispose d'un mécanisme de réessai en cas d'erreurs. En cas d'erreurs, le plugin écrit sur le disque un lot de données qui n'a pas pu être inséré (le format du fichier est pratique : après correction, il est facile d'insérer le lot corrigé à l'aide de clickhouse-client).

La liste complète des logiciels utilisés dans le schéma est présentée dans le tableau :

Liste des logiciels utilisés

Nom

Description

Lien vers le distributeur

NGINX

Reverse-proxy pour limiter l'accès par ports et organiser l'authentification

Actuellement, non utilisé dans le schéma

https://nginx.org/ru/download.html

https://nginx.org/download/nginx-1.16.0.tar.gz

FileBeat

Transmission des fichiers journaux.

https://www.elastic.co/downloads/beats/filebeat (distributeur pour Windows 64 bits).

https://artifacts.elastic.co/downloads/beats/filebeat/filebeat-7.3.0-windows-x86_64.zip

LogStash

Collecteur de journaux.

Utilisé pour collecter des journaux de FileBeat, ainsi que pour collecter des journaux depuis la file RabbitMQ (pour les serveurs situés en DMZ).

https://www.elastic.co/products/logstash

https://artifacts.elastic.co/downloads/logstash/logstash-7.0.1.rpm

Logstash-output-clickhouse

Plugin Logstash pour transmettre des journaux dans la base de données ClickHouse par lots

https://github.com/mikechris/logstash-output-clickhouse

/usr/share/logstash/bin/logstash-plugin install logstash-output-clickhouse

/usr/share/logstash/bin/logstash-plugin install logstash-filter-prune

/usr/share/logstash/bin/logstash-plugin install logstash-filter-multiline

ClickHouse

Stockage des journaux https://clickhouse.yandex/docs/ru/

https://packagecloud.io/Altinity/clickhouse/packages/el/7/clickhouse-server-19.5.3.8-1.el7.x86_64.rpm

https://packagecloud.io/Altinity/clickhouse/packages/el/7/clickhouse-client-19.5.3.8-1.el7.x86_64.rpm

Remarque. Depuis août 2018, des 'builds normaux' rpm sont apparus dans le dépôt de Yandex pour RHEL, donc ils peuvent être essayés. Au moment de l'installation, nous avons utilisé des paquets construits par Altinity.

Grafana

Visualisation des journaux. Configuration des tableaux de bord

https://grafana.com/

https://grafana.com/grafana/download

Redhat & Centos (64 bits) - dernière version

Source de données ClickHouse pour Grafana 4.6+

Plugin pour Grafana avec source de données ClickHouse

https://grafana.com/plugins/vertamedia-clickhouse-datasource

https://grafana.com/api/plugins/vertamedia-clickhouse-datasource/versions/1.8.1/download

LogStash

Routage des journaux via FileBeat vers la file d'attente RabbitMQ.

Remarque. Malheureusement, FileBeat n'a pas de sortie directe vers RabbitMQ, un lien intermédiaire sous la forme de Logstash est donc requis.

https://www.elastic.co/products/logstash

https://artifacts.elastic.co/downloads/logstash/logstash-7.0.1.rpm

RabbitMQ

File d'attente de messages. C'est un tampon pour les enregistrements de journaux dans la DMZ.

https://www.rabbitmq.com/download.html

https://github.com/rabbitmq/rabbitmq-server/releases/download/v3.7.14/rabbitmq-server-3.7.14-1.el7.noarch.rpm

Erlang Runtime (Nécessaire pour RabbitMQ)

Environnement d'exécution Erlang. Nécessaire pour le fonctionnement de RabbitMQ.

http://www.erlang.org/download.html

https://www.rabbitmq.com/install-rpm.html#install-erlang http://www.erlang.org/downloads/21.3

La configuration du serveur avec la base de données ClickHouse est présentée dans le tableau suivant :

Nom

Valeur

Remarque

Configuration

HDD : 40 Go
RAM : 8 Go
Processeur : Core 2 2Ghz

Il est important de prêter attention aux conseils pour l'exploitation de la base de données ClickHouse (https://clickhouse.yandex/docs/ru/operations/tips/)

Logiciel système général

OS : Red Hat Enterprise Linux Server (Maipo)

JRE (Java 8)

Comme vous pouvez le voir, c'est une station de travail classique.

La structure de la table pour le stockage des journaux est la suivante :

log_web.sql

CREATE TABLE log_web (
  logdate Date,
  logdatetime DateTime CODEC(Delta, LZ4HC),
   
  fld_log_file_name LowCardinality( String ),
  fld_server_name LowCardinality( String ),
  fld_app_name LowCardinality( String ),
  fld_app_module LowCardinality( String ),
  fld_website_name LowCardinality( String ),
 
  serverIP LowCardinality( String ),
  method LowCardinality( String ),
  uriStem String,
  uriQuery String,
  port UInt32,
  username LowCardinality( String ),
  clientIP String,
  clientRealIP String,
  userAgent String,
  referer String,
  response String,
  subresponse String,
  win32response String,
  timetaken UInt64
   
  , uriQuery__utm_medium String
  , uriQuery__utm_source String
  , uriQuery__utm_campaign String
  , uriQuery__utm_term String
  , uriQuery__utm_content String
  , uriQuery__yclid String
  , uriQuery__region String
 
) Engine = MergeTree()
PARTITION BY toYYYYMM(logdate)
ORDER BY (fld_app_name, fld_app_module, logdatetime)
SETTINGS index_granularity = 8192;

Nous utilisons les valeurs par défaut pour le partitionnement (par mois) et la granularité de l'index. Tous les champs correspondent pratiquement aux enregistrements de journaux IIS pour l'enregistrement des requêtes http. À noter, des champs séparés pour le stockage des balises utm (elles sont analysées lors de l'insertion dans la table à partir du champ de chaîne de requête).

De plus, plusieurs champs système ont été ajoutés pour stocker des informations sur les systèmes, les composants et les serveurs. La description de ces champs se trouve ci-dessous dans le tableau. Nous stockons des journaux pour plusieurs systèmes dans une même table.

Nom

Description

Exemple

fld_app_name

Nom de l'application/système
Valeurs autorisées :

• site1.domain.com Site externe 1
• site2.domain.com Site externe 2
• internal-site1.domain.local Site interne 1

site1.domain.com

fld_app_module

Module du système
Valeurs autorisées :

• web — Site web
• svc — Service web du site
• intgr — Service web d'intégration
• bo — Interface administrateur (BackOffice)

web

fld_website_name

Nom du site dans IIS

Plusieurs systèmes peuvent être déployés sur un même serveur, ou même plusieurs instances d'un même module système.

web-main

fld_server_name

Nom du serveur

web1.domain.com

fld_log_file_name

Chemin vers le fichier journal sur le serveur

C:inetpublogsLogFiles
W3SVC1u_ex190711.log

Cela permet de construire efficacement des graphiques dans Grafana. Par exemple, de visualiser les requêtes provenant du frontend d'un système spécifique. Cela ressemble à un compteur de site dans Yandex.Metrica.

Voici quelques statistiques sur l'utilisation de la base de données sur deux mois.

Nombre d'enregistrements classés par systèmes et leurs composants

SELECT
    fld_app_name,
    fld_app_module,
    count(fld_app_name) AS rows_count
FROM log_web
GROUP BY
    fld_app_name,
    fld_app_module
    WITH TOTALS
ORDER BY
    fld_app_name ASC,
    rows_count DESC
 
┌─fld_app_name─────┬─fld_app_module─┬─rows_count─┐
│ site1.domain.ru  │ web            │     131441 │
│ site2.domain.ru  │ web            │    1751081 │
│ site3.domain.ru  │ web            │  106887543 │
│ site3.domain.ru  │ svc            │   44908603 │
│ site3.domain.ru  │ intgr          │    9813911 │
│ site4.domain.ru  │ web            │     772095 │
│ site5.domain.ru  │ web            │   17037221 │
│ site5.domain.ru  │ intgr          │     838559 │
│ site5.domain.ru  │ bo             │       7404 │
│ site6.domain.ru  │ web            │     595877 │
│ site7.domain.ru  │ web            │   27778858 │
└──────────────────┴────────────────┴────────────┘
 
Totaux :
┌─fld_app_name─┬─fld_app_module─┬─rows_count─┐
│              │                │  210522593 │
└──────────────┴────────────────┴────────────┘
 
11 lignes dans l'ensemble. Temps écoulé : 4.874 sec. Traitement de 210,52 millions de lignes, 421,67 Mo (43,19 millions de lignes/s., 86,51 Mo/s.)

Volume de données sur disque

SELECT
    formatReadableSize(sum(data_uncompressed_bytes)) AS uncompressed,
    formatReadableSize(sum(data_compressed_bytes)) AS compressed,
    sum(rows) AS total_rows
FROM system.parts
WHERE table = 'log_web'
 
┌─uncompressed─┬─compressed─┬─total_rows─┐
│ 54.50 GiB    │ 4.86 GiB   │  211427094 │
└──────────────┴────────────┴────────────┘
 
1 ligne dans l'ensemble. Temps écoulé : 0.035 sec.

Taux de compression des données dans les colonnes

SÉLECTIONNER
    nom,
    formatReadableSize(data_uncompressed_bytes) AS non_compressé,
    formatReadableSize(data_compressed_bytes) AS compressé,
    data_uncompressed_bytes / data_compressed_bytes AS ratio_de_compression
DE system.columns
OÙ table = 'log_web'
 
┌─nom─────────────────────┬─non_compressé─┬─compressé─┬─────ratio_de_compression─┐
│ logdate                │ 401.53 MiB   │ 1.80 MiB   │ 223.16665968777315 │
│ logdatetime            │ 803.06 MiB   │ 35.91 MiB  │ 22.363966401202305 │
│ fld_log_file_name      │ 220.66 MiB   │ 2.60 MiB   │  84.99905736932571 │
│ fld_server_name        │ 201.54 MiB   │ 50.63 MiB  │  3.980924816977078 │
│ fld_app_name           │ 201.17 MiB   │ 969.17 KiB │ 212.55518183686877 │
│ fld_app_module         │ 201.17 MiB   │ 968.60 KiB │ 212.67805817411906 │
│ fld_website_name       │ 201.54 MiB   │ 1.24 MiB   │  162.7204926761546 │
│ serverIP               │ 201.54 MiB   │ 50.25 MiB  │  4.010824061219731 │
│ method                 │ 201.53 MiB   │ 43.64 MiB  │  4.617721053304486 │
│ uriStem                │ 5.13 GiB     │ 832.51 MiB │  6.311522291936919 │
│ uriQuery               │ 2.58 GiB     │ 501.06 MiB │  5.269731450124478 │
│ port                   │ 803.06 MiB   │ 3.98 MiB   │ 201.91673864241824 │
│ username               │ 318.08 MiB   │ 26.93 MiB  │ 11.812513794583598 │
│ clientIP               │ 2.35 GiB     │ 82.59 MiB  │ 29.132328640073343 │
│ clientRealIP           │ 2.49 GiB     │ 465.05 MiB │  5.478382297052563 │
│ userAgent              │ 18.34 GiB    │ 764.08 MiB │  24.57905114484208 │
│ referer                │ 14.71 GiB    │ 1.37 GiB   │ 10.736792723669906 │
│ response               │ 803.06 MiB   │ 83.81 MiB  │  9.582334090987247 │
│ subresponse            │ 399.87 MiB   │ 1.83 MiB   │  218.4831068635027 │
│ win32response          │ 407.86 MiB   │ 7.41 MiB   │ 55.050315514606815 │
│ timetaken              │ 1.57 GiB     │ 402.06 MiB │ 3.9947395692010637 │
│ uriQuery__utm_medium   │ 208.17 MiB   │ 12.29 MiB  │ 16.936148912472955 │
│ uriQuery__utm_source   │ 215.18 MiB   │ 13.00 MiB  │ 16.548367623199912 │
│ uriQuery__utm_campaign │ 381.46 MiB   │ 37.94 MiB  │ 10.055156353418509 │
│ uriQuery__utm_term     │ 231.82 MiB   │ 10.78 MiB  │ 21.502540454070672 │
│ uriQuery__utm_content  │ 441.34 MiB   │ 87.60 MiB  │  5.038260760449327 │
│ uriQuery__yclid        │ 216.88 MiB   │ 16.58 MiB  │  13.07721335008116 │
│ uriQuery__region       │ 204.35 MiB   │ 9.49 MiB   │  21.52661903446796 │
└────────────────────────┴──────────────┴────────────┴────────────────────┘
 
28 lignes dans l'ensemble. Durée: 0.005 sec.

Description des composants utilisés

FileBeat. Transmission des fichiers de logs

Ce composant suit les modifications dans les fichiers de logs sur le disque et transmet les informations à LogStash. Il est installé sur tous les serveurs où des fichiers de logs sont écrits (en général, IIS). Il fonctionne en mode tail (c'est-à-dire qu'il ne transmet que les enregistrements ajoutés au fichier). Cependant, il peut être configuré pour transmettre des fichiers en entier. C'est pratique lorsque vous devez télécharger des données des mois précédents. Il suffit de placer le fichier de log dans le dossier et il le lira entièrement.

Lors de l'arrêt du service, les données cessent d'être transférées vers le stockage.

Un exemple de configuration est le suivant :

filebeat.yml

filebeat.inputs:
- type: log
  enabled: true
  paths:
    - C:/inetpub/logs/LogFiles/W3SVC1/*.log
  exclude_files: ['.gz$','.zip$']
  tail_files: true
  ignore_older: 24h
  fields:
    fld_server_name: "site1.domain.fr"
    fld_app_name: "site1.domain.fr"
    fld_app_module: "web"
    fld_website_name: "web-main"

- type: log
  enabled: true
  paths:
    - C:/inetpub/logs/LogFiles/__Import/access_log-*
  exclude_files: ['.gz$','.zip$']
  tail_files: false
  fields:
    fld_server_name: "site2.domain.fr"
    fld_app_name: "site2.domain.fr"
    fld_app_module: "web"
    fld_website_name: "web-main"
    fld_logformat: "logformat__apache"


filebeat.config.modules:
  path: ${path.config}/modules.d/*.yml
  reload.enabled: false
  reload.period: 2s

output.logstash:
  hosts: ["log.domain.com:5044"]

  ssl.enabled: true
  ssl.certificate_authorities: ["C:/filebeat/certs/ca.pem", "C:/filebeat/certs/ca-issuing.pem"]
  ssl.certificate: "C:/filebeat/certs/site1.domain.fr.cer"
  ssl.key: "C:/filebeat/certs/site1.domain.fr.key"

#================================ Processors =====================================

processors:
  - add_host_metadata: ~
  - add_cloud_metadata: ~

LogStash. Agrégateur de logs

Ce composant est conçu pour recevoir des enregistrements de logs de FileBeat (ou via une file d'attente RabbitMQ), les parser et les insérer par lots dans la base de données ClickHouse.

Pour l'insertion dans ClickHouse, le plugin Logstash-output-clickhouse est utilisé. Le plugin Logstash dispose d'un mécanisme de réessai des requêtes, mais lors d'un arrêt normal, il est préférable d'arrêter le service lui-même. Lors de l'arrêt, des messages s'accumuleront dans la file d'attente RabbitMQ, donc s'il doit être arrêté pendant une longue période, il est préférable d'arrêter les Filebeat sur les serveurs. Dans le schéma où RabbitMQ n'est pas utilisé (dans le réseau local, Filebeat envoie directement les logs à Logstash), les Filebeat fonctionnent de manière tout à fait acceptable et sûre, donc pour eux, l'indisponibilité de la sortie se passe sans conséquences.

Un exemple de configuration est le suivant :

log_web__filebeat_clickhouse.conf

input {
 
    beats {
        port => 5044
        type => 'iis'
        ssl => true
        ssl_certificate_authorities => ["/etc/logstash/certs/ca.cer", "/etc/logstash/certs/ca-issuing.cer"]
        ssl_certificate => "/etc/logstash/certs/server.cer"
        ssl_key => "/etc/logstash/certs/server-pkcs8.key"
        ssl_verify_mode => "peer"
 
            add_field => {
                "fld_server_name" => "%{[fields][fld_server_name]}"
                "fld_app_name" => "%{[fields][fld_app_name]}"
                "fld_app_module" => "%{[fields][fld_app_module]}"
                "fld_website_name" => "%{[fields][fld_website_name]}"
                "fld_log_file_name" => "%{source}"
                "fld_logformat" => "%{[fields][fld_logformat]}"
            }
    }
 
    rabbitmq {
        host => "queue.domain.com"
        port => 5671
        user => "q-reader"
        password => "password"
        queue => "web_log"
        heartbeat => 30
        durable => true
        ssl => true
        #ssl_certificate_path => "/etc/logstash/certs/server.p12"
        #ssl_certificate_password => "password"
 
        add_field => {
            "fld_server_name" => "%{[fields][fld_server_name]}"
            "fld_app_name" => "%{[fields][fld_app_name]}"
            "fld_app_module" => "%{[fields][fld_app_module]}"
            "fld_website_name" => "%{[fields][fld_website_name]}"
            "fld_log_file_name" => "%{source}"
            "fld_logformat" => "%{[fields][fld_logformat]}"
        }
    }
 
}
 
filter { 
 
      if [message] =~ "^#" {
        drop {}
      }
 
      if [fld_logformat] == "logformat__iis_with_xrealip" {
     
          grok {
            match => ["message", "%{TIMESTAMP_ISO8601:log_timestamp} %{IP:serverIP} %{WORD:method} %{NOTSPACE:uriStem} %{NOTSPACE:uriQuery} %{NUMBER:port} %{NOTSPACE:username} %{IPORHOST:clientIP} %{NOTSPACE:userAgent} %{NOTSPACE:referer} %{NUMBER:response} %{NUMBER:subresponse} %{NUMBER:win32response} %{NUMBER:timetaken} %{NOTSPACE:xrealIP} %{NOTSPACE:xforwarderfor}"]
          }
      } else {
   
          grok {
             match => ["message", "%{TIMESTAMP_ISO8601:log_timestamp} %{IP:serverIP} %{WORD:method} %{NOTSPACE:uriStem} %{NOTSPACE:uriQuery} %{NUMBER:port} %{NOTSPACE:username} %{IPORHOST:clientIP} %{NOTSPACE:userAgent} %{NOTSPACE:referer} %{NUMBER:response} %{NUMBER:subresponse} %{NUMBER:win32response} %{NUMBER:timetaken}"]
          }
 
      }
 
      date {
        match => [ "log_timestamp", "YYYY-MM-dd HH:mm:ss" ]
          timezone => "Etc/UTC"
        remove_field => [ "log_timestamp", "@timestamp" ]
        target => [ "log_timestamp2" ]
      }
 
        ruby {
            code => "tstamp = event.get('log_timestamp2').to_i
                        event.set('logdatetime', Time.at(tstamp).strftime('%Y-%m-%d %H:%M:%S'))
                        event.set('logdate', Time.at(tstamp).strftime('%Y-%m-%d'))"
        }
 
      if [bytesSent] {
        ruby {
          code => "event['kilobytesSent'] = event['bytesSent'].to_i / 1024.0"
        }
      }
 
 
      if [bytesReceived] {
        ruby {
          code => "event['kilobytesReceived'] = event['bytesReceived'].to_i / 1024.0"
        }
      }
 
   
        ruby {
            code => "event.set('clientRealIP', event.get('clientIP'))"
        }
        if [xrealIP] {
            ruby {
                code => "event.set('clientRealIP', event.get('xrealIP'))"
            }
        }
        if [xforwarderfor] {
            ruby {
                code => "event.set('clientRealIP', event.get('xforwarderfor'))"
            }
        }
 
      mutate {
        convert => ["bytesSent", "integer"]
        convert => ["bytesReceived", "integer"]
        convert => ["timetaken", "integer"] 
        convert => ["port", "integer"]
 
        add_field => {
            "clientHostname" => "%{clientIP}"
        }
      }
 
        useragent {
            source=> "useragent"
            prefix=> "browser"
        }
 
        kv {
            source => "uriQuery"
            prefix => "uriQuery__"
            allow_duplicate_values => false
            field_split => "&"
            include_keys => [ "utm_medium", "utm_source", "utm_campaign", "utm_term", "utm_content", "yclid", "region" ]
        }
 
        mutate {
            join => { "uriQuery__utm_source" => "," }
            join => { "uriQuery__utm_medium" => "," }
            join => { "uriQuery__utm_campaign" => "," }
            join => { "uriQuery__utm_term" => "," }
            join => { "uriQuery__utm_content" => "," }
            join => { "uriQuery__yclid" => "," }
            join => { "uriQuery__region" => "," }
        }
 
}
 
output { 
  #stdout {codec => rubydebug}
    clickhouse {
      headers => ["Authorization", "Basic abcdsfks..."]
      http_hosts => ["http://127.0.0.1:8123"]
      save_dir => "/etc/logstash/tmp"
      table => "log_web"
      request_tolerance => 1
      flush_size => 10000
      idle_flush_time => 1
        mutations => {
            "fld_log_file_name" => "fld_log_file_name"
            "fld_server_name" => "fld_server_name"
            "fld_app_name" => "fld_app_name"
            "fld_app_module" => "fld_app_module"
            "fld_website_name" => "fld_website_name"
 
            "logdatetime" => "logdatetime"
            "logdate" => "logdate"
            "serverIP" => "serverIP"
            "method" => "method"
            "uriStem" => "uriStem"
            "uriQuery" => "uriQuery"
            "port" => "port"
            "username" => "username"
            "clientIP" => "clientIP"
            "clientRealIP" => "clientRealIP"
            "userAgent" => "userAgent"
            "referer" => "referer"
            "response" => "response"
            "subresponse" => "subresponse"
            "win32response" => "win32response"
            "timetaken" => "timetaken"
             
            "uriQuery__utm_medium" => "uriQuery__utm_medium"
            "uriQuery__utm_source" => "uriQuery__utm_source"
            "uriQuery__utm_campaign" => "uriQuery__utm_campaign"
            "uriQuery__utm_term" => "uriQuery__utm_term"
            "uriQuery__utm_content" => "uriQuery__utm_content"
            "uriQuery__yclid" => "uriQuery__yclid"
            "uriQuery__region" => "uriQuery__region"
        }
    }
 
}

pipelines.yml

# This file is where you define your pipelines. You can define multiple.
# For more information on multiple pipelines, see the documentation:
#   https://www.elastic.co/guide/en/logstash/current/multiple-pipelines.html
 
- pipeline.id: log_web__filebeat_clickhouse
  path.config: "/etc/logstash/log_web__filebeat_clickhouse.conf"

ClickHouse. Stockage des journaux

Les journaux de tous les systèmes sont stockés dans une seule table (voir au début de l'article). Elle est conçue pour conserver des informations sur les requêtes : tous les paramètres sont similaires pour différents formats, par exemple les journaux IIS, les journaux Apache et Nginx. Pour les journaux d'applications où sont enregistrés, par exemple, les erreurs, les messages d'information et les avertissements, une table distincte sera prévue, avec une structure correspondante (actuellement en phase de conception).

Lors de la conception de la table, il est très important de déterminer la clé primaire (sur laquelle les données seront triées lors du stockage). Cela influence le degré de compression des données et la vitesse des requêtes. Dans notre exemple, la clé est
ORDER BY (fld_app_name, fld_app_module, logdatetime)
C'est-à-dire par le nom du système, le nom du composant du système et la date de l'événement. Initialement, la date de l'événement était en première position. Après l'avoir déplacée à la dernière, les requêtes ont commencé à fonctionner environ deux fois plus vite. Modifier la clé primaire nécessitera de recréer la table et de recharger les données, afin que ClickHouse réorganise les données sur le disque. C'est une opération lourde, donc il est préférable de réfléchir à l'avance à ce qui doit entrer dans la clé de tri.

Il convient également de noter que dans les dernières versions, un type de données LowCardinality est apparu. Son utilisation réduit considérablement la taille des données compressées pour les champs ayant une faible cardinalité (peu de variations).

Nous utilisons actuellement la version 19.6, et nous prévoyons d'essayer de mettre à jour vers la dernière version. Celle-ci a introduit de superbes fonctionnalités telles que la granularité adaptative, les indices de saut et le codec DoubleDelta.

Par défaut, lors de l'installation, le niveau de journalisation trace est configuré. Les journaux sont tournés et archivés, mais sont étendus jusqu'à un gigaoctet. Si ce n'est pas nécessaire, il est possible de définir le niveau warning, ce qui réduit considérablement la taille du journal. La configuration de la journalisation est définie dans le fichier config.xml :

warning

Quelques commandes utiles

Étant donné que les packages d'installation originaux sont construits à partir de Debian, pour d'autres versions de Linux, il est nécessaire d'utiliser les packages fournis par la société Altinity.

Vous trouverez des instructions avec des liens vers leur dépôt à cette adresse : https://www.altinity.com/blog/2017/12/18/logstash-with-clickhouse
sudo yum search clickhouse-server
sudo yum install clickhouse-server.noarch

1. Vérification du statut
sudo systemctl status clickhouse-server

2. Arrêt du serveur
sudo systemctl stop clickhouse-server

3. Démarrage du serveur
sudo systemctl start clickhouse-server

Lancement pour exécuter des requêtes en mode multiligne (à exécuter après le symbole ";")
clickhouse-client --multiline
clickhouse-client --multiline --host 127.0.0.1 --password pa55w0rd
clickhouse-client --multiline --host 127.0.0.1 --port 9440 --secure --user default --password pa55w0rd

Le plugin Clickhouse pour Logstash en cas d'erreur sur une ligne sauvegarde tout le lot dans le fichier /tmp/log_web_failed.json
Il est possible de corriger manuellement ce fichier et d'essayer de le charger manuellement dans la base de données :
clickhouse-client --host 127.0.0.1 --password password --query="INSERT INTO log_web FORMAT JSONEachRow" < /tmp/log_web_failed__fixed.json

sudo mv /etc/logstash/tmp/log_web_failed.json /etc/logstash/tmp/log_web_failed__fixed.json
sudo chown user_dev /etc/logstash/tmp/log_web_failed__fixed.json
sudo clickhouse-client --host 127.0.0.1 --password password --query="INSERT INTO log_web FORMAT JSONEachRow" < /etc/logstash/tmp/log_web_failed__fixed.json
sudo mv /etc/logstash/tmp/log_web_failed__fixed.json /etc/logstash/tmp/log_web_failed__fixed_.json

Sortie de la ligne de commande
quit;
## Configuration TLS
https://www.altinity.com/blog/2019/3/5/clickhouse-networking-part-2

openssl s_client -connect log.domain.com:9440 < /dev/null

LogStash. Routeur de logs de FileBeat vers la file d'attente RabbitMQ

Ce composant est utilisé pour router les logs provenant de FileBeat vers la file d'attente RabbitMQ. Il y a deux aspects à considérer :

1. Malheureusement, FileBeat ne dispose pas d'un plugin de sortie pour écrire directement dans RabbitMQ. Et cette fonctionnalité, d'après un problème sur leur GitHub, n'est pas prévue pour l'implémentation. Il existe un plugin pour Kafka, mais pour certaines raisons, nous ne pouvons pas l'utiliser.
2. Il y a des exigences pour la collecte de logs dans la DMZ. En conséquence, les logs doivent d'abord être stockés dans une file d'attente et ensuite LogStash lit les enregistrements de l'extérieur à partir de cette file.

C'est donc dans le cas où les serveurs sont situés dans la DMZ que ce schéma quelque peu compliqué doit être utilisé. Un exemple de configuration est le suivant :

iis_w3c_logs__filebeat_rabbitmq.conf

input {
 
    beats {
        port => 5044
        type => 'iis'
        ssl => true
        ssl_certificate_authorities => ["/etc/pki/tls/certs/app/ca.pem", "/etc/pki/tls/certs/app/ca-issuing.pem"]
        ssl_certificate => "/etc/pki/tls/certs/app/queue.domain.com.cer"
        ssl_key => "/etc/pki/tls/certs/app/queue.domain.com-pkcs8.key"
        ssl_verify_mode => "peer"
    }
 
}
 
output { 
  #stdout {codec => rubydebug}
 
    rabbitmq {
        host => "127.0.0.1"
        port => 5672
        exchange => "monitor.direct"
        exchange_type => "direct"
        key => "%{[fields][fld_app_name]}"
        user => "q-writer"
        password => "password"
        ssl => false
    }
}

RabbitMQ. File d'attente des messages

Ce composant est utilisé pour la mise en mémoire tampon des enregistrements de logs dans la DMZ. L'enregistrement se fait via un lien Filebeat → LogStash. La lecture se fait de l'extérieur de la DMZ via LogStash. Lors de l'exploitation via RabbitMQ, environ 4 000 messages sont traités par seconde.

Le routage des messages est configuré selon le nom du système, c'est-à-dire sur la base des données de configuration de FileBeat. Tous les messages vont dans une seule file d'attente. Si, pour une raison quelconque, le service des files d'attente est arrêté, cela ne causera pas de perte de messages : les FileBeat recevront des erreurs de connexion et suspendront temporairement l'envoi. LogStash, qui lit depuis la file d'attente, recevra également des erreurs réseau et attendra la restauration de la connexion. Pendant ce temps, les données cesseront bien sûr d'être écrites dans la base de données.

Les instructions suivantes sont utilisées pour créer et configurer les files d'attente :

sudo /usr/local/bin/rabbitmqadmin/rabbitmqadmin declare exchange --vhost=/ name=monitor.direct type=direct sudo /usr/local/bin/rabbitmqadmin/rabbitmqadmin declare queue --vhost=/ name=web_log durable=true
sudo /usr/local/bin/rabbitmqadmin/rabbitmqadmin --vhost="/" declare binding source="monitor.direct" destination_type="queue" destination="web_log" routing_key="site1.domain.ru"
sudo /usr/local/bin/rabbitmqadmin/rabbitmqadmin --vhost="/" declare binding source="monitor.direct" destination_type="queue" destination="web_log" routing_key="site2.domain.ru"

Grafana. Dashboards

Ce composant est utilisé pour la visualisation des données de monitoring. Dans ce cas, il est nécessaire d'installer le plugin ClickHouse datasource for Grafana 4.6+. Nous avons dû l'ajuster légèrement pour améliorer l'efficacité du traitement des filtres SQL sur le tableau de bord.

Par exemple, nous utilisons des variables, et si elles ne sont pas définies dans le champ de filtre, nous préférerions qu'il ne génère pas de condition dans le WHERE du type ( uriStem = » AND uriStem != » ). Dans ce cas, ClickHouse lira la colonne uriStem. En général, nous avons essayé différentes options et finalement ajusté le plugin (macro $valueIfEmpty), de sorte qu'en cas de valeur vide, il renvoie 1, sans mentionner la colonne elle-même.

Et maintenant, nous pouvons utiliser une telle requête pour le graphique

$columns(response, count(*) c) from $table where $adhoc
and $valueIfEmpty($fld_app_name, 1, fld_app_name = '$fld_app_name')
and $valueIfEmpty($fld_app_module, 1, fld_app_module = '$fld_app_module') and $valueIfEmpty($fld_server_name, 1, fld_server_name = '$fld_server_name') and $valueIfEmpty($uriStem, 1, uriStem like '%$uriStem%')
and $valueIfEmpty($clientRealIP, 1, clientRealIP = '$clientRealIP')

qui se transforme en cette requête SQL (notez que les champs vides uriStem sont devenus simplement 1)

SELECT
t,
groupArray((response, c)) AS groupArr
FROM (
SELECT
(intDiv(toUInt32(logdatetime), 60) * 60) * 1000 AS t, response,
count(*) AS c FROM default.log_web
WHERE (logdate >= toDate(1565061982)) AND (logdatetime >= toDateTime(1565061982)) AND 1 AND (fld_app_name = 'site1.domain.ru') AND (fld_app_module = 'web') AND 1 AND 1 AND 1
GROUP BY
t, response
ORDER BY
t ASC,
response ASC
)
GROUP BY t ORDER BY t ASC

Conclusion

L'apparition de la base de données ClickHouse a été un événement marquant sur le marché. Il était difficile d'imaginer qu'en un instant, nous disposions gratuitement d'un outil puissant et pratique pour travailler avec de grandes quantités de données. Bien sûr, avec l'augmentation des besoins (comme le sharding et la réplication sur plusieurs serveurs), le schéma deviendra plus complexe. Mais d'après les premières impressions, travailler avec cette base de données est très agréable. On voit que le produit a été conçu « pour les gens ».

Comparé à ElasticSearch, les coûts de stockage et de traitement des journaux, selon des estimations préliminaires, sont réduits de cinq à dix fois. En d'autres termes, si nous devions configurer un cluster de plusieurs machines pour le volume actuel de données, avec ClickHouse, une seule machine peu puissante suffira. Bien sûr, ElasticSearch dispose également de mécanismes de compression des données sur disque et d'autres fonctionnalités qui permettent de réduire considérablement la consommation des ressources, mais cela demandera des coûts plus élevés par rapport à ClickHouse.

Sans aucune optimisation spéciale de ma part, avec les réglages par défaut, le chargement des données et les requêtes sur la base de données fonctionnent à une vitesse incroyable. Pour l'instant, nous avons peu de données (environ 200 millions d'enregistrements), mais le serveur est faible. Cet outil peut à l'avenir être utilisé pour d'autres objectifs, non liés au stockage de journaux. Par exemple, pour l'analyse globale, dans le domaine de la sécurité, et l'apprentissage automatique.

Pour finir, parlons un peu des inconvénients et des avantages.

Inconvénients

1. Chargement des enregistrements en grandes vagues. D'une part, c'est une fonctionnalité, mais il faut tout de même utiliser des composants supplémentaires pour le buffering des enregistrements. Cette tâche n'est pas toujours simple, mais elle reste néanmoins résoluble. Et nous aimerions simplifier le schéma.
2. Certain exotic features or new functionalities often break in new versions. This raises concerns and reduces the desire to upgrade to a new version. For example, the Kafka table engine is a very useful feature that allows directly reading events from Kafka without implementing consumers. However, judging by the number of issues on GitHub, we are currently cautious about using this engine in production. Nevertheless, if one avoids drastic shifts and utilizes the core functionality, it operates steadily.

Avantages

1. Ne ralentit pas.
2. Seuil d'entrée bas.
3. Open-source.
4. Gratuit.
5. Bien évolutif (sharding/réplication « en boîte »)
6. Figure dans le registre des logiciels russes recommandés par le ministère des Communications.
7. Support officiel de Yandex.

Source : habr.com