Skip to content

OpenPanel is an open-source web and product analytics platform, an open-source alternative to Mixpanel with optional self-hosting.

18.8 KB
Raw Blame
import crypto from 'node:crypto';
import type { EventEmitter } from 'node:events';
import { createClient, formatClickhouseDate } from './client';

interface CreateTableOptions {
  name: string;
  columns: string[];
  indices?: string[];
  engine?: string;
  orderBy: string[];
  partitionBy?: string;
  settings?: Record<string, string | number>;
  distributionHash: string;
  replicatedVersion: string;
  isClustered: boolean;
}

interface CreateMaterializedViewOptions {
  name: string;
  tableName: string;
  query: string;
  engine?: string;
  orderBy: string[];
  partitionBy?: string;
  settings?: Record<string, string | number>;
  populate?: boolean;
  distributionHash: string;
  replicatedVersion: string;
  isClustered: boolean;
}

const CLUSTER_REPLICA_PATH =
  '/clickhouse/{installation}/{cluster}/tables/{shard}/openpanel/v{replicatedVersion}/{table}';

const replicated = (tableName: string) => `${tableName}_replicated`;

/**
 * The database the migrations create and query, from `CLICKHOUSE_URL`'s path the same way the
 * client resolves it, so an isolated database (a hub worktree's `openpanel_<name>`) migrates
 * into itself and not into `openpanel`.
 */
export function migrationDatabase(): string {
  const raw = (process.env.CLICKHOUSE_URL ?? '').split(',')[0]?.trim();
  if (!raw) {
    return 'openpanel';
  }
  try {
    const name = new URL(raw).pathname.replace(/^\/+|\/+$/g, '');
    return /^[A-Za-z_][A-Za-z0-9_]*$/.test(name) ? name : 'openpanel';
  } catch {
    return 'openpanel';
  }
}

export const chMigrationClient = createClient({
  url: process.env.CLICKHOUSE_URL,
  request_timeout: 3_600_000, // 1 hour in milliseconds
  keep_alive: {
    enabled: true,
  },
  compression: {
    request: true,
    response: true,
  },
  clickhouse_settings: {
    wait_end_of_query: 1,
    // Progress headers keep the connection active so long queries are not idled out by proxies.
    send_progress_in_http_headers: 1,
    // Under 60s, the usual proxy idle timeout.
    http_headers_progress_interval_ms: '50000',
  },
});

export function createDatabase(name: string, isClustered: boolean) {
  if (isClustered) {
    return `CREATE DATABASE IF NOT EXISTS ${name} ON CLUSTER '{cluster}'`;
  }

  return `CREATE DATABASE IF NOT EXISTS ${name}`;
}

/** Creates SQL statements for table creation, clustered or not. */
export function createTable({
  name: tableName,
  columns,
  indices = [],
  engine = 'MergeTree()',
  orderBy = ['tuple()'],
  partitionBy,
  settings = {},
  distributionHash,
  replicatedVersion,
  isClustered,
}: CreateTableOptions): string[] {
  const columnDefinitions = [...columns, ...indices].join(',\n  ');

  const settingsClause = Object.entries(settings).length
    ? `SETTINGS ${Object.entries(settings)
        .map(([key, value]) => `${key} = ${value}`)
        .join(', ')}`
    : '';

  const partitionByClause = partitionBy ? `PARTITION BY ${partitionBy}` : '';

  if (!isClustered) {
    return [
      `CREATE TABLE IF NOT EXISTS ${tableName} (
  ${columnDefinitions}
)
ENGINE = ${engine}
${partitionByClause}
ORDER BY (${orderBy.join(', ')})
${settingsClause}`.trim(),
    ];
  }

  return [
    `CREATE TABLE IF NOT EXISTS ${replicated(tableName)} ON CLUSTER '{cluster}' (
  ${columnDefinitions}
)
ENGINE = Replicated${engine.replace(/^(.+?)\((.+?)?\)/, `$1('${CLUSTER_REPLICA_PATH.replace('{replicatedVersion}', replicatedVersion)}', '{replica}', $2)`).replace(/, \)$/, ')')}
${partitionByClause}
ORDER BY (${orderBy.join(', ')})
${settingsClause}`.trim(),
    `CREATE TABLE IF NOT EXISTS ${tableName} ON CLUSTER '{cluster}' AS ${replicated(tableName)}
ENGINE = Distributed('{cluster}', currentDatabase(), ${replicated(tableName)}, ${distributionHash})`,
  ];
}

