ClickHouse destinations

For more information on destinations, see the Destinations page. When new data is read from a SaaS instance via a Read Action or Subscribe Action, Ampersand inserts the payload as rows into your ClickHouse table.

Prerequisites

Before setting up a ClickHouse destination, ensure that you have:

A ClickHouse instance reachable over HTTPS (ClickHouse Cloud or self-hosted)
A table to insert into (see Data format and table setup)
A username and password with INSERT privileges on that table

Data format and table setup

Ampersand delivers each read or subscribe message as a JSON object. The full payload schema is defined in webhook.yaml. ClickHouse matches the payload’s top-level keys to your table’s columns by name. Create a column for each top-level key you want to store. Keys with no matching column are ignored. result is the array of synced records; store it as a JSON string.

CREATE TABLE ampersand_events (
  action         String,
  objectName     String,
  operationId    String,
  installationId String,
  operationTime  String,
  result         String,
  ...
)
ENGINE = MergeTree()
ORDER BY tuple();

Create a ClickHouse destination

Go to the Destinations page in the Ampersand Dashboard and create a new ClickHouse destination. You’ll need to provide:

Field	Type	Required	Example	Description
Destination name	string	Yes	`ampersandClickHouse`	Alias to reference in your `amp.yaml` file
URL	string	Yes	`https://abc.clickhouse.cloud:8443`	HTTPS URL of your ClickHouse instance
Username	string	Yes	`default`	ClickHouse username
Password	string	Yes		ClickHouse password
Table name	string	Yes	`ampersand_events`	Table to insert rows into
Database	string	No	`default`	Database containing the table
Batch size	integer	No	`1000`	Rows to buffer before flushing a batch (defaults to 1000)
Max wait (seconds)	integer	No	`30`	Max seconds to wait before flushing a batch (defaults to 30)

Refer to the destination in your integration

After creating your ClickHouse destination, reference it in your amp.yaml file:

specVersion: 1.0.0
integrations:
  - name: salesforceToClickHouse
    displayName: Salesforce to ClickHouse
    provider: salesforce
    read:
      objects:
        - objectName: account
          destination: ampersandClickHouse
        - objectName: contact
          destination: ampersandClickHouse

​Prerequisites

​Data format and table setup

​Create a ClickHouse destination

​Refer to the destination in your integration

Prerequisites

Data format and table setup

Create a ClickHouse destination

Refer to the destination in your integration