ClickHouse

Overview

Signals reads high volume event data straight from a ClickHouse deployment on a schedule and sends the columns you nominate to your ad platforms.

Reading it directly means there is no file job to maintain and nothing to go stale between runs. Signals reads only; nothing is written back.

You can point it at a table, or at a query. The query path is what you want when the shape Signals needs does not match anything you already have.

Each column is stored separately, so a read costs what the columns it names cost rather than what the table is wide. SELECT * against a sixty-column events table reads all sixty to deliver the six a destination wants, which is the cheapest way to push a scheduled read past the time limit set on it.

A column holds no nulls unless it was declared Nullable. A String column that was not gets the empty string wherever a value was never supplied, so a record with no email address is a record carrying an empty email address. Test for the empty string to keep those out of the read, because there is no null sitting in the column for a null test to find.

Supported destinations

Destinations supported by ClickHouse

Category	Supported
Offline Conversions	Google Offline Conversions API Google Store Sales LinkedIn Offline Conversions API Meta Offline Conversions API OpenAI Offline Conversions API Snapchat Offline Conversions API TikTok Offline Events API
Lead Conversions	Google Enhanced Conversions for Leads LinkedIn Leads Conversions API Meta Conversions API for CRM Snapchat Leads Conversions API TikTok CRM Integrations
Custom Audience	Google Customer Match Meta Custom Audience Snapchat Custom Audience TikTok Custom Audience

Authentication

In Studio, open Sources, find the Database category, and click the ClickHouse tile.

Field	What it is	Where to find it
Authentication Name	Your own label for this set of credentials.	You choose it. Name it for the database it belongs to.
Username	The database user Signals connects as.	Created in the database by your administrator.
Password	The password for that user.	Set when the user was created.

Caution

Create a dedicated read-only user for Datahash rather than reusing an application or administrator account. It keeps the connection working when someone rotates a password, and it stops the connection seeing more than it needs. Enter the password on the Datahash connection screen only.

Two statements make the account, and neither of them does anything on a server where SQL-driven access control has never been turned on:

CREATE USER datahash IDENTIFIED BY '...';
GRANT SELECT ON appdb.* TO datahash;

A fresh install has that feature disabled for every user. The default account the server ships with holds every permission and is precisely the account not allowed to use SQL-driven access control, so CREATE USER issued as default produces nothing to connect with. Switching the feature on means giving an administrative user access_management set to 1 in users.xml on the server, a file edit rather than a statement, so the step belongs to whoever administers the deployment.

readonly = 1 in a settings profile attached to the account earns the extra step. It withholds more than writes: at that level the session cannot issue SET at all, so the connection has no way to raise a limit it runs into partway through a read. readonly = 2 is the variant permitting reads together with setting changes.

Configuration

Instance Setup

Select the Integration Type, which is the kind of data your records hold. Offline Events is the common choice. Give the instance a name, then choose the table path or the query path. On either path you enter the Host Name, Port and Database Name, and then either the Table Name or a SQL query.

The integration type decides the columns Signals expects and which destinations the connection can feed, so it is worth settling before anyone models the data. It also decides which file format you get on the next screen.

Which port, and what the server does when it is the wrong one

Four ports answer on a stock server, in two pairs. 8123 carries HTTP and 8443 carries HTTPS; 9000 carries the native TCP protocol and 9440 carries that protocol over TLS. Which pair a connection needs is settled by the driver rather than by preference, and ClickHouse Cloud endpoints publish only the encrypted two of the four.

Getting this wrong does not present as a closed port. An HTTP request arriving on 9000 comes back as an HTTP/1.0 400 Bad Request whose body reads Port 9000 is for clickhouse-client program followed by You must use port 8123 for HTTP. A driver waiting for a result set makes whatever it can of that, so read the raw response before concluding that the host is unreachable.

Reading a ReplacingMergeTree

A ReplacingMergeTree table collapses rows sharing the same key only while its parts merge, and merges run in the background at a moment ClickHouse picks. A merge cannot be planned for and the absence of duplicates is not guaranteed at any point. A SELECT with no FINAL reads the parts as they currently stand and returns every version of a row that no merge has removed yet. One conversion in the table becomes two or more rows in the feed.

