This topic describes the data types and parameters that MySQL Writer supports and how to configure it by using the codeless user interface (UI) and code editor.

Prerequisites

A MySQL connection is configured. For more information, see Configure a MySQL connection.

Background information

MySQL Writer allows you to write data to tables stored in MySQL databases. Specifically, MySQL Writer connects to a remote MySQL database by using Java Database Connectivity (JDBC), and executes an INSERT INTO or a REPLACE INTO statement to write data to the MySQL database. MySQL uses the InnoDB engine so that data is written to the database in batches.

Users such as database administrators can use MySQL Writer as a data migration tool. MySQL Writer obtains data from a Data Integration reader, and writes the data to the destination database based on value of the writeMode parameter.
Note A sync node that uses MySQL Writer must have at least the permission to execute the INSERT INTO or REPLACE INTO statement. Whether other permissions are required depends on the SQL statements that you specify in the preSql and postSql parameters when you configure the node.

Data types

MySQL Writer supports most MySQL data types. Make sure that your data types are supported.

The following table describes the data types that MySQL Writer supports.
Category MySQL data type
Integer INT, TINYINT, SMALLINT, MEDIUMINT, BIGINT, and YEAR
Floating point FLOAT, DOUBLE, and DECIMAL
String VARCHAR, CHAR, TINYTEXT, TEXT, MEDIUMTEXT, and LONGTEXT
Date and time DATE, DATETIME, TIMESTAMP, and TIME
Boolean BOOL
Binary TINYBLOB, MEDIUMBLOB, BLOB, LONGBLOB, and VARBINARY

Parameters

Parameter Description Required Default value
datasource The connection name. It must be the same as the name of the added connection. You can add connections in the code editor. Yes N/A
table The name of the destination table. Yes N/A
writeMode The write mode. Valid values: insert into, on duplicate key update, and replace into.
  • insert into: If a primary key conflict or unique index conflict occurs, data cannot be written to the conflicting rows and is regarded as dirty data.

    If you create a sync node by using the code editor, set writeMode to insert.

  • on duplicate key update: If no primary key conflict or unique index conflict occurs, the action is the same as that of insert into. If a conflict occurs, specified fields in original rows are updated.

    If you create a sync node by using the code editor, set writeMode to update.

  • replace into: If no primary key conflict or unique index conflict occurs, the action is the same as that of insert into. If a conflict occurs, original rows are deleted and new rows are inserted. This means that all the field values of the original rows are replaced.

    If you create a sync node by using the code editor, set writeMode to replace.

No insert into
column The columns in the destination table to which data is written. Separate the columns with commas (,), for example, "column":["id","name","age"]. To write data to all the columns in the destination table, set the value to an asterisk (*), for example, "column":["*"]. Yes N/A
preSql The SQL statement to execute before the sync node is run. You can execute only one SQL statement on the codeless UI and multiple SQL statements in the code editor. For example, you can execute the TRUNCATE TABLE table name statement to clear outdated data before data synchronization.
Note If you specify multiple SQL statements in the code editor, the system may not execute them in the same transaction.
No N/A
postSql The SQL statement to execute after the sync node is run. You can execute only one SQL statement on the codeless UI and multiple SQL statements in the code editor. For example, you can execute the alter table tablename add colname timestamp DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP statement to add a timestamp after data synchronization.
Note If you specify multiple SQL statements in the code editor, the system may not execute them in the same transaction.
No N/A
batchSize The number of data records to write at a time. Set this parameter to a proper value based on your actual needs. This can greatly reduce the interactions between Data Integration and the MySQL database on the network, and increase the throughput. However, an excessively large value may lead to the out of memory (OOM) error during the data synchronization process. No 1,024

Configure MySQL Writer by using the codeless UI

Create a sync node and configure the node. For more information, see Create a sync node by using the codeless UI.

Complete the following steps on the configuration tab of the node:
  1. Configure the connections.
    Configure the connections to the source and destination data stores for the sync node.Select data source section
    Parameter Description
    Data source The datasource parameter in the preceding parameter description. Select a connection type and select the name of a connection that you have configured in DataWorks.
    Table The table parameter in the preceding parameter description.
    Prepare statement before import The preSql parameter in the preceding parameter description. Enter an SQL statement to execute before the sync node is run.
    Complete statement after import The postSql parameter in the preceding parameter description. Enter an SQL statement to execute after the sync node is run.
    Primary key conflict The writeMode parameter in the preceding parameter description. Select the required write mode.
  2. Configure field mapping. It is equivalent to setting the column parameter in the preceding parameter description. Fields in the source table on the left have a one-to-one mapping with fields in the destination table on the right.Field Mapping section
    GUI element Description
    The same name mapping Click The same name mapping to establish a mapping between fields with the same name. Note that the data types of the fields must match.
    Peer mapping Click Peer mapping to establish a mapping between fields in the same row. Note that the data types of the fields must match.
    Unmap Click Unmap to remove mappings that have been established.
    Automatic typesetting Click Automatic typesetting to sort the fields based on specified rules.
  3. Configure channel control policies.Channel control section
    Parameter Description
    Maximum number of concurrent tasks expected The maximum number of concurrent threads that the sync node uses to read data from or write data to data stores. You can configure the concurrency for the node on the codeless UI.
    Synchronization rate Specifies whether to enable bandwidth throttling. You can enable bandwidth throttling and set a maximum transmission rate to avoid heavy read workload of the source. We recommend that you enable bandwidth throttling and set the maximum transmission rate to a proper value.
    The number of error records exceeds The maximum number of dirty data records allowed.

Configure MySQL Writer by using the code editor

The following example shows how to configure a sync node to write data to a MySQL database. For more information, see Create a sync node by using the code editor.
{
    "type":"job",
    "version":"2.0", // The version number.
    "steps":[ 
        {
            "stepType":"stream",
            "parameter":{},
            "name":"Reader",
            "category":"reader"
        },
        {
            "stepType":"mysql",// The writer type.
            "parameter":{
                "postSql":[],// The SQL statement to execute after the sync node is run.
                "datasource":"",// The connection name.
                "column":[// The columns to which data is written.
                    "id",
                    "value"
                ],
                "writeMode":"insert",// The write mode. Valid values: insert, replace, and update.
                "batchSize":1024,// The number of data records to write at a time.
                "table":"",// The name of the destination table.
                "preSql":[ 
                     "delete from XXX;" // The SQL statement to execute before the sync node is run.
                   ]
            },
            "name":"Writer",
            "category":"writer"
        }
    ],
    "setting":{
        "errorLimit":{// The maximum number of dirty data records allowed.
            "record":"0"
        },
        "speed":{
            "throttle":false, // Specifies whether to enable bandwidth throttling.
            "concurrent":1// The maximum number of concurrent threads.
        }
    },
    "order":{
        "hops":[
            {
                "from":"Reader",
                "to":"Writer"
            }
        ]
    }
}