ClickHouse

Connect ClickHouse Cloud or self-hosted ClickHouse to sync data into Conversion.

Before You Begin

Admin access to your ClickHouse service: You’ll create a role and a user and grant privileges. In ClickHouse Cloud, the default user in the SQL console has what you need. On a self-hosted server, use a user with ACCESS MANAGEMENT.
Your service hostname and HTTPS port: In ClickHouse Cloud, open your service and click Connect; the hostname looks like abc123.us-central1.gcp.clickhouse.cloud and the HTTPS port is 8443. For a self-hosted server, use the host and the HTTPS port configured for the HTTP interface.
A database and tables to sync: Identify the data Conversion should be able to read.

Step 1: Set Up ClickHouse Access

Conversion connects over HTTPS using a username and password to run read-only queries. Run the following statements in the ClickHouse Cloud SQL console (or clickhouse-client on a self-hosted server) as an admin user.

Create a Role and Grant Access

Create a role with read access to your database. Database-level grants also cover new tables you add later.

-- Create the role
CREATE ROLE IF NOT EXISTS conversion_sync_role;

-- Allow the role to read everything (current + future) inside the database
-- Replace my_database with the database you want Conversion to sync from
GRANT SELECT, SHOW ON my_database.* TO conversion_sync_role;

SHOW lets Conversion list your tables and columns in the schema browser. Conversion reads table metadata from system.tables and system.columns, which need no additional grant.

Granting Access table-by-table

If your database contains data Conversion shouldn’t see, grant access to specific tables instead of the whole database:

CREATE ROLE IF NOT EXISTS conversion_sync_role;

-- Replace with your database and table names
GRANT SHOW   ON my_database.*        TO conversion_sync_role;
GRANT SELECT ON my_database.users    TO conversion_sync_role;
GRANT SELECT ON my_database.orders   TO conversion_sync_role;
-- Add more tables as needed

Grant access to each new table you want to sync.

Create a User

Create a dedicated user with a strong password and assign the role.

