ClickHouse integration

ClickHouse is a database built for fast analytics over very large datasets. With this integration, you can pull records into Clay with a query you write, and send data back by inserting, looking up, updating, or upserting rows in your ClickHouse tables.

Connecting to ClickHouse

To connect ClickHouse, you'll need the address and port of your ClickHouse instance, plus a username and password that can reach it. Clay asks for these the first time you add a ClickHouse source or action and you click + Add account.

• Name your connection: A name to identify this connection in Clay.
• Host (e.g. https://abc123.us-east-1.aws.clickhouse.cloud): The address of your ClickHouse instance. If you leave off the https://, Clay adds it for you.
• Port (8443 for ClickHouse Cloud): The port to connect on.
• Username and Password: The ClickHouse user Clay connects as.
• Default database: Optional. The database Clay falls back to when an action doesn't name one.

Clay tests the credentials when you save, so you'll know right away whether the user can reach the server. Give that user permission to write to the tables you plan to send data to — a read-only user can still import rows and run the Lookup row action, but ClickHouse will reject the write actions.

Creating a table with ClickHouse

1. In a workbook, click + Add at the bottom.
2. Search for ClickHouse and select Import from ClickHouse from the results.
3. In the modal, you will be asked to Select ClickHouse account.
   • If you haven't connected ClickHouse yet, click + Add account and fill in the connection fields.

Source Import from ClickHouse

Pulls records from your ClickHouse instance into a Clay table using a query you write.

Inputs

• SQL query: The query to run, for example SELECT * FROM my_table LIMIT 100. Only SELECT and WITH statements are accepted, and Clay adds a consistent sort order behind the scenes so that paging through a large result set doesn't repeat or skip rows.
• Unique identifier: The column that identifies each row uniquely, which Clay uses to deduplicate records as they arrive. The picker appears once your query is valid and lists the columns the query returns, so include that column in your SELECT.

Enriching data with ClickHouse

1. While in a Clay table, click Add enrichment and search for ClickHouse.
2. Under Integrations, select one of the ClickHouse options.
3. In the modal, you will be asked to Select ClickHouse account.
   • If you haven't already connected your ClickHouse account, click + Add account and fill in the connection fields.

Every write action needs at least one column mapped in Column mapping before it will run. Beyond that, the three differ mainly in which rows they end up affecting.

Database and Table are pickers filled from your connection. Click the gear button on either one and switch it to Text with tokens to set the target per row from your own table data.

Action Insert row

Adds a new row to a ClickHouse table.

Inputs

Required:

• Database: The database that contains the table. (Required if your connection has no Default database.)
• Table: The table to write to.

Optional:

• Column mapping: One field per column in the selected table. Map the values you want to write and leave the rest blank to let each column use its own default.

Outputs

• Inserted: The number of rows written for the record.

Action Lookup row

Checks whether a row exists in your ClickHouse instance and returns what it finds.

Inputs

Required:

• SQL query: The query to run, for example SELECT * FROM my_table WHERE id = 1. Only SELECT and WITH statements are accepted.

Outputs

• Rows: The matching rows, with every column the query returned. When nothing matches, the cell reports that no rows were found.

Action Update row

Updates the rows in a ClickHouse table that match a condition you write.

Inputs

Required:

• Database: The database that contains the table. (Required if your connection has no Default database.)
• Table: The table to update.
• Where clause: The condition selecting the rows to update, for example WHERE id = 1. It has to start with WHERE.

Optional:

• Column mapping: One field per column in the selected table. Map the columns you want to change and leave the rest blank to keep their current values.

Outputs

• Update completed: Returns true once ClickHouse has finished rewriting the matching rows.

ClickHouse applies an update by rewriting each matching row, and Clay waits for that work to finish before the cell succeeds. A success therefore means the new values are really in the table, and an update against a very large table can take a while.

Action Upsert row

Writes a row and lets ClickHouse merge it with the existing row for the same key, so the table keeps one row per key.

Inputs

Required:

• Database: The database that contains the table. (Required if your connection has no Default database.)
• Table: The table to write to.

Optional:

• Column mapping: One field per column in the selected table. Map the values you want to write and leave the rest blank to let each column use its own default.

Outputs

• Upserted: The number of rows written for the record.

Unlike Update row, you don't tell Upsert row which rows to match. ClickHouse does the matching itself, using the target table's ORDER BY key, and it happens when the table merges in the background rather than the moment you write.

Both rows can exist for a short while until that merge runs, so a read taken straight after an upsert may return two. What the merge does with them depends on the table's engine.

Run settings

• Auto-update: Use it to keep ClickHouse in step with your Clay table as rows are added or change.
• Only run if: The enrichment will only run if conditions are met. (Learn more about conditional formulas).

FAQs

Why does Update row take longer than the other write actions?

Clay runs fewer updates at once than inserts, and sends them in smaller groups, so updates to the same table don't queue up behind each other.

An insert only appends data, which is why it comes back so much faster.

Will re-running an insert write the row twice?

Clay tags each write, so a repeat of the same values — whether an automatic retry or a re-run you trigger yourself — is recognized as the same row rather than a second one. ClickHouse Cloud and replicated tables honor that tag; a plain MergeTree table ignores it unless your server is set up for insert deduplication.

If a write fails with a timeout, Clay reports it instead of retrying, so you can check the table yourself before running it again.

Can I write a value for ClickHouse to work out, like a timestamp or a fresh ID?

In Update row, type null, true, false, or a function such as now() or generateUUIDv4() into a column, and Clay hands it to ClickHouse to evaluate rather than storing it as text.

Typing null is also how you clear a column, since leaving the field blank means "leave this column alone" instead.

Can I read from or write to a database other than the one on my connection?

Yes. Insert row, Update row, and Upsert row each have their own Database picker, so they can target any database the connected user can see.

Import from ClickHouse and Lookup row take a query instead of a picker, so include the database in the table name — my_database.my_table — whenever you're reading outside your connection's Default database.

Why did every record in a run fail when only one value was wrong?

Records heading for the same table are written together in a single request, so if ClickHouse rejects one value the whole group fails and each record shows the same error. Update row works the same way: every record in the group is applied as one statement, so a single bad value or Where clause stops all of them.

The message ClickHouse returns names what it couldn't accept, which is usually enough to find the column behind it.