export const modifyTTL = ({
  tableName,
  isClustered,
  ttl,
}: {
  tableName: string;
  isClustered: boolean;
  ttl: string;
}) => {
  if (isClustered) {
    return `ALTER TABLE ${replicated(tableName)} ON CLUSTER '{cluster}' MODIFY TTL ${ttl}`;
  }

  return `ALTER TABLE ${tableName} MODIFY TTL ${ttl}`;
};

export function addColumns(
  tableName: string,
  columns: string[],
  isClustered: boolean
): string[] {
  if (isClustered) {
    return columns.flatMap((col) => [
      `ALTER TABLE ${replicated(tableName)} ON CLUSTER '{cluster}' ADD COLUMN IF NOT EXISTS ${col}`,
      `ALTER TABLE ${tableName} ON CLUSTER '{cluster}' ADD COLUMN IF NOT EXISTS ${col}`,
    ]);
  }

  return columns.map(
    (col) => `ALTER TABLE ${tableName} ADD COLUMN IF NOT EXISTS ${col}`
  );
}

export function dropColumns(
  tableName: string,
  columnNames: string[],
  isClustered: boolean
): string[] {
  if (isClustered) {
    return columnNames.flatMap((colName) => [
      `ALTER TABLE ${replicated(tableName)} ON CLUSTER '{cluster}' DROP COLUMN IF EXISTS ${colName}`,
      `ALTER TABLE ${tableName} ON CLUSTER '{cluster}' DROP COLUMN IF EXISTS ${colName}`,
    ]);
  }

  return columnNames.map(
    (colName) => `ALTER TABLE ${tableName} DROP COLUMN IF EXISTS ${colName}`
  );
}

export async function getExistingTables() {
  try {
    const existingTablesQuery = await chMigrationClient.query({
      query:
        'SELECT name FROM system.tables WHERE database = {database:String}',
      query_params: { database: migrationDatabase() },
      format: 'JSONEachRow',
    });
    return (await existingTablesQuery.json<{ name: string }>())
      .map((table) => table.name)
      .filter((table) => !table.includes('.inner_id'));
  } catch (e) {
    console.error(e);
    return [];
  }
}

// A MergeTree setting that is unknown to the server fails the CREATE, so a
// migration checks before it sets one that only newer releases know.
export async function hasMergeTreeSetting(name: string): Promise<boolean> {
  const result = await chMigrationClient.query({
    query:
      'SELECT count() AS total FROM system.merge_tree_settings WHERE name = {name:String}',
    query_params: { name },
    format: 'JSONEachRow',
  });
  const [row] = await result.json<{ total: string }>();
  return Number(row?.total ?? 0) > 0;
}

export interface TableKeys {
  sortingKey: string[];
  partitionKey: string;
}

/** Sort and partition key of every table in the migration database, by name. */
export async function getTableKeys() {
  const response = await chMigrationClient.query({
    query:
      'SELECT name, sorting_key, partition_key FROM system.tables WHERE database = {database:String}',
    query_params: { database: migrationDatabase() },
    format: 'JSONEachRow',
  });
  const rows = await response.json<{
    name: string;
    sorting_key: string;
    partition_key: string;
  }>();
  return new Map<string, TableKeys>(
    rows.map((row) => [
      row.name,
      {
        sortingKey: row.sorting_key.split(', '),
        partitionKey: row.partition_key,
      },
    ])
  );
}

export function renameTable({
  from,
  to,
  isClustered,
}: {
  from: string;
  to: string;
  isClustered: boolean;
}) {
  if (isClustered) {
    return [
      `RENAME TABLE ${replicated(from)} TO ${replicated(to)} ON CLUSTER '{cluster}'`,
      `RENAME TABLE ${from} TO ${to} ON CLUSTER '{cluster}'`,
    ];
  }

  return [`RENAME TABLE ${from} TO ${to}`];
}

