Configure MySQL writer

The MySQL Writer plug‑in provides the ability to write data into a target table of a MySQL database. At the underlying implementation level, MySQL Reader connects to a remote MySQL database through JDBC, and runs the “insert into …” (or “replace into …”) SQL statement to write data into MySQL. Data is submitted and written into the database in batch within MySQL, and the database must use InnoDB engine. You must configure data source before configuring the MySQL Writer plug-in. For more information, see MySQL data source config.

MySQL Writer is designed for ETL developers to import data from data warehouses to MySQL. MySQL Writer can also be used as a data migration tool by DBA and other users. MySQL Writer acquires the protocol data generated by Reader based on writeMode by means of the Data Synchronization framework.

Note: The entire task requires at least the insert/replace into... permission. Whether other permissions are required depends on the statements you specified in preSql and postSql when you configure the task.

• insert into…: If the primary key conflicts with the unique index, data cannot be written into the conflicting lines.

• replace into…: If the primary key does not conflict with the unique index, it performs the same operation with “insert into”. When a conflict exists, all the fields in the original line are replaced with the fields in the new line and data is written into MySQL. To improve performance, we use PreparedStatement + Batch and configure rewriteBatchedStatements=trueto buffer data to the thread context buffer. A write request is submitted only when the amount of data in the buffer reaches the threshold.

• insert ignore…: When the primary key / uniqueness index conflicts, the data integration directly ignores the update, and does not record it.

Similar to MySQL Reader, MySQL Writer currently supports most data types in MySQL. Check whether your data type is supported.

MySQL Writer converts the MySQL data types as follows.

Parameter description

• datasource

  • Description: Data source name. It must be identical to the data source name added. Adding data source is supported in script mode.

  • Required: Yes

  • Default value: None

• table

  • Description: The table selected for synchronization.

  • Required: Yes

  • Default value: None

• writeMode

  • Description: Select an import mode. The insert/replace modes are supported.

    • insert into: If the primary key conflicts with the unique index, Data Integration processes the data as dirty data.
    • replace into: If the primary key does not conflict with the unique index, it performs the same operation with “insert into”. When a conflict exists, all the fields in the original line are replaced with the fields in the new line.
    • INSERT INTO table (a,b,c) VALUES (1,2,3) ON DUPLICATE KEY UPDATE…: If the primary key does not conflict with the unique index, it performs the same operation with “insert into”. When a conflict exists, all the specified fields in the original line are replaced with the fields in the new line. The mode writes data to MySQL.

  • Required: No

  • Default value: insert

• column

  • Description: The target table fields into which data is required to be written. These fields are separated by commas. For example: “column”: [“id”, “name”, “age”]. Use * if it is required to write data into all columns in sequence. For example: “column”: [“*”]

  • Required: Yes

  • Default value: None

• preSql

  • Description: The SQL statement that is run before the data synchronization task is run. Currently, you can run only one SQL statement in wizard mode, and more than one SQL statement in script mode. For example, clear the old data.

  • Required: No

  • Default value: None

• postSql

  • Description: The SQL statement that is run after the data synchronization task is run. Currently, you can run only one SQL statement in wizard mode, and more than one SQL statement in script mode. For example, add a timestamp.

  • Required: No

  • Default value: None

• batchSize

  • Description: The quantity of records submitted in one operation. Setting this parameter can greatly reduce the interactions between Data Synchronization and MySQL, and increase the overall throughout. However, an excessively large value may cause the running process of Data Synchronization to become out of memory (OOM).

  • Required: No

  • Default value: 1024

Development in wizard mode

• Data Sources: datasource in the preceding parameter description. Select the MySQL data source.

• Table: table in the preceding parameter description. Select the table to be synchronized.

• Prepared Statement before Import: preSql in the preceding parameter description, namely, the SQL statement that is run before the data synchronization task is run.

• Prepared Statement after Import: postSql in the preceding parameter description, namely, the SQL statement that is run after the data synchronization task is run.

• Key Conflict: writeMode in the preceding parameter description. You can select the expected import mode.

• Field Mapping: column in the preceding parameter description.

  

Development in script mode

The following is a script configuration sample. For relevant parameters, see Parameter description.

{
    "type": "job",
    "version": "1.0",
    "configuration": {
        "reader": {
        },
        "writer": {
            "plugin": "mysql",
            "parameter": {
                "datasource": "datasourceName",
                "table": "table",
                "column": [
                    "id", "name", "age"
                ],
                "preSql": [
                    "delete from XXX;"
                ]
            }
        }
    }
}