Skip to content

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

12.0 KB
Raw Blame
import type {
  ClickHouseClient,
  ClickHouseSettings,
  ResponseJSON,
} from '@clickhouse/client';
import { ClickHouseLogLevel, createClient } from '@clickhouse/client';
import type { NodeClickHouseClientConfigOptions } from '@clickhouse/client/dist/config';
import { getSafeJson } from '@openpanel/shared';
import { createLogger } from '../logger';
import {
  nodeLabel,
  RoundRobinPicker,
  withRoundRobinRetry,
} from './round-robin';
import { type SqlFragment, toStatement } from './sql';

export { createClient } from '@clickhouse/client';

const logger = createLogger({ name: 'clickhouse' });

import type { Logger } from '@clickhouse/client';

interface LogParams {
  module: string;
  message: string;
  args?: Record<string, unknown>;
}
type ErrorLogParams = LogParams & { err: Error };
type WarnLogParams = LogParams & { err?: Error };

class CustomLogger implements Logger {
  trace({ message, args }: LogParams) {
    logger.debug({ args }, message);
  }
  debug({ message, args }: LogParams) {
    if (message.includes('Query:') && args?.response_status === 200) {
      return;
    }
    logger.debug({ args }, message);
  }
  info({ message, args }: LogParams) {
    logger.info({ args }, message);
  }
  warn({ message, args }: WarnLogParams) {
    logger.warn({ args }, message);
  }
  error({ message, args, err }: ErrorLogParams) {
    logger.error({ err, args }, message);
  }
}

export const TABLE_NAMES = {
  events: 'events',
  profiles: 'profiles',
  alias: 'profile_aliases',
  self_hosting: 'self_hosting',
  events_bots: 'events_bots',
  dau_mv: 'dau_mv',
  event_names_mv: 'distinct_event_names_mv',
  event_property_values_mv: 'event_property_values_mv',
  cohort_events_mv: 'cohort_events_mv',
  sessions: 'sessions',
  events_imports: 'events_imports',
  session_replay_chunks: 'session_replay_chunks',
  gsc_daily: 'gsc_daily',
  gsc_pages_daily: 'gsc_pages_daily',
  gsc_queries_daily: 'gsc_queries_daily',
  groups: 'groups',
  cohort_members: 'cohort_members',
  cohort_metadata: 'cohort_metadata',
  ai_events: 'ai_events',
  // Dropped in migration 24; kept because migrations 13, 14 and 15 still name them.
  profile_event_summary_mv: 'profile_event_summary_mv',
  // Keyed for the cohort criteria (event + window first, profile last) rather than by profile.
  event_profile_summary_mv: 'event_profile_summary_mv',
  event_property_profile_summary_mv: 'event_property_profile_summary_mv',
  profile_event_property_summary_mv: 'profile_event_property_summary_mv',
};

/**
 * Whether ClickHouse runs as a cluster with `<table>_replicated` targets.
 * Keyed on CLICKHOUSE_CLUSTER alone — the same rule the code migrations use
 * when they create the schema — so runtime never mutates tables that were
 * never created.
 */
export function isClickhouseClustered(): boolean {
  return (
    process.env.CLICKHOUSE_CLUSTER === 'true' ||
    process.env.CLICKHOUSE_CLUSTER === '1'
  );
}

export function getReplicatedTableName(tableName: string): string {
  if (isClickhouseClustered()) {
    return `${tableName}_replicated ON CLUSTER '{cluster}'`;
  }
  return tableName;
}

function getClickhouseSettings(): ClickHouseSettings {
  const additionalSettings =
    getSafeJson<ClickHouseSettings>(process.env.CLICKHOUSE_SETTINGS || '{}') ||
    {};

  return {
    distributed_product_mode: 'allow',
    date_time_input_format: 'best_effort',
    // ClickHouse 26.3+ defaults async_insert to 1. The buffers already batch
    // client-side, so inserts stay synchronous; a buffer can still opt in per query.
    async_insert: 0,
    ...(process.env.CLICKHOUSE_SETTINGS_REMOVE_CONVERT_ANY_JOIN
      ? {}
      : {
          query_plan_convert_any_join_to_semi_or_anti_join: 0,
        }),
    ...additionalSettings,
  };
}

// Gzip runs on the main thread and can block the event loop for payload-heavy
// buffers (replay), so CLICKHOUSE_REQUEST_COMPRESSION=false turns it off.
const requestCompressionEnabled =
  process.env.CLICKHOUSE_REQUEST_COMPRESSION !== 'false' &&
  process.env.CLICKHOUSE_REQUEST_COMPRESSION !== '0';

