DataWorks:MySQL data source

Parameter

Description

Data source name

The data source name must be unique within the workspace. We recommend a name that clearly identifies the business and environment, such as rds_mysql_order_dev.

Configuration mode

Select Alibaba Cloud instance mode. For more information about configuration modes, see Scenario 1: Instance mode (current Alibaba Cloud account) and Scenario 2: Instance mode (other Alibaba Cloud account).

Alibaba Cloud account

Select the account to which the instance belongs. If you select Other Alibaba Cloud Account, you must configure cross-account permissions. For more information, see Cross-account authorization (RDS, Hive, or Kafka).

If you select Other Alibaba Cloud account, you must provide the following information:

• Other Alibaba Cloud account ID: The ID of the Alibaba Cloud account that owns the instance.

• RAM role name for authorization: The RAM role provided by the other Alibaba Cloud account. This role must have permissions to use the target instance.

Region

The region where the instance is located.

Instance

Select the name of the instance to connect to.

Read replica settings

If this data source has a read-only instance (read replica), you can choose to read from the replica when you configure a task. The benefit is that it prevents interference with the primary database and does not affect its performance.

Instance endpoint

After you select the correct instance, click Get Latest Endpoint to view the public or private endpoint, VPC, and vSwitch information for the instance.

Database

The name of the database that the data source will access. Ensure that the specified user has access permissions for this database.

Username/Password

The username and password for the MySQL database. If you use an RDS instance, you can create and maintain accounts in the Account Management section of the instance.

Enable OSS binary log reading

When enabled, if the system cannot access the RDS binary log, it will try to get the binary log from OSS to prevent the real-time sync task from being interrupted. For configuration details, see OSS binary log read authorization. Set the OSS Binary Log Access Identity based on the authorization configuration.

Authentication options

You can choose no authentication or SSL authentication. If you choose SSL authentication, the instance itself must have SSL authentication enabled. Prepare the certificate file and upload it to Certificate File Management.

Version

You can log on to the MySQL server and run `SELECT VERSION()` to check the version number.

Parameter

Description

Data source name

The data source name must be unique within the workspace. We recommend a name that clearly identifies the business and environment, such as rds_mysql_order_dev.

Configuration mode

Select Connection String Mode. This connects to the database using a JDBC URL.

Connection string preview

After you enter the connection address and database name, DataWorks automatically combines them into a JDBC URL for you to preview.

Connection address

Host address IP: Enter the IP address or domain name of the database host. If the database is an Alibaba Cloud RDS instance, you can find the address in the instance details by clicking Database Connection.

Port: The database port. The default is 3306.

Database name

The name of the database that the data source will access. Ensure that the specified user has access permissions for this database.

Username/Password

The username and password for the MySQL database. If you use an RDS instance, you can create and maintain accounts in the Account Management section of the instance.

Version

You can log on to the MySQL server and run `SELECT VERSION()` to check the version number.

Authentication options

You can choose no authentication or SSL authentication. If you choose SSL authentication, the instance itself must have SSL authentication enabled. Prepare the certificate file and upload it to Certificate File Management.

Advanced parameters

Parameter: Click the parameter dropdown list and select a supported parameter name, such as connectTimeout.

Value: Enter a suitable value for the selected parameter, such as 3000.

The URL will be automatically updated to: jdbc:mysql://192.168.90.28:3306/test?connectTimeout=50000

Script parameter

Description

Required

Default value

datasource

The name of the data source. In the code editor, you can add a data source. The value of this parameter must be the same as the name of the added data source.

Yes

None

table

The name of the table to synchronize. A data integration task can read data from only one table.

Yes

None

column

A collection of column names to be synchronized from the configured table, described as a JSON array. By default, all columns are used, for example, [*].

• Column cropping: You can select a subset of columns to export.

• Column reordering: You can export columns in an order different from the table schema.

• Constant configuration: You must follow MySQL SQL syntax. For example, ["id","table","1","'mingya.wmy'","'null'","to_char(a+1)","2.3","true"].

  • id is a regular column name.

  • table is a column name that is also a reserved word.

  • 1 is an integer constant.

  • 'mingya.wmy' is a string constant. Note the single quotation marks.

  • About null:

    • " " represents an empty string.

    • null represents a NULL value.

    • 'null' represents the string "null".

  • to_char(a+1) is a function to calculate string length.

  • 2.3 is a floating-point number.

  • true is a Boolean value.

• The column parameter must explicitly specify the set of columns to synchronize. It cannot be empty.

Yes

None

splitPk

When you use MySQL Reader to extract data, if you specify the splitPk parameter, the data is sharded based on the field that splitPk represents. This allows data synchronization to start concurrent tasks, which improves efficiency.

