> ## Documentation Index
> Fetch the complete documentation index at: https://docs.goldsky.com/llms.txt
> Use this file to discover all available pages before exploring further.

# ClickHouse

> Write data to ClickHouse for high-performance analytical queries

## Overview

Write data to ClickHouse for high-performance analytical queries. Tables are created automatically with a `ReplacingMergeTree` engine that deduplicates rows on the primary key.

## Configuration

```yaml theme={null}
sinks:
  my_clickhouse_sink:
    type: clickhouse
    from: my_transform
    table: my_table
    primary_key: id
    secret_name: MY_CLICKHOUSE_SECRET
```

## Parameters

<ParamField path="type" type="string" required>
  Must be `clickhouse`
</ParamField>

<ParamField path="from" type="string" required>
  The transform or source to read data from
</ParamField>

<ParamField path="table" type="string" required>
  ClickHouse table name. Created automatically if it doesn't exist. The database is taken from the secret (see [Secret Format](#secret-format)).
</ParamField>

<ParamField path="primary_key" type="string" required>
  Primary key column. Used as the `ORDER BY` key of the created table and as the deduplication key for `ReplacingMergeTree`. For composite keys, pass a comma-separated list (e.g. `block_number,id`).
</ParamField>

<ParamField path="secret_name" type="string" required>
  Name of the Goldsky secret containing ClickHouse connection details
</ParamField>

<ParamField path="batch_size" type="number" default="1000">
  Number of rows to batch before flushing to ClickHouse.
</ParamField>

<ParamField path="batch_flush_interval" type="string" default="100ms">
  Maximum time to wait before flushing a batch. Accepts a duration string (e.g. `1s`, `500ms`).
</ParamField>

<ParamField path="parallelism" type="number" default="1">
  Number of parallel writers used when sending batches to ClickHouse.
</ParamField>

<ParamField path="append_only_mode" type="boolean" default="true">
  When `true` (default), the table is created as `ReplacingMergeTree(insert_time, is_deleted)` and an `is_deleted` column is automatically derived from `_gs_op`. Deletes are handled by the merge process.

  When `false`, the table is created as a plain `ReplacingMergeTree()`. Upserts use `INSERT`, and deletes are issued as `ALTER TABLE ... DELETE` statements.
</ParamField>

<ParamField path="version_column_name" type="string" default="insert_time">
  Column used as the version argument to `ReplacingMergeTree`. When not set, an `insert_time DateTime DEFAULT now()` column is added automatically. When set, the named column must already exist in the input schema. Only applies in `append_only_mode: true`.
</ParamField>

<ParamField path="schema_override" type="object">
  Optional per-column type overrides applied at table creation, mapping column name to a raw ClickHouse type expression (e.g. `timestamp: "DateTime64(3)"`, `updated_at: "DateTime64(3) CODEC(Delta, ZSTD)"`). Useful for narrowing types or adding codecs.
</ParamField>

## Secret Format

Create a ClickHouse secret with `goldsky secret create` and select the `clickHouse` type. The secret stores the following fields:

| Field          | Description                                         |
| -------------- | --------------------------------------------------- |
| `url`          | ClickHouse HTTPS endpoint, e.g. `https://host:8443` |
| `username`     | ClickHouse user                                     |
| `password`     | ClickHouse password                                 |
| `databaseName` | Database the sink will write to                     |

The sink writes to `databaseName`.`table`. The database is configured in the secret, not in the sink YAML.

## Behavior

* **Auto table creation**: The sink creates the destination table on startup using `CREATE TABLE IF NOT EXISTS`.
* **Engine**: `ReplacingMergeTree(insert_time, is_deleted)` by default, `ReplacingMergeTree()` when `append_only_mode: false`.
* **`ORDER BY`**: the `primary_key` column(s).
* **Deduplication**: handled by ClickHouse merges. To read the latest version of each row, query with `SELECT ... FROM table FINAL` or use the `FINAL` modifier in ClickHouse's query settings.
* **Deletes** (`_gs_op = 'd'`): in the default mode, rows are marked `is_deleted = 1` and cleaned up by merges. In `append_only_mode: false`, deletes are issued as `ALTER TABLE ... DELETE`.
* **Type conversion**: Arrow types are mapped to ClickHouse types automatically. Use `schema_override` to customize any column.

## Example: Solana Blocks to ClickHouse

```yaml theme={null}
name: solana-blocks-analytics
resource_size: m

sources:
  solana_blocks:
    type: solana_source
    start_block: "312229952"
    num_workers: "50"

transforms:
  parsed_blocks:
    type: sql
    primary_key: slot
    sql: |
      SELECT
        slot,
        to_timestamp_micros(blockTime * 1000000) as block_timestamp,
        array_length(transactions) as transaction_count,
        blockhash,
        _gs_op
      FROM solana_blocks

sinks:
  clickhouse_blocks:
    type: clickhouse
    from: parsed_blocks
    table: solana_blocks
    primary_key: slot
    secret_name: MY_CLICKHOUSE
```

<Tip>
  ClickHouse is ideal for high-volume data and analytical queries. Use it when
  you need to run aggregations and analytics on your blockchain data.
</Tip>