// Pool size per worker process: parallel chunk inserts (cap 5 per flush)
// from several buffers flushing at once.
const maxOpenConnections = process.env.CLICKHOUSE_MAX_OPEN_CONNECTIONS
  ? Math.max(
      1,
      Number.parseInt(process.env.CLICKHOUSE_MAX_OPEN_CONNECTIONS, 10)
    )
  : 50;

// Short enough that a stuck insert fails over to another node quickly; 30s is
// enough for any legitimate batch insert.
const requestTimeoutMs = process.env.CLICKHOUSE_REQUEST_TIMEOUT_MS
  ? Math.max(
      1000,
      Number.parseInt(process.env.CLICKHOUSE_REQUEST_TIMEOUT_MS, 10)
    )
  : 30_000;

// How long to skip a node after a connection failure, before trying it
// again. Short by default — we want fast recovery once a node comes back.
const unhealthyMarkMs = process.env.CLICKHOUSE_UNHEALTHY_MARK_MS
  ? Math.max(0, Number.parseInt(process.env.CLICKHOUSE_UNHEALTHY_MARK_MS, 10))
  : 5000;

export const CLICKHOUSE_OPTIONS: NodeClickHouseClientConfigOptions = {
  max_open_connections: maxOpenConnections,
  request_timeout: requestTimeoutMs,
  keep_alive: {
    enabled: true,
    // Must be lower than server-side keep_alive_timeout (default 10s) so we
    // never reuse a socket the server has already closed.
    idle_socket_ttl: 7000,
  },
  compression: {
    request: requestCompressionEnabled,
  },
  clickhouse_settings: getClickhouseSettings(),
  log: {
    LoggerClass: CustomLogger,
    level: ClickHouseLogLevel.DEBUG,
  },
  // Custom JSON serializer used on inserts. For buffers that already
  // hold JSONEachRow lines as strings (event/replay/bot/group — pulled
  // straight out of Redis), this is a passthrough — no JSON.stringify
  // on the hot path. For buffers that pass real objects (session /
  // profile, which need to parse + transform before inserting), it
  // falls back to JSON.stringify. The client appends '\n' itself.
  json: {
    stringify: <T>(value: T): string =>
      typeof value === 'string' ? value : JSON.stringify(value),
  },
};

// CLICKHOUSE_URL accepts a single URL or comma-separated list. With a list,
// we round-robin across nodes ourselves and fail over on connection errors —
// avoids putting an L4 LB on the hot path.
//
// Tests sometimes import this module without CLICKHOUSE_URL set; in that
// case we fall back to a single client (URL = undefined, delegated to the
// client library's own handling).
const rawClickhouseUrls = (process.env.CLICKHOUSE_URL ?? '')
  .split(',')
  .map((s) => s.trim())
  .filter(Boolean);

const clickhouseUrls = rawClickhouseUrls.length > 0 ? rawClickhouseUrls : [''];

const clients: ClickHouseClient[] = clickhouseUrls.map((url) =>
  createClient({
    url: url || process.env.CLICKHOUSE_URL,
    ...CLICKHOUSE_OPTIONS,
  })
);

const picker = new RoundRobinPicker(clients, clickhouseUrls, unhealthyMarkMs);

logger.info(
  {
    nodeCount: clients.length,
    nodes: clickhouseUrls.map(nodeLabel),
    requestTimeoutMs,
    unhealthyMarkMs,
    options: { ...CLICKHOUSE_OPTIONS, log: undefined },
  },
  'ClickHouse clients initialized'
);

// Bypasses the retry/round-robin layer, for one-off DDL or where retries
// aren't wanted. Points at the first node.
export const originalCh = clients[0]!;

const cleanQuery = (query?: string) =>
  typeof query === 'string'
    ? query.replace(/\n/g, '').replace(/\s+/g, ' ').trim()
    : undefined;

/**
 * Run `operation` against the next round-robin node, retrying on the next
 * node when a connection error is hit. Non-connection errors (SQL, auth,
 * 4xx) propagate immediately.
 *
 * `operation` receives the picked client and a context with the node URL
 * (useful for adding `host` to log lines / metrics).
 */
export async function withRetry<T>(
  operation: (
    client: ClickHouseClient,
    ctx: { url: string; index: number }
  ) => Promise<T>
): Promise<T> {
  return withRoundRobinRetry(picker, operation, logger);
}

