Azure SQL Database

DataCater uses a JDBC-based connector for streaming data from Azure SQL Database to the DataCater platform.


Requirements

Please make sure that the Azure SQL database allows connections from the public IP address of DataCater.

Please also make sure that the Azure SQL user can access the configured table and the system tables information_schema.key_column_usage, database_name.information_schema.tables, and information_schema.columns.


Configuration

This source connector supports the following configuration options:

Azure server name

The name of the Azure server.

Username

The name of the user.

Password

The password of the user.

Database name

The name of the Azure SQL database.

Schema name

The name of the Azure SQL schema (default: dbo).

Table name

The name of the Azure SQL database table. You may retrieve the list of tables available in the given database and schema by clicking on Fetch table names.

Change Data Capture mode

You may choose one of the following modes for change data capture:

  • BULK: Recurringly load all data from the BigQuery table. Basically no change data capture at all.
  • INCREMENTING: Use the primary key column, specified using the Primary key column configuration option, to recurringly extract new records. This mode does only extract INSERTs, but skips UPDATEs and DELETEs.
  • TIMESTAMP/INCREMENTING: Use the primary key column and the timestamp column, specified using the Primary key column and Timestamp column configuration options, to recurringly extract new and updated records. This mode does only extract INSERTs and UPDATEs, but skips DELETEs.
Primary key column

If the database table does not specify any primary key, please define a column to be used as primary key here.

Timestamp column

DataCater can use a timestamp column, which stores the time of the most recent update of a record, to detect record updates. Specifying the timestamp column is required when using TIMESTAMP/INCREMENTING as Change Data Capture mode.

Sync interval

The interval in seconds between synchronizations of the Azure SQL Database and DataCater (default: 3600).


Data Types

The following table shows the mapping between Azure SQL Database data types and the data types used by DataCater.

Azure SQL Database data type DataCater data type
bigint Long
bit Int
char String
date Date
datetime Timestamp
decimal Double
float Double
int Int
ntext String
numeric Double
nvarchar String
real Float
smallint Int
time Time
tinyint Int