Pirsch Schema

schema.sql

CREATE TABLE pirsch.event
(
    `client_id` UInt64,
    `time` DateTime64(3, 'UTC'),
    `duration_seconds` UInt32 DEFAULT 0,
    `path` String,
    `language` LowCardinality(String),
    `country_code` LowCardinality(FixedString(2)),
    `referrer` String,
    `referrer_name` String,
    `referrer_icon` String,
    `os` LowCardinality(String),
    `os_version` LowCardinality(String),
    `browser` LowCardinality(String),
    `browser_version` LowCardinality(String),
    `desktop` Int8 DEFAULT 0,
    `mobile` Int8 DEFAULT 0,
    `screen_class` LowCardinality(String),
    `utm_source` String,
    `utm_medium` String,
    `utm_campaign` String,
    `utm_content` String,
    `utm_term` String,
    `event_name` String,
    `event_meta_keys` Array(String),
    `event_meta_values` Array(String),
    `title` String,
    `session_id` UInt32 DEFAULT 0,
    `city` String,
    `visitor_id` UInt64,
    `region` LowCardinality(String),
    `hostname` String,
    `channel` LowCardinality(String)
)
ENGINE = ReplicatedMergeTree('/clickhouse/tables/event/{shard}',
 '{replica}')
PARTITION BY toYYYYMM(time)
ORDER BY (client_id,
 visitor_id,
 session_id,
 time)
SAMPLE BY visitor_id
SETTINGS index_granularity = 8192;

CREATE TABLE pirsch.page_view
(
    `client_id` UInt64,
    `visitor_id` UInt64,
    `session_id` UInt32 DEFAULT 0,
    `time` DateTime64(3, 'UTC'),
    `duration_seconds` UInt32 DEFAULT 0,
    `path` String,
    `title` String,
    `language` LowCardinality(String),
    `country_code` LowCardinality(FixedString(2)),
    `city` String,
    `referrer` String DEFAULT '',
    `referrer_name` String DEFAULT '',
    `referrer_icon` String DEFAULT '',
    `os` LowCardinality(String),
    `os_version` LowCardinality(String),
    `browser` LowCardinality(String),
    `browser_version` LowCardinality(String),
    `desktop` Int8 DEFAULT 0,
    `mobile` Int8 DEFAULT 0,
    `screen_class` LowCardinality(String),
    `utm_source` String DEFAULT '',
    `utm_medium` String DEFAULT '',
    `utm_campaign` String DEFAULT '',
    `utm_content` String DEFAULT '',
    `utm_term` String DEFAULT '',
    `tag_keys` Array(String),
    `tag_values` Array(String),
    `region` LowCardinality(String),
    `hostname` String,
    `channel` LowCardinality(String)
)
ENGINE = ReplicatedMergeTree('/clickhouse/tables/page_view/{shard}',
 '{replica}')
PARTITION BY toYYYYMM(time)
ORDER BY (client_id,
 visitor_id,
 session_id,
 time)
SAMPLE BY visitor_id
SETTINGS index_granularity = 8192;

CREATE TABLE pirsch.session
(
    `sign` Int8,
    `version` UInt16,
    `client_id` UInt64,
    `visitor_id` UInt64,
    `session_id` UInt32,
    `time` DateTime64(3, 'UTC'),
    `start` DateTime64(3, 'UTC'),
    `duration_seconds` UInt32,
    `is_bounce` Int8,
    `entry_path` String,
    `exit_path` String,
    `entry_title` String,
    `exit_title` String,
    `page_views` UInt16,
    `language` LowCardinality(String),
    `country_code` LowCardinality(FixedString(2)),
    `region` LowCardinality(String),
    `city` String,
    `referrer` String,
    `referrer_name` String,
    `referrer_icon` String,
    `os` LowCardinality(String),
    `os_version` LowCardinality(String),
    `browser` LowCardinality(String),
    `browser_version` LowCardinality(String),
    `desktop` Int8,
    `mobile` Int8,
    `screen_class` LowCardinality(String),
    `utm_source` String,
    `utm_medium` String,
    `utm_campaign` String,
    `utm_content` String,
    `utm_term` String,
    `extended` UInt16 DEFAULT 0,
    `hostname` String,
    `channel` LowCardinality(String)
)
ENGINE = ReplicatedVersionedCollapsingMergeTree('/clickhouse/tables/session/{shard}',
 '{replica}',
 sign,
 version)
PARTITION BY toYYYYMM(time)
ORDER BY (client_id,
 visitor_id,
 session_id,
 time)
SAMPLE BY visitor_id
SETTINGS index_granularity = 8192;