DataWorks:SQL Server data source

Parameter

Description

Required

Default value

datasource

The name of the data source. The code editor supports adding data sources. 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 from which you want to synchronize data. You can synchronize data from only one table in a job.

Yes

None

column

The columns that you want to synchronize from the source table. Use a JSON array to specify the column information. By default, all columns are synchronized. Example: ["*"].

• Column pruning is supported. You can select specific columns to export.

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

• Constant configuration is supported. You must specify the constants in the MySQL SQL syntax format. For example, ["id", "table","1", "'john.doe'", "'null'", "to_char(a+1)", "2.3" , "true"] .

  • id is a regular column name.

  • table is the column name that contains a reserved word.

  • 1 is an integer constant.

  • 'mingya.wmy' is a string constant (note that it must be enclosed in single quotation marks).

  • 'null' is a string.

  • to_char(a + 1) is a function expression.

  • 2.3 is a floating-point number.

  • true is a Boolean value.

• The column parameter must specify the collection of columns to sync and cannot be empty.

Yes

None

splitFactor

The sharding factor. You can configure the number of shards for data synchronization. If you configure multiple concurrent threads, the data is split into concurrency × splitFactor shards. For example, if concurrency is 5 and splitFactor is 5, the data is split into 5 × 5 = 25 shards and processed by five concurrent threads.

No

5

splitPk

When SQL Server Reader extracts data, you can specify the splitPk parameter to shard the data based on the specified field. This allows the data synchronization system to start concurrent tasks to synchronize data, which improves efficiency.

• We recommend that you use the primary key of the table for splitPk. Primary keys are usually evenly distributed, which helps prevent data hotspots in the shards.

• Currently, splitPk supports sharding only for integer fields. If you specify a field of another type, such as string, floating-point, or date, SQL Server Reader reports an error.

No

None

where

The filter condition. SQL Server Reader constructs a SQL statement based on the specified column, table, and where parameters, and then extracts data based on the SQL statement. For example, for testing purposes, you can set the where condition to limit 10. In actual business scenarios, you might synchronize data for the current day by setting the where condition to gmt_create > ${bizdate}.

• The where condition can be used for efficient incremental data synchronization.

• If you leave the where parameter empty, all data in the table is synchronized.

No

None

querySql

Format: "querysql" : "query statement". In some business scenarios, the where parameter is not sufficient to define the filter conditions. You can use this parameter to customize the filter SQL statement. When you configure this parameter, the data synchronization system ignores the tables and columns parameters and uses the content of this parameter 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, SQL Server Reader ignores the column, table, and where parameters.

No

None

fetchSize

This parameter specifies the number of records to fetch in each batch from the database server. This value determines the number of network interactions between Data Integration and the server, which can improve data extraction performance.

No

1024

driverVersion

The version of the SQL Server driver. The default value is 4.0. You can set this parameter to 12.10 to use a version that supports Active Directory Service Principal authentication.

No

4.0

Parameter

Description

Required

Default value

datasource

The name of the data source. The code editor supports adding data sources. 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 which you want to synchronize data.

Yes

None

column

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

Yes

None

preSql

The SQL statement that is executed before the data synchronization task runs. In the codeless UI, you can execute only one SQL statement. In the code editor, you can execute multiple SQL statements, such as statements to clear old data.

No

None

postSql

The SQL statement that is executed after the data synchronization task runs. In the codeless UI, you can execute only one SQL statement. In the code editor, you can execute multiple SQL statements, such as statements to add a timestamp.

No

None

writeMode

The import mode. The insert mode is supported. If a primary key or unique index conflict occurs, Data Integration treats the data as dirty data but retains the original data.

No

insert

batchSize

The number of records to submit in each batch. A larger value can significantly reduce network interactions between the data synchronization system and SQL Server and improve overall throughput. If this value is too large, an OOM error may occur in the data synchronization process.

No

1,024

driverVersion

The version of the SQL Server driver. The default value is 4.0. You can set this parameter to 12.10 to use a version that supports Active Directory Service Principal authentication.

No

4.0