/** Best-effort URL → hostname extraction for log labels. */
function urlHostname(url: string | undefined): string | undefined {
  if (!url) {
    return undefined;
  }
  try {
    return new URL(url).hostname;
  } catch {
    return undefined;
  }
}

const INSERT_DEFAULT_SETTINGS: ClickHouseSettings = {
  max_execution_time: 300,
  max_insert_block_size: '500000',
  max_http_get_redirects: '0',
  input_format_parallel_parsing: 1,
  // Keep long-running inserts/queries from idling out at proxies by sending progress headers
  send_progress_in_http_headers: 1,
  http_headers_progress_interval_ms: '50000',
  wait_end_of_query: 1,
};

/**
 * High-level CH client. For insert/command/query, picks a node round-robin
 * and retries on a *different* node if the connection fails. The methods
 * live as own-properties on the proxy target so `vi.spyOn(ch, 'insert')`
 * works in tests. Other method accesses (rare — config readers, instance
 * checks) fall through to the first client.
 */
const chTarget = {
  insert: (params: any) =>
    withRetry((client) => {
      params.clickhouse_settings = {
        ...INSERT_DEFAULT_SETTINGS,
        ...params.clickhouse_settings,
      };
      return client.insert(params);
    }),
  command: (params: any) => withRetry((client) => client.command(params)),
  query: (params: any) => withRetry((client) => client.query(params)),
};

export const ch = new Proxy(chTarget as unknown as ClickHouseClient, {
  get(target, property, receiver) {
    if (property in target) {
      return Reflect.get(target, property, receiver);
    }
    return (originalCh as any)[property];
  },
}) as ClickHouseClient;

/**
 * A query is either finished SQL text or a `sql` fragment carrying its own
 * bound params. Both go through the same round-robin/retry path.
 */
export type ChQueryInput = string | SqlFragment;

export async function chQueryWithMeta<T extends Record<string, any>>(
  query: ChQueryInput,
  clickhouseSettings?: ClickHouseSettings
): Promise<ResponseJSON<T>> {
  const start = Date.now();
  let host: string | undefined;
  const statement = toStatement(query);
  const paramNames = Object.keys(statement.query_params);
  const hasParams = paramNames.length > 0;
  const queryParams = hasParams ? statement.query_params : undefined;
  const res = await withRetry((client, ctx) => {
    host = urlHostname(ctx.url);
    return client.query({
      query: statement.query,
      query_params: queryParams,
      clickhouse_settings: clickhouseSettings,
    });
  });
  const json = await res.json<T>();
  const keys = Object.keys(json.data[0] || {});
  const response = {
    ...json,
    data: json.data.map((item) => {
      return keys.reduce((acc, key) => {
        const meta = json.meta?.find((m) => m.name === key);
        return {
          ...acc,
          [key]:
            item[key] && meta?.type.includes('Int')
              ? Number.parseFloat(item[key] as string)
              : item[key],
        };
      }, {} as T);
    }),
  };

  logger.info(
    {
      host,
      query: cleanQuery(statement.query),
      // Names only: bound values are user data and must not reach the logs.
      queryParams: hasParams ? paramNames : undefined,
      rows: json.rows,
      stats: response.statistics,
      elapsed: Date.now() - start,
      clickhouseSettings,
    },
    'query info'
  );

  return response;
}

export async function chQuery<T extends Record<string, any>>(
  query: ChQueryInput,
  clickhouseSettings?: ClickHouseSettings
): Promise<T[]> {
  return (await chQueryWithMeta<T>(query, clickhouseSettings)).data;
}

export function formatClickhouseDate(
  date: Date | string,
  skipTime = false
): string {
  if (skipTime) {
    return new Date(date).toISOString().split('T')[0]!;
  }
  return new Date(date)
    .toISOString()
    .replace('T', ' ')
    .replace(/(\.\d{3})?Z+$/, '');
}

export function convertClickhouseDateToJs(date: string) {
  return new Date(`${date.replace(' ', 'T')}Z`);
}

const ROLLUP_DATE_PREFIX = '1970-01-01';
export function isClickhouseDefaultMinDate(date: string): boolean {
  return date.startsWith(ROLLUP_DATE_PREFIX) || date.startsWith('1969-12-31');
}
export function toNullIfDefaultMinDate(date?: string | null): Date | null {
  if (!date) {
    return null;
  }
  return isClickhouseDefaultMinDate(date)
    ? null
    : convertClickhouseDateToJs(date);
}