SQL Connector
Data Store- Overview
- Setup
- Data & mappings
- Operations & API
- Changelog
The SQL Connector integrates Brinqa with any relational database reachable over JDBC. Rather than targeting a single fixed schema, it is a generic, query-driven connector: an operator supplies the SQL queries used to test connectivity, discover the schema, and retrieve data, and the connector turns each returned row into a connector object. Because the synced data is defined entirely by the customer-supplied query, the connector is schema-agnostic and can be pointed at any table, view, or join that the configured database user can read.
The following JDBC databases are supported out of the box (the driver is selected automatically from the connection URL prefix):
| Database | Connection URL prefix |
|---|---|
| H2 | jdbc:h2: |
| MySQL | jdbc:mysql: |
| jTDS (Sybase / MS SQL) | jdbc:jtds: |
| PostgreSQL | jdbc:postgresql: |
| Oracle | jdbc:oracle: |
| Microsoft SQL Server | jdbc:sqlserver: |
| Snowflake | jdbc:snowflake: |
| Databricks | jdbc:databricks: |
Data retrieved from SQL Connector
| Connector Object | Required | Maps to Data Model |
|---|---|---|
| Record (Current SQL Connector) | Yes | Record (Current SQL Connector) |
| Record (Deprecated SQL Connector) | Yes | Record (Deprecated SQL Connector) |
For detailed steps on how to view the data retrieved from SQL Connector in the Brinqa Platform, see How to view your data.
Note: This repository ships two connector variants. The current connector supports dynamic schema discovery, per-phase retry/timeout, MD5 unique-ID generation, boolean field coercion, and Databricks OAuth2 authentication. A legacy connector (labeled SQL Connector (Deprecated)) is retained for backward compatibility and adds incremental sync via a sync token, multi-value field parsing, JNDI lookup, and create/update write-back. Configuration for both variants is documented in Configuration.
Connection settings
When setting up a data integration, select SQL Connector from the Connector dropdown and provide the following:
| Setting | Required | Default | Description |
|---|---|---|---|
| Connection URL | Yes | — | JDBC URL used to connect to the database (driver is selected from the URL prefix). |
| Username | No | — | Database username. |
| Password | No | — | Database password. |
| Private Key | No | — | Private key (P8/PEM text) used to access the instance, e.g. for Snowflake key-pair auth. |
| Private Key Passphrase | No | — | Passphrase that unlocks the provided private key. |
| Client ID | No | — | Service principal client ID for Databricks OAuth2 authentication. |
| Client secret | No | — | Service principal client secret/password for Databricks OAuth2 authentication. |
| Tenant ID | No | — | Azure tenant/domain the credential is authorized for (Databricks). |
| Test query | Yes | — | Read-only query used to verify connectivity and credentials (limit to 1 row). |
| Test query, retry | Yes | 3 | Number of times to retry the test query. |
| Test query, timeout | Yes | 60 | Seconds before the test query times out and retries. |
| Schema query | Yes | — | Query used to determine the schema; suggested to be the Data query with a limit of 1. |
| Schema query, retry | Yes | 3 | Number of times to retry the schema query. |
| Schema query, timeout | Yes | 60 | Seconds before the schema query times out and retries. |
| Data query | Yes | — | Query to retrieve database records. Use ? as a last-modified timestamp placeholder for incremental retrieval. |
| Data query, retry | Yes | 3 | Number of times to retry the data query. |
| Data query, timeout | Yes | 60 | Seconds before the data query times out and retries. |
| Unique id fields | Yes | id | Comma-separated list of columns that uniquely identify a row in the Data query. |
| Convert UID to MD5 | No | true | When true, unique IDs are stored as MD5 hashes; when false the unique-ID field values are stored in their original form. |
| Last modified field | No | — | (Optional) Column used to determine changes since the last sync. Use ? in the Data query to filter on it (UTC). |
| Boolean fields | No | (empty) | (Optional) Comma-separated list of columns that should be treated as boolean even when not natively boolean. |
| Property | No | Default | Description |
| --- | No | --- | --- |
| Connection URL | Yes | — | JDBC URL used to connect to the database; for JNDI specify the JNDI context. |
| JNDI context | Yes | false | Whether the connection URL is a JNDI context. |
| Username | No | — | Database username. |
| Password | No | — | Database password. |
| Private Key Passphrase | No | — | Passphrase that unlocks the provided private key. |
| Private Key | No | — | Private key (P8/PEM text) used to access the instance. |
| Test query | Yes | SELECT 1 | Read-only query used to test the connection (limit to 1 row). |
| Search query | Yes | — | Query to search database records; also used to retrieve the database schema. |
| Sync query | Yes | — | Query to sync database records; should include a where clause on the timestamp field to return only changed records. |
| Latest sync token query | No | — | (Optional) Query to obtain the latest sync token; not required in most cases. |
| Insert query | No | — | (Optional) Insert statement using named parameters, enabling create operations. |
| Update query | No | — | (Optional) Update statement using named parameters (use old_ prefix for identifier parameters), enabling update operations. |
| Sync timestamp field | No | — | (Optional) Column used to determine changes since the last sync; when not specified the search query is used. |
| Target type | Yes | Record | The target object class/data model name. |
| Unique field | No | (empty) | (Optional) Comma-separated column name(s) that contain the unique identifier; if none, one is generated automatically. |
| Multi-value fields | No | (empty) | (Optional) Comma-separated list of field=delimiter entries identifying columns that contain multiple values (e.g. High=:, Low=;). |
| Identifier Fields | No | (empty) | (Optional) Comma-separated, order-significant list of columns used as identifiers (e.g. a,b). |
| Text qualifier | Yes | " | Qualifier marking the beginning and end of a field when parsing multi-value columns. |
| Field delimiter | Yes | , | Delimiter separating fields when parsing multi-value columns. |
| Fetch size | Yes | 1000 | Number of rows fetched per batch during query processing. |
| Test query, timeout | Yes | 1 | Seconds before the test query times out and retries. |
| Test query, retry | Yes | 3 | Number of times to retry the test query. |
| Search query, timeout | Yes | 600 | Seconds before the search query times out and retries. |
| Search query, retry | Yes | 3 | Number of times to retry the search query. |
| Sync query, timeout | Yes | 600 | Seconds before the sync query times out and retries. |
| Sync query, retry | Yes | 3 | Number of times to retry the sync query. |
| Filter Binary data | No | false | Removes binary-type columns from wildcard queries. |
Authentication
The connector authenticates directly to the target database through the JDBC driver. There is no separate token-issuing endpoint or REST handshake — the supplied credentials are placed into the JDBC connection Properties and the driver establishes (and authenticates) the connection. The connection is opened read-only where the driver supports it (all databases except Databricks).
Three credential modes are supported and may be combined as the database requires:
| Mode | Properties used | Notes |
|---|---|---|
| Username / Password | user, password | Standard basic database authentication. |
| Private Key | privateKey | A P8/PEM private key (optionally protected by a passphrase) is parsed and added to the connection properties. Used by databases such as Snowflake for key-pair authentication. |
| Databricks OAuth2 (Azure service principal) | OAuth2ClientID, OAuth2Secret, AzureTenantID | Used for jdbc:databricks: connections authenticating with an Azure service principal. |
Credential-to-property mapping
The connector builds the JDBC connection properties as follows, then calls the driver's connect:
{
"user": "<Username>",
"password": "<Password>",
"privateKey": "<parsed P8/PEM Private Key>",
"OAuth2ClientID": "<Client ID>",
"OAuth2Secret": "<Client secret>",
"AzureTenantID": "<Tenant ID>"
}
A property is only added when its corresponding configuration value is provided. The private key, when supplied, is decoded from PEM text (decrypting with the passphrase if the key is encrypted) into a PrivateKey object before being attached. Once the connection is established, the same authenticated connection is reused for the test, schema, and data queries within that operation.
Connection test
Connectivity and credentials are verified by executing the configured Test query. The query must be read-only and is expected to return at most one row (for example SELECT 1). A successful execution confirms the credentials and reachability of the database.
Sync Behavior
Both connector variants perform incremental (delta) syncs when a change marker is configured, and fall back to a full sync otherwise. The current SQL Connector filters on a configured Last modified field: its timestamp is bound to the Data query as a UTC since value so only rows changed after the previous sync are returned (on a full sync the marker is bound to the epoch, 1970-01-01T00:00:00Z). The deprecated SQL Connector instead uses a numeric sync token high-water mark (bound to 0 on a full sync). When no last-modified field or sync token is configured, every run re-fetches all rows.
How to obtain SQL Connector credentials
Obtain the required credentials (Connection URL, Test query, Test query, retry, Test query, timeout, Schema query, Schema query, retry, Schema query, timeout, Data query, Data query, retry, Data query, timeout, Unique id fields, Connection URL, JNDI context, Test query, Search query, Sync query, Target type, Text qualifier, Field delimiter, Fetch size, Test query, timeout, Test query, retry, Search query, timeout, Search query, retry, Sync query, timeout, Sync query, retry) from your SQL Connector administrator or the SQL Connector admin console, then enter them in the connection settings above.
Attribute mappings
Expand the sections below to view the mappings between the source and the Brinqa data model attributes:
Record (Current SQL Connector)
| Source Field Name | SDM Attribute |
|---|---|
| Data query result column | (one per result column) |
| Configured Last modified field column | (modified date marker) |
Configured **Unique id fields** (default id) | UID |
| Same as UID | NAME |
Record (Deprecated SQL Connector)
| Source Field Name | SDM Attribute |
|---|---|
| Search / Sync query result column | (one per result column) |
| Configured Sync timestamp field column | (modified date marker) |
| Configured Unique field | UID |
| Same as UID | NAME |
Operations & API
Expand each connector object to see its operation options, delta-sync behavior, and the API it uses. See connector operation options for how to apply operation options (keys and values are case-sensitive).
Record (Current SQL Connector)
Operation options
This object does not support any operation options.
Delta sync
Supported. The connector performs an incremental (delta) sync via the since sync token, filtering on configured Last modified field (configuration.getLastModifiedField()).
API
- Type: JDBC (direct relational-database access through the selected driver)
Record (Deprecated SQL Connector)
Operation options
This object does not support any operation options.
Delta sync
Supported. The connector performs an incremental (delta) sync via the sync token sync token, filtering on configured timestamp/sync-token field (numeric high-water mark).
API
- Type: JDBC (relational-database access via a Spring
JdbcTemplate; supports JNDI-context connection URLs)
Changelog
The SQL Connector connector has undergone the following changes:
| Version | Description | Migration Steps |
|---|---|---|
| 3.1.14 | New Features - Added support for Databricks as a data source, including authentication with an Azure service principal (Client ID, Client Secret, and Tenant ID). | N/A |
| 3.1.13 | Improvements - Refined the help text for the unique identifier fields to match the connector documentation, making configuration clearer. | N/A |
| 3.1.12 | Improvements - Refined the help text for the unique identifier fields to match the connector documentation, making configuration clearer. | N/A |
| 3.1.11 | New Features - Added a "Convert UID to MD5" option that controls how unique identifiers are stored. When enabled, unique IDs are stored as MD5 hashes; when disabled, the configured unique ID field values are stored in their original form. The default preserves the existing hashing behavior for backwards compatibility. Improvements - Reorganized and clarified the connection, query, and identifier configuration help text for easier setup. | N/A |
| 3.1.10 | Improvements Dependency Upgrades Upgraded internal framework and model libraries to the latest stable versions for improved reliability, security patches, and alignment with the rest of the connector platform. | N/A |
| 3.1.9 | Improvements Dependency Upgrades Upgraded internal framework and model libraries to the latest stable versions for improved reliability, security patches, and alignment with the rest of the connector platform. | N/A |
| 3.1.8 | Bug Fixes - Fixed a resource leak when reading large text (CLOB) columns by ensuring each result set is properly closed after processing, improving stability during large syncs. | N/A |
| 3.1.7 | Improvements - Improved boolean field handling so that fields are matched correctly after column-name sanitization. | N/A |
| 3.1.6 | New Features - Added support for boolean values. Columns that hold boolean data but are not reported as a native boolean type by the database can now be listed in the new "Boolean fields" setting so they are synchronized as true/false values. | • Synced data model: Fields you designate as boolean are now stored as boolean values instead of text. Re-sync the affected data model so the field values are stored with the correct type. |
| 3.1.5 | New Features - Added a dedicated Test Query used to verify database connectivity independently of the schema and data queries. Improvements - Clarified and corrected the display labels and help text for the Test, Schema, and Data queries (and their timeout and retry settings) to remove confusion about which query is which. | N/A |
| 3.1.4 | New Features - Introduced a redesigned SQL integration with clearer connection, schema, and data query configuration and configurable unique identifier fields, making it easier to define how each row is uniquely identified. Improvements - Improved handling of large text (CLOB) columns when building record identifiers. - Added the ability to filter out binary columns from synchronized data. Bug Fixes - Fixed unique identifier construction and last-modified timestamp handling so that records are tracked and updated reliably across syncs. | • Synced data model: The way unique record identifiers are constructed was corrected. Existing records may be re-keyed, so re-sync the affected data model to align stored records with the new identifiers. |
| 3.0.7 | Improvements - Strengthened the protection of the private key passphrase by handling it as a confidential, securely stored value. | N/A |
| 3.0.6 | New Features - Added support for authenticating to Snowflake with a private key, including an optional passphrase to unlock the key. The passphrase is stored as a confidential value. | N/A |
| 3.0.5 | Bug Fixes - Fixed driver registration so that connections to Snowflake (and other databases) succeed when the database driver is not automatically detected. | N/A |
| 3.0.4 | Bug Fixes - Fixed storage of the database driver configuration so the correct driver is used when connecting. | N/A |
| 3.0.3 | Improvements - Simplified database connection setup by relying on standard driver resolution instead of a fixed list of hard-coded drivers, allowing a broader range of databases to connect. | N/A |
| 3.0.2 | New Features - Added a bundled Snowflake JDBC driver so Snowflake databases can be connected without supplying a driver separately. | N/A |
| 3.0.1 | Improvements - Internal reliability improvement to how target data model types are registered. | N/A |
| 3.0.0 | Overview The SQL Connector integrates Brinqa with any relational database reachable over JDBC. Rather than targeting a fixed schema, it is a query-driven, schema-agnostic connector: an operator supplies the SQL queries used to test connectivity, discover the schema, and retrieve data, and each returned row is synchronized as a record. Category: Data Store Models | N/A |