ICD信息技术局远程服务通道能力中心负责人Alexey Lizunov
作为ELK堆栈（ElasticSearch，Logstash，Kibana）的替代方法，我们正在研究将ClickHouse数据库用作日志的数据仓库。
在本文中，我们想谈谈使用ClickHouse数据库的经验以及试验操作的初步结果。 应当立即指出，结果令人印象深刻。
接下来，我们将更详细地描述系统的配置方式及其组成部分。 但是，现在我想谈谈整个数据库，以及为什么要注意它。 ClickHouse数据库是Yandex的高性能分析柱数据库。 它用于Yandex服务，最初是Yandex.Metrica的主要数据仓库。 开源系统是免费的。 从开发人员的角度来看，我一直对他们的实现方式很感兴趣，因为那里有大量的大数据。 度量用户界面本身非常灵活且快速。 初识该数据库时，印象是：“好，最后！ 发“为人”！ 从安装过程开始，以发送请求结束。”
该数据库的进入阈值非常低。 即使是普通的开发人员也可以在几分钟内安装该数据库并开始使用它。 一切正常。 即使是Linux新手，也可以快速完成安装并进行简单的操作。 早先，当大数据，Hadoop，Google BigTable，HDFS一词出现时，通常的开发人员就想到了他们谈论的是数TB，PB的问题，一些超人参与了这些系统的设置和开发，然后随着ClickHouse数据库的出现，我们得到了一个简单易懂的工具，可用来解决以前无法完成的一系列任务。 只需一辆相当普通的汽车，五分钟即可安装。 也就是说，我们有一个数据库，例如MySql，但仅用于存储数十亿条记录！ 某种SQL主管。 好像人们得到了外国人的武器。
关于我们的日志收集系统
为了收集信息，使用了标准格式的Web应用程序的IIS日志文件（我们也从事解析应用程序日志的工作，但是与我们一起进行试运行阶段的主要目标是收集IIS日志）。
由于各种原因，我们未能完全放弃ELK堆栈，而我们继续使用LogStash和Filebeat组件，它们已被证明自己性能良好，并且运行十分可靠且可预测。
下图显示了常规的日志记录方案：
将数据写入ClickHouse数据库的功能是不大批量（每秒一次）插入记录。 显然，这是您第一次使用ClickHouse数据库时遇到的最“有问题”的部分：该方案有点复杂。
LogStash插件在这里起到了很大作用，可以直接将数据插入ClickHouse。 该组件与数据库本身部署在同一服务器上。 因此，一般来讲，不建议这样做，而是从实际角度出发，以免在将同一服务器上部署服务器时不产生单独的服务器。 我们没有发现与数据库有任何故障或资源冲突。 此外，应注意的是，插件在发生错误的情况下具有重新启动机制。 并且在出现错误的情况下，该插件将无法插入的数据包写入磁盘（文件格式很方便：编辑后，您可以使用clickhouse-client轻松插入已更正的数据包）。
下表列出了该方案中使用的软件的完整列表：
下表中显示了ClickHouse数据库的服务器配置：
如您所见，这是一个常规工作站。
用于存储日志的表的结构如下：
log_web.sqlCREATE 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;
我们使用默认值进行分区（按月）和索引的粒度。 实际上，所有字段都与用于注册http请求的IIS日志条目相对应。 分别地，用于存储utm标记的单独字段（在从查询字符串字段插入表中的阶段对它们进行了解析）。
还在表中添加了几个系统字段，用于存储有关系统，组件，服务器的信息。 有关这些字段的说明，请参见下表。 在一个表中，我们存储多个系统的日志。
这使您可以在Grafana中有效地构建图形。 例如，查看来自特定系统前端的请求。 这类似于Yandex.Metrica中的站点计数器。
以下是有关使用数据库两个月的一些统计信息。
按系统和组件划分的记录数 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）。 它以尾部模式工作（即，仅将添加的记录传输到文件中）。 但是您可以分别配置整个文件传输。 当您需要从前几个月下载数据时，这很有用。 只需将日志文件放在一个文件夹中，他就会完整地读取它。
当服务停止时，数据将停止进一步传输到存储。
配置示例如下：
filebeat.yml filebeat.inputs: - type: log enabled: true paths: - C:/inetpub/logs/LogFiles/W3SVC1
LogStash 日志采集器
该组件用于接收来自FileBeat的日志条目（或通过RabbitMQ队列），解析分发包并将其插入ClickHouse数据库。
要插入ClickHouse，请使用Logstash-output-clickhouse插件。 Logstash插件具有一种检索请求的机制，但是如果定期关闭，最好停止服务本身。 停止时，消息将累积在RabbitMQ队列中，因此，如果长时间停止，则最好在服务器上停止Filebeats。 在不使用RabbitMQ的方案中（在本地网络上，Filebeat将日志直接发送到Logstash），Filebeats的工作原理是可以接受且安全的，因此对于它们而言，输出不可访问性不会带来任何后果。
配置示例如下：
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
ClickHouse。 日志储存
所有系统的日志都保存在一个表中（请参阅本文的开头）。 它用于存储有关请求的信息：所有参数对于不同的格式都是相似的，例如IIS日志，apache和nginx日志。 对于其中记录了例如错误，信息性消息，警告的应用程序日志，将提供具有相应结构的单独表（现在处于设计阶段）。
设计表时，确定主键（在存储期间对数据进行排序）非常重要。 数据压缩的程度和查询速度取决于此。 在我们的示例中，关键是
ORDER BY（fld_app_name，fld_app_module，logdatetime）
即，通过系统名称，系统组件名称和事件日期。 活动的原始日期排在第一位。 将其移到最后一个位置后，查询开始以大约两倍的速度运行。 更改主键将需要重新创建表并重新加载数据，以便ClickHouse对磁盘上的数据进行重新排序。 这是一项困难的操作，因此建议您提前考虑排序关键字中应包含的内容。
还应注意，相对较新的版本出现了LowCardinality数据类型。 使用它时，对于基数较低的字段（很少的选项），压缩数据的大小会急剧减小。
现在使用版本19.6，我们计划尝试将版本更新为最新版本。 例如，它们包括诸如自适应粒度，跳过索引和DoubleDelta编解码器等出色的功能。
默认情况下，在安装过程中，配置日志级别设置为trace。 日志被轮换和存档，但扩展到1 GB。 如果不需要，则可以设置警告级别，然后日志的大小会急剧减少。 日志设置在config.xml文件中设置：
<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
LogStash FileBeat日志路由器到RabbitMQ队列
该组件用于将来自FileBeat的日志路由到RabbitMQ队列。 有两点：
- 不幸的是，FileBeat没有用于直接写入RabbitMQ的输出插件。 根据ish在其github上的判断，此类功能尚未计划实现。 有一个Kafka插件，但由于某些原因，我们不能在家中使用它。
- 有在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 {
RabbitMQ。 消息队列
此组件用于在DMZ中缓冲日志条目。 通过一堆Filebeat→LogStash进行记录。 通过LogStash从DMZ外部进行读取。 通过RabboitMQ运行时，每秒大约处理4000条消息。
根据系统名称配置消息路由，即基于FileBeat配置数据。 所有消息都属于一个队列。 如果出于某种原因停止了队列服务，则不会导致消息丢失：FileBeats将收到连接错误并暂停临时发送。 从队列中读取的LogStash也将收到网络错误，并等待连接恢复。 当然，在这种情况下，数据将不再写入数据库。
以下说明用于创建和配置队列：
sudo /usr/local/bin/rabbitmqadmin/rabbitmqadmin declare exchange
格拉法纳 仪表板
该组件用于可视化监视数据。 在这种情况下，您必须安装用于Grafana 4.6+插件的ClickHouse数据源。 我们必须对其进行一些调整，以提高在仪表板上处理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相比，这将是昂贵的。
在没有任何特殊优化的情况下，在默认设置下，加载数据和从数据库检索数据的速度非常快。 到目前为止，我们只有少量数据（大约2亿条记录），但是服务器本身很薄弱。 将来，我们可以将此工具用于与日志存储无关的其他目的。 例如，对于端到端分析，在安全领域，机器学习。
最后，介绍了一些利弊。
缺点
- 大捆下载记录。 一方面，这是一项功能，但仍然必须使用其他组件来缓冲记录。 这个任务并不总是那么简单，但是仍然可以解决。 我想简化这个计划。
- 一些奇特的功能或新功能通常会在新版本中中断。 这引起了人们的关注，减少了升级到新版本的愿望。 例如，Kafka表引擎是一项非常有用的功能，可让您直接从Kafka读取事件，而无需使用使用者。 但是从github上的Issues数量来看，我们仍然对在生产中使用此引擎持谨慎态度。 但是，如果您不向侧面剧烈移动并使用基本功能，则它会稳定运行。
优点
- 它不会减慢速度。
- 低进入门槛。
- 开源的
- 它是免费的。
- 很好地扩展（现成的分片/复制）
- 它已包含在通信部推荐的俄文软件注册簿中。
- Yandex提供官方支持。