Skip to content

🐸 Rybbit - open-source and privacy-friendly alternative to Google Analytics that is 10x more intuitive.

11.1 KB
Raw Blame
import {
  clickhouseInitLogger as logger,
  ensureUtcTimeColumns,
  execClickhouseInitStep,
  getTableCreateQuery,
} from "../initUtils.js";
import { UTC_TIME_COLUMNS } from "../timeColumns.js";

const SESSION_HOURLY_MV_NAME = "session_hourly_mv";
const SESSION_HOURLY_MV_REFRESH_INTERVAL = "REFRESH EVERY 1 HOUR";
const SESSION_HOURLY_MV_SOURCE = "sessions_mv_target FINAL";

const SESSION_HOURLY_MV_CREATE_QUERY = `
  CREATE MATERIALIZED VIEW ${SESSION_HOURLY_MV_NAME}
  ${SESSION_HOURLY_MV_REFRESH_INTERVAL}
  TO session_hourly_mv_target
  AS
  SELECT
    site_id,
    toStartOfHour(start_time) AS session_hour,
    count() AS sessions,
    sum(session_pageviews) AS pageviews,
    uniqState(user_id) AS users,
    sum(toUInt64(end_time - start_time)) AS total_session_duration_seconds,
    countIf(session_pageviews = 1) AS bounced_sessions
  FROM (
    SELECT
      site_id,
      user_id,
      start_time,
      end_time,
      pageviews AS session_pageviews
    FROM ${SESSION_HOURLY_MV_SOURCE}
  ) AS sessions
  GROUP BY site_id, session_hour
`;

async function ensureSessionHourlyMaterializedView() {
  const existingCreateQuery = await getTableCreateQuery(SESSION_HOURLY_MV_NAME);
  const isCurrent =
    existingCreateQuery?.includes(SESSION_HOURLY_MV_REFRESH_INTERVAL) &&
    existingCreateQuery.includes(SESSION_HOURLY_MV_SOURCE);

  if (isCurrent) {
    logger.debug("Session hourly materialized view is up to date");
    return;
  }

  if (existingCreateQuery) {
    logger.info("Replacing outdated session hourly materialized view");
    await execClickhouseInitStep(
      "drop outdated session hourly materialized view",
      `DROP VIEW IF EXISTS ${SESSION_HOURLY_MV_NAME} SYNC`,
      { lockAcquireTimeoutSeconds: 15 }
    );
  }

  await execClickhouseInitStep("create session hourly materialized view", SESSION_HOURLY_MV_CREATE_QUERY);
}

