Set up a ClickHouse target for snapshots and CDC

Create the database and the user:

CREATE DATABASE IF NOT EXISTS analytics;
CREATE USER gluesync IDENTIFIED BY '<a strong password>';

Give the user the rights on the target database. SELECT lets Gluesync read the metadata of the tables, INSERT is the write path, TRUNCATE is used by the snapshot option Empty the table first, and CREATE TABLE lets Gluesync run the statement of the Create table dialog:

GRANT SELECT, INSERT, TRUNCATE, CREATE TABLE ON analytics.* TO gluesync;

Give the user the three read rights that schema discovery uses. Without them the object browser cannot list the target:

GRANT SELECT ON information_schema.schemata TO gluesync;
GRANT SELECT ON information_schema.tables TO gluesync;
GRANT SELECT ON system.parts TO gluesync;

Check the result:

SHOW GRANTS FOR gluesync;

In the Control Plane, select Create your first pipeline, or the + button of the pipeline list if you already have pipelines. The Agents setup wizard opens;

In 1. Setup source, choose the PostgreSQL CDC agent tile and fill Base settings: Host name / Address, Port, Database name, Username and Password of your source database. Select Test and continue, then Next;

In 2. Setup target(s), choose the ClickHouse agent tile and fill Base settings:

Select Test and continue. The target appears with the user and the address. Select Next, enter a Pipeline name and select Finish.

In the pipeline, select Entities. The Add entities page opens with the Object browser of the source. Expand the schema public and select the table orders. The Field mapping dialog opens;

Under Target, select the Target Schema analytics. The list shows only the databases that the Gluesync user can see. Then select the Target Table orders from the list of existing tables. The dialog draws one arrow for each source column;

The technical columns _version and _is_deleted appear in the target with no arrow, and the dialog shows the error Field function is required or define a UDF. Clear the check box at the start of both rows. The connector fills them, so they must not be mapped;

Select Confirm, then Confirm and continue.

On Hold, select the start options button, the one next to the play button. The Snapshot (copy-only) dialog opens;

Keep Keep and merge under Data already in the target. It writes every row again as a new version of its key. Empty the table first runs TRUNCATE TABLE before the copy, needs the TRUNCATE grant, and asks you to confirm with I understand that rows in the target will be deleted;

Select Start snapshot.

Select the play button of the entity. The Sync changes (CDC) dialog opens. Keep Where it stopped, which continues from the last checkpoint, and select Start entity. The entity becomes Active;

On the source, insert, update and delete one row each:

INSERT INTO orders VALUES (300001, 'cdc-customer', 12.34, 'NEW', now(), current_date, true, ARRAY['x','y'], 'inserted');
UPDATE orders SET status = 'SHIPPED', amount = 999.99 WHERE id = 5;
DELETE FROM orders WHERE id = 6;

After a few seconds, read the three keys twice, as stored and as current:

SELECT id, status, amount, _is_deleted FROM analytics.orders WHERE id IN (5, 6, 300001) ORDER BY id, _version;
SELECT id, status, amount FROM analytics.orders FINAL WHERE id IN (5, 6, 300001) ORDER BY id;
SELECT count() FROM analytics.orders FINAL;

In the left bar, select Query Studio. At the top right, open the SQL-capable Agent list and choose the ClickHouse target;

Enter a query and select Run:

SELECT count() AS n, uniqExact(id) AS keys FROM analytics.orders

Batch size. Pause the entity, open the three-dot menu of the entity, select Edit settings, then the Entity tab. Bulk insert batch size (10000 by default) is the number of rows in one INSERT. Delete mutation batch size (1000 by default) is used only for a table written in append mode, see step 8;

Writers. In the Snapshot (copy-only) dialog, under Speed and Custom, set Writers during the copy (1 to 10). The entity tab Fine tuning has a matching setting, Snapshot writing concurrency, with the same default of 2 and the same range;

Connection options. In the Advanced connection properties of the target, add Enable HTTP compression (requests and responses) (on by default) or Enable ClickHouse async inserts with Async insert flush interval (ms). The option Use RowBinary for prepared INSERT batches is a driver beta for ClickHouse 25.x and older: leave it off.

Remove one of the three discovery grants of step 1 and open Add entities. The object browser stays empty and the notification Schemas Fetch shows Code: 497. DB::Exception: gluesync: Not enough privileges. To execute this query, it’s necessary to have the grant SELECT ON information_schema.schemata. (ACCESS_DENIED). Without the grant on information_schema.tables, the message names system.parts, because the view reads that table. Fix: run the three GRANT SELECT statements;

Remove TRUNCATE, and start a snapshot with Empty the table first. The entity goes to Error, retries every few seconds, and the notification Access Denied shows …​ it’s necessary to have the grant TRUNCATE ON analytics.orders. (ACCESS_DENIED). Fix: GRANT TRUNCATE ON analytics.* TO gluesync;. The next retry runs the snapshot;

Save the connection with a wrong password. The page shows Failed to save: The connection was saved, but agent <id> of pipeline <id> did not accept the new credentials: Code: 516. DB::Exception: gluesync: Authentication failed: password is incorrect, or there is no user with such name. (AUTHENTICATION_FAILED). A wrong port or an unknown host gives …​ did not accept the new credentials: Query request failed (attempt: 4, duration: 6ms, queryId: <id>), and a database that does not exist gives Code: 81. DB::Exception: Database nodb does not exist. (UNKNOWN_DATABASE). The wrong values are saved, and the pipeline shows On error with Error: Unknown error. Please check agent clickhouse (<id>) logs in order to find more details. Fix: save the correct values with Test and continue, and the pipeline becomes healthy again;

Create a table from the Create table dialog and get Sorting key contains nullable columns: create the table yourself, as in step 3.