export function dropTable(tableName: string, isClustered: boolean) {
  if (isClustered) {
    return `DROP TABLE IF EXISTS ${tableName} ON CLUSTER '{cluster}'`;
  }

  return `DROP TABLE IF EXISTS ${tableName}`;
}

export function moveDataBetweenTables({
  from,
  to,
  batch,
  columns,
}: {
  from: string;
  to: string;
  batch?: {
    column: string;
    interval?: 'day' | 'week' | 'month';
    transform?: (date: Date) => string;
    endDate?: Date;
    startDate?: Date;
  };
  columns?: string[];
}): string[] {
  const sqls: string[] = [];

  const selectClause = columns && columns.length > 0 ? columns.join(', ') : '*';

  if (!batch) {
    return [`INSERT INTO ${to} SELECT ${selectClause} FROM ${from}`];
  }

  // Default window: 3 years back from tomorrow, so today is included.
  const endDate = batch.endDate || new Date();
  if (!batch.endDate) {
    endDate.setDate(endDate.getDate() + 1);
  }
  const startDate = batch.startDate || new Date();
  if (!batch.startDate) {
    startDate.setFullYear(startDate.getFullYear() - 3);
  }

  let currentDate = endDate;
  const interval = batch.interval || 'day';

  const getWeekStart = (date: Date): Date => {
    const d = new Date(date);
    const day = d.getDay();
    const diff = d.getDate() - day + (day === 0 ? -6 : 1);
    d.setDate(diff);
    d.setHours(0, 0, 0, 0);
    return d;
  };

  const shouldContinue = (
    current: Date,
    start: Date,
    intervalType: string
  ): boolean => {
    if (intervalType === 'month') {
      const currentYear = current.getFullYear();
      const currentMonth = current.getMonth();
      const startYear = start.getFullYear();
      const startMonth = start.getMonth();
      return (
        currentYear > startYear ||
        (currentYear === startYear && currentMonth >= startMonth)
      );
    }
    if (intervalType === 'week') {
      const currentWeekStart = getWeekStart(current);
      const startWeekStart = getWeekStart(start);
      return currentWeekStart >= startWeekStart;
    }
    return current > start;
  };

  while (shouldContinue(currentDate, startDate, interval)) {
    const previousDate = new Date(currentDate);

    switch (interval) {
      case 'month':
        previousDate.setMonth(previousDate.getMonth() - 1);
        // Clamp to the month containing startDate so it is still covered.
        if (
          previousDate.getFullYear() < startDate.getFullYear() ||
          (previousDate.getFullYear() === startDate.getFullYear() &&
            previousDate.getMonth() < startDate.getMonth())
        ) {
          previousDate.setFullYear(startDate.getFullYear());
          previousDate.setMonth(startDate.getMonth());
          previousDate.setDate(1);
        }
        break;
      case 'week': {
        previousDate.setDate(previousDate.getDate() - 7);
        const startWeekStart = getWeekStart(startDate);
        const prevWeekStart = getWeekStart(previousDate);
        if (prevWeekStart < startWeekStart) {
          previousDate.setTime(startWeekStart.getTime());
        }
        break;
      }
      default:
        previousDate.setDate(previousDate.getDate() - 1);
        break;
    }

    // The WHERE clause uses > previousDate AND <= upperBoundDate to get exactly one period.
    let upperBoundDate = currentDate;
    if (upperBoundDate > endDate) {
      upperBoundDate = endDate;
    }

    const sql = `INSERT INTO ${to} 
      SELECT ${selectClause} FROM ${from} 
      WHERE ${batch.column} > '${batch.transform ? batch.transform(previousDate) : formatClickhouseDate(previousDate, true)}' 
      AND ${batch.column} <= '${batch.transform ? batch.transform(upperBoundDate) : formatClickhouseDate(upperBoundDate, true)}'`;
    sqls.push(sql);

    if (interval === 'month') {
      const prevYear = previousDate.getFullYear();
      const prevMonth = previousDate.getMonth();
      const startYear = startDate.getFullYear();
      const startMonth = startDate.getMonth();
      if (prevYear === startYear && prevMonth === startMonth) {
        break;
      }
    } else if (interval === 'week') {
      const prevWeekStart = getWeekStart(previousDate);
      const startWeekStart = getWeekStart(startDate);
      if (prevWeekStart.getTime() === startWeekStart.getTime()) {
        break;
      }
    }

    currentDate = previousDate;
  }

  return sqls;
}

