Databricks agent

Target Databricks

Prerequisites

To have Gluesync writing into your Databricks workspace you will need:

A workspace with Unity Catalog, reachable over HTTPS from Core Hub;
A SQL warehouse. Serverless warehouses start on the first statement;
A service principal with an OAuth secret (recommended), or a personal access token;
These grants for that identity:
- CAN USE on the SQL warehouse;
- USE CATALOG on the catalog and USE SCHEMA on the target schemas;
- SELECT and MODIFY on the target tables;
- CREATE TABLE on the schema when Gluesync creates the tables.

Setup via Web UI

Protocol (protocol): HTTPS, the only value;
Workspace URL (host): https://<workspace-host>;, for example https://adb-1234567890123456.7.azuredatabricks.net or https://dbc-1a2b3c4d-5e6f.cloud.databricks.com. It must start with https://;
Port (port): 443;
Service principal client ID (username): the OAuth client ID of the service principal. Leave it empty with a personal access token;
Client secret or personal access token (password): the OAuth secret of the service principal, or the personal access token;
Authentication mode (authMode): SERVICE_PRINCIPAL (default) uses OAuth machine-to-machine; PAT uses a personal access token;
SQL warehouse ID (warehouseId): the last part of the warehouse HTTP path, /sql/1.0/warehouses/<id>, under SQL Warehouses → Connection details;
Unity Catalog catalog (catalog): the catalog that holds the target schemas;
Statement timeout in seconds (statementTimeoutSeconds): default 600. A statement still running after this time is canceled on the warehouse and the write fails;
Staging volume for bulk snapshots (stagingVolume): catalog.schema.volume of the Unity Catalog volume bulk snapshots are staged in. Empty uses <catalog>.gluesync_staging.bulk_files, which the connector creates when missing.

The connection check reads the state of the warehouse without starting it, so a stopped warehouse passes the check; the first statement starts it.

Specific configuration

Disable truncate before snapshot (disableTruncateOnSnapshot): default false.

Per entity:

Rows per bulk snapshot load (bulkSnapshotFileRows): default 1000000. Used only when bulk operations are enabled for the snapshot;
Maximum rows per MERGE statement (maxRowsPerStatement): default 1000. A statement is also cut before it reaches 3,000,000 characters, well below the 16 MiB a Databricks statement may take. Raise it for large snapshots;
SQL pre/post snapshot and CDC commands: SQL run on the warehouse around the snapshot and the CDC of the entity.

Writes

Every batch is applied as Delta MERGE statements:

Inserts and updates: MERGE … WHEN MATCHED THEN UPDATE SET … WHEN NOT MATCHED THEN INSERT …. Rows are matched null-safely (<⇒) on the key they had before the change, so an update that changes the key moves the row instead of inserting a second one;
Deletes: MERGE … WHEN MATCHED THEN DELETE;
Truncate, and the first snapshot batch unless Disable truncate before snapshot is set: TRUNCATE TABLE on every table of the entity.

A Delta MERGE fails when two source rows match the same target row, and a CDC batch can change a row many times. Gluesync therefore keeps the order of the changes and cuts them into statements in which every key appears once: a later image of a row replaces the earlier one when both carry the same columns, and a key change or a different set of columns starts a new statement. The last change of a row is always the one left on the table.

Delta enforces no primary key, so an insert never fails on a duplicate: the insert conflict strategy has no effect on this target.

Rate limiting (HTTP 429), an unavailable warehouse (HTTP 502–504) and dropped connections are retried up to five times with backoff, following Retry-After. A revoked OAuth token is replaced once. Every statement the connector writes can run twice without changing the result; pre/post commands you write should be idempotent too.

Bulk snapshots

Enable bulk operations while snapshot on an entity to load its snapshot in bulk: the rows are staged in a file and loaded from a Unity Catalog volume with one MERGE per load (every Rows per bulk snapshot load rows, and at the end of the snapshot), instead of one MERGE per transaction. Gluesync confirms the staged transactions only once they are loaded, so a restart sends them again and they are written once. CDC and multi-table entities always use the per-transaction MERGE.

The identity needs READ VOLUME and WRITE VOLUME on the staging volume, or CREATE SCHEMA and CREATE VOLUME on the catalog for the connector to create the default one. Staged files are deleted after each load.

Tables Gluesync generates

CREATE TABLE IF NOT EXISTS <catalog>.<schema>.<table> (…) USING DELTA. The key columns are NOT NULL and become an informational PRIMARY KEY constraint named pk_<table>: Delta does not enforce it, but schema discovery reads it back, so the key is found again when an entity is mapped onto the table.

Tables you create

Any Delta table in the catalog can be a target. Schemas, tables (data_source_format = 'DELTA') and columns are read from <catalog>.information_schema; a column is a key when the table declares a PRIMARY KEY constraint on it, otherwise the key comes from the entity mapping.

Columns are mapped explicitly: a column the change carries and the table lacks fails the write with UNRESOLVED_COLUMN instead of being added.

Data types

Every value is written as a literal cast to the type of its column. Timestamps have microsecond precision.

Gluesync type Default Databricks type Notes

