Set up a Databricks target with bulk snapshots

Create the schema for the target tables and a volume for the staged files:

CREATE SCHEMA IF NOT EXISTS <catalog>.gluesync_guide;
CREATE VOLUME IF NOT EXISTS <catalog>.gluesync_guide.staging;

Grant the user whose token Gluesync will use the access listed in Access the identity needs. Write the user name between backticks. If the user owns the catalog, as in the test of this guide, you can skip this statement:

GRANT USE CATALOG ON CATALOG <catalog> TO `<user>`;
GRANT USE SCHEMA, SELECT, MODIFY, CREATE TABLE ON SCHEMA <catalog>.gluesync_guide TO `<user>`;
GRANT READ VOLUME, WRITE VOLUME ON VOLUME <catalog>.gluesync_guide.staging TO `<user>`;

Note the Warehouse ID. In SQL Warehouses, open the warehouse and select Connection details. The ID is the last part of the HTTP path, /sql/1.0/warehouses/<id>.

In Databricks, select your user name in the top bar, then Settings and Developer. Next to Access tokens select Manage, then Generate new token;

Enter a name and a lifetime in days. If the dialog asks for scopes, choose Other APIs and select sql and files. Gluesync uses the SQL Statement Execution API and the SQL warehouses API, and bulk snapshots upload their files with the Files API;

Select Generate, copy the token, and keep it in a safe place. Databricks shows it only once.

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. The source appears with a green check mark. Select Next;

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

Under Advanced connection properties select Add property, choose the key Staging volume for bulk snapshots (catalog.schema.volume) and enter <catalog>.gluesync_guide.staging as the value. Leave the property out to use the default volume <catalog>.gluesync_staging.bulk_files, which Gluesync creates when it is missing;

Select Test and continue. The target appears with a green check mark. Select Next, enter a Pipeline name and select Finish.

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

Under Target, select the Target Schema gluesync_guide. In Target Delta table type events and choose Create table "events". The dialog shows New table events will be created on confirm. and proposes a target type for every column;

Check the proposed types. A bigint, integer, numeric(12,2) and the two timestamps map to BIGINT, INT, DECIMAL (precision 12, scale 2), TIMESTAMP_NTZ and TIMESTAMP. A varchar(20) maps to STRING. The jsonb column payload and the array column tags map to STRING with a length of 0, and the dialog shows Missing required field until you fix them;

Fix the two columns:

Select Confirm. The Create table events dialog shows the Read-only SQL statement. Read it, then select Create table events to run it on the warehouse;

Back on the Add entities page, select Confirm and continue.

In the pipeline, find the entity. 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 overwrites rows with the same key and keeps the others;

Under Speed select Custom. In the Writing column, under During the copy, select Bulk load. The diagram changes to Bulk, Staging table, Applied in one step, and the writers slider is disabled, because a bulk load writes in one stream;

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 loses nothing, and select Start entity. The dialog shows This target has no bulk load for the live changes. The entity becomes Active;

On the source, insert, update, delete and change a key, and update one row three times in one transaction:

INSERT INTO events VALUES (400001, 'new', 1, 12.50, now(), now(), '{"n":400001}', '{x,y}');
UPDATE events SET val = -1, kind = 'edited' WHERE id = 5;
DELETE FROM events WHERE id = 6;
UPDATE events SET id = 500000 WHERE id = 9;
BEGIN;
UPDATE events SET val = 100 WHERE id = 8;
UPDATE events SET val = 200 WHERE id = 8;
UPDATE events SET val = 300 WHERE id = 8;
COMMIT;

Wait up to a minute. Each batch is a statement on the warehouse, so a few seconds to some tens of seconds pass before a change shows. Then read the rows on Databricks:

SELECT id, kind, val FROM <catalog>.gluesync_guide.events WHERE id IN (5, 6, 8, 9, 400001, 500000) ORDER BY id;
SELECT count(*), count(DISTINCT id) FROM <catalog>.gluesync_guide.events;

Enter a token that Databricks does not know and select Test and continue. The page shows Failed to save: Check connection failed. Could not connect to host "<workspace url>" at port 443 as user "". Databricks API returned HTTP 403 403: Invalid access token. Fix: generate a token in the workspace of the Workspace URL, and enter it in Client secret or personal access token;

Enter a warehouse ID that does not exist. The page shows Failed to save: The connection was saved, but agent <id> of pipeline <id> did not accept the new credentials: Check connection failed. …​ Databricks API returned HTTP 404 RESOURCE_DOES_NOT_EXIST: SQL warehouse <id> does not exist. The connection is saved with the wrong value. Fix: enter the ID from Connection details and select Test and continue again;

Enter a workspace address that does not exist. The page shows Databricks API request failed after 5 attempts: null. Gluesync retries the request five times before it gives up. Fix: copy the address of the workspace from the browser, without a path;

Choose ARRAY for the tags column, confirm, and select Create table events. The dialog shows Failed to create table, and the notification Create Target Table shows Create target table execution error: BAD_REQUEST: [UNSUPPORTED_DATATYPE] Unsupported data type "_TEXT". The statement the dialog proposed contains tags _text , which Databricks does not know. Fix: choose STRING for the column, or create the table yourself with ARRAY<STRING> and choose it in Target Delta table.