quickhouse

Move tables from PostgreSQL, MySQL, BigQuery, or ClickHouse into ClickHouse or BigQuery — fast, in one function call.

quickhouse is a small, typed Python API on top of a native Rust engine. You hand it a source, a destination, and a table name; it figures out the schema, creates the destination table, streams the rows across in parallel, and keeps memory flat the whole way. The heavy lifting never touches Python objects — each database's native wire protocol flows straight into Apache Arrow and out the other side.

import quickhouse

src = quickhouse.Postgres("postgresql://user:pw@localhost:5432/shop")
dst = quickhouse.ClickHouse("http://localhost:8123", database="analytics")

result = quickhouse.sync(src, dst, dest_table="orders",
                         source_table="orders", mode="full", key=["id"])
print(result)   # rows_read, rows_written, bytes_written, duration_secs, new_watermark

Status: pre-1.0. quickhouse is used against real production data and is covered by an integration test suite, but the Python API may still change between minor versions before 1.0. Pin a compatible range (e.g. quickhouse~=0.7) and watch the CHANGELOG. A few knobs are marked experimental in the API docs — those may change without a major bump.

When to use quickhouse

Reach for it when you want to move whole tables — full refresh or incremental — from PostgreSQL/MySQL/BigQuery/ClickHouse into ClickHouse or BigQuery, fast, from your own Python jobs with almost no setup. It fits cron/Airflow/Dagster tasks and one-off backfills well.

Look elsewhere when you need in-warehouse SQL transformations (use dbt), change-data-capture or streaming (use Debezium/Kafka), a large catalog of SaaS connectors (use Airbyte / Fivetran / dlt), or arbitrary source↔destination pairs — quickhouse deliberately supports a focused, fast set:

Supported sources and destinations

BigQuery and ClickHouse are each both a source and a destination — so a cross-cluster ClickHouse copy, or publishing a ClickHouse mart into BigQuery, is an ordinary sync(). Some knobs are engine-specific: column_transforms / read_max_rows_per_sec apply to the PostgreSQL, MySQL and ClickHouse sources; merge_prune_partition_by / delete_stale_in_window apply to BigQuery-destination incremental syncs.

Why quickhouse

• It's fast. Rows are decoded straight off the wire into Arrow, in Rust — no per-row Python, no intermediate DataFrame. Tables are split into ranges and read in parallel, and decoding overlaps uploading. On a laptop-class box a 1M-row, 20-column full refresh runs at hundreds of thousands of rows per second, and memory is bounded by max_memory_bytes rather than by table size. Reproduce it with python benchmarks/bench_transfer.py.

• It's one function call. sync() replaces the cursor loop, manual batching, retry logic, and CREATE TABLE you'd otherwise write by hand. Defaults handle table creation, type mapping, parallelism, and batching, and a typed stub gives you autocomplete on every argument.

• It's safe with real, messy data. Full refreshes swap in atomically, so a crash never leaves a half-written table. Incremental syncs are idempotent — safe to re-run or retry. Transient network blips retry automatically. And legacy quirks like MySQL zero-dates or out-of-range timestamps are coerced to NULL with a warning instead of aborting the run.

• It's gentle on a small production database. Set read_max_rows_per_sec and quickhouse paces the read to that aggregate rate across all partitions; because COPY/streaming results only produce as fast as the client consumes, the source scan itself backs off — you're throttling the database's work, not just your own. Combine it with parallelism=1 (one connection), incremental mode (only new rows), and a statement_timeout_secs, point it at a read replica, and a bulk export stops competing with your app. The Postgres connection also shows up as application_name = 'quickhouse' in pg_stat_activity, so a DBA can see and kill it.

• There's nothing to stand up. pip install quickhouse and you're done — no JVM, no Spark cluster, no separate service. It's an ordinary Python dependency that runs wherever your jobs already run: cron, Airflow, Dagster, a Lambda, or a plain script.

Install

pip install quickhouse
pip install "quickhouse[progress]"   # adds a ready-made tqdm progress bar

