Skip to content

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

1.3 KB
Raw Blame
import { ensureUtcTimeColumns, execClickhouseInitStep } from "../initUtils.js";
import { UTC_TIME_COLUMNS } from "../timeColumns.js";

// Hourly per-site event counts, used by cloud usage tracking / billing.
export async function initializeCloudTables() {
  await execClickhouseInitStep(
    "create hourly events by site target table",
    `
      CREATE TABLE IF NOT EXISTS hourly_events_by_site_mv_target (
        event_hour DateTime('UTC'),          -- The specific hour
        site_id UInt16,
        event_count UInt64            -- The count of events for that site in that hour
      )
      ENGINE = SummingMergeTree()     -- Sums 'event_count' for rows with the same sorting key
      PARTITION BY toYYYYMM(event_hour)
      ORDER BY (event_hour, site_id)
      TTL event_hour + INTERVAL 60 DAY
    `
  );
  await ensureUtcTimeColumns("hourly_events_by_site_mv_target", UTC_TIME_COLUMNS.hourly_events_by_site_mv_target);

  await execClickhouseInitStep(
    "create hourly events by site materialized view",
    `
      CREATE MATERIALIZED VIEW IF NOT EXISTS hourly_events_by_site_mv
      TO hourly_events_by_site_mv_target -- Name of the target table
      AS SELECT
        toStartOfHour(timestamp) AS event_hour,
        site_id,
        count() AS event_count
      FROM events
      GROUP BY event_hour, site_id
    `
  );
}