// Materialized views that back the simplified high-traffic dashboard.
// All views are hourly-bucketed and feed the lite endpoints; raw `events`
// is still queried for filters that aren't keyed in these MVs.
export async function initializeLiteDashboardMVs() {
  // Per-session rollup. Replaces the AllSessionPageviews / FilteredSessions
  // CTEs that getOverview and getOverviewBucketed run over raw events.
  //
  // This is a streaming MV: it fires once per inserted block and writes a
  // PARTIAL session row for whatever events were in that block. AggregatingMergeTree
  // then composes the SimpleAggregateFunction columns (min/max/sum) across parts
  // sharing (site_id, session_id), and the read queries additionally re-aggregate
  // by session_id at query time — so pageviews/start_time/end_time are always
  // correct even before merges complete.
  //
  // The plain metadata columns below (country, region, device_type, browser,
  // operating_system, hostname) are session-INVARIANT, so every partial row of a
  // session carries the same value and `any()` is well-defined. Entry/acquisition
  // attributes (entry_page, referrer, channel) are deliberately NOT stored here:
  // they are first-event values that differ across a session's blocks, can't be
  // composed correctly per-partial-row, and so are served by the raw-events
  // fallback instead (see LITE_SESSION_FILTER_COLUMNS in lite/utils.ts).
  await execClickhouseInitStep(
    "create sessions rollup target table",
    `
      CREATE TABLE IF NOT EXISTS sessions_mv_target (
        site_id UInt16,
        session_id String,
        user_id String,
        start_time SimpleAggregateFunction(min, DateTime('UTC')),
        end_time SimpleAggregateFunction(max, DateTime('UTC')),
        pageviews SimpleAggregateFunction(sum, UInt64),
        events SimpleAggregateFunction(sum, UInt64),
        country LowCardinality(FixedString(2)),
        region LowCardinality(String),
        device_type LowCardinality(String),
        browser LowCardinality(String),
        operating_system LowCardinality(String),
        hostname String,
        last_seen SimpleAggregateFunction(max, DateTime('UTC'))
      )
      ENGINE = AggregatingMergeTree()
      PARTITION BY toYYYYMM(start_time)
      ORDER BY (site_id, session_id)
    `
  );
  await ensureUtcTimeColumns("sessions_mv_target", UTC_TIME_COLUMNS.sessions_mv_target);

  await execClickhouseInitStep(
    "create sessions rollup materialized view",
    `
      CREATE MATERIALIZED VIEW IF NOT EXISTS sessions_mv
      TO sessions_mv_target
      AS SELECT
        site_id,
        session_id,
        any(user_id) AS user_id,
        min(timestamp) AS start_time,
        max(timestamp) AS end_time,
        countIf(type = 'pageview') AS pageviews,
        count() AS events,
        any(country) AS country,
        any(region) AS region,
        any(device_type) AS device_type,
        any(browser) AS browser,
        any(operating_system) AS operating_system,
        any(hostname) AS hostname,
        max(timestamp) AS last_seen
      FROM events
      GROUP BY site_id, session_id
    `
  );

  // Hourly overview rollup. Drives the bucketed chart and overview cards.
  await execClickhouseInitStep(
    "create hourly overview rollup target table",
    `
      CREATE TABLE IF NOT EXISTS overview_hourly_mv_target (
        site_id UInt16,
        event_hour DateTime('UTC'),
        pageviews SimpleAggregateFunction(sum, UInt64),
        events SimpleAggregateFunction(sum, UInt64),
        users AggregateFunction(uniq, String),
        sessions AggregateFunction(uniq, String)
      )
      ENGINE = AggregatingMergeTree()
      PARTITION BY toYYYYMM(event_hour)
      ORDER BY (site_id, event_hour)
    `
  );
  await ensureUtcTimeColumns("overview_hourly_mv_target", UTC_TIME_COLUMNS.overview_hourly_mv_target);

  await execClickhouseInitStep(
    "create hourly overview rollup materialized view",
    `
      CREATE MATERIALIZED VIEW IF NOT EXISTS overview_hourly_mv
      TO overview_hourly_mv_target
      AS SELECT
        site_id,
        toStartOfHour(timestamp) AS event_hour,
        countIf(type = 'pageview') AS pageviews,
        count() AS events,
        uniqState(user_id) AS users,
        uniqState(session_id) AS sessions
      FROM events
      GROUP BY site_id, event_hour
    `
  );

  // Top-pathname rollup. Cardinality is bounded by hour, so high-URL sites
  // still get smaller-than-raw storage. Filtered queries fall back to events.
  await execClickhouseInitStep(
    "create hourly pathname rollup target table",
    `
      CREATE TABLE IF NOT EXISTS pathname_hourly_mv_target (
        site_id UInt16,
        event_hour DateTime('UTC'),
        pathname String,
        hostname String,
        pageviews SimpleAggregateFunction(sum, UInt64),
        users AggregateFunction(uniq, String),
        sessions AggregateFunction(uniq, String)
      )
      ENGINE = AggregatingMergeTree()
      PARTITION BY toYYYYMM(event_hour)
      ORDER BY (site_id, event_hour, pathname)
    `
  );
  await ensureUtcTimeColumns("pathname_hourly_mv_target", UTC_TIME_COLUMNS.pathname_hourly_mv_target);

  await execClickhouseInitStep(
    "create hourly pathname rollup materialized view",
    `
      CREATE MATERIALIZED VIEW IF NOT EXISTS pathname_hourly_mv
      TO pathname_hourly_mv_target
      AS SELECT
        site_id,
        toStartOfHour(timestamp) AS event_hour,
        pathname,
        any(hostname) AS hostname,
        countIf(type = 'pageview') AS pageviews,
        uniqState(user_id) AS users,
        uniqState(session_id) AS sessions
      FROM events
      WHERE type = 'pageview'
      GROUP BY site_id, event_hour, pathname
    `
  );

  // Country/region rollup. Cardinality is naturally bounded.
  await execClickhouseInitStep(
    "create hourly country rollup target table",
    `
      CREATE TABLE IF NOT EXISTS country_hourly_mv_target (
        site_id UInt16,
        event_hour DateTime('UTC'),
        country LowCardinality(FixedString(2)),
        region LowCardinality(String),
        pageviews SimpleAggregateFunction(sum, UInt64),
        users AggregateFunction(uniq, String),
        sessions AggregateFunction(uniq, String)
      )
      ENGINE = AggregatingMergeTree()
      PARTITION BY toYYYYMM(event_hour)
      ORDER BY (site_id, event_hour, country, region)
    `
  );
  await ensureUtcTimeColumns("country_hourly_mv_target", UTC_TIME_COLUMNS.country_hourly_mv_target);

  await execClickhouseInitStep(
    "create hourly country rollup materialized view",
    `
      CREATE MATERIALIZED VIEW IF NOT EXISTS country_hourly_mv
      TO country_hourly_mv_target
      AS SELECT
        site_id,
        toStartOfHour(timestamp) AS event_hour,
        country,
        region,
        countIf(type = 'pageview') AS pageviews,
        uniqState(user_id) AS users,
        uniqState(session_id) AS sessions
      FROM events
      GROUP BY site_id, event_hour, country, region
    `
  );

  // Device-type rollup.
  await execClickhouseInitStep(
    "create hourly device type rollup target table",
    `
      CREATE TABLE IF NOT EXISTS device_type_hourly_mv_target (
        site_id UInt16,
        event_hour DateTime('UTC'),
        device_type LowCardinality(String),
        pageviews SimpleAggregateFunction(sum, UInt64),
        users AggregateFunction(uniq, String),
        sessions AggregateFunction(uniq, String)
      )
      ENGINE = AggregatingMergeTree()
      PARTITION BY toYYYYMM(event_hour)
      ORDER BY (site_id, event_hour, device_type)
    `
  );
  await ensureUtcTimeColumns("device_type_hourly_mv_target", UTC_TIME_COLUMNS.device_type_hourly_mv_target);

  await execClickhouseInitStep(
    "create hourly device type rollup materialized view",
    `
      CREATE MATERIALIZED VIEW IF NOT EXISTS device_type_hourly_mv
      TO device_type_hourly_mv_target
      AS SELECT
        site_id,
        toStartOfHour(timestamp) AS event_hour,
        device_type,
        countIf(type = 'pageview') AS pageviews,
        uniqState(user_id) AS users,
        uniqState(session_id) AS sessions
      FROM events
      GROUP BY site_id, event_hour, device_type
    `
  );

  // Session-keyed hourly rollup, populated by a REFRESHABLE materialized view.
  // Streaming MVs can't compute bounce_rate or session_duration because they
  // see one event at a time, never the full per-session state. The refreshable
  // MV compacts the already-populated per-session rollup hourly instead of
  // repeatedly rebuilding sessions from every raw event.
  await execClickhouseInitStep(
    "create session hourly rollup target table",
    `
      CREATE TABLE IF NOT EXISTS session_hourly_mv_target (
        site_id UInt16,
        session_hour DateTime('UTC'),
        sessions UInt64,
        pageviews UInt64,
        users AggregateFunction(uniq, String),
        total_session_duration_seconds UInt64,
        bounced_sessions UInt64
      )
      ENGINE = MergeTree()
      PARTITION BY toYYYYMM(session_hour)
      ORDER BY (site_id, session_hour)
    `
  );
  await ensureUtcTimeColumns("session_hourly_mv_target", UTC_TIME_COLUMNS.session_hourly_mv_target);

  // CREATE IF NOT EXISTS cannot update an already-installed view. Inspect the
  // stored definition so deployments replace the old five-minute raw-events
  // refresh once, while subsequent startups remain no-ops.
  await ensureSessionHourlyMaterializedView();
}