den-crane

inspired by ClickHouse/ClickHouse#100960

CREATE TABLE test_JSON_max_dynamic_paths0                    
(                                                                    
    `timestamp` DateTime64(3, 'Etc/UTC') CODEC(DoubleDelta, ZSTD(1)),
    `host` LowCardinality(String) CODEC(ZSTD(1)),                    
    `program` LowCardinality(String) CODEC(ZSTD(1)),                 
    `platform` LowCardinality(String) CODEC(ZSTD(1)),                
    `environment` LowCardinality(String) CODEC(ZSTD(1)),             

den-crane

sources:
  sourceKafka:
    type: kafka
    bootstrap_servers: "kraftbroker1.mykafka.localdomain:9092"
    group_id: "kafka_logs"
    topics: ["topic1", "topic2", "topic3"]
    sasl:
      enabled: true
      mechanism: "PLAIN"

den-crane

https://fiddle.clickhouse.com/2fcd76f1-0389-4745-8cc1-ff7a87bfbef3

https://clickhouse.com/docs/sql-reference/data-types/newjson

-- max_dynamic_paths how many real columns to store, excess be will stored in a sigle Map column

CREATE TABLE mt (json JSON(max_dynamic_paths=2048)) 
  ENGINE=MergeTree 
order by tuple();

den-crane

CREATE TABLE test(A Int64, X String)
ENGINE = ReplicatedReplacingMergeTree('/clickhouse/tables/test', 'R1')
ORDER BY A;

system stop merges test;
insert into test values(1, 0);
insert into test values(1, 1);
insert into test values(1, 2);
insert into test values(1, 4);
insert into test values(1, 5);

den-crane

CREATE TABLE test(A Int64)
ENGINE = ReplicatedReplacingMergeTree('/clickhouse/tables/test', '{replica}')
ORDER BY tuple();

R1: insert into test values(1); detach table test;

R2: insert into test values(2); detach table test;

SYSTEM DROP REPLICA 'R1' FROM ZKPATH '/clickhouse/tables/test';
SYSTEM DROP REPLICA 'R2' FROM ZKPATH '/clickhouse/tables/test';

den-crane

https://fiddle.clickhouse.com/fabe3448-a682-4866-af76-90bdf816d74a


CREATE TABLE dummy (date Date, id  String ) 
ENGINE = Null;

CREATE TABLE t (
  date Date,
  idstate13 AggregateFunction(uniqCombined64(13), String),
  idstate17 AggregateFunction(uniqCombined64(17), String),

den-crane

https://fiddle.clickhouse.com/afd6bac1-a4c1-4d51-bed8-2043012861c1

insert into function file(currentDatabase()||'_parallelize_output_from_storages_dedup_test.tsv', 'TSV')
select number, toString(arrayMap(x->cityHash64(x), range(number%11)))
from numbers(10000) order by cityHash64(number);

set min_insert_block_size_bytes=1000000,
    min_chunk_bytes_for_parallel_parsing = 10000;
set parallelize_output_from_storages = 0;

den-crane

Let me explain how ClickHouse can utilize an index in this case, despite the expression intDiv(X, 10) = ? appearing non-sargable at first glance.

The key lies in ClickHouse's ability to perform monotonic function analysis. Here's how it works:

First, let's understand what makes intDiv(X, 10) special. The integer division function is monotonic - as X increases, intDiv(X, 10) either increases or stays the same, never decreases. For example:

X = 45 -> intDiv(X, 10) = 4
X = 46 -> intDiv(X, 10) = 4
X = 50 -> intDiv(X, 10) = 5

den-crane

https://fiddle.clickhouse.com/35bf07da-101e-4e55-a408-1a597099f99b

CREATE TABLE t(
  int64 UInt64 codec(Delta, ZSTD(3)),
  ulid FixedString(26) default generateULID() codec(ZSTD(3)),
  uuid4 UUID default generateUUIDv4() codec(ZSTD(3)),
  Snowflake UInt64 default generateSnowflakeID()  codec(Delta,ZSTD(3)),
  uuid7 UUID default generateUUIDv7() codec(ZSTD(3))
)ENGINE = MergeTree() order by int64;

den-crane

create table t Engine=Memory empty as select * from format('JSONEachRow', '{
  "ID": "<id>",
  "FrameUri": "<uri>",
  "Detections": [
    {
      "CategoryId":  1,
      "ID": "<id>",
      "SegmentationPoints": [
        {
          "X": 1,