Introduction

A separate database plugin to support MemSQL-specific features and configurations

Use-cases

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 data pipeline using source and sink plugins should run on both MapReduce and Spark engines
Users can choose and install MemSQL source and sink plugins
Users should see MemSQL logo on plugin configuration page for better experience
Users should get relevant information from the tooltip
- The tooltip for the connection string should be customized specifically to the MemSQL database
- The tooltip should describe accurately what each field is used for

User Stories

Users should have each tool tip accurately describe what each field does
Users should know the format for the MemSQL connection string by hovering over tooltip for connection string
Users should get field level lineage information for the MemSQL source and sink
Users should be able to setup a pipeline avoiding specifying redundant information
Users should get updated reference document for MemSQL 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 / Implementation Tips

MemSQL Client Driver Downloads: https://docs.memsql.com/client-downloads/

MemSQL data types: https://docs.memsql.com/v6.8/reference/sql-reference/datatypes/

Design

Currently MemSQL 6.8 is supported. We suggest using MariaDB Connector/J 2.4 since it is the last stable release which is recommended by MemSQL.

Sink Properties

Section	User Facing Name	Type	Description	Constraints
Basic	Label	String	Label for UI	Required
	Reference Name	String	Uniquely identified name for lineage	Required
	Host	String	MemSQL host	Required (defaults to localhost on UI)
	Port	Number	Specific port where MemSQL running on	Optional (default 3306)
	Database	String	Database name to connect	Required
	Table Name	String	Name of a database table to write to	Required

Credentials	Username	String	DB username	Required
	Password	Password	User password	Required

SSL	Use SSL	Toggle	Turns on SSL encryption. The connection will fail if SSL is not available
	Keystore URL	String	URL to the client certificate KeyStore (if not specified, use defaults). Must be accessible at the same location on host where CDAP Master is running and all hosts on which at least one HDFS, MapReduce, or YARN daemon role is running
	Keystore password	Password	Password for the client certificates KeyStore
	Truststore URL	String	URL to the trusted root certificate KeyStore (if not specified, use defaults). Must be accessible at the same location on host where CDAP Master is running and all hosts on which at least one HDFS, MapReduce, or YARN daemon role is running
	Truststore password	Password	Password for the trusted root certificates KeyStore

Advanced	Connection Arguments	Keyvalue	A list of arbitrary string tag/value pairs as connection arguments, list of properties. See https://mariadb.com/kb/en/library/about-mariadb-connector-j/#connection-strings
	Transaction Isolation Level	Select	Transaction isolation level for queries run by this sink
	Use compression protocol	Toggle	Use zlib compression when communicating with the server. Select this option for WAN connections

Source Properties

	User Facing Name	Type	Description	Constraints
Basic	Label	String	Label for UI	Required
	Reference Name	String	Uniquely identified name for lineage	Required
	Host	String	MemSQL host	Required (defaults to localhost on UI)
	Port	Number	Specific port where MemSQL running on	Optional (default 3306)
	Database	String	Database name to connect	Required
	Import Query	String	Query for import data	Valid SQL query

Credentials	Username	String	DB username	Required
	Password	Password	User password	Required

SSL	Use SSL	Toggle	Turns on SSL encryption. The connection will fail if SSL is not available
	Keystore URL	String	URL to the client certificate KeyStore (if not specified, use defaults). Must be accessible at the same location on host where CDAP Master is running and all hosts on which at least one HDFS, MapReduce, or YARN daemon role is running
	Keystore password	Password	Password for the client certificates KeyStore
	Truststore URL	String	URL to the trusted root certificate KeyStore (if not specified, use defaults). Must be accessible at the same location on host where CDAP Master is running and all hosts on which at least one HDFS, MapReduce, or YARN daemon role is running
	Truststore password	Password	Password for the trusted root certificates KeyStore