export function createMaterializedView({
  name: tableName,
  query,
  engine = 'AggregatingMergeTree()',
  orderBy,
  partitionBy,
  settings = {},
  populate = false,
  distributionHash = 'rand()',
  replicatedVersion,
  isClustered,
}: CreateMaterializedViewOptions): string[] {
  const settingsClause = Object.entries(settings).length
    ? `SETTINGS ${Object.entries(settings)
        .map(([key, value]) => `${key} = ${value}`)
        .join(', ')}`
    : '';

  const partitionByClause = partitionBy ? `PARTITION BY ${partitionBy}` : '';

  const transformedQuery = query.replace(/\{(\w+)\}/g, (_, tableName) =>
    isClustered ? replicated(tableName) : tableName
  );

  if (!isClustered) {
    return [
      `CREATE MATERIALIZED VIEW IF NOT EXISTS ${tableName}
ENGINE = ${engine}
${partitionByClause}
ORDER BY (${orderBy.join(', ')})
${settingsClause}
${populate ? 'POPULATE' : ''}
AS ${transformedQuery}`.trim(),
    ];
  }

  return [
    `CREATE MATERIALIZED VIEW IF NOT EXISTS ${replicated(tableName)} ON CLUSTER '{cluster}'
ENGINE = Replicated${engine.replace(/^(.+?)\((.+?)?\)/, `$1('${CLUSTER_REPLICA_PATH.replace('{replicatedVersion}', replicatedVersion)}', '{replica}', $2)`).replace(/, \)$/, ')')}
${partitionByClause}
ORDER BY (${orderBy.join(', ')})
${settingsClause}
${populate ? 'POPULATE' : ''}
AS ${transformedQuery}`.trim(),
    `CREATE TABLE IF NOT EXISTS ${tableName} ON CLUSTER '{cluster}' AS ${replicated(tableName)}
ENGINE = Distributed('{cluster}', currentDatabase(), ${replicated(tableName)}, ${distributionHash})`,
  ];
}

export function countRows(tableName: string) {
  return `SELECT count() FROM ${tableName}`;
}

// KILL QUERY ... SYNC returns once the query stopped; past this, assume it
// did not.
const KILL_QUERY_TIMEOUT_MS = 60_000;

/**
 * An aborted statement that ClickHouse did not confirm stopping. It may still
 * be writing, so nothing may start the same work until it is known to be gone.
 */
export class MigrationQueryStillRunningError extends Error {
  constructor(queryId: string, cause: unknown) {
    super(`Could not confirm migration query ${queryId} stopped`, { cause });
    this.name = 'MigrationQueryStillRunningError';
  }
}

let isAbortRequested = false;
const inFlight = new Set<AbortController>();

/**
 * Stops runClickhouseMigrationCommands: the statement in flight is killed and
 * no further one starts, in this call or a later one, until
 * allowMigrationCommands(). Sticky because a migration may issue several
 * calls, and an abort between two of them must not let the second start.
 */
export function abortMigrationCommands() {
  isAbortRequested = true;
  for (const controller of inFlight) {
    controller.abort();
  }
}

export function allowMigrationCommands() {
  isAbortRequested = false;
}

