ClickHouse Cheat Sheet

CREATE TABLE orders (
  id UInt64,
  customer_id UInt64,
  total Decimal(10, 2),
  created_at DateTime
)
ENGINE = MergeTree
PARTITION BY toYYYYMM(created_at)
ORDER BY (customer_id, created_at);

CREATE TABLE orders_backup AS orders;            -- structure only
INSERT INTO orders_backup SELECT * FROM orders;  -- then the rows

RENAME TABLE orders TO customer_orders;

ALTER TABLE orders ADD COLUMN status LowCardinality(String) DEFAULT 'new';

ALTER TABLE orders DROP COLUMN status;

ALTER TABLE orders MODIFY COLUMN total Decimal(12, 2);

TRUNCATE TABLE orders;

DESCRIBE TABLE orders;
SHOW CREATE TABLE orders;

INSERT INTO orders (id, customer_id, total, created_at)
VALUES (1, 10, 49.90, now());

INSERT INTO orders_archive SELECT * FROM orders WHERE created_at < now() - INTERVAL 1 YEAR;

clickhouse-client --query "INSERT INTO orders FORMAT CSVWithNames" < orders.csv

ALTER TABLE orders UPDATE status = 'shipped' WHERE id = 42;

ALTER TABLE orders DELETE WHERE created_at < now() - INTERVAL 2 YEAR;

DELETE FROM orders WHERE id = 42;

SELECT mutation_id, command, is_done, latest_fail_reason
FROM system.mutations WHERE table = 'orders' AND is_done = 0;

CREATE TABLE orders (
  id UInt64, total Decimal(10, 2), updated_at DateTime
) ENGINE = ReplacingMergeTree(updated_at) ORDER BY id;

INSERT INTO orders VALUES (1, 59.90, now());  -- newest wins after merge

SELECT * FROM orders FINAL;

CREATE VIEW recent_orders AS
SELECT * FROM orders WHERE created_at > now() - INTERVAL 30 DAY;

CREATE MATERIALIZED VIEW daily_totals
ENGINE = SummingMergeTree ORDER BY day
AS SELECT toDate(created_at) AS day, sum(total) AS total
FROM orders GROUP BY day;

INSERT INTO daily_totals
SELECT toDate(created_at) AS day, sum(total) FROM orders GROUP BY day;

CREATE MATERIALIZED VIEW daily_totals
REFRESH EVERY 1 HOUR
ENGINE = MergeTree ORDER BY day
AS SELECT toDate(created_at) AS day, sum(total) FROM orders GROUP BY day;

SELECT today(), now(), now64(3);

SELECT toStartOfMonth(created_at) AS month, count()
FROM orders GROUP BY month ORDER BY month;

SELECT created_at + INTERVAL 7 DAY,
       dateDiff('hour', created_at, delivered_at)
FROM orders;

SELECT formatDateTime(created_at, '%Y-%m-%d %H:%M') FROM orders;

SELECT customer_id, groupArray(sku) FROM order_items GROUP BY customer_id;

SELECT id, tag FROM orders ARRAY JOIN tags AS tag;

SELECT uniq(customer_id), uniqExact(customer_id) FROM orders;

SELECT sumIf(total, status = 'shipped'), countIf(total > 100) FROM orders;

CREATE DATABASE IF NOT EXISTS analytics;

SHOW DATABASES;
SHOW TABLES FROM analytics;

SELECT table,
       formatReadableSize(sum(bytes_on_disk)) AS size,
       sum(rows) AS rows
FROM system.parts WHERE active GROUP BY table ORDER BY sum(bytes_on_disk) DESC;

ALTER TABLE orders DROP PARTITION '202601';

ALTER TABLE orders MODIFY TTL created_at + INTERVAL 90 DAY;

SELECT query_id, elapsed, formatReadableSize(memory_usage), query
FROM system.processes ORDER BY elapsed DESC;

KILL QUERY WHERE query_id = 'a1b2c3d4-0000-0000-0000-000000000000';

GRANT SELECT ON analytics.* TO analyst;

SELECT version(), currentDatabase(), currentUser();