Gluesync type	Default Databricks type	Notes
`SHORT`	`SMALLINT`	Alternatives: `TINYINT`, `INT`, `BIGINT`.
`INT`	`INT`	Alternative: `BIGINT`.
`LONG`	`BIGINT`	Alternative: `DECIMAL`.
`FLOAT`	`FLOAT`	Alternative: `DOUBLE`. NaN and infinities are kept.
`DOUBLE`	`DOUBLE`	NaN and infinities are kept.
`BIG_DECIMAL`	`DECIMAL(p, s)`	Precision and scale come from the source, up to 38 digits. An unsized decimal is `DECIMAL(38, 18)`. Alternatives: `DOUBLE`, and `STRING` for wider values, written in plain notation.
`BOOLEAN`	`BOOLEAN`
`STRING`	`STRING`	Alternative: `VARIANT`, parsed from the JSON text.
`BYTE_ARRAY`	`BINARY`	Alternative: `STRING`, as Base64.
`LOCAL_DATE`	`DATE`	Alternatives: `TIMESTAMP_NTZ`, `STRING`.
`LOCAL_TIME`	`STRING`	Databricks has no time-of-day type. Text `HH:mm:ss.SSSSSS`.
`OFFSET_TIME`	`STRING`	Text `HH:mm:ss.SSSSSS+HH:MM`.
`LOCAL_DATE_TIME`	`TIMESTAMP_NTZ`	Wall-clock time, as the source has it. Alternatives: `TIMESTAMP`, `STRING`.
`OFFSET_DATE_TIME`	`TIMESTAMP`	Stored as an instant and shown in the session time zone. Alternative: `STRING`, in UTC.
`ARRAY`	`STRING`	The JSON text of the array. Alternatives: `ARRAY<…>`, filled with `from_json` using the column’s element type, and `VARIANT`. A generated native column is `ARRAY<STRING>`.
`MAP`	`STRING`	The JSON text of the map. Alternatives: `MAP<…>` and `VARIANT`. A generated native column is `MAP<STRING, STRING>`.

SHORT

SMALLINT

Alternatives: TINYINT, INT, BIGINT.

INT

Alternative: BIGINT.

LONG

BIGINT

Alternative: DECIMAL.

FLOAT

Alternative: DOUBLE. NaN and infinities are kept.

DOUBLE

NaN and infinities are kept.

BIG_DECIMAL

DECIMAL(p, s)

Precision and scale come from the source, up to 38 digits. An unsized decimal is DECIMAL(38, 18). Alternatives: DOUBLE, and STRING for wider values, written in plain notation.

BOOLEAN

STRING

Alternative: VARIANT, parsed from the JSON text.

BYTE_ARRAY

BINARY

Alternative: STRING, as Base64.

LOCAL_DATE

DATE

Alternatives: TIMESTAMP_NTZ, STRING.

LOCAL_TIME

STRING

Databricks has no time-of-day type. Text HH:mm:ss.SSSSSS.

OFFSET_TIME

STRING

Text HH:mm:ss.SSSSSS+HH:MM.

LOCAL_DATE_TIME

TIMESTAMP_NTZ

Wall-clock time, as the source has it. Alternatives: TIMESTAMP, STRING.

OFFSET_DATE_TIME

TIMESTAMP

Stored as an instant and shown in the session time zone. Alternative: STRING, in UTC.

ARRAY

STRING

The JSON text of the array. Alternatives: ARRAY<…>, filled with from_json using the column’s element type, and VARIANT. A generated native column is ARRAY<STRING>.

MAP

STRING

The JSON text of the map. Alternatives: MAP<…> and VARIANT. A generated native column is MAP<STRING, STRING>.

Query Studio and data comparison

The agent is SQL-capable: Query Studio runs queries on the configured SQL warehouse and catalog, and data comparison reads the target tables through it. The same identity and warehouse as the pipeline are used.

Each query runs on its own through the Statement Execution API, which keeps no session between statements: there are no manual transactions and no session settings. In read-only mode a query must be a single read (SELECT, WITH, SHOW, DESCRIBE, EXPLAIN) before it is sent.
Names are quoted with backticks and strings escape with backslashes ('it's'), as Databricks SQL reads them.
Write values into the SQL: the Statement Execution API binds named markers only, so Query Studio parameters are refused.
Explain shows the EXPLAIN text plan, and the DDL viewer shows SHOW CREATE TABLE.
A cancelled query, or one that outlives its timeout, is cancelled on the warehouse too. A write is sent only once: a submission that may have reached the warehouse is not retried, except after rate limiting.
The Statement Execution API returns timestamps with millisecond precision, so the result grid shows milliseconds. Cast a column to STRING to see its microseconds. Data comparison reads timestamps as text with every microsecond, so it compares them in full.

Setup via Rest APIs

Here following an example of calling the Core Hub’s Rest API via curl to setup the connection for this Agent.

Connect the agent

curl --location --request PUT 'http://core-hub-ip-address/pipelines/{pipelineId}/agents/{agentId}/config/credentials' \
--header 'Content-Type: application/json' \
--header 'Authorization: ••••••' \
--data '{
        "hostCredentials": {
        "connectionName": "myAgentNickName",
        "host": "https://dbc-1a2b3c4d-5e6f.cloud.databricks.com",
        "port": 443,
        "username": "",
        "password": "",
        "maxConnectionsCount": 1,
        "enableTls": true
      },
      "customHostCredentials": {
        "authMode": "SERVICE_PRINCIPAL",
        "warehouseId": "1a2b3c4d5e6f7a8b",
        "catalog": "main",
        "statementTimeoutSeconds": 600,
        "stagingVolume": ""
      }
}'

authMode is SERVICE_PRINCIPAL or PAT. With SERVICE_PRINCIPAL, username is the client ID and password the OAuth secret; with PAT, password is the personal access token. port is 443, statementTimeoutSeconds defaults to 600, and an empty stagingVolume uses <catalog>.gluesync_staging.bulk_files.

Not covered in this release

Databricks as a source (Change Data Feed), Partner Connect and Marketplace packaging;
Schema evolution.