Introduction

A separate database plugin to support MSSQL-specific features and configurations.

Use-Case

Users can choose and install MSSQL source and sink plugins.
Users should see MSSQL logo on plugin configuration page for better experience.
Users should get relevant information from the tool tip:
- The tool tip for the connection string should be customized specifically to the MSSQL database,
- The tool tip should describe accurately what each field is used for.
Users should not have to specify any redundant configuration (ex: JDBC type in source plugin, columns in the sink plugin).
Users should get field level lineage for the source and sink that is being used.
Reference documentation should be updated to account for the changes.
The source code for MSSQL database plugin should be placed in repo under data-integrations org.
Integration tests for MSSQL database plugin should be added in the test repo.
The data pipeline using source and sink plugins should run on both mapreduce and spark engines.

User Stories

User should be able to install MSSQL specific database source and sink plugins from the Hub
Users should have each tool tip accurately describe what each field does
Users should get field level lineage information for the MSSQL source and sink
Users should be able to setup a pipeline avoiding specifying redundant information
Users should get updated reference document for MSSQL source and sink
Users should be able to read all the DB types

Plugin Type

Batch Source
Batch Sink
Real-time Source
Real-time Sink
Action
Post-Run Action
Aggregate
Join
Spark Model
Spark Compute

Design Tips

MSSQL support connection using Azure Active Directory(AD), to connect to AD the https://github.com/AzureAD/azure-activedirectory-library-for-java need to be on classpath.

Information about types of AD connections: https://docs.microsoft.com/en-us/sql/connect/jdbc/connecting-using-azure-active-directory-authentication?view=sql-server-2017

MSSQL connector reference: https://docs.microsoft.com/en-us/sql/connect/jdbc/download-microsoft-jdbc-driver-for-sql-server?view=sql-server-2017

Existing database plugins: https://github.com/cdapio/hydrator-plugins/tree/develop/database-plugins

MSSQL datatypes mappings and conversions: https://docs.microsoft.com/en-us/sql/connect/jdbc/using-basic-data-types?view=sql-server-2017

Design

The suggestion is to create maven submodule MSSQL under database-plugins repo.

Sink Properties

User Facing Name	Type	Description	Constraints
Label	String	Label for UI
Reference Name	String	Uniquely identified name for lineage
Host	String	MSSQL host (serverName)	Required (defaults to localhost on UI)
Port	Number	The port where SQL Server is listening. If the port number is specified in the connection string, no request to SQLbrowser is made. When the port and instanceName are both specified, the connection is made to the specified port. However, the instanceName is validated and an error is thrown if it does not match the port. Important: We recommend that the port number is always specified, as this is more secure than using SQLbrowser	Optional (default 1433)
Database	String	Database name to connect	Required
Authentication Type	Select	Indicates which SQL authentication method will be used for the connection. Use 'SQL Login' to connect to a SQL Server using username and password properties. 'Active Directory Password' can be used to connect to an Azure SQL Database/Data Warehouse using an Azure AD principal name and password
Username	String	DB username	Required
Password	Password	User password	Required
Transaction Isolation Level	Select	Transaction isolation level for queries run by this sink
Connection Arguments	Keyvalue	A list of arbitrary string tag/value pairs as connection arguments, list of properties https://docs.microsoft.com/en-us/sql/connect/jdbc/setting-the-connection-properties?view=sql-server-2017
Table Name	String	Name of a database table to write to
Instance Name	String	The SQL Server instance name to connect to. When it is not specified, a connection is made to the default instance. For the case where both the instanceName and port are specified, see the notes for port. If you specify a Virtual Network Name in the Server connection property, you cannot use instanceName connection property	Optional
Query Timeout	Number	The number of seconds to wait before a timeout has occurred on a query. The default value is -1, which means infinite timeout. Setting this to 0 also implies to wait indefinitely.	Optional
Connect Timeout	Number	Time in seconds to wait for a connection to the server before terminating the attempt and generating an error.	Optional
Column Encryption	Select	Default column encryption setting for all the commands on the connection. When enabled the JDBC driver will transparently encrypt and decrypt sensitive data stored in encrypted database columns in the SQL Server.	Possible values are: 'Enabled' and 'Disabled'. Default: 'Disabled'.
Encrypt	Select	When set to 'Yes', SQL Server uses SSL encryption for all data sent between the client and server if the server has a certificate installed.	Possible values are: 'Yes' and 'No'. Default: 'No'.
Trust Server Certificate	Select	When set to 'Yes' (and encryption enabled), SQL Server uses SSL encryption for all data sent between the client and server without validating the server certificate.	Possible values are: 'Yes' and 'No'. Default: 'No'.
Workstation ID	String	Used to identify the specific workstation in various SQL Server profiling and logging tools.	Optional
Failover Partner	String	The name or network address of the instance of SQL Server that acts as failover partner.	Optional
Packet Size	Number	The network packet size used to communicate with SQL Server, specified in bytes. It's not recommended to specify packet size property when the encryption is enabled. Otherwise, the driver might raise a connection error.	Optional
Current Language	String	Must correspond to the SQL Server language record name and specifies the language environment for the session. The session language determines the datetime formats and system messages.	Optional

