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

Prerequisites

A MySQL data source is configured. For more information, see Add a MySQL data source.

Background information

MySQL Reader connects to a remote MySQL database by using Java Database Connectivity (JDBC), generates an SQL statement based on your configurations, and then sends the statement to the database. The system executes the statement on the database and returns data. Then, MySQL Reader assembles the returned data into abstract datasets of the data types supported by Data Integration and sends the datasets to a writer.

MySQL Reader can read data from tables and views. If you want to read data from a table, you can specify all or some of the columns in the table in an order that is specified by the table schema, specify constant fields, or configure MySQL functions such as now(). You can also specify the columns in an order that is different from the order specified by the table schema.

Data types

The following table lists the data types that are supported by MySQL Reader.
Category MySQL data type
Integer INT, TINYINT, SMALLINT, MEDIUMINT, and BIGINT
Floating point FLOAT, DOUBLE, and DECIMAL
String VARCHAR, CHAR, TINYTEXT, TEXT, MEDIUMTEXT, and LONGTEXT
Date and time DATE, DATETIME, TIMESTAMP, TIME, and YEAR
Boolean BIT and BOOLEAN
Binary TINYBLOB, MEDIUMBLOB, BLOB, LONGBLOB, and VARBINARY
Notice
  • Data types that are not listed in the preceding table are not supported.
  • MySQL Reader processes TINYINT(1) as an integer data type.

Parameters

Parameter Description Required Default value
datasource The name of the data source. It must be the same as the name of the added data source. You can add data sources by using the code editor. Yes No default value
table The name of the table from which you want to read data. Each synchronization node can be used to synchronize data to only one table.
For a sharded table, you can use the table parameter to specify the partitions from which you want to read data. Examples:
  • Set the table parameter to 'table_[0-99]'. This value indicates that MySQL Reader reads data from the partitions 'table_0' to 'table_99' of the sharded table.
  • Set the table parameter to '"table":["table_00[0-9]", "table_0[10-99]", "table_[100-999]"]'. This value indicates that MySQL Reader reads data from the partitions 'table_000' to 'table_999' of the sharded table. You can use this method only if the numerical suffixes of all the partition names are of the same length.
