ClickHouse table schema

[kobsio]

ClickHouse table schema

[ricoberger]

Hi @UnamedRus thanks for your help and suggestions. They are very welcome 🙂.

The current schema is our first try and works quite nice. Since we are not that experienced with ClickHouse we are definitely open for improvements. Could you imagine to create a PR for the schema adjustments?

Regarding your questions:

1. Yes host is the hostname of the k8s node. We were not sure if we should include it in the order by clause or not, because searching through the logs of a specific host is a rare use case for us. What do you think about?
2. We are trying to reverse the timestamp order because most of the queries are using ORDER BY timestamp DESC. Does this makes sense? Currently the logs are retrieved as follows:

The user can provide a query (e.g. namespace='kobs' _and_ app='kobs' _and_ container_name='kobs') via the kobs ui, which is comparable with Kibana for Elasticsearch.

Based on the user input we are running the following query to create a list of buckets in the selected time range:

SELECT
  toStartOfInterval(timestamp, INTERVAL 30 second) AS interval_data,
  count(*) AS count_data
FROM
  logs.logs
WHERE
  timestamp >= FROM_UNIXTIME(1641923841)
  AND timestamp <= FROM_UNIXTIME(1641924741)
  AND namespace='kobs'
  AND app='kobs'
  AND container_name='kobs'
GROUP BY
  interval_data
ORDER BY
  interval_data
WITH FILL
  FROM toStartOfInterval(FROM_UNIXTIME(1641923841), INTERVAL 30 second)
  TO toStartOfInterval(FROM_UNIXTIME(1641924741), INTERVAL 30 second)
STEP
  30

The returned data is then used to render the buckets chart in the UI and looks as follows:

[{"interval":1641923820,"count":0},{"interval":1641923850,"count":0},{"interval":1641923880,"count":0},{"interval":1641923910,"count":0},{"interval":1641923940,"count":0},{"interval":1641923970,"count":0},{"interval":1641924000,"count":0},{"interval":1641924030,"count":0},{"interval":1641924060,"count":4},{"interval":1641924090,"count":0},{"interval":1641924120,"count":0},{"interval":1641924150,"count":0},{"interval":1641924180,"count":4},{"interval":1641924210,"count":1},{"interval":1641924240,"count":0},{"interval":1641924270,"count":0},{"interval":1641924300,"count":0},{"interval":1641924330,"count":0},{"interval":1641924360,"count":0},{"interval":1641924390,"count":0},{"interval":1641924420,"count":0},{"interval":1641924450,"count":0},{"interval":1641924480,"count":0},{"interval":1641924510,"count":0},{"interval":1641924540,"count":0},{"interval":1641924570,"count":0},{"interval":1641924600,"count":0},{"interval":1641924630,"count":0},{"interval":1641924660,"count":0},{"interval":1641924690,"count":0}]

We are then using this list to create the query to get the logs from ClickHouse. For that we are only looking into the intervals were the count is larger then 0, which was a good optimization for large time intervals with a small number of logs (<1000).

SELECT
  timestamp,
  cluster,
  namespace,
  app,
  pod_name,
  container_name,
  host,
  fields_string.key,
  fields_string.value,
  fields_number.key,
  fields_number.value,
  log
FROM
  logs.logs
WHERE
  (
    (timestamp >= FROM_UNIXTIME(1641924210) AND timestamp <= FROM_UNIXTIME(1641924240))
    OR (timestamp >= FROM_UNIXTIME(1641924180) AND timestamp <= FROM_UNIXTIME(1641924210))
    OR (timestamp >= FROM_UNIXTIME(1641924060) AND timestamp <= FROM_UNIXTIME(1641924090))
  )
  AND namespace='kobs'
  AND app='kobs'
  AND container_name='kobs'
ORDER BY
  timestamp DESC
LIMIT
  1000

3. Your provided query returns the following results for one of the ClickHouse nodes (the results on the other nodes are looking very similar):

