@otter-sh/source-clickhouse

ClickHouse source for Otter. Implements the Source interface from @otter-sh/core over the ClickHouse HTTP interface using FORMAT JSONEachRow — no native ClickHouse client dependency. Streams rows via fetch in batches of 5 000.

Install

bun add @otter-sh/source-clickhouse

Add it to your project's dependencies alongside @otter-sh/core and @otter-sh/cli. Requires Bun.

Configuration

Import clickhouseSource and declare the source under sources in otter.config.ts:

import { postgresAdapter } from "@otter-sh/adapter-postgres";
import { defineConfig } from "@otter-sh/core";
import { clickhouseSource } from "@otter-sh/source-clickhouse";

export default defineConfig({
  profiles: { dev: { target: postgresAdapter({ url: process.env.PG_URL ?? "" }) } },
  sources: {
    events_ch: clickhouseSource({
      url: process.env.CLICKHOUSE_URL ?? "http://localhost:8123",
    }),
  },
  modelsDir: "models",
});

| Option | Type | Default | Description | | ------ | -------- | ------- | ------------------------------------ | | url | string | — | HTTP(S) URL of the ClickHouse server |

Auth

Embed credentials in the URL (http://user:[email protected]:8123). The driver strips them from the URL and attaches an Authorization: Basic … header. URL-encoded characters in the user/password are decoded before base64 encoding.

Streams

A stream name passed to otter load <source>.<stream> maps directly to a ClickHouse table. Dotted names like default.events are split and each segment is backtick-quoted independently (`default`.`events`).

otter load events_ch.events
otter load events_ch.logs.app_errors   # quoted as `logs`.`app_errors`

Extract behavior

• DESCRIBE TABLE runs first so the target CREATE TABLE uses real Postgres types. ClickHouse types are mapped: Int64/UInt64 → bigint, Float64 → double precision, DateTime* → timestamptz, etc.
• HTTP POST to the ClickHouse endpoint with default_format=JSONEachRow on the query string.
• Body: SELECT * FROM <quoted-stream>[ WHERE <cursor_field> > <cursor>][ ORDER BY <cursor_field> ASC] FORMAT JSONEachRow.
• The response body is read as a stream; each newline-delimited JSON line is parsed into a Row and buffered into 5 000-row batches yielded as AsyncIterable<Row[]>.

Incremental loads

Declare a cursor per stream in sources/<name>.ts:

// sources/events_ch.ts
import { defineSource } from "@otter-sh/core";

export default defineSource({
  streams: {
    events: {
      write_disposition: "append",
      incremental: { cursor_field: "event_time" },
    },
  },
});

The driver rewrites the SQL to WHERE <cursor_field> > <cursor> ORDER BY <cursor_field> ASC and writes the max value seen in the response back to .otter/state.db after the stream drains. Pass --full-refresh to otter load to clear the cursor.

The full result set streams in a single HTTP request — no client-side pagination.

Example

otter load events_ch.events --strategy append

Full documentation

• Driver reference — source-clickhouse
• Interface — sources
• Incremental models — models#model-api, materializations#incremental
• otter load CLI — cli#load

License

MIT