-- Create the user with a strong password (you'll paste it into Conversion in Step 2)
CREATE USER IF NOT EXISTS conversion_sync_user
  IDENTIFIED WITH sha256_password BY '<strong-password>'
  SETTINGS readonly = 2;

-- Assign the role
GRANT conversion_sync_role TO conversion_sync_user;

readonly = 2 allows SELECT queries and per-query settings, but prevents writes.

Verify the grants worked

Confirm the user can see your database and cannot write:

SHOW GRANTS FOR conversion_sync_user;                 -- expect the role
SELECT count() FROM my_database.users;                -- run as conversion_sync_user: expect a number
CREATE TABLE my_database.should_fail (x UInt8) ENGINE = Memory;  -- as conversion_sync_user: expect ACCESS_DENIED

Allow Conversion’s IP Addresses

Conversion connects from a fixed set of IP addresses:

Region	IP Addresses
US	35.239.90.161, 35.188.167.166, 34.56.101.43, 34.122.97.230, 34.29.176.66, 35.226.154.44

ClickHouse Cloud: the service’s IP Access List controls who can connect. Open your service, go to Settings → IP Access List, and add each address above (choose Add IP rather than Anywhere). Changes take effect within a minute. Self-hosted ClickHouse: allow these addresses through your firewall on the HTTPS port. You can also restrict the user’s allowed hosts:

ALTER USER conversion_sync_user HOST
  IP '35.239.90.161', IP '35.188.167.166', IP '34.56.101.43',
  IP '34.122.97.230', IP '34.29.176.66', IP '35.226.154.44';

ALTER USER ... HOST replaces the user’s allowed hosts; it doesn’t append. If the user must also log in from elsewhere, include those addresses in the same statement.

If your self-hosted server exposes only HTTP (port 8123), enable TLS with https_port or a reverse proxy. The endpoint must be reachable from Conversion’s IP addresses. Contact us if your server has no public endpoint.

Step 2: Connect ClickHouse to Conversion

In Conversion, go to Settings → CRM & Syncing → Connections.
Click Add ClickHouse connection.

Enter your connection details:

Field	What to enter
Name	A label such as `Production ClickHouse`
Host	Your service hostname, without `https://` or a port
Port	`8443` for ClickHouse Cloud, or your self-hosted HTTPS port
Database name	The database you granted access to in Step 1
Username	`conversion_sync_user`
Password	The password you set in Step 1. Conversion stores it encrypted.

Click Connect to verify the connection.

ClickHouse Cloud services pause when idle and take a few seconds to wake up. The first connection or sync after a quiet period may be slower than usual.

Step 3: Create a Sync

Open your ClickHouse connection and go to the Syncs tab. Follow Setting Up a Sync to choose a destination, enter your query, and set a schedule.

ClickHouse SQL Reference

Table names

Refer to tables as database.table. ClickHouse has no schema layer between the two, so a three-part name like database.schema.table is an error. The schema browser inserts the correct form when you click a table.

Converting Timestamps

Conversion expects Unix timestamps for date/time fields. Use toUnixTimestamp() to convert DateTime and DateTime64 columns:

SELECT
  email,
  toUnixTimestamp(created_at) AS created_at,
  toUnixTimestamp(last_login) AS last_login
FROM my_database.users
WHERE updated_at >= toDateTime({{last_sync_time}})

Using last_sync_time

For incremental syncing, compare your DateTime or DateTime64 column with toDateTime({{last_sync_time}}). The variable is an integer in Unix seconds, so don’t wrap it in quotes:

WHERE updated_at >= toDateTime({{last_sync_time}})

Or convert your column to Unix seconds and compare integers directly:

WHERE toUnixTimestamp(updated_at) >= {{last_sync_time}}

To add a buffer that catches rows whose updated_at might be slightly stale, subtract seconds before converting:

WHERE updated_at >= toDateTime({{last_sync_time}} - 300)  -- 5 minute buffer

Building Nested Objects

Nested values such as relationshipFields are sent to Conversion as JSON objects. Build them with a named tuple:

SELECT
  email,
  CAST((role, quantity, toUnixTimestamp(started_at))
       AS Tuple(role String, quantity UInt32, started_at UInt32)) AS relationshipFields
FROM my_database.memberships

Map and JSON columns are also delivered as JSON objects, and Array columns as JSON arrays.

Converting Booleans

Bool columns arrive as true / false. If a flag is stored as UInt8, convert it explicitly:

SELECT
  email,
  if(is_active = 1, 'true', 'false') AS is_active
FROM my_database.users

Handling NULLs

Use coalesce() or ifNull() to provide default values for Nullable columns:

SELECT
  email,
  coalesce(first_name, '') AS first_name,
  ifNull(phone, '') AS phone
FROM my_database.users

Casting Types

Use CAST or the to* functions to convert between types:

SELECT
  toString(user_id) AS id,
  toInt32(score) AS lead_score
FROM my_database.users

Large integers (UInt64, Int64) and Decimal values are delivered as strings so no precision is lost. Timestamps are delivered in UTC.

Troubleshooting

”Authentication failed” errors

The username or password is wrong. Re-run ALTER USER conversion_sync_user IDENTIFIED WITH sha256_password BY '<new-password>' and update the connection in Conversion.
On ClickHouse Cloud, confirm you are using the user you created, not the default user’s credentials shown in the Connect dialog.

”Access denied” or “Query failed” errors

ClickHouse error code 497 means the user is authenticated but lacks a grant or tried to write. Check SHOW GRANTS FOR conversion_sync_user and confirm the query is a SELECT.
Error code 47 (Unknown identifier) or 60 (Unknown table) means a column or table name in the query is wrong or not covered by a grant. Remember to use database.table, not database.schema.table.
Error code 164 means the query tried to change a setting the read-only user may not change.

”Could not connect” errors

Verify that:

The host is the hostname only, without https:// or a port, and the port is your HTTPS port (8443 on ClickHouse Cloud)
Conversion’s IP addresses are in the service’s IP Access List (Cloud) or allowed by your firewall (self-hosted)
The service is not stopped; paused Cloud services wake automatically, stopped ones do not

”Result set too large” errors

Conversion caps how many rows a preview or a single run may return.

Filter by {{last_sync_time}} so each run only pulls changed rows
Select only the columns you need
For a first sync of a very large table, add a WHERE clause that syncs it in a few batches

Frequently Asked Questions

Which ClickHouse versions are supported?

Any recent ClickHouse version works for ordinary syncs. Syncs whose first run returns more than a million rows are processed in pages, which requires ClickHouse 23.12 or later (or an explicit ORDER BY in your query on older versions).

How do I sync from multiple databases?

Grant the role on each database and use fully-qualified table names in your query:

SELECT u.email AS email, o.order_id AS order_id
FROM crm.users u
JOIN sales.orders o ON u.id = o.user_id

Records

Emails

Workflows

Audiences

Forms

Content

Campaigns

Data Syncing

Unified Query Engine

Folders

Liquid

Tracking

Workspace Settings

Data and Privacy

Before You Begin

Step 1: Set Up ClickHouse Access

Create a Role and Grant Access

Granting Access table-by-table

Create a User

Verify the grants worked

Allow Conversion’s IP Addresses

Step 2: Connect ClickHouse to Conversion

Step 3: Create a Sync

ClickHouse SQL Reference

Table names

Converting Timestamps

Using last_sync_time

Building Nested Objects

Converting Booleans

Handling NULLs

Casting Types

Troubleshooting

”Authentication failed” errors

”Access denied” or “Query failed” errors

”Could not connect” errors

”Result set too large” errors

Frequently Asked Questions

​Before You Begin

​Step 1: Set Up ClickHouse Access

​Create a Role and Grant Access

​Granting Access table-by-table

​Create a User

​Verify the grants worked

​Allow Conversion’s IP Addresses

​Step 2: Connect ClickHouse to Conversion

​Step 3: Create a Sync

​ClickHouse SQL Reference

​Table names

​Converting Timestamps

​Using last_sync_time

​Building Nested Objects

​Converting Booleans

​Handling NULLs

​Casting Types

​Troubleshooting

​”Authentication failed” errors

​”Access denied” or “Query failed” errors

​”Could not connect” errors

​”Result set too large” errors

​Frequently Asked Questions

Before You Begin

Step 1: Set Up ClickHouse Access

Create a Role and Grant Access

Granting Access table-by-table

Create a User

Verify the grants worked

Allow Conversion’s IP Addresses

Step 2: Connect ClickHouse to Conversion

Step 3: Create a Sync

ClickHouse SQL Reference

Table names

Converting Timestamps

Using last_sync_time

Building Nested Objects

Converting Booleans

Handling NULLs

Casting Types

Troubleshooting

”Authentication failed” errors

”Access denied” or “Query failed” errors

”Could not connect” errors

”Result set too large” errors

Frequently Asked Questions