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

SQL Server Reader reads data from SQL Server.

SQL Server Reader connects to a remote SQL Server 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, SQL Server Reader assembles the returned data into abstract datasets of the data types supported by Data Integration and sends the datasets to a writer. If you use the code editor to configure SQL Server Reader, take note of the following items:
  • SQL Server Reader generates the SQL statement based on the settings of the table, column, and where parameters and sends the generated statement to the SQL Server database.
  • If you specify the querySql parameter, SQL Server Reader sends the value of this parameter to the SQL Server database.

SQL Server Reader supports most SQL Server data types. Make sure that the data types of your database are supported.

SQL Server versions

SQL Server Reader uses the driver com.microsoft.sqlserver sqljdbc4 4.0. For more information about the capabilities of the driver, see the official documentation. The following table lists the commonly used SQL Server versions and describes whether they are supported by the driver.

Version Supported
SQL Server 2016 Yes
SQL Server 2014 Yes
SQL Server 2012 Yes
PDW 2008R2 AU34 Yes
SQL Server 2008 R2 Yes
SQL Server 2008 Yes
SQL Server 2019 No
SQL Server 2018 No

Data types

The following table lists the data types that are supported by SQL Server Reader.

Category SQL Server data type
Integer BIGINT, INT, SMALLINT, and TINYINT
Floating point FLOAT, DECIMAL, REAL, and NUMERIC
String CHAR, NCHAR, NTEXT, NVARCHAR, TEXT, VARCHAR, NVARCHAR (MAX), and VARCHAR (MAX)
Date and time DATE, DATETIME, and TIME
Boolean BIT
Binary BINARY, VARBINARY, VARBINARY (MAX), and TIMESTAMP

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. 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 SQL Server, 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': a string.
    • to_char(a + 1): a function expression.
    • 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 SQL Server 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. 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 this parameter to a field of an unsupported data type, such as a string, floating point, or date data type, SQL Server Reader returns an error.
No No default value
where The WHERE clause. SQL Server Reader generates an SQL statement based on the settings of the column, table, and where parameters and uses the generated statement to read data. For example, when you perform a test, you can set the where parameter to limit 10. To read the data that is generated on the current day, you can set the where parameter to gmt_create > $bizdate.
  • You can use the WHERE clause to read incremental data.
  • If you do not specify the where parameter, SQL Server Reader reads all data.
No No default value
querySql The SQL statement that is used for refined data filtering. Specify this parameter in the format of "querysql" : "SQL statement",. 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. If you specify this parameter, SQL Server Reader ignores the settings of the column, table, and where parameters. No No default value
fetchSize The number of data records to read at a time. This parameter determines the number of interactions between Data Integration and the source database and affects read efficiency.
Note If you set this parameter to a value greater than 2048, an out of memory (OOM) error may occur during data synchronization.
No 1024

Configure SQL Server Reader by using the codeless UI

  1. Configure data sources.

    Configure Source and Target for the synchronization node.

    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.
  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 SQL Server Reader by using the code editor

In the following code, a synchronization node is configured to read data from an SQL Server database:
{
    "type":"job",
    "version":"2.0",// The version number. 
    "steps":[
        {
            "stepType":"sqlserver",// The reader type. 
            "parameter":{
                "datasource":"",// The name of the data source. 
                "column":[// The names of the columns from which you want to read data. 
                    "id",
                    "name"
                ],
                "where":"",// The WHERE clause. 
                "splitPk":"",// The shard key based on which the table is sharded. 
                "table":""// The name of the table from which you want to read data. 
            },
            "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"
            }
        ]
    }
}
You can use the querySql parameter to specify an SQL statement to read data. The following code provides an example. In the following code, sql_server_source is the SQL Server data source, dbo.test_table is the table from which you want to read data, and name is the column from which you want to read data.
{
    "stepType": "sqlserver",
    "parameter": {
        "querySql": "select name from dbo.test_table",
        "datasource": "sql_server_source",
        "column": [
            "name"
        ],
        "where": "",
        "splitPk": "id"
    },
    "name": "Reader",
    "category": "reader"
},

Additional information

  • Data synchronization between primary and secondary databases

    A secondary SQL Server database can be deployed for disaster recovery. The secondary database continuously synchronizes data from the primary database based on binary logs. Data latency between the primary and secondary databases cannot be prevented. This may result in data inconsistency.

  • Data consistency control

    SQL Server is a relational database management system (RDBMS) that supports strong consistency for data queries. A database snapshot is created before a synchronization node starts. SQL Server Reader reads data from the database snapshot. Therefore, if new data is written to the database during data synchronization, SQL Server Reader cannot obtain the new data.

    Data consistency cannot be ensured if you enable SQL Server Reader to use parallel threads to read data in a synchronization node.

    SQL Server Reader shards the source table based on the value of the splitPk parameter and uses parallel threads to read data. These parallel threads belong to different transactions and read data at different points in time. Therefore, the parallel threads observe different snapshots.

    Theoretically, data inconsistencies cannot be prevented if parallel threads are used for a synchronization node. The following workarounds can be used:
    • Enable SQL Server Reader to use a single thread to read data in a synchronization node. This indicates that you do not need to specify a shard key for SQL Server Reader. This way, data consistency is ensured, but data is synchronized at low efficiency.
    • Make sure that no data is written to the source table during data synchronization. This ensures that the data in the source table remains unchanged during data synchronization. For example, you can lock the source table or disable data synchronization between primary and secondary databases. This way, data can be efficiently synchronized, but your ongoing services may be interrupted.
  • Character encoding

    SQL Server Reader uses JDBC to read data. This enables SQL Server Reader to automatically convert the encoding formats of characters. Therefore, you do not need to specify the encoding format.

  • Incremental data synchronization
    SQL Server Reader uses JDBC to connect to a database and uses a SELECT statement with a WHERE clause to read incremental data.
    • For batch data, incremental add, update, and delete operations (including logically delete operations) are distinguished by timestamps. Specify the WHERE clause based on a specific timestamp. The time indicated by the timestamp must be later than the time indicated by the latest timestamp in the previous synchronization.
    • For streaming data, specify the WHERE clause based on the ID of a specific record. The ID must be greater than the maximum ID involved in the previous synchronization.

    If the data that is added or modified cannot be distinguished, SQL Server Reader can read only full data.

  • Syntax validation

    SQL Server Reader allows you to specify custom SELECT statements by using the querySql parameter but does not verify the syntax of these statements.