Versions Compared

Old Version 1

changes.mady.by.user Illia

Saved on

compared with

New Version Current

changes.mady.by.user Vlad Hlinskiy

Saved on

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.

Introduction

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

Use-Case

  • Users can choose and install MySQL PostgreSQL source and sink plugins.
  • Users should see MySQL see PostgreSQL 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 MySQL the PostgreSQL database,
    • The tool tip should describe accurately what each field is used for.
  • User should get a performance comparable to Sqoop by utilizing sqoop libraries for the data ingestion and egress.
  • 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 MySQL PostgreSQL database plugin should be placed in repo under data-integrations org.
  • Integration tests for MySQL PostgreSQL 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 MySQL PostgreSQL specific database source and sink plugins from the Hub
  • Users should have each tool tip accurately describe what each field does
  • Users should know the format for the MySql connection string by hovering over tool tip for connection stringUsers should get field level lineage information for the MySQL PostgreSQL source and sink 
  • Users should get a performance comparable to Sqoop when ingesting data from mysql PostgreSQL and while writing data to MySQL PostgreSQL (within ~15% of the time taken for sqoop)
  • Users should be able to setup a pipeline avoiding specifying redundant information
  • Users should get updated reference document for MySQL PostgreSQL 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

MySQL Connector/J 8.0 PostgreSQL connector reference: https://devjdbc.mysqlpostgresql.comorg/docdownload/connector-j/8.0/en/postgresql-9.4.1211.jar

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

MySQL PostgreSQL datatypes mappings and conversions: https://dev.mysql.com/doc/connector-j/8.0/en/connector-j-reference-type-conversions.html


Design

Currently the two major MySQL versions supported are 5 and 8. We suggest using MySQL Connector/J 8.0 since it is backward compatible with older versions of MySQL and supports all the new features of recent releases.

The suggestion is to move create maven submodule PostgreSQL under database-plugins module from hydrator-plugins repository to database-plugins repo in data-integrations organization as described in Plugins Repo Split. There is existing code in database-plugins that may be reused for MySQL plugin. We suggest creating a multi-module Maven project where existing `database-plugins` will be a common functionality module for all subsequent DB plugins and each plugin for a specific database (in this case MySQL) will depend on it. Having each DB plugin in a dedicated module allows us to create separately deliverable artifacts, so user can upload only those plugins they need.repo.


Sink Properties

User Facing NameTypeDescriptionConstraints
LabelStringLabel for UI
Reference NameStringUniquely identified name for lineage
HostString
Mysql
PostgreSQL hostRequired (defaults to localhost on UI)
PortNumberSpecific port where
mysql
PostgreSQL running on

Optional

(default

3306

5432)

DatabaseStringDatabase name to connectRequired
Import QueryStringQuery for import data
Valid SQL queryValid SQL querySplit-By Field NameStringField name which will be used to generate splitsNumber of Splits to GenerateNumberNumber of splits to generate
UsernameStringDB usernameRequired
PasswordPasswordUser passwordRequired
Bounding QueryStringReturns max and minof split-By Filed
Transaction Isolation LevelSelectTransaction isolation level for queries run by this sink
Connection ArgumentsKeyvalue

A list of arbitrary string tag/value pairs as connection arguments, list of properties

https://

dev

jdbc.

mysql

postgresql.

com

org/

doc

documentation/

connector-j

head/

8.0/en/connector-j-reference-configuration-properties.html

connect.html#connection-parameters


Table NameStringName of a database table to write to
Connect TimeoutNumberThe timeout value used for socket connect operations. If connecting to the server takes longer than this value, the connection is broken. The timeout is specified in seconds and a value of zero means that it is disabled

Source Properties


User Facing NameTypeDescriptionConstraints
LabelStringLabel for UI
Reference NameStringUniquely identified name for lineage
HostStringMysql PostgreSQL hostRequired (defaults to localhost on UI)
PortNumberSpecific port where mysql PostgreSQL running on

Optional