Source Properties

User Facing Name	Type	Description	Constraints
Label	String	Label for UI
Reference Name	String	Uniquely identified name for lineage
Host	String	MSSQL host (serverName)	Required (defaults to localhost on UI)
Port	Number	The port where SQL Server is listening. If the port number is specified in the connection string, no request to SQLbrowser is made. When the port and instanceName are both specified, the connection is made to the specified port. However, the instanceName is validated and an error is thrown if it does not match the port. Important: We recommend that the port number is always specified, as this is more secure than using SQLbrowser	Optional (default 1433)
Database	String	Database name to connect	Required
Import Query	String	Query for import data	Valid SQL query
Authentication Type	Select	Indicates which SQL authentication method will be used for the connection. Use 'SQL Login' to connect to a SQL Server using username and password properties. 'Active Directory Password' can be used to connect to an Azure SQL Database/Data Warehouse using an Azure AD principal name and password
Username	String	DB username	Required
Password	String	User password	Required
Bounding Query	String	Returns max and min of split-By Filed	Valid SQL query
Split-By Field Name	String	Field name which will be used to generate splits
Number of Splits to Generate	Number	Number of splits to generate
Transaction Isolation Level	Select	Transaction isolation level for queries run by this sink
Connection Arguments	Keyvalue	A list of arbitrary string tag/value pairs as connection arguments, list of properties https://docs.microsoft.com/en-us/sql/connect/jdbc/setting-the-connection-properties?view=sql-server-2017
Instance Name	String	The SQL Server instance name to connect to. When it is not specified, a connection is made to the default instance. For the case where both the instanceName and port are specified, see the notes for port. If you specify a Virtual Network Name in the Server connection property, you cannot use instanceName connection property	Optional
Query Timeout	Number	The number of seconds to wait before a timeout has occurred on a query. The default value is -1, which means infinite timeout. Setting this to 0 also implies to wait indefinitely.	Optional
Connect Timeout	Number	Time in seconds to wait for a connection to the server before terminating the attempt and generating an error.	Optional
Column Encryption	Select	Default column encryption setting for all the commands on the connection. When enabled the JDBC driver will transparently encrypt and decrypt sensitive data stored in encrypted database columns in the SQL Server.	Possible values are: 'Enabled' and 'Disabled'. Default: 'Disabled'.
Encrypt	Select	When set to 'Yes', SQL Server uses SSL encryption for all data sent between the client and server if the server has a certificate installed.	Possible values are: 'Yes' and 'No'. Default: 'No'.
Trust Server Certificate	Select	When set to 'Yes' (and encryption enabled), SQL Server uses SSL encryption for all data sent between the client and server without validating the server certificate.	Possible values are: 'Yes' and 'No'. Default: 'No'.
Workstation ID	String	Used to identify the specific workstation in various SQL Server profiling and logging tools.	Optional
Failover Partner	String	The name or network address of the instance of SQL Server that acts as failover partner.	Optional
Packet Size	Number	The network packet size used to communicate with SQL Server, specified in bytes. It's not recommended to specify packet size property when the encryption is enabled. Otherwise, the driver might raise a connection error.	Optional
Current Language	String	Must correspond to the SQL Server language record name and specifies the language environment for the session. The session language determines the datetime formats and system messages.	Optional

Action Properties

