title: Clickhouse - data transformation/parsing
body: <p>We are using Clickhouse to store HAProxy and Kong logs and metrics.</p>
  
  <p>The "pipeline" is built around the syslog protocol and rsyslog, as follow : HAProxy/Kong -> local rsyslog -> remote rsyslog (TCP) -> omclickhouse rsyslog module -> clickhouse.</p>
  
  <p>The format of the syslog messages of course differs between HAProxy and Kong.</p>
  
  <p>HAProxy messages look like this :</p>
  
  <pre><code>1.2.3.4:58629 [06/Jun/2020:14:54:59.932] HTTPS~ HAPBACKEND/HAPSERVER 0/0/1/36/37 200 778 - - ---- 32/32/1/1/0 0/0 "GET /api/map/v2/GetSomeStuff/json?Latitude=47.22960133109915&amp;Longitude=-1.5727845858797176 HTTP/1.1"
  </code></pre>
  
  <p>as described here : <a href="https://cbonte.github.io/haproxy-dconv/1.7/configuration.html#8.2.3" rel="nofollow noreferrer">https://cbonte.github.io/haproxy-dconv/1.7/configuration.html#8.2.3</a> , </p>
  
  <p>Kong messages are JSON-based and look like this :</p>
  
  <pre><code>{
      "request": {
          "method": "GET",
          "uri": "/get",
          "url": "http://httpbin.org:8000/get",
          "size": "75",
          "querystring": {},
          "headers": {
              "accept": "*/*",
              "host": "httpbin.org",
              "user-agent": "curl/7.37.1"
          },
          "tls": {
              "version": "TLSv1.2",
  </code></pre>
  
  <p>as described here : <a href="https://docs.konghq.com/hub/kong-inc/syslog/" rel="nofollow noreferrer">https://docs.konghq.com/hub/kong-inc/syslog/</a></p>
  
  <p>The rsyslog omclickhouse module inserts (by default) all the syslog message into a table named "SystemEvents", which has the following structure :</p>
  
  <pre><code>┌─severity─┬─facility─┬───────────timestamp─┬─hostname─────────────────┬─tag────────────┬─message──────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐
  │        6 │       18 │ 2020-06-06 15:01:00 │ reverseproxy.fqdn        │ haproxy[6892]: │  1.2.3.4:57220 [06/Jun/2020:15:00:59.996] HTTPS~ HAPBACKEND/HAPSRV 15/0/1/2/18 500 617 - - ---- 48/42/9/9/0 0/0 "POST /SOAPService HTTP/1.1" │
  └──────────┴──────────┴─────────────────────┴──────────────────────────┴────────────────┴──────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘
  </code></pre>
  
  <p>(We didn't want to get into the development of a custom rsyslog parsing C module)</p>
  
  <p>For reporting purposes, we are interested in the HAProxy (or Kong) details contained in the syslog message field, rather than in the whole syslog content itself.
  So, to be able to get "fine-grained" querying capabilities, we created another table, say "HAPROXY_LOGS", with the following structure :</p>
  
  <pre><code>(`CLIENT_IP` String, `CLIENT_PORT` Int32, `REQUEST_DATE` DateTime, `FRONTEND_NAME` String, `BACKEND_NAME` String, `SERVER_NAME` String, `TREQ` Int32, `TWAIT` Int32, `TCONNECTION` Int32, `TRESPONSE` Int32, `TACTIVE` Int32, `STATUS_CODE` Int32, `BYTES_READ` Int32, `CAPTURED_REQUEST_COOKIE` String, `CAPTURED_RESPONSE_COOKIE` String, `TERMINATION_STATE` String, `ACTCONN` Int32, `FECONN` Int32, `BECONN` Int32, `SRV_CONN` Int32, `RETRIES` Int32, `SRV_QUEUE` Int32, `BACKEND_QUEUE` Int32, `METHOD` String, `REQUEST` String, `PARAMETERS` String, `PROTOCOL` String) ENGINE = MergeTree() PARTITION BY toYYYYMM(REQUEST_DATE) ORDER BY (REQUEST_DATE, TRESPONSE, STATUS_CODE, PARAMETERS) SETTINGS index_granularity = 8192
  </code></pre>
  
  <p>This is where the things start to, well, get weirder...
  Clickhouse itself does not seems to provide neither some kind of scheduler, à la MSSQL, nor a way to embed a programming langage into the engine (PL/pgSQL, PL/Python - like), nor triggers (we didn't investigated materialized views yet).
  So, to transform and move the data from one table to another, a shell script is launched by cron every minute, use clickhouse-client to get the input data, pipe it to a Python script, whose result itself is then piped again to clickhouse-client for insertion :</p>
  
  <pre><code>* * * * * { /usr/bin/clickhouse-client &lt; /path/clkh/extract-system-events.sql | /path/clkh/latestmessages-to-TSV-pipe.py 2&gt;/path/clkh/errors-haproxy.log ; } |/usr/bin/clickhouse-client --query="INSERT INTO HAPROXY_LOGS FORMAT TSV" &gt;&gt; /var/log/latestmessages-to-TSV-pipe.log
  </code></pre>
  
  <p>The Python script is different for HAProxy and Kong parsing.</p>
  
  <p>Sounds like a dirty hack...</p>
  
  <p>Is there a better way to accomplish the same things ?</p>
  
  <p>(Despite this hack, the whole stuff works great, reporting build time was reduced by a large factor, Clickhouse stores 600M+ rows without any issue.)</p>
  
  <p>Thanks</p>