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

# ClickHouse

> Connect ClickHouse to sync contacts, custom objects, and events into Conversion.

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.

```sql theme={null}
-- 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;
```

<Note>
  `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.
</Note>

#### Granting Access table-by-table

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

```sql theme={null}
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
```

<Warning>
  Grant access to each new table you want to sync.
</Warning>

### Create a User

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

```sql theme={null}
-- 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;
```

<Info>
  `readonly = 2` allows `SELECT` queries and per-query settings, but prevents writes.
</Info>

#### Verify the grants worked

Confirm the user can see your database and cannot write:

```sql theme={null}
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:

```sql theme={null}
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';
```

<Warning>
  `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.
</Warning>

<Note>
  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.
</Note>

## Step 2: Connect ClickHouse to Conversion

1. In Conversion, go to **Settings → CRM & Syncing → Connections**.
2. Click **Add ClickHouse connection**.
3. 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. |
4. Click **Connect** to verify the connection.

<Note>
  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.
</Note>

***

## Step 3: Create a Sync

Open your ClickHouse connection and go to the **Syncs** tab. Follow [Setting Up a Sync](/product-docs/sync/data-warehouse/overview#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:

```sql theme={null}
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](/product-docs/sync/data-warehouse/overview#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:

```sql theme={null}
WHERE updated_at >= toDateTime({{last_sync_time}})
```

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

```sql theme={null}
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:

```sql theme={null}
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**:

```sql theme={null}
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:

```sql theme={null}
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:

```sql theme={null}
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:

```sql theme={null}
SELECT
  toString(user_id) AS id,
  toInt32(score) AS lead_score
FROM my_database.users
```

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

***

## 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

<AccordionGroup>
  <Accordion title="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).
  </Accordion>

  <Accordion title="How do I sync from multiple databases?">
    Grant the role on each database and use fully-qualified table names in your query:

    ```sql theme={null}
    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
    ```
  </Accordion>
</AccordionGroup>