Advanced	Bounding Query	String	Returns max and min of Split-By Field	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 See https://mariadb.com/kb/en/library/about-mariadb-connector-j/#connection-strings
	Use compression protocol	Toggle	Use zlib compression when communicating with the server. Select this option for WAN connections
	Use ANSI quotes to quote identifiers	Toggle	Treats " as an identifier quote character and not as a string quote character

Action Properties

	User Facing Name	Type	Description	Constraints
Basic	Label	String	Label for UI	Required
	Host	String	MemSQL host	Required (defaults to localhost on UI)
	Port	Number	Specific port where MemSQL running on	Optional (default 3306)
	Database	String	Database name to connect	Required
	Database Command	String	Database command to run	Valid SQL query

Credentials	Username	String	DB username	Required
	Password	Password	User password	Required

SSL	Use SSL	Toggle	Turns on SSL encryption. The connection will fail if SSL is not available
	Keystore URL	String	URL to the client certificate KeyStore (if not specified, use defaults). Must be accessible at the same location on host where CDAP Master is running and all hosts on which at least one HDFS, MapReduce, or YARN daemon role is running
	Keystore password	Password	Password for the client certificates KeyStore
	Truststore URL	String	URL to the trusted root certificate KeyStore (if not specified, use defaults). Must be accessible at the same location on host where CDAP Master is running and all hosts on which at least one HDFS, MapReduce, or YARN daemon role is running
	Truststore password	Password	Password for the trusted root certificates KeyStore

Advanced	Connection Arguments	Keyvalue	A list of arbitrary string tag/value pairs as connection arguments, list of properties See https://mariadb.com/kb/en/library/about-mariadb-connector-j/#connection-strings
	Use compression protocol	Toggle	Use zlib compression when communicating with the server. Select this option for WAN connections.
	Use ANSI quotes to quote identifiers	Toggle	Treats " as an identifier quote character and not as a string quote character

Data Types Mapping

MemSQL Data Type	CDAP Schema Data Type	Support	Comment
BOOL	Schema.Type.INT Schema.Type.BOOLEAN	+	BOOL and BOOLEAN are synonymous with TINYINT. A value of 0 is considered FALSE, non-zero values are considered TRUE. For compatibility with other plugins custom mapping to boolean schema type will be supported for TINYINT database type.
BIT	Schema.Type.BOOLEAN	+
TINYINT	Schema.Type.INT	+
SMALLINT	Schema.Type.INT	+
MEDIUMINT	Schema.Type.INT	+
INT	Schema.Type.INT	+
BIGINT	Schema.Type.LONG	+
DOUBLE	Schema.Type.DOUBLE	+
FLOAT	Schema.Type.FLOAT	+
DECIMAL	Schema.LogicalType.DECIMAL	+
DATE	Schema.Type.DATE	+
TIME	Schema.LogicalType.TIME_MICROS	+
TIMESTAMP	Schema.LogicalType.TIMESTAMP_MICROS	+
DATETIME	Schema.LogicalType.TIMESTAMP_MICROS	+
YEAR	Schema.Type.DATE	+
CHAR	Schema.Type.STRING	+
BINARY	Schema.Type.BYTES	+
VARCHAR	Schema.Type.STRING	+
VARBINARY	Schema.Type.BYTES	+
LONGTEXT	Schema.Type.STRING	+
LONGBLOB	Schema.Type.BYTES	+
MEDIUMBLOB	Schema.Type.BYTES	+
BLOB	Schema.Type.BYTES	+
TINYBLOB	Schema.Type.BYTES	+
MEDIUMTEXT	Schema.Type.STRING	+
TEXT	Schema.Type.STRING	+
TINYTEXT	Schema.Type.STRING	+
JSON	Schema.Type.STRING	+
GEOGRAPHYPOINT	Schema.Type.STRING	+
GEOGRAPHY	Schema.Type.STRING	+
ENUM	Schema.Type.STRING	+
SET	Schema.Type.STRING	+

Approach

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

UI Impact or Changes

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

Test Case(s)

TODO

Sample Pipeline