Configure MySQL Reader

MySQL Reader plug-in can connect to remote MySQL database by the JDBC connector. SQL query statements are generated and sent to the remote MySQL database based on your configuration. Then, the SQL statements are run and the returned results are assembled into abstract datasets using custom data types of data synchronization. Datasets are passed to the downstream writer for processing.

In short, With the JDBC connector, MySQL Reader connects to the remote MySQL database and runs SQL statements to select data from MySQL database and read data from MySQL database from the underlying level.

MySQL Reader supports table and view reading. In table field, you can specify all columns in sequence, specify certain columns, adjust the column order, specify constant fields, and configure MySQL functions, such as now().

MySQL Reader supports the following MySQL data types:

Note:

• Apart from the field types listed here, other types are not supported.
• MySQL Reader classifies tinyint(1) as the integer type.

Parameter description

Development in wizard mode

Parameter description:

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

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

• Data filtering: where in the preceding parameter description. You can enter a WHERE filtering statement (with WHERE keyword not required) based on the corresponding SQL syntax. This filtering condition can be used as an incremental synchronization criterion.

• Splitting key: splitPk in the preceding parameter description. Supports only integer fields. During data reading, the data is split based on the configured fields to achieve concurrent reading, improving data synchronization efficiency. The configuration of splitting key is related to the source selection in data synchronization. The splitting key configuration item is displayed only when you configure the data source.

  Note:

  • You can enter constants. The value must be embraced with a pair of single quotation marks, such as abc, 123.
  • Scheduling parameters can be used together, such as ${bdp.system.bizdate}.
  • You can enter functions supported by relational database, such as now() and count(1).
  • If the value you entered cannot be parsed, the type is displayed as -.

• Field mapping: column in the preceding parameter description. The Source Table Field on the left maps with the Target Table Field on the right. Click Add Line, and then a field is added. Hover the cursor over a line, click Delete, and then the line is deleted.

Development in script mode

The script configuration example is shown as follows (single database/table). For more information about how to enter parameters, see Parameter Description:

{
    "type": "job",
    "version": "1.0",
    "configuration": {
        "reader": {
            "plugin": "mysql",
            "parameter": {
                "datasource": "datasourceName",
                "table": "`table`",
                "splitPk": "id",
                "column": [
                    "id", "name", "age"
                ],
                "where": "1 < id and id < 100"
            }
        },
        "writer": {            
        }
    }
}

Script configuration examples are provided as follows (database/table sharding). For more information about how to enter parameters, see Parameter Description:

{
    "type": "job",
    "version": "1.0",
    "configuration": {
        "reader": {
            "plugin": "mysql",
            "parameter": {
                "connection": [
                    {
                        "table": [
                            "tbl1",
                            "tbl2",
                            "tbl3"
                        ],
                        "datasource": "datasourceName1"
                    },
                    {
                        "table": [
                            "tbl4",
                            "tbl5",
                            "tbl6"
                        ],
                        "datasource": "datasourceName2"
                    }
                ],
                "singleOrMulti": "multi",
                "splitPk": "db_id",
                "column": [
                    "id", "name", "age"
                ],
                "where": "1 < id and id < 100"
            }
        },
        "writer": {            
        }
    }
}