Prebuilt wheels ship for Python 3.9+ on Linux (x86_64), macOS (Apple Silicon), and Windows (x64) — no Rust toolchain needed. On other platforms (Intel macOS, Linux aarch64) pip builds from the source distribution, which needs a Rust toolchain (see CONTRIBUTING.md).

Using it

A fuller call, with the options you'll reach for most:

import quickhouse as qh

src = qh.Postgres("postgresql://user:pw@localhost:5432/shop")
dst = qh.ClickHouse("http://localhost:8123", database="analytics")

qh.sync(
    src, dst,
    dest_table="orders",
    source_table="orders",        # or source_query="SELECT ..."
    mode="incremental",           # or "full"
    watermark="updated_at",       # required for incremental
    key=["id"],                   # dedup key / ORDER BY
    parallelism=8,
    exclude=["internal_notes"],
    rename={"amount": "amt"},
    on_progress=lambda p: print(f"{p.rows_written:,} rows @ {p.rows_per_sec:,.0f}/s"),
)

Sources and destinations

Pick a source and a destination by constructing the matching object — everything else about sync() stays the same:

# sources
qh.Postgres("postgresql://user:pw@host:5432/db")
qh.MySQL("mysql://user:pw@host:3306/db", require_tls=True)
qh.BigQuery("my-gcp-project")                       # source_table="dataset.table"
qh.ClickHouse("http://ch-a:8123", database="raw")   # ClickHouse reads too

# destinations
qh.ClickHouse("http://host:8123", database="analytics")
qh.BigQuery("my-gcp-project", dataset_id="analytics")

BigQuery authenticates with a service-account key (credentials_file=...) or Application Default Credentials. As a destination it also takes write_method: the default "storage_write" (the gRPC Storage Write API — free up to 2 TiB/month and higher-throughput) or the legacy "insert_all" (tabledata.insertAll, billed at roughly double and slower).

DataFrames — from_pandas

A frame you already hold in memory goes in through from_pandas rather than sync (pip install 'quickhouse[pandas]'):

qh.from_pandas(df, dst, dest_table="orders", mode="full", key=["id"])

# Upsert on key — and unlike every other source, no watermark is needed.
# A database source needs one to know where to resume reading; you already
# hold every row, so the frame IS the delta.
qh.from_pandas(df, dst, dest_table="orders", mode="incremental", key=["id"])

The conversion is Arrow, so pandas is the headline rather than the requirement: a pyarrow.Table, a polars.DataFrame, a DuckDB relation, or anything exposing __arrow_c_stream__ all work. Every other sync() argument is forwarded unchanged. Categoricals, nullable Int64, Decimal objects and tz-aware timestamps are handled; nanosecond precision, nested columns and out-of-range dates are refused by column name rather than silently coerced. See the DataFrames guide.

HTTP API sources — CleverTap, AppsFlyer & generic HTTP

These pull directly from HTTP APIs into either destination (BigQuery or ClickHouse): purpose-built CleverTap and AppsFlyer sources, plus a generic HttpApi source for any other REST/JSON or CSV endpoint. API data has no catalog, so you declare the schema: each column's name, its BigQuery-style type, and — for nested JSON — a dotted path into the record. The declared type drives the destination table; the watermark's from/to date window drives incremental pulls.

# CleverTap Data Export API (events) -> BigQuery.
# region MUST match your CleverTap account's region (sg1 / us1 / eu1 / in1 / aps3 / mec1);
# it selects the API host, and the wrong region silently returns no data.
qh.sync(
    qh.CleverTap(
        account_id="...", passcode="...",   # or load from a secret manager
        event_name="App Launched", region="sg1",
        columns=[
            ("identity", "STRING", "profile.identity"),   # dotted path into the record
            ("email",    "STRING", "profile.email"),
            ("ts",       "TIMESTAMP"),                     # packed yyyyMMddHHmmSS int (parsed as UTC)
            ("app_ver",  "STRING", "profile.app_version"),
        ],
        from_date="2026-07-01",             # window start (first-run floor for incremental)
    ),
    qh.BigQuery("my-gcp-project", dataset_id="analytics"),
    dest_table="clevertap_app_launched",
    mode="incremental", watermark="ts", key=["identity"],
)

