mirror of
https://github.com/supabase/supabase.git
synced 2026-09-06 09:59:03 +08:00
3bc52101ee
## What kind of change does this PR introduce? Docs update ## Summary - Add Early Access setup and reference guides for ClickHouse, DuckLake, and Snowflake. - Update Pipelines navigation and shared documentation with destination-specific data models, source requirements, schema-change support, and recovery behavior. - Keep all three destinations organization-gated. DuckLake is documented only as a Pipelines replication destination i.e. query compute remains external and this is not a Warehouse launch. <!-- This is an auto-generated comment: release notes by coderabbit.ai --> ## Summary by CodeRabbit * **New Features** * Added ClickHouse, DuckLake, and Snowflake as Early Access Pipelines destinations. * Added BigQuery as a managed destination. * Added destination navigation and setup guides covering configuration, replication behavior, schema changes, type mappings, troubleshooting, and monitoring. * **Documentation** * Clarified destination availability, regional guidance, requirements, limitations, and processing behavior. * Documented destination-specific schema-change support, table identity requirements, reset behavior, and CDC replication modes. <!-- end of auto-generated comment: release notes by coderabbit.ai -->
197 lines
14 KiB
Plaintext
197 lines
14 KiB
Plaintext
---
|
|
id: 'clickhouse-destination'
|
|
title: 'ClickHouse destination'
|
|
description: 'Configure ClickHouse as a Supabase Pipelines destination.'
|
|
subtitle: 'Replicate Supabase Postgres changes to ClickHouse.'
|
|
sidebar_label: 'ClickHouse'
|
|
---
|
|
|
|
<$Partial path="pipelines-public-alpha.mdx" />
|
|
|
|
{/* supa-mdx-lint-disable Rule003Spelling */}
|
|
|
|
<Admonition type="note" label="Early Access">
|
|
|
|
The ClickHouse destination is in Early Access and available only to approved organizations. [Request access](/go/supabase-pipelines-new-destinations) before following this guide.
|
|
|
|
</Admonition>
|
|
|
|
[ClickHouse](https://clickhouse.com/) is a column-oriented database for analytics. Supabase Pipelines can maintain current-state tables in ClickHouse or write an append-only change history, depending on the selected table engine.
|
|
|
|
{/* supa-mdx-lint-disable-next-line Rule001HeadingCase */}
|
|
|
|
## Prepare ClickHouse resources
|
|
|
|
Before creating the destination:
|
|
|
|
1. Create or choose a ClickHouse database for the replicated tables.
|
|
2. Create a dedicated ClickHouse user for Pipelines.
|
|
3. Grant the user access to the target database. Pipelines must be able to:
|
|
- Query `system.databases`, `system.tables`, and `system.columns`
|
|
- Create, alter, truncate, and drop tables
|
|
- Create and drop views when using `ReplacingMergeTree`
|
|
- Insert rows into managed tables
|
|
4. Copy the database's HTTPS endpoint, including its port when required. Pipelines rejects HTTP endpoints and private or internal hostnames.
|
|
|
|
Keep the database otherwise empty. Pipelines manages the replicated tables and current-state views. Don't pre-create or manually alter those objects.
|
|
|
|
The default `ReplacingMergeTree` engine requires ClickHouse **23.5 or newer**. The `MergeTree` event-log engine does not have this minimum-version requirement.
|
|
|
|
{/* supa-mdx-lint-disable-next-line Rule001HeadingCase */}
|
|
|
|
## Configure ClickHouse as a destination
|
|
|
|
1. Open [**Database > Replication**](/dashboard/project/_/database/replication)
|
|
2. Click **Add destination**
|
|
3. Select **ClickHouse**. If it isn't available, [request Early Access](/go/supabase-pipelines-new-destinations).
|
|
4. Select a Postgres publication and enter a destination name
|
|
5. Enter the ClickHouse settings:
|
|
- **URL**: The HTTPS endpoint, including its port when required
|
|
- **User**: The dedicated ClickHouse user
|
|
- **Password**: The user's password, if authentication requires one
|
|
- **Database**: The existing target database
|
|
- **Table engine**: Choose **ReplacingMergeTree** for current-state tables or **MergeTree** for an append-only event log
|
|
6. Click **Create and start pipeline**
|
|
|
|
Managed Pipelines run in **AWS `eu-central-1` (Frankfurt)**. When possible, place the ClickHouse service close to Frankfurt to reduce network latency and replication lag.
|
|
|
|
## How table names are mapped
|
|
|
|
Pipelines maps each Postgres schema and table pair to one ClickHouse table. Existing underscores are doubled, and the schema and table names are joined with one underscore:
|
|
|
|
| Postgres table | ClickHouse table |
|
|
| ---------------- | ----------------- |
|
|
| `public.orders` | `public_orders` |
|
|
| `my_schema.logs` | `my__schema_logs` |
|
|
|
|
Postgres schema and table names cannot start or end with `_` or contain `"` or `;` when replicating to ClickHouse.
|
|
|
|
## Choose a table engine
|
|
|
|
The table engine controls how ClickHouse represents changes. It is selected for the entire destination.
|
|
|
|
| Engine | Data model | Source primary key | Query pattern |
|
|
| ------------------------------ | ----------------------------- | ------------------------------------------------------- | ------------------------------------------- |
|
|
| `ReplacingMergeTree` (default) | Current-state tables | Required | Query the generated `<table>__current` view |
|
|
| `MergeTree` | Append-only CDC event history | Optional for insert-only tables; see requirements below | Query the base table |
|
|
|
|
### ReplacingMergeTree
|
|
|
|
`ReplacingMergeTree` is the default and is intended for current-state analytics. Pipelines:
|
|
|
|
- Uses the source primary key as ClickHouse's sorting and deduplication key
|
|
- Adds an `_etl_version UInt128` ordering column
|
|
- Adds an `_etl_deleted UInt8` tombstone column
|
|
- Creates a `<table>__current` view that runs the base table with `FINAL` and removes deleted rows
|
|
|
|
The `_etl_version` and `_etl_deleted` names are reserved and can't be used by source columns.
|
|
|
|
During Early Access, source primary-key values must remain immutable when using `ReplacingMergeTree`. Updating a primary-key value can leave the old key visible in the generated current-state view. This limitation will be removed when primary-key update handling is available.
|
|
|
|
Query the generated view for the current state:
|
|
|
|
```sql
|
|
select *
|
|
from "public_orders__current";
|
|
```
|
|
|
|
ClickHouse background merges combine older row versions over time. Until they do, querying the base table without `FINAL` can return multiple versions of the same source row. Use the generated `__current` view for normal current-state queries.
|
|
|
|
Pipelines does not run `OPTIMIZE ... FINAL CLEANUP`. ClickHouse operators remain responsible for any physical tombstone cleanup required by their storage-retention policy.
|
|
|
|
### MergeTree
|
|
|
|
`MergeTree` stores every replicated change as an append-only event. Pipelines adds:
|
|
|
|
- `cdc_operation`, containing `INSERT`, `UPDATE`, or `DELETE`
|
|
- `cdc_lsn`, containing the Postgres commit LSN for the change
|
|
|
|
The `cdc_operation` and `cdc_lsn` names are reserved and can't be used by source columns.
|
|
|
|
Read the base table to analyze the event history. Multiple changes committed in one Postgres transaction can share the same `cdc_lsn`, so it is not a unique event ID or a total ordering for reconstructing current state.
|
|
|
|
A source `TRUNCATE` truncates the ClickHouse table for either engine. Resetting a table also drops and recreates its table and, for `ReplacingMergeTree`, its generated view. These operations erase the destination data accumulated for that table before a new initial sync begins.
|
|
|
|
## Source table requirements
|
|
|
|
ClickHouse requirements depend on the engine and the operations published for a table.
|
|
|
|
| Source table and publication | Support | Guidance |
|
|
| --------------------------------------------------- | ------- | ------------------------------------------------------------------------------------------------------------------------------------------- |
|
|
| `ReplacingMergeTree` table without a primary key | No | Add a source primary key, include all primary-key columns in the publication, or use `MergeTree` for an event-log layout. |
|
|
| Insert-only `MergeTree` table without a primary key | Yes | No row identity is required for inserts. |
|
|
| Table with a primary key | Yes | Include every primary-key column in the publication. |
|
|
| Updates with primary-key replica identity | No | Use `REPLICA IDENTITY FULL` so unchanged out-of-line values can be reconstructed. |
|
|
| Deletes with primary-key replica identity | Yes | The publication must include every primary-key column. |
|
|
| Updates or deletes with `REPLICA IDENTITY FULL` | Yes | Full identity provides the complete row image required for updates. |
|
|
| Updates with `REPLICA IDENTITY USING INDEX` | No | Use `REPLICA IDENTITY FULL`. |
|
|
| Deletes with `REPLICA IDENTITY USING INDEX` | Limited | Supported only when the selected index resolves to the same columns as the source primary key. Alternative unique indexes aren't supported. |
|
|
| Updates or deletes with `REPLICA IDENTITY NOTHING` | No | Configure primary-key or full identity for deletes and full identity for updates. |
|
|
|
|
Top-level Postgres array columns can contain arrays with nullable elements, but the array column itself must not contain `NULL`. ClickHouse's RowBinary format cannot encode a top-level `NULL` array. Replace existing `NULL` values and make the source array column `NOT NULL`, or ensure producers always write an array value. Empty arrays remain supported.
|
|
|
|
## Type mapping
|
|
|
|
Pipelines creates ClickHouse columns with these mappings:
|
|
|
|
| Postgres type | ClickHouse type |
|
|
| ----------------------------- | ---------------------------- |
|
|
| `boolean` | `Boolean` |
|
|
| `smallint` | `Int16` |
|
|
| `integer` | `Int32` |
|
|
| `bigint` | `Int64` |
|
|
| `real` | `Float32` |
|
|
| `double precision` | `Float64` |
|
|
| `date` | `Date32` |
|
|
| `timestamp without time zone` | `DateTime64(6)` |
|
|
| `timestamp with time zone` | `DateTime64(6, 'UTC')` |
|
|
| `uuid` | `UUID` |
|
|
| `oid` | `UInt32` |
|
|
| Other scalar and custom types | `String` |
|
|
| Arrays | `Array(Nullable(<element>))` |
|
|
|
|
Nullable scalar columns are wrapped in `Nullable(...)`. Character, text, `numeric`, `money`, JSON, `time`, `interval`, binary, bit-string, enum, and unsupported custom values are serialized into `String` columns rather than stored as native ClickHouse types.
|
|
|
|
## Schema change support
|
|
|
|
ClickHouse schema change support is limited during Early Access.
|
|
|
|
Supported changes include:
|
|
|
|
- Adding columns
|
|
- Renaming columns, except moving a nested subcolumn to a different parent
|
|
- Dropping columns
|
|
- Dropping `NOT NULL` from an existing scalar column
|
|
- Adding, changing, or removing supported column defaults
|
|
|
|
The following changes are not applied automatically:
|
|
|
|
- Changing a column's data type
|
|
- Adding `NOT NULL` to an existing nullable column
|
|
- Changing, dropping, or renaming a source primary-key column when using `ReplacingMergeTree`
|
|
- Renaming a source table or schema
|
|
|
|
New scalar columns are made nullable when ClickHouse needs a value for historical rows and the source default cannot be represented safely. Some Postgres defaults cannot be translated to ClickHouse and are skipped with a warning.
|
|
|
|
ClickHouse DDL is not transactional. An interrupted multi-column schema change can leave a partially applied destination schema. Don't repair managed tables or views manually. If the pipeline remains failed after a restart, [contact support](/dashboard/support/new).
|
|
|
|
## Troubleshooting
|
|
|
|
| Issue | Resolution |
|
|
| -------------------------------------------------- | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
|
|
| ClickHouse isn't available in the destination list | The destination is organization-gated during Early Access. [Request access](/go/supabase-pipelines-new-destinations). |
|
|
| URL validation fails | Use the public ClickHouse HTTPS endpoint, including its port when required. HTTP, localhost, and private or internal endpoints are not supported. |
|
|
| Connection fails | Confirm the endpoint is reachable from the internet and that the username and password are correct. |
|
|
| Database validation fails | Create the configured database and grant the user permission to read `system.databases`. |
|
|
| Validation succeeds but table setup or writes fail | Grant the user the target-database permissions listed above. Check for a pre-existing table or view with the generated name, and don't manually modify managed objects. |
|
|
| `ReplacingMergeTree` initialization fails | Confirm the server is ClickHouse 23.5 or newer and every source table has a published primary key. |
|
|
| Updates or deletes fail | Use `REPLICA IDENTITY FULL` for updates. Deletes can use primary-key identity or full identity. Include all identity columns in the publication. |
|
|
| A nullable array fails to replicate | Replace top-level `NULL` array values and make the source column `NOT NULL`, or ensure producers always write an array. Empty arrays are supported. |
|
|
| A schema change fails | Check the supported changes above. ClickHouse DDL can be partially applied, so don't repair managed objects manually. [Contact support](/dashboard/support/new) with the pipeline ID and error details. |
|
|
|
|
## Additional resources
|
|
|
|
- [ClickHouse documentation](https://clickhouse.com/docs)
|
|
- [ReplacingMergeTree](https://clickhouse.com/docs/engines/table-engines/mergetree-family/replacingmergetree)
|
|
- [Monitor pipeline status](/docs/guides/database/replication/pipelines-monitoring)
|