[Plausible Analytics] Clickhouse primary key migration

migration.sql

-- new events table
CREATE TABLE events_v2
(
    `timestamp` DateTime Codec(DoubleDelta, LZ4),
    `name` String,
    `domain` String,
    `user_id` UInt64,
    `session_id` UInt64,
    `hostname` String,
    `pathname` String,
    `referrer` String,
    `referrer_source` String,
    `country_code` LowCardinality(FixedString(2)),
    `screen_size` LowCardinality(String),
    `operating_system` LowCardinality(String),
    `browser` LowCardinality(String),
    `utm_medium` String,
    `utm_source` String,
    `utm_campaign` String,
    `meta.key` Array(String),
    `meta.value` Array(String),
    `browser_version` LowCardinality(String),
    `operating_system_version` LowCardinality(String)
)
ENGINE = MergeTree
PARTITION BY toYYYYMM(timestamp)
ORDER BY (domain, toDate(timestamp), user_id)
SAMPLE BY user_id
SETTINGS index_granularity = 8192;

-- new sessions table
CREATE TABLE sessions_v2
(
    `session_id` UInt64,
    `sign` Int8,
    `domain` String,
    `user_id` UInt64,
    `hostname` String,
    `timestamp` DateTime Codec(DoubleDelta, LZ4),
    `start` DateTime Codec(DoubleDelta, LZ4),
    `is_bounce` UInt8,
    `entry_page` String,
    `exit_page` String,
    `pageviews` Int32,
    `events` Int32,
    `duration` UInt32,
    `referrer` String,
    `referrer_source` String,
    `country_code` LowCardinality(FixedString(2)),
    `screen_size` LowCardinality(String),
    `operating_system` LowCardinality(String),
    `browser` LowCardinality(String),
    `utm_medium` String,
    `utm_source` String,
    `utm_campaign` String,
    `browser_version` LowCardinality(String),
    `operating_system_version` LowCardinality(String)
)
ENGINE = CollapsingMergeTree(sign)
PARTITION BY toYYYYMM(start)
ORDER BY (domain,  toDate(start),  user_id, session_id)
SAMPLE BY user_id
SETTINGS index_granularity = 8192;

-- insert data to new tables

INSERT INTO events_v2 SELECT * FROM events;
INSERT INTO sessions_v2 SELECT * FROM sessions;

-- rename old tabes
RENAME TABLE events TO events_v1;
RENAME TABLE sessions TO sessions_v1;

-- rename new tabes
RENAME TABLE events_v2 TO events;
RENAME TABLE sessions_v2 TO sessions;

-- optional: drop data from old tables
-- DROP TABLE events_v1;
-- DROP TABLE sessions_v1;