MemSQL database plugin

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.

BITSchema.Type.BOOLEAN+
TINYINTSchema.Type.INT+
SMALLINTSchema.Type.INT+
MEDIUMINTSchema.Type.INT+
INTSchema.Type.INT+
BIGINTSchema.Type.LONG+
DOUBLESchema.Type.DOUBLE+
FLOATSchema.Type.FLOAT+
DECIMALSchema.LogicalType.DECIMAL+
DATESchema.Type.DATE+
TIMESchema.LogicalType.TIME_MICROS+
TIMESTAMPSchema.LogicalType.TIMESTAMP_MICROS+
DATETIMESchema.LogicalType.TIMESTAMP_MICROS+
YEARSchema.Type.DATE+
CHARSchema.Type.STRING+
BINARYSchema.Type.BYTES+
VARCHARSchema.Type.STRING+
VARBINARYSchema.Type.BYTES+
LONGTEXTSchema.Type.STRING+
LONGBLOBSchema.Type.BYTES+
MEDIUMBLOBSchema.Type.BYTES+
BLOBSchema.Type.BYTES+
TINYBLOBSchema.Type.BYTES+
MEDIUMTEXTSchema.Type.STRING+
TEXTSchema.Type.STRING+
TINYTEXTSchema.Type.STRING+
JSONSchema.Type.STRING+
GEOGRAPHYPOINTSchema.Type.STRING+
GEOGRAPHYSchema.Type.STRING+
ENUMSchema.Type.STRING+
SETSchema.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

TODO