БД ClickHouse для людей, или Технологии инопланетян

    Алексей Лизунов, руководитель направления центра компетенций дистанционных каналов обслуживания дирекции информационных технологий МКБ



    В качестве альтернативы стеку ELK (ElasticSearch, Logstash, Kibana) мы проводим исследовательские работы по использованию БД ClickHouse в качестве хранилища данных для логов.

    В этой статье мы хотели бы рассказать о нашем опыте использования БД ClickHouse и о предварительных результатах по итогам пилотной эксплуатации. Стоит отметить сразу, что результаты получились впечатляющие.




    Далее мы опишем подробнее, как у нас настроена система, и из каких компонентов она состоит. Но сейчас хотелось бы немного рассказать об этой БД в целом, и почему на нее стоит обратить внимание. БД ClickHouse – это высокопроизводительная аналитическая столбцовая БД от Яндекса. Используется в сервисах Яндекса, изначально это основное хранилище данных для Яндекс.Метрики. Система open-source, бесплатная. С точки зрения разработчика, мне всегда было интересно, как же у них это реализовано, ведь там фантастически большие данные. И сам пользовательский интерфейс Метрики очень гибок и работает быстро. При первом знакомстве с этой БД впечатление: «Ну, наконец-то! Сделано «для людей»! Начиная от процесса установки и заканчивая отправкой запросов».

    У этой БД очень низкий порог входа. Даже средней квалификации разработчик может за несколько минут установить эту БД и начать пользоваться. Все работает четко. Даже люди, которые плохо знакомы с Linux, достаточно быстро могут справиться с установкой и делать простейшие операции. Если раньше, при слове Big Data, Hadoop, Google BigTable, HDFS, у обычного разработчика возникали представления, что там речь о каких-то терабайтах, петабайтах, что настройками и разработкой для этих систем занимаются некие сверхлюди, то с появлением БД ClickHouse мы получили простой, понятный инструмент, при помощи которого можно решать до этого недостижимый круг задач. Достаточно лишь одна довольно средняя машина и пять минут на установку. То есть мы получили такую БД как, например, MySql, но только для хранения миллиардов записей! Некий суперархиватор с языком SQL. Это как будто людям передали оружие инопланетян.

    О нашей системе сбора логов


    Для сбора информации используются файлы логов IIS веб-приложений стандартного формата (также сейчас мы занимаемся и парсингом логов приложений, но основная цель на этапе пилотной эксплуатации у нас – это сбор логов IIS).

    Полностью от стека ELK нам отказаться по различным причинам не удалось, и мы продолжаем использовать компоненты LogStash и Filebeat, которые зарекомендовали себя хорошо и работают вполне надежно и предсказуемо.

    Общая схема логирования представлена на рисунке ниже:



    Особенностью записи данных в БД ClickHouse является нечастая (раз в секунду) вставка записей большими пачками. Это, судя по всему, самая «проблемная» часть, с которой сталкиваешься при первом опыте работы с БД ClickHouse: схема немного усложняется.
    Здесь сильно помог плагин для LogStash, который напрямую вставляет данные в ClickHouse. Этот компонент разворачивается на том же сервере, что и сама БД. Так, вообще говоря, не рекомендуется делать, но с практической точки зрения, чтобы не плодить отдельные сервера, пока он развернут на том же сервере. Ни сбоев, ни конфликтов ресурсов с БД мы не наблюдали. К тому же необходимо отметить, что у плагина предусмотрен механизм ретрая в случае ошибок. И в случае ошибок плагин пишет на диск пачку данных, которые не удалось вставить (формат файла удобный: после правки, можно легко заинсертить исправленную пачку с помощью clickhouse-client).

    Полный список ПО, которое используется в схеме представлен в таблице:

    Cписок используемого ПО
    Название
    Описание
    Ссылка на дистрибутив

    NGINX


    Reverse-proxy для ограничения доступа по портам и организации авторизации



    На данный момент не используется в схеме


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


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


    FileBeat


    Передача файловых логов.


    https://www.elastic.co/downloads/beats/filebeat (дистрибутив для Windows 64bit).


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


    LogStash


    Сборщик логов.



    Используется для сбора логов от FileBeat, а также для сбора логов из очереди RabbitMQ (для серверов, которые находятся в DMZ.)


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


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


    Logstash- output- clickhouse


    Плагин Loagstash для передачи логов в БД ClickHouse пачками


    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


    Хранилище логов 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


    Примечание. Начиная с августа 2018 в репозитории Яндекса появились "нормальные" сборки rpm для RHEL, поэтому можно пробовать использовать их. На момент установки мы использовали пакеты, собранные Altinity.


    Grafana


    Визуализация логов. Настройка дашбордов



    https://grafana.com/


    https://grafana.com/grafana/download



    Redhat & Centos(64 Bit) – последнюю версию


    ClickHouse datasource for Grafana 4.6+


    Плагин для Grafana с источником данных ClickHouse


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


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


    LogStash


    Маршрутизатор логов от FileBeat в очередь RabbitMQ.


    Примечание. К сожалению у FileBeat нет output напрямую в RabbitMQ, поэтому требуется промежуточное звено в виде Logstash


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


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


    RabbitMQ


    Очередь сообщений. Это буфер записей логов в 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 (Необходим для RabbitMQ)


    Среда выполнения Erlang. Требуется для работы RabbitMQ


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


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




    Конфигурация сервера с БД ClickHouse представлена в следующей таблице:
    Название
    Значение
    Примечание

    Конфигурация


    HDD: 40GB
    RAM: 8GB
    Processor: Core 2 2Ghz


    Необходимо обратить внимание на советы по эксплуатации БД ClickHouse (https://clickhouse.yandex/docs/ru/operations/tips/)


    Общесистемное ПО


    ОС: Red Hat Enterprise Linux Server (Maipo)


    JRE (Java 8)


     



    Как видно, это обычная рабочая станция.

    Структура таблицы для хранения логов выглядит следующим образом:

    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;


    Мы используем значения по умолчанию для партиционирования (по месяцам) и гранулярность индекса. Все поля практически соответствуют записям лога IIS для регистрации http-запросов. Отдельно отметим, отдельные поля для хранения utm-меток (они парсятся на этапе вставки в таблицу из поля строки запроса).

    Также в таблице добавлены несколько системных полей для хранения информации о системах, компонентах, серверах. Описание этих полей см. ниже в таблице. В одной таблице мы храним логи по нескольким системам.

    Название
    Описание
    Пример

    fld_app_name


    Название приложения/системы
    Допустимые значения:


    • site1.domain.com Внешний сайт 1
    • site2.domain.com Внешний сайт 2
    • internal-site1.domain.local Внутренний сайт 1

    site1.domain.com

    fld_app_module


    Модуль системы
    Допустимые значения:


    • web — Веб-сайт
    • svc — Веб-сервис сайта
    • intgr — Веб-сервис интеграции
    • bo — Админка (BackOffice)

    web


    fld_website_name


    Название сайта в IIS


    На одном сервере может быть развернуто несколько систем, или даже несколько экземпляров одного модуля системы


    web-main


    fld_server_name


    Имя сервера


    web1.domain.com


    fld_log_file_name


    Путь к файлу лога на сервере


    С:\inetpub\logs\LogFiles
    \W3SVC1\u_ex190711.log

    Это позволяет эффективно строить графики в Grafana. Например, просматривать запросы с фронтенда конкретной системы. Это похоже на счетчик сайта в Яндекс.Метрике.

    Вот некоторая статистика по использованию БД за два месяца.

    Количество записей с разбивкой по системам и их компонентам
    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 │
    └──────────────────┴────────────────┴────────────┘
     
    Totals:
    ┌─fld_app_name─┬─fld_app_module─┬─rows_count─┐
    │              │                │  210522593 │
    └──────────────┴────────────────┴────────────┘
     
    11 rows in set. Elapsed: 4.874 sec. Processed 210.52 million rows, 421.67 MB (43.19 million rows/s., 86.51 MB/s.)

    Объем данных на диске
    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 rows in set. Elapsed: 0.035 sec.

    Степень сжатия данных в столбцах
    SELECT
        name,
        formatReadableSize(data_uncompressed_bytes) AS uncompressed,
        formatReadableSize(data_compressed_bytes) AS compressed,
        data_uncompressed_bytes / data_compressed_bytes AS compress_ratio
    FROM system.columns
    WHERE table = 'log_web'
     
    ┌─name───────────────────┬─uncompressed─┬─compressed─┬─────compress_ratio─┐
    │ 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 rows in set. Elapsed: 0.005 sec.


    Описание используемых компонентов

    FileBeat. Передача файловых логов


    Этот компонент отслеживает изменения в файлах логов на диске и передает информацию в LogStash. Устанавливается на всех серверах, где пишутся файлы с логами (как правило, IIS). Работает в режиме tail (т. е. передает только добавленные записи в файл). Но отдельно можно настроить на передачу файлов целиком. Это удобно, когда нужно загрузить данные за предыдущие месяцы. Просто положить файл с логом в папку и он сам его прочитает целиком.

    При остановке сервиса, данные перестают передаваться дальше в хранилище.

    Пример конфигурации выглядит следующим образом:

    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.ru"
        fld_app_name: "site1.domain.ru"
        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.ru"
        fld_app_name: "site2.domain.ru"
        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.ru.cer"
      ssl.key: "C:/filebeat/certs/site1.domain.ru.key"
     
    #================================ Processors =====================================
     
    processors:
      - add_host_metadata: ~
      - add_cloud_metadata: ~


    LogStash. Сборщик логов


    Этот компонент предназначен для получения записей логов от FileBeat (либо через очередь RabbitMQ), парсинга и вставки пачками в БД ClickHouse.

    Для вставки в ClickHouse используется плагин Logstash-output-clickhouse. У плагина Logstash есть механизм ретрая запросов, но при штатном останове, лучше все-таки останавливать сам сервис. При остановке будут копиться сообщения в очереди RabbitMQ, поэтому если останов на продолжительное время, то тогда лучше останавливать Filebeat'ы на серверах. В схеме, где не используется RabbitMQ (в локальной сети Filebeat напрямую отправляет логи в Logstash), Filebeat'ы работают вполне приемлемо и безопасно, поэтому для них недоступность output проходит без последствий.

    Пример конфигурации выглядит следующим образом:

    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. Хранилище логов


    Логи по всем системам сохраняются в одну таблицу (см. в начале статьи). Она предназначения для хранения информации о запросах: все параметры похожи для различных форматов, например логи IIS, логи apache и nginx. Для логов приложений, в которых регистрируются, например, ошибки, информационные сообщения, варнинги, будет предусмотрена отдельная таблица, с соответствующей структурой (сейчас на стадии проектирования).

    При проектировании таблицы очень важно определиться с первичным ключом (по которому будут сортироваться данные при хранении). От этого зависит степень сжатия данных и скорость запросов. В нашем примере ключом является
    ORDER BY (fld_app_name, fld_app_module, logdatetime)
    Т. е. по названию системы, названию компонента системы и дате события. Первоначально дата события была на первом месте. После перемещения ее на последнее место запросы стали работать примерно в два раза быстрее. Изменение первичного ключа потребует пересоздания таблицы и перезаливки данных, чтобы ClickHouse пересортировал данные на диске. Это тяжелая операция, поэтому желательно сильно заранее продумать, что должно входить в ключ сортировки.

    Также необходимо отметить, что относительно в последних версиях появился тип данных LowCardinality. При его использовании резко сокращается размер сжатых данных для тех полей, у которых низкая кардинальность (мало вариантов).

    Сейчас используется версия 19.6, и мы планируем попробовать обновить версию до последней. В них появились такие замечательные фичи как Adaptive Granularity, Skipping indices и кодек DoubleDelta, например.

    По умолчанию при установке в конфигурации установлен уровень логирования trace. Логи ротируются и архивируются, но при этом расширяются до гигабайта. Если нет необходимости, то можно поставить уровень warning, тогда размер лога резко уменьшается. Настройка логирования задается в файле config.xml:

    <!-- Possible levels: https://github.com/pocoproject/poco/blob/develop/Foundation/include/Poco/Logger. h#L105 -->
    <level>warning</level>

    Некоторые полезные команды
    Поскольку оригинальные пакеты установки собираются по Debian, то для других версий Linux необходимо использовать пакеты собранные компанией Altinity.
     
    Вот по этой ссылке есть инструкции с ссылками на их репозиторий: https://www.altinity.com/blog/2017/12/18/logstash-with-clickhouse
    sudo yum search clickhouse-server
    sudo yum install clickhouse-server.noarch
      
    1. проверка статуса
    sudo systemctl status clickhouse-server
     
    2. остановка сервера
    sudo systemctl stop clickhouse-server
     
    3. запуск сервера
    sudo systemctl start clickhouse-server
     
    Запуск для выполнения запросов в многострочном режиме (выполнение после знака ";")
    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
     
    Плагин кликлауза для логстеш в случае ошибки в одной строке сохраняет всю пачку в файл /tmp/log_web_failed.json
    Можно вручную исправить этот файл и попробовать залить его в БД вручную:
    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
     
    выход из командной строки
    quit;
    ## Настройка TLS
    https://www.altinity.com/blog/2019/3/5/clickhouse-networking-part-2
     
    openssl s_client -connect log.domain.com:9440 < /dev/null


    LogStash. Маршрутизатор логов от FileBeat в очередь RabbitMQ


    Этот компонент используется для маршрутизации логов, поступающих от FileBeat в очередь RabbitMQ. Здесь два момента:

    1. К сожалению, FileBeat не имеет output плагина для записи напрямую в RabbitMQ. И такой функционал, судя по ишью на их гитхабе, не планируется к реализации. Есть плагин для Кафки, но по определенным причинам мы не можем ее использовать у себя.
    2. Есть требования по сбору логов в DMZ. Исходя из них, логи сначала должны складываться в очередь и потом LogStash извне читает из очереди записи.

    Поэтому именно для случая расположения серверов в DMZ приходится использовать такую немного усложненную схему. Пример конфигурации выглядит следующим образом:

    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. Очередь сообщений


    Этот компонент используется для буферизации записей логов в DMZ. Запись производится через связку Filebeat → LogStash. Чтение осуществляется извне DMZ через LogStash. При эксплуатации через RabboitMQ обрабатывается около 4 тысяч сообщений в секунду.

    Раутинг сообщений настроен по названию системы, т. е. на основе данных конфигурации FileBeat. Все сообщения попадают в одну очередь. Если по каким либо причинам будет остановлен сервис очередей, то это не приведет к потере сообщений: FileBeat'ы будут получать ошибки соединения и приостановят временно отправку. А LogStash, который читает из очереди, также будет получать сетевые ошибки и ждать, когда воcстановится соединение. Данные при этом, конечно, перестанут писаться в БД.

    Следующие инструкции используются для создания и настройки очередей:

    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. Дашборды


    Этот компонент используется для визуализации данных мониторинга. При этом необходимо установить плагин ClickHouse datasource for Grafana 4.6+. Нам пришлось его немного подправить, чтобы повысить эффективность обработки SQL-фильтров на дашборде.

    Например, мы используем переменные, и если они не заданы в поле фильтра, то хотелось бы, чтобы он не генерировал условие в WHERE вида ( uriStem = '' AND uriStem != '' ). В таком случае, ClickHouse будет читать колонку uriStem. В общем, мы попробовали разные варианты и в конце концов поправили плагин (макрос $valueIfEmpty), чтобы в случае пустого значения он возвращать 1, без упоминания самого столбца.

    И теперь можно использовать вот такой запрос для графика

    $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')

    который преобразуется в такой SQL (обратите внимание, что пустые поля uriStem преобразовались в просто 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

    Заключение


    Появление БД ClickHouse стало знаковым событием на рынке. Трудно было представить, что совершенно бесплатно в одно мгновение мы вооружились мощным и практичным инструментом для работы с большими данными. Безусловно, при увеличении потребностей (например, шардирование и репликация на несколько серверов) схема будет усложняться. Но по первым впечатлениями, работать с этой БД очень приятно. Видно, что продукт сделан «для людей».

    По сравнению с ElasticSearch, затраты на хранение и обработку логов, по предварительным оценкам, сокращаются от пяти до десяти раз. Другими словами, если для текущего объема данных нам пришлось бы настраивать кластер из нескольких машин, то при использовании ClickHouse нам достаточно одной маломощной машины. Да, конечно, в ElasticSearch также есть механизмы сжатия данных на диске и другие фичи, которые позволяют заметно снизить потребление ресурсов, но по сравнению с ClickHouse это потребует больших затрат.

    Без каких-либо специальных оптимизаций со своей стороны, на дефолтовых настройках, загрузка данных и выборки из БД работают с потрясающей скоростью. Данных пока у нас немного (около 200 млн записей), но сам сервер слабый. Этот инструмент мы в будущем можем использовать и для других целей, не связанных с хранением логов. Например, для сквозной аналитики, в области безопасности, машинном обучении.

    В конце немного о минусах и плюсах.

    Минусы


    1. Загрузка записей большими пачками. Это, с одной стороны, фича, но все-таки приходится использовать дополнительные компоненты для буферизации записей. Эта задача не всегда простая, но все же решаемая. И хотелось бы упрощать схему.
    2. Некоторый экзотический функционал или новые фичи часто в новых версиях ломаются. Это вызывает опасения, уменьшая желание обновиться до новой версии. Например, движок таблиц Kafka – очень полезная фича, которая позволяет напрямую читать события из кафки, без реализации консьюмеров. Но судя по количеству Issue на гитхабе, мы пока остерегаемся использовать этот движок в продакшене. Впрочем, если не делать резких телодвижений в сторону и использовать основной функционал, то он работает стабильно.

    Плюсы


    1. Не тормозит.
    2. Низкий порог входа.
    3. Open-source.
    4. Бесплатна.
    5. Хорошо масштабируется (шардирование/репликация «из коробки»)
    6. Входит в реестр российского ПО, рекомендованного МинКомСвязи.
    7. Наличие официальной поддержки от Яндекс.

    Comments 11

      +2
      Трудно не отметить длину титула должности. Внушает!
        0
        А что собственно с просмотром логов? Ничего лучше Kibana (которая, к сожалению, только с еластиком работает) не нашел.
          0
          Мы используем Grafana. Для мониторинга вполне хватает. Поскольку Clickhouse — sql-like база данных, есть odbc-драйвер. Это позволяет использовать множество готовых UI-инструментов: от Tableau и Power BI до SuperSet и Redash. Впрочем, мы их пока не использовали для таких целей.
            0
            Не поделитесь скриншотами с текстами логов?

            Полностью разделяю ваш восторг от ClickHouse. У нас он заменил связку ElasticSearch+Spark. Я был в шоке, когда увидел, что ClickHouse с легкостью делает в реальном времени то, для чего требовались кластеры ES и DataBricks и много минут на перегонку данных.
            Хочу поделиться одним из кейсов. Из API стороннего сервиса грузим отчёт в десятки тысяч строк. Потом этот отчёт мапится в миллионы строк. Памяти категорически не хватает, а нужно сделать агрегацию, прежде чем сложить всё в базу. Думал, что придётся писать модуль на Сях. Но ClickHouse оказался просто магией: загрузил во временную таблицу промежуточные данные и сагрегировал за секунды. Сами данные съедали в разы меньше, чем удалось выжать из Питона с NumPy, но во время агрегации памяти уже не хватало. Достаточно поменять настройку КликХауса, и он волшебным образом как-то всё проделывает с использованием диска, по-прежнему за считанные секунды.

            Так что рекомендую попробовать не только как хранилище, но и как число-дробилку.
              0
              У кликхауса есть утилита clickhouse-local, кстати. Которая позволяет использовать движок в командой строке (нет необходимости использовать сам сервер). На вход файл для обработки, на выход файл с результатом.

              Не поделитесь скриншотами с текстами логов?

              К сожалению, нет возможности — это конфиденциальная информация. Но надо сказать, что это обычный лог, генерируемый Microsoft IIS в формате w3c. Основной нюанс здесь — это составить grok-шаблон для логстеша, чтобы он расщеплялся на поля. Чтобы можно было их в фильтре дообработать. Так можно и логи апач распарсить или nginx.

                0
                Ого, про clickhouse-local не знал, спасибо.

                Хотелось на конечный интерфейс к логам посмотреть. Не получилось быстро нагуглить. Как выше уже написали, Kibana для чтения логов прекрасна. По некоторым причинам использую вместо неё Graylog и не перестаю плеваться. Интересно, может ли Grafana составить конкуренцию.
                  0
                  А в начале статьи есть картинка из графаны. Это реальные графики на основе логов.
                  У графаны есть ещё виджет Таблица. Мы его тоже используем. Например, топ url запросов или топ ошибочных урл. Все понятно и удобно.
                  В целом, для дашборда можно задавать фильтры. Например, можно задать часть url.
                    +1
                    Попробую расшифровать вопрос Balek, т.к. мне он тоже интересен.
                    Вы показываете скриншоты с графиками и цифрами и называете примеры «топ урлов».
                    Мне же (и Balek) хочется увидеть как выглядит просмотр логов.
                    Представьте себе сервис, который пишет построчно:
                    • Начал работу над ABC...
                    • Подзадача А успешно (заняло 1 сек).
                    • Подзадача B успешно (заняло 5 сек).
                    • Подзадача C сфейлилась с ошибкой 123 (текстовое описание ошибки)
                    • Backtrace:
                    • — main.c:123
                    • — main.c:10


                    Вот как такие вещи смотреть и искать в графане?
                      0
                      Понял. Вы имеете ввиду логи приложения.

                      Обычно в таких логах есть время записи, уровень (error, warn, info и проч.), номер потока и сообщение. Их нужно сначала в логстеше, например, расщепить на столбцы и записать в ClickHouse.
                      Текст сообщения прям в отдельный столбец целиком.

                      А когда записи лога уже в таблице, то в графане просто настраивается виджет Таблица и к нему привязывается select с группировкой и фильтром в where через like. Примерно так:
                      select logdatetime
                       , message
                       , count() as c
                      from default.log_app
                      where message like '%сфейлилась с ошибкой%'
                       and logdatetime > $from -- это фильтр времени в графане
                      group by logdatetime
                       , message
                      order by logdatetime
                      limit by 100
                      


                      В клике, конечно, нет полнотекстового индексирования как в эластике, и есть нюансы при хранении больших строк. Но сообщения из лога (без стек-трейса) вполне нормально могут работать (В последних версиях появилась крутая фича Adaptive granularity).

                      Мы у себя сейчас, как раз, в процессе настройки парсинга логов приложений. На дашборде видим таблицу с сообщениями варнингов и ошибок, например, за последние 12 часов.
                      Нам надо, наверное, отдельную статью написать про такой сценарий. Там есть нюансы. Но текущие результаты вполне устраивают. В файлы с логами смотреть не приходится.
          0
          Такой же опыт с ClickHouse, запросы, которые раньше занимали несколько минут на разных решениях, на ClickHouse начали выполняться за секунды. Ребята из Яндекс Метрики действительно провели очень большую работу по оптимизации. Жаль нельзя использовать ClickHouse как основную базу, так как записи неизменяемые (видимо, чтобы не перестраивать индексы), но для логов подходит идеально.
            0

            Не почему же нельзя как основную. Вполне можно. Мы для одного проекта делали с обновлениями: https://youtu.be/Ptyknfy5DWM
            написав sql- подобный язык для бизнеса.

          Only users with full accounts can post comments. Log in, please.