(default 33065432)
DatabaseStringDatabase name to connectRequired
Import QueryStringQuery for import dataValid SQL query
UsernameStringDB usernameRequired
PasswordStringUser passwordRequired
Bounding QueryStringReturns max and minof split-By FiledValid SQL query
Split-By Field NameStringField name which will be used to generate splits
Number of Splits to GenerateNumberNumber of splits to generate
Transaction Isolation LevelSelectTransaction isolation level for queries run by this sink
Connection ArgumentsKeyvalueA list of arbitrary string tag/value pairs as connection arguments, list of properties properties https://devjdbc.mysqlpostgresql.comorg/docdocumentation/connector-jhead/8.0/en/connector-j-reference-configuration-properties.htmlconnect.html#connection-parameters
Connect TimeoutNumberThe timeout value used for socket connect operations. If connecting to the server takes longer than this value, the connection is broken. The timeout is specified in seconds and a value of zero means that it is disabled


Action Properties


User Facing NameTypeDescriptionConstraints
LabelStringLabel for UI
HostStringMysql PostgreSQL hostRequired (defaults to localhost on UI)
PortNumberSpecific port where mysql PostgreSQL running on

Optional

(default 33065432)
DatabaseStringDatabase name to connectRequired
Username

String

DB usernameRequired
PasswordStringUser passwordRequired
Connection ArgumentsKeyvalue

A list of arbitrary string tag/value pairs as connection arguments, list of

properties

properties 

https://

dev

jdbc.

mysql

postgresql.

com

org/

doc

documentation/

connector-j

head/

8.0/en/connector-j-reference-configuration-properties.html

connect.html#connection-parameters


Database CommandStringDatabase command to runValid SQL query
Connect TimeoutNumberThe timeout value used for socket connect operations. If connecting to the server takes longer than this value, the connection is broken. The timeout is specified in seconds and a value of zero means that it is disabled


Data Types Mapping

Postgres Data TypeCDAP Schema Data TypeSupportComment
BIGINTSchema.Type.LONG+
BIGSERIALSchema.Type.LONG+Serial is autoincremented
BIT(N)Schema.Type.STRING+Bit strings are strings of 1's and 0's
BIT VARYING(N)Schema.Type.STRING+Bit strings are strings of 1's and 0's
BOOLEANSchema.Type.BOOLEAN+
BYTEASchema.Type.BYTES+
CHARACTERSchema.Type.STRING+
CHARACTER VARYINGSchema.Type.STRING+
DOUBLE PRECISIONSchema.Type.DOUBLE+
INTEGERSchema.Type.INT+
NUMERIC(p, s)/DECIMAL(p, s)Schema.LogicalType.DECIMAL+
REALSchema.Type.FLOAT+
SMALLINTSchema.Type.INT+
SMALLSERIALSchema.Type.INT+Serial is autoincremented
SERIALSchema.Type.INT+Serial is autoincremented
TEXTSchema.Type.STRING+
DATESchema.LogicalType.DATE+
TIME [ (P) ] [ WITHOUT TIME ZONE ]Schema.LogicalType.TIME_MICROS+
TIME [ (P) ] WITH TIME ZONESchema.Type.STRING+
TIMESTAMP [ (P) ] [ WITHOUT TIME ZONE ]Schema.LogicalType.TIMESTAMP_MICROS+
TIMESTAMP [ (P) ] WITH TIME ZONESchema.LogicalType.TIMESTAMP_MICROS+Postgresql converts it to UTC(see "Time Stamps" section)
XMLSchema.Type.STRING+
TSQUERYSchema.Type.STRING+
TSVECTORSchema.Type.STRING+
TXID_SNAPSHOT
-Postgresql specific, see documentation
UUIDSchema.Type.STRING+
BOXSchema.Type.STRING+
CIDRSchema.Type.STRING+
CIRCLESchema.Type.STRING+
INETSchema.Type.STRING+
INTERVALSchema.Type.STRING+
JSONSchema.Type.STRING+
JSONBSchema.Type.STRING+
LINESchema.Type.STRING+
LSEGSchema.Type.STRING+
MACADDRSchema.Type.STRING+
MACADDR8Schema.Type.STRING+
MONEYSchema.Type.STRING+
PATHSchema.Type.STRING+
PG_LSN
-Postgresql specific, see documentation
POINTSchema.Type.STRING+
POLYGONSchema.Type.STRING+



Approach

Create a module mysqlpostgresql-plugin in database-plugins project, reuse existing database-plugins code if possible. Add MySQLPostgreSQL-specific properties to configuration, add support for MySQLPostgreSQL-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. MySQL PostgreSQL source and sink are separate entries with MySQL PostgreSQL logo in source and sink lists.

Test Scenarios

TODO

Releases

Release X.Y.Z

Related Work

Database plugin enhancements

Future work

PostgreSQL Oracle database plugin