FINAL after the table name performs the collapse while the query runs, which makes the answer correct whatever state the parts happen to be in. Doing that means reading and merging the parts involved, so the query costs more and takes longer. On a scheduled conversion read the trade falls the right way, because a slower run announces itself and a duplicated conversion does not.

The key being collapsed on is the table’s ORDER BY, not its PRIMARY KEY. Where a table defines the two differently, rows sharing a primary key but differing further along the ORDER BY survive every merge as separate rows, which no amount of FINAL alters.

Table path

Enter the connection details including the table name, then complete the setup. The name is typed rather than picked from a list, so it has to match the database exactly.

Query path

Enter the connection details without the table name, then write the query. Preview the results before finishing, and read the column names in the output rather than just the row count: a query that runs but returns the wrong names will fail silently once the connection is live.

Manage instance

One instance reads one table or query for one integration type. To read another, add another instance from the Manage existing instance table. To change an existing one, open it from there, click the edit option in the menu to the top right, update the fields and click Finish.

File format and schema

Download the file format from the setup screen and shape the table to it. The column names are what Signals matches on, so a column that has been renamed or cased differently is not recognized and the row it belongs to is dropped rather than reported.

On the query path the same rule applies to the column aliases your query returns. Aliasing to the expected names is the usual way to reconcile data you cannot or should not restructure.

Data & identifiers

Personal identifiers are normalized and hashed before they are sent to a destination.

Best practices

• Use a dedicated read-only user scoped to the data Datahash reads, rather than an application account.
• Prefer the query path with explicit column aliases over pointing at a raw table. It lets your team change the underlying model without breaking the connection.
• Name a replica your ingest does not write to. Replication is multi-master here: every replica of a ReplicatedMergeTree table takes inserts as well as reads, so no copy is held back as a standby and the only thing you are choosing is which host carries the scan. Replicas apply each other’s writes asynchronously, so read absolute_delay in system.replicas on the host you name before treating rows missing from a recent run as a delivery failure.
• Confirm the port matches the protocol your deployment exposes. This is the one field on this connector most likely to need changing.
• Restrict the user to the specific table or view, and set a read-only profile if your deployment supports one.
• Clean the data in the view or query: lowercase email, phone in E.164, no stray whitespace. Hashing a badly formatted value produces a hash that will never match.
• Rotate the password periodically and update the connection afterwards.

Troubleshooting & FAQ

The connection times out rather than reporting bad credentials. That points at reachability rather than authentication. Confirm the host is exposed, the port is open, and any firewall or allow list permits the connection.

Credentials are rejected. Check the user can read the data from a client of its own first. Most failures here are permissions rather than typos.

The table name is rejected. It is typed rather than selected, so it has to match exactly.

The query previews correctly but nothing is delivered. The column names it returns do not match the expected schema. Alias them to the names in the downloaded file format.

The same conversion reaches a destination more than once while the source table holds one row for it. The table is a ReplacingMergeTree read without FINAL. SHOW CREATE TABLE names the engine and the ORDER BY it deduplicates against. Add FINAL, or fold the versions together in the query with a GROUP BY on that key and argMax over a version column. OPTIMIZE TABLE ... FINAL forces a merge instead, which repairs today’s rows and leaves tomorrow’s batch exactly where it started.

A query that ran for a year begins failing, or answers with fewer rows than the table holds. max_execution_time, max_memory_usage and max_rows_to_read apply per query, so a read that outgrows one of them stops working with nothing about the query having changed. Which symptom arrives depends on which limit was passed. Exceeding max_memory_usage always raises MEMORY_LIMIT_EXCEEDED and there is no mode that softens it. max_execution_time and max_rows_to_read each carry an overflow mode: at throw, the default, the query ends with an error naming the limit, while at break the query stops and hands back the partial result as though the source data had run out. A run under break reports success and delivers a truncated set, which is the one of these failures that reaches a destination unnoticed. Read the limits and the modes off the settings profile the account carries, and remember that an account held at readonly = 1 cannot lift any of them from its own session.

The run reports success and the rows stop partway through. Over the HTTP interface the status line goes out before the query has finished, so an error raised once rows are already streaming arrives as text on the end of a response that has already returned 200. A client can detect that failure only while parsing the body, which is why it can pass for a short table. Where system.query_log is enabled the server records the exception whether or not any client noticed it, so read that table before concluding the source is short of rows.

It was working and stopped. The password may have been changed or the user disabled. A dedicated service user avoids this.