┌─database─┬─table──────┬─column──────────────────────────┬─type───────────────────┬───────rows─┬─compressed_bytes─┬─compressed──┬─uncompressed─┬──────────────ratio─┬─codec────────────────────┐
│ logs     │ logs_local │ fields_string.key               │ Array(String)          │ 2329771851 │     358178446573 │ 333.58 GiB  │ 2.86 TiB     │  8.784910317881737 │ CODEC(ZSTD(1))           │
│ logs     │ logs_local │ fields_string.value             │ Array(String)          │ 2329771851 │     350285240753 │ 326.23 GiB  │ 2.35 TiB     │  7.366783332109033 │ CODEC(ZSTD(1))           │
│ logs     │ logs_local │ log                             │ String                 │ 2329771851 │     303909067614 │ 283.04 GiB  │ 2.23 TiB     │   8.05202209102257 │ CODEC(ZSTD(1))           │
│ logs     │ logs_local │ fields_number.key               │ Array(String)          │ 2329771851 │      16199072105 │ 15.09 GiB   │ 176.53 GiB   │ 11.701033451940425 │ CODEC(ZSTD(1))           │
│ logs     │ logs_local │ fields_number.value             │ Array(Float64)         │ 2329771851 │      12967873005 │ 12.08 GiB   │ 60.11 GiB    │  4.977477356781071 │ CODEC(ZSTD(1))           │
│ logs     │ logs_local │ content.response_code           │ Float64                │ 2329771851 │       2621257346 │ 2.44 GiB    │ 17.36 GiB    │  7.110369504330232 │                          │
│ logs     │ logs_local │ content.loggerName              │ String                 │ 2329771851 │       1211619167 │ 1.13 GiB    │ 17.37 GiB    │  15.39396583018895 │                          │
│ logs     │ logs_local │ timestamp                       │ DateTime64(3)          │ 2329771851 │        678452055 │ 647.02 MiB  │ 17.36 GiB    │  27.47151867054187 │ CODEC(Delta(8), ZSTD(1)) │
│ logs     │ logs_local │ content.contextMap.branch.appID │ String                 │ 2329771851 │        582468072 │ 555.48 MiB  │ 3.84 GiB     │  7.083428064706008 │                          │
│ logs     │ logs_local │ content.level                   │ String                 │ 2329771851 │        245835895 │ 234.45 MiB  │ 3.69 GiB     │ 16.101778314350717 │                          │
│ logs     │ logs_local │ host                            │ String                 │ 2329771851 │         67291481 │ 64.17 MiB   │ 82.48 GiB    │  1316.036386478104 │ CODEC(ZSTD(1))           │
│ logs     │ logs_local │ pod_name                        │ String                 │ 2329771851 │         57316257 │ 54.66 MiB   │ 66.39 GiB    │  1243.760170958128 │ CODEC(ZSTD(1))           │
│ logs     │ logs_local │ app                             │ String                 │ 2329771851 │         21185633 │ 20.20 MiB   │ 32.99 GiB    │ 1671.7998898121193 │ CODEC(ZSTD(1))           │
│ logs     │ logs_local │ container_name                  │ String                 │ 2329771851 │         16967547 │ 16.18 MiB   │ 27.96 GiB    │ 1769.4741842176716 │ CODEC(ZSTD(1))           │
│ logs     │ logs_local │ namespace                       │ LowCardinality(String) │ 2329771851 │          9309111 │ 8.88 MiB    │ 2.19 GiB     │  252.3147725921412 │ CODEC(ZSTD(1))           │
│ logs     │ logs_local │ cluster                         │ LowCardinality(String) │ 2329771851 │          9181883 │ 8.76 MiB    │ 2.19 GiB     │  255.7960722217872 │ CODEC(ZSTD(1))           │
└──────────┴────────────┴─────────────────────────────────┴────────────────────────┴────────────┴──────────────────┴─────────────┴──────────────┴────────────────────┴──────────────────────────┘

[UnamedRus]

Can you also show result of this query:

SELECT uniqCombinedArray(fields_string.key), uniqCombinedArray(fields_string.value), uniqCombinedArray(fields_number.key), count() FROM logs.logs_local WHERE timestamp  > '2022-01-01';

We are trying to reverse the timestamp order because most of the queries are using ORDER BY timestamp DESC. Does this makes sense? Currently the logs are retrieved as follows:

In general, it's only required for 2 things:

1. Place similar data near by, (improve compression) but for this case it really doesn't matter in which exact order you do this.
2. read_in_order optimizations.

https://clickhouse.com/docs/en/sql-reference/statements/select/order-by/#optimize_read_in_order
https://clickhouse.com/docs/en/sql-reference/statements/select/group-by/#aggregation-in-order

But for your case (when you do simple ORDER BY timestamp DESC) they doesn't work( for now :) )

ClickHouse/ClickHouse#7102
ClickHouse/ClickHouse#32748

Does this makes sense?

But if we will assume that they work for you now.
There were some cases, when reading in reverse order worked worse than in direct order. So it's better to test.
ClickHouse/ClickHouse#16250

[ricoberger]

Sure, here are the results:

┌─uniqCombinedArray(fields_string.key)─┬─uniqCombinedArray(fields_string.value)─┬─uniqCombinedArray(fields_number.key)─┬───count()─┐
│                                35392 │                             1665223720 │                                 6418 │ 680701758 │
└──────────────────────────────────────┴────────────────────────────────────────┴──────────────────────────────────────┴───────────┘

1 rows in set. Elapsed: 767.066 sec. Processed 680.70 million rows, 2.37 TB (887.41 thousand rows/s., 3.09 GB/s.)

[ricoberger]

Hi @UnamedRus, thanks again for all your suggestions 🙂.

We updated the table schema accordingly, so that I would close the issue for now.

If you have further suggestions feel free to open a new issue.