DataWorks:MariaDB data source

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 task can be used to synchronize data to only one table.

The following examples show the advanced usage of the table parameter. In the examples, the table parameter is used to specify a table range.

• Set the table parameter to 'table_[0-99]'. This value indicates that MariaDB Reader reads data from the tables 'table_0' to 'table_99' in sharded databases.

• Set the table parameter to '"table": ["table_00[0-9]", "table_0[10-99]", "table_[100-999]"]'. This value indicates that MariaDB Reader reads data from tables 'table_000' to 'table_999'. You can use this method only if the numerical suffixes of the names of your tables are of the same length.

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 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 MariaDB, 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. This parameter cannot be left empty.

Yes

No default value

splitPk

The field that is used for data sharding when MariaDB Reader reads data. If you configure 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. 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 for data only 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 synchronize incremental data. If the where parameter is not provided or is left empty, MariaDB Reader reads all data.

• Do not set the where parameter to limit 10. This value does not conform to the constraints of MariaDB 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 configure this parameter, MariaDB Reader ignores the settings of the table, column, and splitPk parameters and filters data 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 configure the querySql parameter, MariaDB 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.

No

No default value

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 to which you want to write data.

Yes

No default value

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 the data that is not written to these rows is regarded as dirty data. If you configure a synchronization task by using the code editor, set writeMode to insert into.

• on duplicate key update: If no primary key conflict or unique index conflict occurs, the data is processed in the same way as that when you set this parameter to insert into. If a conflict occurs, specified fields in the original rows are replaced with new rows, and data is written to MariaDB. If you configure a synchronization task by using the code editor, set writeMode to on duplicate key update.

• replace into: If no primary key conflict or unique index conflict occurs, the data is processed in the same way as that when you set this parameter to insert into. If a conflict occurs, the original rows are deleted, and new rows are inserted. This indicates that all data in the original rows are replaced. If you configure a synchronization task by using the code editor, set writeMode to replace into.

No

insert into

column

The names of the columns to which you want to write data. Separate the names with commas (,), such as "column": ["id", "name", "age"]. If you want to write data to all the columns in the destination table, set this parameter to an asterisk (*), such as "column":["*"].

Yes

No default value

preSql

The SQL statement that you want to execute before the synchronization task 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 tablename statement to delete outdated data before the synchronization task is run.

No

No default value

postSql

The SQL statement that you want to execute after the synchronization task 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 the synchronization task is run.

No

No default value

batchSize

The number of data records to write at a time. Set this parameter to an appropriate value based on your business requirements. This greatly reduces the interactions between Data Integration and MariaDB and increases throughput. If you set this parameter to an excessively large value, an out of memory (OOM) error may occur during data synchronization.

No

256

updateColumn

The names of columns that are updated when a primary key conflict or unique index conflict occurs. This parameter takes effect only when the writeMode parameter is set to on duplicate key update. Separate multiple column names with commas (,). Example: "updateColumn":["name", "age"].

No

No default value