Skip to content

Instantly share code, notes, and snippets.

View den-crane's full-sized avatar
🙀
What's happening?

Denny [DBA at Innervate] den-crane

🙀
What's happening?
View GitHub Profile
@den-crane
den-crane / Clickhouse_vector_dev_Kafka_source_example.md
Last active June 3, 2025 14:24
Clickhouse vector.dev Kafka source example
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
den-crane / gist:4bf80c86aa7ba58aae199791bdb5e3d5
Last active June 2, 2025 22:27
ClickHouse JSON examples
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);
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';
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),
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
den-crane / Clickhouse_index_leverage_monotonic_functions.md
Created January 31, 2025 23:39
Clickhouse index leverage for monotonic functions

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
den-crane / gist:4676ad82e7c6db0db4866ddbd111159b
Last active June 2, 2024 22:52
uuid4, uuid7, SnowflakeID, ULID compression rate

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;
create table t Engine=Memory empty as select * from format('JSONEachRow', '{
"ID": "<id>",
"FrameUri": "<uri>",
"Detections": [
{
"CategoryId": 1,
"ID": "<id>",
"SegmentationPoints": [
{
"X": 1,
2024.03.08 03:54:38.022312 [ 141144 ] {} <Trace> SystemLog (system.query_log): Flushing system log, 2 entries to flush up to offset 86304192
2024.03.08 03:54:38.024139 [ 140747 ] {} <Trace> BaseDaemon: Received signal 11
2024.03.08 03:54:38.024287 [ 930727 ] {} <Fatal> BaseDaemon: ########## Short fault info ############
2024.03.08 03:54:38.024679 [ 930727 ] {} <Fatal> BaseDaemon: (version 24.1.3.31 (official build), build id: E65ACEFD4C4A4F209A1529998C6032754B52A0FC, git hash: 135b08cbd28a5832e9e70c3b7d09dd4134845ed3) (from thread 141144) Received signal 11
2024.03.08 03:54:38.024693 [ 930727 ] {} <Fatal> BaseDaemon: Signal description: Segmentation fault
2024.03.08 03:54:38.024700 [ 930727 ] {} <Fatal> BaseDaemon: Address: 0x70. Access: read. Address not mapped to object.
2024.03.08 03:54:38.024705 [ 930727 ] {} <Fatal> BaseDaemon: Stack trace: 0x0000000007234f2e 0x0000000010d3bdd3 0x00000000114c0215 0x00000000114c39f2 0x000000001170b275 0x000000000c931314 0x000000000c8ee83e 0x00007f5233abdfd4 0x00007f5233b