User Facing Name	Type	Description	Constraints
Label	String	Label for UI
Host	String	MSSQL host (serverName)	Required (defaults to localhost on UI)
Port	Number	The port where SQL Server is listening. If the port number is specified in the connection string, no request to SQLbrowser is made. When the port and instanceName are both specified, the connection is made to the specified port. However, the instanceName is validated and an error is thrown if it does not match the port. Important: We recommend that the port number is always specified, as this is more secure than using SQLbrowser	Optional (default 1433)
Database	String	Database name to connect	Required
Authentication Type	Select	Indicates which SQL authentication method will be used for the connection. Use 'SQL Login' to connect to a SQL Server using username and password properties. 'Active Directory Password' can be used to connect to an Azure SQL Database/Data Warehouse using an Azure AD principal name and password
Username	String	DB username	Required
Password	String	User password	Required
Connection Arguments	Keyvalue	A list of arbitrary string tag/value pairs as connection arguments, list of properties https://docs.microsoft.com/en-us/sql/connect/jdbc/setting-the-connection-properties?view=sql-server-2017
Database Command	String	Database command to run	Valid SQL query
Instance Name	String	The SQL Server instance name to connect to. When it is not specified, a connection is made to the default instance. For the case where both the instanceName and port are specified, see the notes for port. If you specify a Virtual Network Name in the Server connection property, you cannot use instanceName connection property	Optional
Query Timeout	Number	The number of seconds to wait before a timeout has occurred on a query. The default value is -1, which means infinite timeout. Setting this to 0 also implies to wait indefinitely.	Optional
Application Intent	Select	Declares the application workload type when connecting to a server.	Possible values: 'ReadWrite' and 'ReadOnly'. Default: 'ReadWrite'.
Connect Timeout	Number	Time in seconds to wait for a connection to the server before terminating the attempt and generating an error.	Optional
Column Encryption	Select	Default column encryption setting for all the commands on the connection. When enabled the JDBC driver will transparently encrypt and decrypt sensitive data stored in encrypted database columns in the SQL Server.	Possible values are: 'Enabled' and 'Disabled'. Default: 'Disabled'.
Encrypt	Select	When set to 'Yes', SQL Server uses SSL encryption for all data sent between the client and server if the server has a certificate installed.	Possible values are: 'Yes' and 'No'. Default: 'No'.
Trust Server Certificate	Select	When set to 'Yes' (and encryption enabled), SQL Server uses SSL encryption for all data sent between the client and server without validating the server certificate.	Possible values are: 'Yes' and 'No'. Default: 'No'.
Workstation ID	String	Used to identify the specific workstation in various SQL Server profiling and logging tools.	Optional
Failover Partner	String	The name or network address of the instance of SQL Server that acts as failover partner.	Optional
Packet Size	Number	The network packet size used to communicate with SQL Server, specified in bytes. It's not recommended to specify packet size property when the encryption is enabled. Otherwise, the driver might raise a connection error.	Optional
Current Language	String	Must correspond to the SQL Server language record name and specifies the language environment for the session. The session language determines the datetime formats and system messages.	Optional

Data Types Mapping

MS SQL Data Type	CDAP Schema Data Type	Support	Comment
BIGINT	Schema.Type.LONG	+
BINARY	Schema.Type.BYTES	+
BIT	Schema.Type.BOOLEAN	+
CHAR	Schema.Type.STRING	+
DATE	Schema.LogicalType.DATE	+
DATETIME	Schema.LogicalType.TIMESTAMP_MICROS	+
DATETIME2	Schema.LogicalType.TIMESTAMP_MICROS	+
DATETIMEOFFSET	Schema.Type.STRING	*
DECIMAL	Schema.LogicalType.DECIMAL	+
FLOAT	Schema.Type.DOUBLE	+
IMAGE	Schema.Type.BYTES	+
INT	Schema.Type.INT	+
MONEY	Schema.LogicalType.DECIMAL	+
NCHAR	Schema.Type.STRING	+
NTEXT	Schema.Type.STRING	+
NUMERIC	Schema.LogicalType.DECIMAL	+
NVARCHAR	Schema.Type.STRING	+
NVARCHAR(MAX)	Schema.Type.STRING	+
REAL	Schema.Type.FLOAT	+
SMALLDATETIME	Schema.LogicalType.TIMESTAMP_MICROS	+
SMALLINT	Schema.Type.INT	+
SMALLMONEY	Schema.LogicalType.DECIMAL	+
TEXT	Schema.Type.STRING	+
TIME	Schema.LogicalType.TIME_MICROS	*	TIME data type has the accuracy of 100 nanoseconds which can not be supported using TIME_MICROS logical type.
TIMESTAMP	Schema.Type.BYTES	*	TIMESTAMP is the synonym for the ROWVERSION data type, values of which are automatically generated. Thus TIMESTAMP can not be supported by Sink plugin.
TINYINT	Schema.Type.INT	+
UDT	Depends on basic type	+	UDT will be mapped as basic type if it's an alias of this type. CLR UDT mapped to Schema.Type.BYTES.
UNIQUEIDENTIFIER	Schema.Type.STRING	+
VARBINARY	Schema.Type.BYTES	+
VARBINARY(MAX)	Schema.Type.BYTES	+
VARCHAR	Schema.Type.STRING	+
VARCHAR(MAX)	Schema.Type.STRING	+
XML	Schema.Type.STRING	+
SQLVARIANT	Schema.Type.STRING	*
GEOMETRY	Schema.Type.BYTES	+
GEOGRAPHY	Schema.Type.BYTES	+

Approach

Create a module mssql-plugin in database-plugins project, reuse existing database-plugins code if possible. Add MSSQL-specific properties to configuration, add support for MSSQL-specific datatypes. Update UI widgets JSON definitions.

Pipeline Samples

API changes

Deprecated Programmatic APIs

database-plugins is moved to Data Integrations

UI Impact or Changes

Configurable database properties are presented as named text fields instead of arbitrary key value pairs. MSSQL source and sink are separate entries with MSSQL logo in source and sink lists.

CDAP

Microsoft SQL Server database plugin