MySQL Sink

Plugin version: 1.10.0

Writes records to a MySQL table. Each record will be written to a row in the table.

Use this sink whenever you need to write to a MySQL table. Suppose you periodically build a recommendation model for products on your online store. The model is stored in a FileSet and you want to export the contents of the FileSet to a MySQL table where it can be served to your users.

Column names would be autodetected from input schema.

Important: If you experience performance issues writing to MySQL, in the Connection Arguments property, set reWriteBatchedStatements to true.

Configuration

Property	Macro Enabled?	Version Introduced	Description

Property	Macro Enabled?	Version Introduced	Description
Use Connection	No	6.7.0/1.8.0	Optional. Whether to use a connection. If a connection is used, you do not need to provide the credentials.
Connection	Yes	6.7.0/1.8.0	Optional. Name of the connection to use. Project and service account information will be provided by the connection. You also can use the macro function ${conn(connection-name)}.
JDBC Driver Name	No		Required. Name of the JDBC driver to use.
Host	Yes		Required. Host that MySQL is running on. Default is localhost.
Port	Yes		Required. Port that MySQL is running on. Default is 3306.
Username	Yes		Optional. User identity for connecting to the specified database.
Password	Yes		Optional. Password to use to connect to the specified database.
Connection Arguments	Yes		Optional. A list of arbitrary string key/value pairs as connection arguments. These arguments will be passed to the JDBC driver as connection arguments for JDBC drivers that may need additional configurations.
Reference Name	No		Required. Name used to uniquely identify this sink for lineage, annotating metadata, etc.
Database	Yes		Required. MySQL database name.
Table Name	Yes		Required. Name of the table to export to.
Use SSL	No		Optional. Turns on SSL encryption. The connection will fail if SSL is not available. Default is `if available`.
Keystore URL	No		Optional. 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	No		Optional. Password for the client certificates KeyStore.
Truststore URL	No		Optional. 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	No		Optional. Password for the trusted root certificates KeyStore.
Use Compression	No		Optional. Use zlib compression when communicating with the server. Select this option for WAN connections. Default is No.
SQL_MODE	No		Optional. Override the default SQL_MODE session variable used by the server.
Auto Reconnect	No		Optional. Should the driver try to re-establish stale and/or dead connections. Default is No.

Data Type Mapping

MySQL Data Type	CDAP Schema Data Type

MySQL Data Type	CDAP Schema Data Type
BIT	boolean
TINYINT	int
BOOL, BOOLEAN	boolean
SMALLINT	int
MEDIUMINT	double
INT, INTEGER	int
BIGINT	long
FLOAT	float
DOUBLE	double
DECIMAL	decimal
DATE	date
DATETIME	timestamp
TIMESTAMP	timestamp
TIME	time
YEAR	date
CHAR	string
VARCHAR	string
BINARY	bytes
VARBINARY	bytes
TINYBLOB	bytes
TINYTEXT	string
BLOB	bytes
TEXT	string
MEDIUMBLOB	bytes
MEDIUMTEXT	string
LONGBLOB	bytes
LONGTEXT	string
ENUM	string
SET	string

Example

Suppose you want to write output records to “users” table of MySQL database named “prod” that is running on “localhost”, port 3306, as “root” user with “root” password (Ensure that the driver for MySQL is installed. You can also provide driver name for some specific driver, otherwise “mysql” will be used), then configure the plugin with:

Property	Value

Property	Value
Reference Name	`snk1`
Driver Name	`mariadb`
Host	`localhost`
Port	`3306`
Database	`prod`
Table Name	`users`
Username	`root`
Password	`root`