# AppsFlyer raw-data Pull API (CSV report) -> BigQuery. columns map to CSV headers.
qh.sync(
    qh.AppsFlyer(
        api_token="...", app_id="id123456789", report_type="installs_report",
        columns={"install_time": "TIMESTAMP", "media_source": "STRING", "campaign": "STRING"},
        from_date="2026-07-01",
    ),
    qh.BigQuery("my-gcp-project", dataset_id="analytics"),
    dest_table="af_installs", mode="full",
)

# Generic HTTP source — any REST/JSON or CSV endpoint. Auth goes in headers;
# records_path locates the JSON array; cursor pagination is optional.
qh.sync(
    qh.HttpApi(
        "https://api.example.com/v1/events?since={from}",
        columns=[("id", "INTEGER"), ("ts", "TIMESTAMP"), ("name", "STRING", "attrs.name")],
        headers={"Authorization": "Bearer ..."},
        records_path="data.items",                 # JSON array location
        next_cursor_path="paging.next", cursor_param="cursor",   # follow the cursor
        from_date="2026-07-01",
    ),
    qh.ClickHouse("http://localhost:8123", database="analytics"),  # or BigQuery
    dest_table="events", mode="incremental", watermark="ts", key=["id"],
)

Notes: incremental re-pulls the boundary day each run, so key is required (BigQuery MERGE dedups it). NUMERIC is delivered exactly (declare it only for values sent as JSON strings/integers); BIGNUMERIC is lossy. CleverTap's top-level ts is a packed yyyyMMddHHmmSS integer (not epoch seconds) — declare it TIMESTAMP/DATETIME/DATE and it's parsed as UTC civil time. Nested RECORD/STRUCT types can't be declared in bq_type; point a JSON (or STRING) column at a nested object/array via its path and it lands as compact JSON text. AppsFlyer's Pull API has hard daily-call/row caps — for high volume use its Data Locker (files in a bucket) instead. A full-refresh (mode="full", the default) REPLACES the destination; since these sources are day/event-scoped, prefer mode="incremental" for an existing table (a shrinking full swap is warned about but still executes).

Three knobs tune the incremental behavior for these sources:

