...
MySQL datatypes mappings and conversions: https://dev.mysql.com/doc/connector-j/8.0/en/connector-j-reference-type-conversions.html
Connecting Securely Using SSL
Configuring Connector/J client to use SSL can be accomplished by the following steps:
1) Import server certificate into the Java default truststore (although tampering the default truststore is not recommended) or by importing it into a custom Java truststore file. Use 'trustCertificateKeyStoreUrl' property to point the driver to the trusted root certificate keystore.
2) Generate the client private key and certificate or use keys and certificate files generated by the MySQL server. Convert the client key and certificate files to a PKCS #12 archive and import the archive into a Java keystore. Use 'clientCertificateKeyStoreUrl' property to point the driver to the client certificate keystore.
3) Use 'clientCertificateKeyStorePassword' and 'trustCertificateKeyStorePassword' properties to specify passwords for the client and trusted certificates keystores.
See:
https://dev.mysql.com/doc/connector-j/8.0/en/connector-j-reference-configuration-properties.html
https://dev.mysql.com/doc/refman/8.0/en/encrypted-connections.html
https://dev.mysql.com/doc/connector-j/8.0/en/connector-j-reference-using-ssl.html
Support of the 'Use ANSI quotes to quote identifiers' property
Should not be specified via JDBC URL parameter since it will override the default SQL_MODE system variable, instead of appending 'ANSI_QUOTES' to it's value. Proper implementation has to read default SQL_MODE(which depends on the version of the MySQL Server), append 'ANSI_QUOTES' and update the value using "SET SESSION sql_mode = 'modes';" statement.
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 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.
Sink Properties
| User Facing Name | Type | Description | Constraints |
|---|---|---|---|
| Label | String | Label for UI | |
| Reference Name | String | Uniquely identified name for lineage | |
| Host | String | Mysql host | Required (defaults to localhost on UI) |
| Port | Number | Specific port where mysql running on | Optional (default 3306) |
| Database | String | Database name to connect | Required |
| 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://dev.mysql.com/doc/connector-j/8.0/en/connector-j-reference-configuration-properties.html | |
| Table Name | String | Name of a database table to write to | |
| Use SSL | Select | 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 | String | 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 | String | Password for the trusted root certificates KeyStore | |
| Use compression protocol | Boolean | Use zlib compression when communicating with the server. Select this option for WAN connections. | |
| SQL_MODE | String | Override the default SQL_MODE session variable used by the server. |
Source Properties
| User Facing Name | Type | Description | Constraints |
|---|---|---|---|
| Label | String | Label for UI | |
| Reference Name | String | Uniquely identified name for lineage | |
| Host | String | Mysql host | Required (defaults to localhost on UI) |
| Port | Number | Specific port where mysql running on | Optional (default 3306) |
| Database | String | Database name to connect | Required |
| Import Query | String | Query for import data | Valid SQL query |
| Username | String | DB username | Required |
| Password | String | User password | Required |
| Bounding Query | String | Returns max and minof 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://dev.mysql.com/doc/connector-j/8.0/en/connector-j-reference-configuration-properties.html | |
| Use SSL | Select | 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 | String | 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 | String | Password for the trusted root certificates KeyStore | |
| Use compression protocol | Boolean | Use zlib compression when communicating with the server. Select this option for WAN connections. | |
| SQL_MODE | String | Override the default SQL_MODE session variable used by the server. | |
Use ANSI quotes to quote identifiers | Boolean | Treats " as an identifier quote character and not as a string quote character. |
Action Properties
| User Facing Name | Type | Description | Constraints |
|---|---|---|---|
| Label | String | Label for UI | |
| Host | String | Mysql host | Required (defaults to localhost on UI) |
| Port | Number | Specific port where mysql running on | Optional (default 3306) |
| Database | String | Database name to connect | Required |
| 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://dev.mysql.com/doc/connector-j/8.0/en/connector-j-reference-configuration-properties.html | |
| Database Command | String | Database command to run | Valid SQL query |
| Use SSL | Select | 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 | String | 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 | String | Password for the trusted root certificates KeyStore | |
| Use compression protocol | Boolean | Use zlib compression when communicating with the server. Select this option for WAN connections. | |
| SQL_MODE | String | Override the default SQL_MODE session variable used by the server. | |
Use ANSI quotes to quote identifiers | Boolean | Treats " as an identifier quote character and not as a string quote character. |
Data Types Mapping
| MySQL Data Type | CDAP Schema Data Type | Support | Comment |
|---|---|---|---|
| BIT | Schema.Type.BOOLEAN | + | |
| TINYINT | Schema.Type.INT | + | |
| BOOL, BOOLEAN | Schema.Type.BOOLEAN | + | |
| SMALLINT | Schema.Type.INT | + | |
| MEDIUMINT | Schema.Type.INT | + | |
| INT,INTEGER | Schema.Type.INT | + | |
| BIGINT | Schema.Type.LONG | + | |
| FLOAT | Schema.Type.FLOAT | + | |
| DOUBLE | Schema.Type.DOUBLE | + | |
| DECIMAL | Schema.LogicalType.DECIMAL | + | |
| DATE | Schema.Type.DATE | + | |
| DATETIME | Schema.LogicalType.TIMESTAMP_MICROS | + | |
| TIMESTAMP | Schema.LogicalType.TIMESTAMP_MICROS | + | |
| TIME | Schema.LogicalType.TIME_MICROS | + | |
| YEAR | Schema.Type.DATE | + | |
| CHAR | Schema.Type.STRING | + | |
| VARCHAR | Schema.Type.STRING | + | |
| BINARY | Schema.Type.BYTES | + | |
| VARBINARY | Schema.Type.BYTES | + | |
| TINYBLOB | Schema.Type.BYTES | + | |
| TINYTEXT | Schema.Type.STRING | + | |
| BLOB | Schema.Type.BYTES | + | |
| TEXT | Schema.Type.STRING | + | |
| MEDIUMBLOB | Schema.Type.BYTES | + | |
| MEDIUMTEXT | Schema.Type.STRING | + | |
| LONGBLOB | Schema.Type.BYTES | + | |
| LONGTEXT | Schema.Type.STRING | + | |
| ENUM | Schema.Type.STRING | * | No such type in java.sql.Types, mapping to String by default |
| SET | Schema.Type.STRING | + |
Approach
Create a module mysql-plugin in database-plugins project, reuse existing database-plugins code if possible. Add MySQL-specific properties to configuration, add support for MySQL-specific datatypes. Update UI widgets JSON definitions.
...