export async function runClickhouseMigrationCommands(sqls: string[]) {
  let abort: AbortController | undefined;
  let activeQueryId: string | undefined;

  const handleTermination = async (signal: string) => {
    console.warn(
      `Received ${signal}. Cleaning up active queries before exit...`
    );

    if (abort) {
      abort.abort();
    }
  };

  const handleSigterm = () => handleTermination('SIGTERM');
  const handleSigint = () => handleTermination('SIGINT');

  process.on('SIGTERM', handleSigterm);
  process.on('SIGINT', handleSigint);

  try {
    for (const sql of sqls) {
      if (isAbortRequested) {
        throw new Error('Migration commands aborted');
      }
      if (abort) {
        inFlight.delete(abort);
      }
      abort = new AbortController();
      inFlight.add(abort);
      let timer: NodeJS.Timeout | undefined;
      let resolve: ((value: unknown) => void) | undefined;
      activeQueryId = crypto.createHash('sha256').update(sql).digest('hex');

      console.log('----------------------------------------');
      console.log('---| Running query | Query ID:', activeQueryId);
      console.log('---| SQL |------------------------------');
      console.log(sql);
      console.log('----------------------------------------');

      try {
        const res = await Promise.race([
          chMigrationClient.command({
            query: sql,
            query_id: activeQueryId,
            abort_signal: abort?.signal,
          }),
          new Promise((r) => {
            resolve = r;
            let checking = false; // Add flag to prevent multiple concurrent checks

            async function check() {
              if (checking) {
                return;
              }
              checking = true;

              try {
                const res = await chMigrationClient
                  .query({
                    query: `SELECT
                              query_id,
                              elapsed,
                              read_rows,
                              written_rows,
                              memory_usage
                            FROM system.processes 
                            WHERE query_id = '${activeQueryId}'`,
                    format: 'JSONEachRow',
                  })
                  .then((res) => res.json());

                const formatMemory = (bytes: number) => {
                  const units = ['B', 'KB', 'MB', 'GB'];
                  let size = bytes;
                  let unitIndex = 0;
                  while (size >= 1024 && unitIndex < units.length - 1) {
                    size /= 1024;
                    unitIndex++;
                  }
                  return `${Math.round(size * 100) / 100}${units[unitIndex]}`;
                };

                const formatNumber = (num: number) => {
                  return num.toString().replace(/\B(?=(\d{3})+(?!\d))/g, ',');
                };

                if (Array.isArray(res) && res.length > 0) {
                  const { elapsed, read_rows, written_rows, memory_usage } =
                    res[0] as any;
                  console.log(
                    `Progress: ${elapsed.toFixed(2)}s | Memory: ${formatMemory(memory_usage)} | Read: ${formatNumber(read_rows)} rows | Written: ${formatNumber(written_rows)} rows`
                  );
                }
              } finally {
                checking = false;
              }

              timer = setTimeout(check, 5000);
            }

            timer = setTimeout(check, 5000);
          }),
        ]);

        if (timer) {
          clearTimeout(timer);
        }
        if (resolve) {
          resolve(res);
        }
      } catch (e) {
        console.log('Failed on query', sql);
        throw e;
      }
    }
  } catch (e) {
    if (abort) {
      abort.abort();
    }

    if (activeQueryId) {
      // An INSERT ... SELECT keeps running on the server after the client
      // goes away, so the abort is only done once ClickHouse says it stopped.
      try {
        await chMigrationClient.command({
          query: `KILL QUERY WHERE query_id = '${activeQueryId}' SYNC`,
          abort_signal: AbortSignal.timeout(KILL_QUERY_TIMEOUT_MS),
        });
        console.log(`Successfully killed query ${activeQueryId}`);
      } catch (err) {
        console.error(`Failed to kill query ${activeQueryId}:`, err);
        throw new MigrationQueryStillRunningError(activeQueryId, err);
      }
    }

    throw e;
  } finally {
    if (abort) {
      inFlight.delete(abort);
    }
    // Clean up event listeners. bun-types' `Process.off` override declares
    // only its own "memoryPressure" overload and wins over the @types/node
    // Signals overload, so the cast is what keeps this compiling (dropping it
    // produces TS2345 on both lines below).
    const emitter = process as unknown as EventEmitter;
    emitter.off('SIGTERM', handleSigterm);
    emitter.off('SIGINT', handleSigint);
  }
}