ClickHouse قاعدة بيانات للبشر ، أو التكنولوجيا الغريبة

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; 

 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.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: ~ 

 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" } } } 

 # 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" 

       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 

 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 } }