ClickHouse Cheatsheet 02 — MergeTree Engines

ENGINE = MergeTree()
ORDER BY (col1, col2)
PARTITION BY toYYYYMM(date)
PRIMARY KEY col1
TTL date + INTERVAL 90 DAY
SETTINGS index_granularity = 8192

ENGINE = ReplacingMergeTree(version)
ORDER BY (user_id, event_id)

SELECT * FROM events FINAL

CREATE TABLE counters (
    date Date, user_id UInt32, count UInt64
) ENGINE = SummingMergeTree()
ORDER BY (date, user_id);

INSERT INTO counters VALUES (today(), 1, 1);
INSERT INTO counters VALUES (today(), 1, 1);
-- After merge: (today, 1, 2)

CREATE TABLE counts (
    day Date,
    state AggregateFunction(count, UInt32)
) ENGINE = AggregatingMergeTree()
ORDER BY day;

CREATE MATERIALIZED VIEW counts_mv TO counts AS
SELECT toDate(ts) AS day, countState() AS state
FROM events GROUP BY day;

-- Query
SELECT day, countMerge(state) FROM counts GROUP BY day;

CREATE TABLE user_state (
    user_id UInt32, active UInt8, sign Int8
) ENGINE = CollapsingMergeTree(sign)
ORDER BY user_id;

INSERT INTO user_state VALUES (1, 1, 1);    -- active
INSERT INTO user_state VALUES (1, 1, -1);   -- delete previous
INSERT INTO user_state VALUES (1, 0, 1);    -- now inactive

ENGINE = ReplicatedMergeTree('/clickhouse/tables/{shard}/events', '{replica}')
ORDER BY ...

CREATE TABLE events_dist AS events
ENGINE = Distributed(cluster_name, db, events, rand())

CREATE TABLE events_buffer AS events ENGINE = Buffer(db, events, 16, 10, 100, 10000, 1000000, 10000000, 100000000)

CREATE TABLE local_csv (...) ENGINE = File(CSV, '/path/data.csv');

CREATE TABLE kafka_events (...)
ENGINE = Kafka()
SETTINGS kafka_broker_list = 'kafka:9092',
         kafka_topic_list = 'events',
         kafka_group_name = 'ch_consumer',
         kafka_format = 'JSONEachRow';

CREATE MATERIALIZED VIEW events_consumer TO events AS
SELECT * FROM kafka_events;

CREATE TABLE s3_data
ENGINE = S3('https://bucket.s3.../data.parquet', 'Parquet');

SELECT * FROM s3('https://...', 'Parquet') LIMIT 10;