Initial commitHEADmain

Created from https://vercel.com/new

1 files changed, 283 insertions, 0 deletions

diff --git a/db/clickhouse/schema.sql b/db/clickhouse/schema.sql
new file mode 100644
index 0000000..e916032
--- /dev/null
+++ b/db/clickhouse/schema.sql
@@ -0,0 +1,283 @@
+-- Create Event
+CREATE TABLE umami.website_event
+(
+    website_id UUID,
+    session_id UUID,
+    visit_id UUID,
+    event_id UUID,
+    --sessions
+    hostname LowCardinality(String),
+    browser LowCardinality(String),
+    os LowCardinality(String),
+    device LowCardinality(String),
+    screen LowCardinality(String),
+    language LowCardinality(String),
+    country LowCardinality(String),
+    region LowCardinality(String),
+    city String,
+    --pageviews
+    url_path String,
+    url_query String,
+    utm_source String,
+    utm_medium String,
+    utm_campaign String,
+    utm_content String,
+    utm_term String,
+    referrer_path String,
+    referrer_query String,
+    referrer_domain String,
+    page_title String,
+    --clickIDs
+    gclid String,
+    fbclid String,
+    msclkid String,
+    ttclid String,
+    li_fat_id String,
+    twclid String,
+    --events
+    event_type UInt32,
+    event_name String,
+    tag String,
+    distinct_id String,
+    created_at DateTime('UTC'),
+    job_id Nullable(UUID)
+)
+ENGINE = MergeTree
+    PARTITION BY toYYYYMM(created_at)
+    ORDER BY (toStartOfHour(created_at), website_id, session_id, visit_id, created_at)
+    PRIMARY KEY (toStartOfHour(created_at), website_id, session_id, visit_id)
+    SETTINGS index_granularity = 8192;
+
+CREATE TABLE umami.event_data
+(
+    website_id UUID,
+    session_id UUID,
+    event_id UUID,
+    url_path String,
+    event_name String,
+    data_key String,
+    string_value Nullable(String),
+    number_value Nullable(Decimal(22, 4)),
+    date_value Nullable(DateTime('UTC')),
+    data_type UInt32,
+    created_at DateTime('UTC'),
+    job_id Nullable(UUID)
+)
+ENGINE = MergeTree
+    ORDER BY (website_id, event_id, data_key, created_at)
+    SETTINGS index_granularity = 8192;
+
+CREATE TABLE umami.session_data
+(
+    website_id UUID,
+    session_id UUID,
+    data_key String,
+    string_value Nullable(String),
+    number_value Nullable(Decimal(22, 4)),
+    date_value Nullable(DateTime('UTC')),
+    data_type UInt32,
+    distinct_id String,
+    created_at DateTime('UTC'),
+    job_id Nullable(UUID)
+)
+ENGINE = ReplacingMergeTree
+    ORDER BY (website_id, session_id, data_key)
+    SETTINGS index_granularity = 8192;
+
+-- stats hourly
+CREATE TABLE umami.website_event_stats_hourly
+(
+    website_id UUID,
+    session_id UUID,
+    visit_id UUID,
+    hostname SimpleAggregateFunction(groupArrayArray, Array(String)),
+    browser LowCardinality(String),
+    os LowCardinality(String),
+    device LowCardinality(String),
+    screen LowCardinality(String),
+    language LowCardinality(String),
+    country LowCardinality(String),
+    region LowCardinality(String),
+    city String,
+    entry_url AggregateFunction(argMin, String, DateTime('UTC')),
+    exit_url AggregateFunction(argMax, String, DateTime('UTC')),
+    url_path SimpleAggregateFunction(groupArrayArray, Array(String)),
+    url_query SimpleAggregateFunction(groupArrayArray, Array(String)),
+    utm_source SimpleAggregateFunction(groupArrayArray, Array(String)),
+    utm_medium SimpleAggregateFunction(groupArrayArray, Array(String)),
+    utm_campaign SimpleAggregateFunction(groupArrayArray, Array(String)),
+    utm_content SimpleAggregateFunction(groupArrayArray, Array(String)),
+    utm_term SimpleAggregateFunction(groupArrayArray, Array(String)),
+    referrer_domain SimpleAggregateFunction(groupArrayArray, Array(String)),
+    page_title SimpleAggregateFunction(groupArrayArray, Array(String)),
+    gclid SimpleAggregateFunction(groupArrayArray, Array(String)),
+    fbclid SimpleAggregateFunction(groupArrayArray, Array(String)),
+    msclkid SimpleAggregateFunction(groupArrayArray, Array(String)),
+    ttclid SimpleAggregateFunction(groupArrayArray, Array(String)),
+    li_fat_id SimpleAggregateFunction(groupArrayArray, Array(String)),
+    twclid SimpleAggregateFunction(groupArrayArray, Array(String)),
+    event_type UInt32,
+    event_name SimpleAggregateFunction(groupArrayArray, Array(String)),
+    views SimpleAggregateFunction(sum, UInt64),
+    min_time SimpleAggregateFunction(min, DateTime('UTC')),
+    max_time SimpleAggregateFunction(max, DateTime('UTC')),
+    tag SimpleAggregateFunction(groupArrayArray, Array(String)),
+    distinct_id String,
+    created_at Datetime('UTC')
+)
+ENGINE = AggregatingMergeTree
+    PARTITION BY toYYYYMM(created_at)
+    ORDER BY (
+        website_id,
+        event_type,
+        toStartOfHour(created_at),
+        cityHash64(visit_id),
+        visit_id
+    )
+    SAMPLE BY cityHash64(visit_id);
+
+CREATE MATERIALIZED VIEW umami.website_event_stats_hourly_mv
+TO umami.website_event_stats_hourly
+AS
+SELECT
+    website_id,
+    session_id,
+    visit_id,
+    hostnames as hostname,
+    browser,
+    os,
+    device,
+    screen,
+    language,
+    country,
+    region,
+    city,
+    entry_url,
+    exit_url,
+    url_paths as url_path,
+    url_query,
+    utm_source,
+    utm_medium,
+    utm_campaign,
+    utm_content,
+    utm_term,
+    referrer_domain,
+    page_title,
+    gclid,
+    fbclid,
+    msclkid,
+    ttclid,
+    li_fat_id,
+    twclid,
+    event_type,
+    event_name,
+    views,
+    min_time,
+    max_time,
+    tag,
+    distinct_id,
+    timestamp as created_at
+FROM (SELECT
+    website_id,
+    session_id,
+    visit_id,
+    arrayFilter(x -> x != '', groupArray(hostname)) hostnames,
+    browser,
+    os,
+    device,
+    screen,
+    language,
+    country,
+    region,
+    city,
+    argMinState(url_path, created_at) entry_url,
+    argMaxState(url_path, created_at) exit_url,
+    arrayFilter(x -> x != '', groupArray(url_path)) as url_paths,
+    arrayFilter(x -> x != '', groupArray(url_query)) url_query,
+    arrayFilter(x -> x != '', groupArray(utm_source)) utm_source,
+    arrayFilter(x -> x != '', groupArray(utm_medium)) utm_medium,
+    arrayFilter(x -> x != '', groupArray(utm_campaign)) utm_campaign,
+    arrayFilter(x -> x != '', groupArray(utm_content)) utm_content,
+    arrayFilter(x -> x != '', groupArray(utm_term)) utm_term,
+    arrayFilter(x -> x != '' and x != hostname, groupArray(referrer_domain)) referrer_domain,
+    arrayFilter(x -> x != '', groupArray(page_title)) page_title,
+    arrayFilter(x -> x != '', groupArray(gclid)) gclid,
+    arrayFilter(x -> x != '', groupArray(fbclid)) fbclid,
+    arrayFilter(x -> x != '', groupArray(msclkid)) msclkid,
+    arrayFilter(x -> x != '', groupArray(ttclid)) ttclid,
+    arrayFilter(x -> x != '', groupArray(li_fat_id)) li_fat_id,
+    arrayFilter(x -> x != '', groupArray(twclid)) twclid,
+    event_type,
+    if(event_type = 2, groupArray(event_name), []) event_name,
+    sumIf(1, event_type != 2) views,
+    min(created_at) min_time,
+    max(created_at) max_time,
+    arrayFilter(x -> x != '', groupArray(tag)) tag,
+    distinct_id,
+    toStartOfHour(created_at) timestamp
+FROM umami.website_event
+GROUP BY website_id,
+    session_id,
+    visit_id,
+    hostname,
+    browser,
+    os,
+    device,
+    screen,
+    language,
+    country,
+    region,
+    city,
+    event_type,
+    distinct_id,
+    timestamp);
+
+-- projections
+ALTER TABLE umami.website_event 
+ADD PROJECTION website_event_url_path_projection (
+SELECT * ORDER BY toStartOfDay(created_at), website_id, url_path, created_at
+);
+
+ALTER TABLE umami.website_event MATERIALIZE PROJECTION website_event_url_path_projection;
+
+ALTER TABLE umami.website_event 
+ADD PROJECTION website_event_referrer_domain_projection (
+SELECT * ORDER BY toStartOfDay(created_at), website_id, referrer_domain, created_at
+);
+
+ALTER TABLE umami.website_event MATERIALIZE PROJECTION website_event_referrer_domain_projection;
+
+-- revenue
+CREATE TABLE umami.website_revenue
+(
+    website_id UUID,
+    session_id UUID,
+    event_id UUID,
+    event_name String,
+    currency String,
+    revenue DECIMAL(18,4),
+    created_at DateTime('UTC')
+)
+ENGINE = MergeTree
+    PARTITION BY toYYYYMM(created_at)
+    ORDER BY (website_id, session_id, created_at)
+    SETTINGS index_granularity = 8192;
+
+
+CREATE MATERIALIZED VIEW umami.website_revenue_mv
+TO umami.website_revenue
+AS
+SELECT DISTINCT
+    ed.website_id,
+    ed.session_id,
+    ed.event_id,
+    ed.event_name,
+    c.currency,
+    coalesce(toDecimal64(ed.number_value, 2), toDecimal64(ed.string_value, 2)) revenue,
+    ed.created_at
+FROM umami.event_data ed
+JOIN (SELECT event_id, string_value as currency
+        FROM umami.event_data
+        WHERE positionCaseInsensitive(data_key, 'currency') > 0) c
+      ON c.event_id = ed.event_id
+WHERE positionCaseInsensitive(data_key, 'revenue') > 0;