edit-icon download-icon

Configure sqlServer writer

Last Updated: Mar 21, 2018

The SqlServer Writer plug‑in provides the ability to write data into the target table of the master SqlServer database. At the underlying implementation level, SqlServer Writer connects to a remote SqlServer database through JDBC, and runs the “insert into …” SQL statement to write data into SqlServer. Data is submitted and written into the database in batch within SqlServer. You must configure the data source before configuring the SqlServer Writer plug‑in. For more information, see Configure the SqlServer Data Source.

SqlServer Writer is designed for ETL developers to import data from data warehouses to SqlServer. It can also be used as a data migration tool by DBA and other users.

SqlServer Writer acquires the protocol data (insert into...) generated by Reader by means of the Data Integration framework.If the primary key conflicts with the unique index, data cannot be written into the conflicting lines. To improve performance, we use PreparedStatement + Batch and configure rewriteBatchedStatements=trueto buffer data to the thread context buffer. A write request is submitted only when the amount of data in the buffer reaches the threshold.

Note:

  • Data can only be written into a target table that resides in a master database.

  • The entire task requires at least the “insert into…” permission. The requirement of the other permissions depend on the statements that you specify in preSql and postSql when you configure the task.

SqlServer Writer supports most data types in SqlServer. Check whether your data type is supported before using it.

SqlServer Writer converts SqlServer data types as follows:

Category SqlServer data type
Integer bigint, int, smallint, tinyint
Floating point float, decimal, real, numeric
String char, nchar, ntext, nvarchar, text, varchar, nvarchar(MAX), varchar(MAX)
Date and time date, datetime, time
Boolean bit
Binary binary, varbinary, varbinary(MAX), timestamp

Parameter description

  • datasource

    • Description: Data source name. It must be identical to the data source name added. Adding data source is supported in script mode.

    • Required: Yes

    • Default value: None

  • table

    • Description: The table selected for synchronization.

    • Required: Yes

    • Default value: None

  • column

    • Description: The fields of the target table into which data is required to be written. These fields are separated by commas. For example: “column”: [“id”, “name”, “age”]. Use * if it is required to write data into all columns in sequence. For example: “column”: [“*”]

    • Required: Yes

    • Default value: None

  • preSql

    • Description: The SQL statement that is run before the data synchronization task is run. Currently, you can run only one SQL statement in wizard mode, and more than one SQL statement in script mode. For example, clear old data.

    • Required: No

    • Default value: None

  • postSql

    • Description: The SQL statement that is run after the data synchronization task is run. Currently, you can run only one SQL statement in wizard mode, and more than one SQL statement in script mode. For example, add a timestamp.

    • Required: No

    • Default value: None

  • writeMode

    • Description: Select an import mode. “insert” is supported.

      • insert: If the primary key conflicts with the unique index, Data Integration deems the data as dirty data, but the original data is retained.
    • Required: No

    • Default value: insert

  • batchSize

    • Description: The quantity of records submitted in one operation. Setting this parameter can greatly reduce the interactions between Data Integration and SqlServer, and increase the overall throughout. However, an excessively large value may cause the running process of CDP to become out of memory (OOM).

    • Required: No

    • Default value: 1024

Development in wizard mode

TargetsqlServer

  • Data source: datasource in the preceding parameter description. Select the SqlServer data source.

  • Table: table in the preceding parameter description. Select the table to be synchronized.

  • Prepared Statement before Import: preSql in the preceding parameter description, namely, the SQL statement that is run before the data synchronization task is run.

  • Prepared statement after import: postSql in the preceding parameter description, namely, the SQL statement that is run after the data synchronization task is run.

  • Primary Key Conflict: writeMode in the preceding parameter description. You can select the expected import mode.

  • Field mapping: column in the preceding parameter description.

Development in script mode

Configure the data synchronization job to write data to SqlServer:

  1. {
  2. "type": "job",
  3. "version": "1.0",
  4. "configuration": {
  5. "reader": {},
  6. "writer": {
  7. "plugin": "sqlserver",
  8. "parameter": {
  9. "datasource": "datasourceName",
  10. "table": "table",
  11. "column": [
  12. "id", "name", "age"
  13. ],
  14. "preSql": [
  15. "delete from XXX;"
  16. ]
  17. }
  18. }
  19. }
  20. }
Thank you! We've received your feedback.