• lookback_days=N — on each resume, re-pull a rolling N-day window before the cursor so late-arriving/restated events past the boundary day aren't missed (both APIs restate history; AppsFlyer attribution updates for days). MERGE-on-key dedups the overlap in incremental mode.
• mode="append" — a bronze-landing write: insert each window's rows straight into the destination with no staging/merge/swap and no dedup, running your own consolidation MERGE downstream. watermark drives the resume window; key isn't required. Avoids per-run table-metadata churn (BigQuery's ~5-ops/10s/table limit) since there's no staging create/swap. API sources only.
• delete_stale_in_window=True (BigQuery incremental) — also DELETE destination rows inside the merged window that are absent from the source pull ("replace this window"; a NULL merge key nets to a replace instead of duplicating). Requires merge_prune_partition_by — the DELETE is scoped to that immutable column's staging range, so it never touches history outside the batch (a hard error otherwise).

Backup to cloud storage during the transfer

Either destination can also archive every synced batch to Google Cloud Storage or S3 as a data lake — a secondary, best-effort-free backup independent of the destination's own retention:

dst = qh.ClickHouse(
    "http://host:8123", database="analytics",
    archive=qh.backup(destination="gcs", format="parquet",
                      bucket="my-data-lake", prefix="quickhouse"),
)
qh.sync(src, dst, dest_table="orders", source_table="orders", mode="full", key=["id"])

backup() is a thin factory over the two descriptors, which you can also name directly — qh.GcsArchive(bucket=...) and qh.S3Archive(bucket=...). Parquet is the only supported format; any other value is rejected at the call.

This streams Parquet — one file per parallel partition (per chunk, with chunk_rows), never fully buffered in memory — to gs:// or s3://{bucket}/{prefix}/{dest_table}/dt=<date>/ run=<id>/part-<partition>.parquet, a Hive-style layout directly queryable by BigQuery external tables, Athena, Spark, or DuckDB. It works for every source and both destinations, so a CleverTap pull or a from_pandas() frame is archived the same way a Postgres table is.

GCS credentials fall back to Application Default Credentials and the standard SERVICE_ACCOUNT/GOOGLE_SERVICE_ACCOUNT environment, exactly like the BigQuery descriptor; pass credentials_file= or credentials_json= to override. S3 falls back to the standard AWS chain (env vars, IAM role); pass endpoint= for an S3-compatible service like MinIO.

A persistent upload failure fails the whole sync() call, same as a destination insert failure — the archive never silently falls behind. Set archive= on the destination descriptor: one set on the source does nothing, and says so with an ignored_source_archive warning. Storage and request costs are billed by Google/AWS as usual (free on a self-hosted MinIO).

The DDL knobs (engine, partition_by, order_by, primary_key, key) are interpreted per destination — for ClickHouse they shape the MergeTree DDL; for BigQuery they map to partitioning and clustering. quickhouse creates the table for you (create_if_missing=True by default) with a sensible schema derived from the source.

Full vs. incremental

Full reloads the whole table into a staging table, then swaps it into place atomically — a crash mid-run never leaves the destination partial. For a BigQuery destination that swap runs as a query (a billed scan of the staged data), not a free copy job — BigQuery's copy jobs can silently skip rows still sitting in a table's streaming buffer, so a real query is what keeps this correct rather than just fast. One accepted tradeoff on the ClickHouse path: an insert retried after a lost acknowledgment (not after a crash — the transfer is still running) can duplicate one batch's rows in the staging table, since mode="full" has no engine-level dedup like ReplacingMergeTree — rare, and harmless for key-based incremental syncs, but worth knowing if you see an unexpected small over-count on a full-refresh right after a transient network blip.

Incremental tracks a high-water mark (the watermark column) in a small state table in the destination and copies only newer rows. Updated rows are deduplicated on key — via ClickHouse's ReplacingMergeTree, or a MERGE upsert on BigQuery (where key is therefore required). Re-running with no new data does nothing.

Both modes stage through a per-run-unique table ({dest}_quickhouse_tmp_<id>) that's dropped when the run finishes, including on failure. The unique name is what makes rapid re-runs and whole-call retries safe on BigQuery, whose streaming ingestion rejects writes into a table recently recreated under the same name.

For daily syncs that need to catch late-arriving or edited rows, set lookback_seconds to re-scan a trailing window (e.g. 3 * 86400 for the last three days) — the dedup above keeps that overlap from creating duplicates.

Cursor control. A few knobs make the incremental cursor robust in the real world:

• state_key pins the cursor's identity. By default it's keyed by the source table (or query text) + destination — so editing a source_query's WHERE would silently start a fresh full pull, and two syncs into one destination tracking different watermark columns would clobber each other's cursor. Set state_key="orders:updated_at" to give each a stable, distinct identity.
• skip_to_max=True (or seed_watermark="<value>") seeds the cursor on the first run only, then self-retires. Use it when the destination already holds complete data from a previous pipeline and a full first pull would be a waste — it starts the cursor at the source's current max instead of scanning everything.
• advance_watermark=False reads and merges a window without moving the cursor — for loading a historical backfill without rewinding your regular schedule.

MERGE cost on large BigQuery tables. By default an incremental MERGE scans the whole destination table (it joins on key only), so upserting a few delta rows into a huge partitioned table bills the whole table each run. Set merge_prune_partition_by="created_at" to bound the scan to the staging batch's range and let BigQuery prune partitions. Only do this for an immutable column — one whose value never changes for a given key, i.e. a created_at/inserted-at column that is also the partition column. Do not point it at a updated_at/updated-at column: an updated row's new value lands in a different partition than the existing row, so pruning would miss it and insert a duplicate key instead of updating. quickhouse can't detect mutability, so this is a deliberate opt-in; the default full scan is always correct.

Watching progress and diagnosing failures

on_progress is a plain callback you can point at anything; qh.progress_bar() wraps tqdm for a ready-made bar. Every sync() also logs each step to stderr (RUST_LOG=quickhouse_core=debug for the actual SQL).

When something goes wrong, sync() raises a RuntimeError written to be actionable on its own: it names the table involved, and for a bad config or an unmappable column it says exactly what's wrong and how to fix it (e.g. exclude= the column or cast it in a source_query). Underlying database errors are surfaced verbatim rather than wrapped in something generic.

Full parameter list

How types are mapped

quickhouse maps each source type to a sensible destination type automatically: integers to integers, floats to floats, text/JSON/UUID to strings, dates and timestamps across as-is, and booleans preserved. A few deliberate choices worth knowing:

• Arbitrary-precision decimals (numeric/DECIMAL/NUMERIC) default to Float64, since precision can't be recovered from the type alone — pin an exact type with type_overrides (e.g. "Decimal(18, 2)", P <= 38) and the value is decoded exactly (no Float64 round-trip), not just declared with the right destination type. A value that doesn't fit the declared precision, or is NaN/Infinity (PostgreSQL numeric only), coerces to NULL with a warning, same as the out-of-range-date handling below. P > 38 (Decimal256) isn't supported yet and is rejected as a config error up front, rather than silently falling back to Float64.
• TIME columns transfer as canonical text into a String column (ClickHouse has no time-of-day type).
• MySQL DATETIME/TIMESTAMP map to a UTC-aware timestamp (BigQuery TIMESTAMP, ClickHouse DateTime64(6, 'UTC')) — the wall-clock value is read as UTC, matching how a TIMESTAMP column expects it. To land a column as a naive BigQuery DATETIME instead, opt out per-column with type_overrides={"col": "DATETIME"} — that flips the actual encoding, not just the declared type. (PostgreSQL keeps the distinction natively: timestamptz → UTC-aware, timestamp → naive.)
• Out-of-range dates (and MySQL zero-dates like 0000-00-00) coerce to NULL with a warning rather than failing the transfer.
• Nullable source columns stay nullable in the destination.

Arrays and composite (RECORD/STRUCT) types aren't supported yet.

Limitations

• Auth: connect with a DSN string or discrete host/port/user/ password/database fields; add a private CA via ca_cert_file=... (e.g. AWS RDS), or client-certificate mTLS via client_cert_file=... + client_key_file=... (Postgres and MySQL). BigQuery accepts credentials_file, inline credentials_json, or ADC.
• Array / composite types aren't mapped yet.
• from_pandas is not bounded-memory — the frame is in RAM by definition. Expect a transient peak of roughly 3–4x its Arrow footprint while it is converted, serialized and decoded. Reading a table back into a DataFrame (to_pandas) is not implemented yet.
• BigQuery as a source reads through a single connection — parallelism becomes a server-side hint rather than true client-side fan-out (a limitation of the underlying crate's read API).
• CDC / streaming and arbitrary in-source transforms are out of scope.
• Single table per call — sync() moves one table; loop over a list for many.

Command-line interface

For cron/CI jobs you can drive a transfer from a TOML file instead of writing Python:

pip install "quickhouse[cli]"   # the extra is only needed on Python < 3.11
quickhouse run job.toml
quickhouse --version

The job file has three tables — [source], [target], [sync]. type picks the engine; every other key maps to that descriptor's constructor / to sync()'s arguments, and ${ENV_VAR} is expanded so credentials stay in the environment:

[source]
type = "postgres"
dsn = "${PG_DSN}"

[target]
type = "clickhouse"
url = "http://localhost:8123"
database = "analytics"

[sync]
dest_table = "orders"
source_table = "orders"
mode = "incremental"
watermark = "updated_at"
key = ["id"]

See examples/job.toml.

Stability and versioning

quickhouse follows Semantic Versioning. It is pre-1.0: while on 0.x, minor versions (0.7 → 0.8) may contain breaking API changes, and patch versions (0.7.0 → 0.7.1) are backwards-compatible fixes. Every change is recorded in the CHANGELOG; pin a compatible range (e.g. quickhouse~=0.7).

A few surfaces are experimental and may change even in a patch release — they're flagged in the API docstrings: chunk_rows, merge_prune_partition_by, delete_stale_in_window, BigQuery(write_method="storage_write"), and column_transforms. The core sync() call and the connection descriptor classes are considered stable within a minor version.

Contributing

Bug reports, new source/type mappings, and PRs are welcome — see CONTRIBUTING.md for build steps, tests, and layout.

License

MIT