Note MySQL Reader reads data from the columns that are specified by the column parameter in the partitions that are specified by the table parameter. If a specified partition or column does not exist, the synchronization node fails.
Yes No default value
column The names of the columns from which you want to read data. Specify the names in a JSON array. The default value is [ * ], which indicates all the columns in the source table.
  • You can select specific columns to read.
  • The column order can be changed. This indicates that you can specify columns in an order different from the order specified by the schema of the source table.
  • Constants are supported. The column names must be arranged in compliance with the SQL syntax supported by MySQL, such as ["id","table","1","'mingya.wmy'","'null'","to_char(a+1)","2.3","true"].
    • id: a column name.
    • table: the name of a column that contains reserved keywords.
    • 1: an integer constant.
    • 'mingya.wmy': a string constant, which is enclosed in single quotation marks (').
    • null:
      • " " indicates an empty string.
      • null indicates a null value.
      • 'null' indicates the string null.
    • to_char(a+1): a function expression that is used to calculate the length of a string.
    • 2.3: a floating-point constant.
    • true: a Boolean value.
  • The column parameter must explicitly specify all the columns from which you want to read data. The parameter cannot be left empty.
Yes No default value
splitPk The field that is used for data sharding when MySQL Reader reads data. If you specify this parameter, the source table is sharded based on the value of this parameter. Data Integration then runs parallel threads to read data. This way, data can be synchronized more efficiently.
  • We recommend that you set the splitPk parameter to the name of the primary key column of the table. This way, data can be evenly distributed to different shards based on the primary key column, instead of being intensively distributed only to specific shards.
  • The splitPk parameter supports sharding only for data of integer data types. If you set the splitPk parameter to a field of an unsupported data type, such as a string, floating point, or date data type, the setting of this parameter is ignored, and a single thread is used to read data.
  • If the splitPk parameter is not provided or is left empty, a single thread is used to read data.
No No default value
where The WHERE clause. For example, you can set this parameter to gmt_create > $bizdate to read the data that is generated on the current day.
  • You can use the WHERE clause to read incremental data. If the where parameter is not provided or is left empty, MySQL Reader reads all data.
  • Do not set the where parameter to limit 10. This value does not conform to the constraints of MySQL on the SQL WHERE clause.
No No default value
querySql (advanced parameter, which is available only in the code editor) The SQL statement that is used for refined data filtering. If you specify this parameter, data is filtered based only on the value of this parameter. For example, if you want to join multiple tables for data synchronization, set this parameter to select a,b from table_a join table_b on table_a.id = table_b.id. The priority of the querySql parameter is higher than the priorities of the table, column, where, and splitPk parameters. If you specify the querySql parameter, MySQL Reader ignores the settings of the table, column, where, and splitPk parameters. The system parses the information, such as the username and password, of the data source specified by the datasource parameter from the querySql parameter.
Note The name of the querySql parameter is case-sensitive. For example, querysql does not take effect.
No No default value
singleOrMulti (available only for sharded tables) Specifies that the source table is a sharded table. After you switch from the codeless UI to the code editor, the "singleOrMulti":"multi" configuration is automatically generated. However, if you use the code editor at the beginning, the configuration is not automatically generated, and you must manually add the configuration in the code editor. If you do not add the configuration, MySQL Reader reads data only from the first shard. Yes multi

Configure MySQL Reader by using the codeless UI

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

Perform the following steps on the configuration tab of the synchronization node:
  1. Configure data sources.
    Configure Source and Target for the synchronization node. Configure data sources
    Parameter Description
    Connection The name of the data source from which you want to read data. This parameter is equivalent to the datasource parameter that is described in the preceding section.
    Table The name of the table from which you want to read data. This parameter is equivalent to the table parameter that is described in the preceding section.
    Filter The condition that is used to filter the data you want to read. Filtering based on the LIMIT keyword is not supported. The SQL syntax is determined by the selected data source.
    Shard Key The shard key. You can use a column in the source table as the shard key. We recommend that you use the primary key column or an indexed column. Only integer columns are supported.
    If you specify this parameter, data sharding is performed based on the value of this parameter, and parallel threads can be used to read data. This improves data synchronization efficiency.
    Note The Shard Key parameter is displayed only after you select the data source for the synchronization node.
  2. Configure field mappings. This operation is equivalent to setting the column parameter that is described in the preceding section.
    Fields in the source on the left have a one-to-one mapping with fields in the destination on the right. You can click Add to add a field. To remove an added field, move the pointer over the field and click the Remove icon. Field mappings
    Operation Description
    Map Fields with the Same Name Click Map Fields with the Same Name to establish mappings between fields with the same name. The data types of the fields must match.
    Map Fields in the Same Line Click Map Fields in the Same Line to establish mappings between fields in the same row. The data types of the fields must match.
    Delete All Mappings Click Delete All Mappings to remove the mappings that are established.
    Auto Layout Click Auto Layout. Then, the system automatically sorts the fields based on specific rules.
    Change Fields Click the Change Fields icon. In the Change Fields dialog box, you can manually edit the fields in the source table. Each field occupies a row. The first and the last blank rows are included, whereas other blank rows are ignored.
    Add Click Add to add a field. Take note of the following rules when you add a field:
    • You can enter constants. Each constant must be enclosed in single quotation marks ('), such as 'abc' and '123'.
    • You can use scheduling parameters, such as ${bizdate}.
    • You can enter functions that are supported by relational databases, such as now() and count(1).
    • If the field that you entered cannot be parsed, the value of Type for the field is Unidentified.
  3. Configure channel control policies. Channel control
    Parameter Description
    Expected Maximum Concurrency The maximum number of parallel threads that the synchronization node uses to read data from the source or write data to the destination. You can configure the parallelism for the synchronization node on the codeless UI.
    Bandwidth Throttling Specifies whether to enable bandwidth throttling. You can enable bandwidth throttling and specify a maximum transmission rate to prevent heavy read workloads on the source. We recommend that you enable bandwidth throttling and set the maximum transmission rate to an appropriate value based on the configurations of the source.
    Dirty Data Records Allowed The maximum number of dirty data records allowed.
    Distributed Execution

    The distributed execution mode that allows you to split your node into pieces and distribute them to multiple Elastic Compute Service (ECS) instances for parallel execution. This speeds up synchronization. If you use a large number of parallel threads to run your synchronization node in distributed execution mode, excessive access requests are sent to the data sources. Therefore, before you use the distributed execution mode, you must evaluate the access load on the data sources. You can enable this mode only if you use an exclusive resource group for Data Integration. For more information about exclusive resource groups for Data Integration, see Exclusive resource groups for Data Integration and Create and use an exclusive resource group for Data Integration.

Configure MySQL Reader by using the code editor

For more information about how to configure a synchronization node by using the code editor, see Create a sync node by using the code editor.

The following sample code provides examples on how to configure a synchronization node to read data from a table that is not sharded and how to configure a synchronization node to read data from a sharded table.
  • Configure a synchronization node to read data from a table that is not sharded
    {
        "type":"job",
        "version":"2.0",// The version number. 
        "steps":[
            {
                "stepType":"mysql",// The reader type. 
                "parameter":{
                    "column":[// The names of the columns from which you want to read data. 
                        "id"
                    ],
                    "connection":[
                        {   "querySql":["select a,b from join1 c join join2 d on c.id = d.id;"], // The SQL statement that is used to read data from the source table. 
                            "datasource":"",// The name of the data source. 
                            "table":[// The name of the source table. The table name must be enclosed in brackets []. 
                                "xxx"
                            ]
                        }
                    ],
                    "where":"",// The WHERE clause. 
                    "splitPk":"",// The shard key. 
                    "encoding":"UTF-8"// The encoding format. 
                },
                "name":"Reader",
                "category":"reader"
            },
            {
                "stepType":"stream",
                "parameter":{},
                "name":"Writer",
                "category":"writer"
            }
        ],
        "setting":{
            "errorLimit":{
                "record":"0"// The maximum number of dirty data records allowed. 
            },
            "speed":{
                "throttle":true,// Specifies whether to enable bandwidth throttling. The value false indicates that bandwidth throttling is disabled, and the value true indicates that bandwidth throttling is enabled. The mbps parameter takes effect only when the throttle parameter is set to true. 
                "concurrent":1,// The maximum number of parallel threads. 
                "mbps":"12"// The maximum transmission rate.
            }
        },
        "order":{
            "hops":[
                {
                    "from":"Reader",
                    "to":"Writer"
                }
            ]
        }
    }
  • Configure a synchronization node to read data from a sharded table
    Note When you configure a synchronization node to read data from a sharded MySQL table, you can select multiple partitions that have the same schema.
    {
        "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": {
                "stepType":"stream",
                "parameter":{},
                "name":"Writer",
                "category":"writer"    
            }
            "setting":{
                "errorLimit":{
                   "record":"0"// The maximum number of dirty data records allowed. 
            },
                "speed":{
                   "throttle":false,// Specifies whether to enable bandwidth throttling. The value false indicates that bandwidth throttling is disabled, and the value true indicates that bandwidth throttling is enabled. The mbps parameter takes effect only when the throttle parameter is set to true. 
                   "concurrent":1,// The maximum number of parallel threads. 
            }
        },
             "order":{
                "hops":[
                {
                    "from":"Reader",
                    "to":"Writer"
                }
            ]
        }
        }
    }