• We recommend using the table's primary key for splitPk. Primary keys are usually distributed evenly, which helps prevent data hot spots in the shards.

• Currently, splitPk only supports sharding on integer data types. It does not support strings, floating-point numbers, dates, or other types. If you specify an unsupported type, the splitPk function is ignored, and data is synchronized in a single channel.

• If you do not specify the splitPk parameter (that is, you do not provide splitPk or the splitPk value is empty), data synchronization for the table uses a single channel.

No

None

splitFactor

The sharding factor. This determines the number of shards for data synchronization. If you configure concurrency, the data is split into a number of shards equal to concurrency × splitFactor. For example, if concurrency is 5 and splitFactor is 5, the data is split into 25 shards and processed by 5 concurrent threads.

No

5

where

A filter condition. In many business scenarios, you might synchronize only the current day's data. To do this, set the where condition to gmt_create>$bizdate.

• The where clause enables efficient incremental data synchronization. If you do not specify a where clause, which includes cases where you do not provide a key or value for where, data synchronization is treated as a full data synchronization.

• Do not set the where condition to `limit 10`. This does not conform to the MySQL SQL WHERE clause constraints.

No

None

querySql (Advanced mode. This parameter is not supported in the codeless UI.)

In some business scenarios, the `where` parameter is not sufficient to describe the filter conditions. You can use this parameter to define a custom filter SQL statement. When this parameter is configured, the data synchronization system ignores the `tables`, `columns`, and `splitPk` parameters and uses this SQL statement to filter data. For example, to synchronize data after a multi-table join, use select a,b from table_a join table_b on table_a.id = table_b.id. When you configure querySql, MySQL Reader ignores the `table`, `column`, `where`, and `splitPk` parameters. querySql has a higher priority than table, column, where, and splitPk. The system uses datasource to parse information such as the username and password.

No

None

useSpecialSecret

For multi-source data sources, this specifies whether to use the password of each individual data source. Valid values:

• true

• false

If you have configured multiple source data sources with different usernames and passwords, you can set this parameter to true to use the password of each respective data source.

No

false

Script parameter

Description

Required

Default value

datasource

The name of the data source. In the code editor, you can add a data source. The value of this parameter must be the same as the name of the added data source.

Yes

None

table

The name of the table to synchronize.

Yes

None

writeMode

The import mode. You can choose insert into, on duplicate key update, or replace into.

• insert into: If a primary key or unique index conflict occurs, the conflicting row is not written and is treated as dirty data.

  If you configure the task in the code editor, set writeMode to insert.

• on duplicate key update: If there is no primary key or unique index conflict, the behavior is the same as insert into. If a conflict occurs, the new row replaces the values of the specified fields in the existing row.

  If you configure the task in the code editor, set writeMode to update.

• replace into: If there is no primary key or unique index conflict, the behavior is the same as insert into. If a conflict occurs, the original row is deleted, and the new row is inserted. The new row replaces all fields of the original row.

  If you configure the task in the code editor, set writeMode to replace.

No

insert

nullMode

The NULL value handling policy. Valid values:

• writeNull: When a source field is NULL, a NULL value is written to the destination field.

• skipNull: When a source field is NULL, the destination field is not written. If the destination field has a default value, that value is used. If there is no default value, the value will be NULL. When you configure this parameter, you must also configure the skipNullColumn parameter.

No

writeNull

skipNullColumn

When nullMode is set to skipNull, the columns configured in this parameter are not forced to be written as NULL. The default value of the corresponding column is used instead.

Format: ["c1","c2",...], where c1 and c2 must be a subset of the column parameter.

No

All columns configured for the task.

column

The fields in the destination table to which data should be written. Separate fields with commas, for example, "column":["id","name","age"]. To write to all columns in order, use an asterisk (*), for example, "column":["*"].

Yes

None

preSql

An SQL statement that is executed before the data synchronization task starts. The codeless UI allows only one SQL statement. The code editor supports multiple SQL statements. For example, you can clear old data from a table before execution (`truncate table tablename`).

No

None

postSql

An SQL statement that is executed after the data synchronization task finishes. The codeless UI allows only one SQL statement. The code editor supports multiple SQL statements. For example, you can add a timestamp column: ALTER TABLE tablename ADD colname TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP.

No

None

batchSize

The number of records to submit in a single batch. A larger value can significantly reduce network interactions between the data synchronization system and MySQL and improve overall throughput. If this value is too large, it may cause an out-of-memory (OOM) error in the data synchronization process.

No

256

updateColumn

When writeMode is set to update, these are the fields to update when a primary key or unique index conflict occurs. Separate fields with commas, for example, "updateColumn":["name","age"].

No

None