DataWorks:Offline synchronization FAQ

• If the connectivity test passed previously, run it again to confirm that the resource group and the database are still connected. Ensure that no changes have been made to the database.

• Check whether the resource group used for the connectivity test is the same as the one used for task execution.

  To view the resource group on which the task is running:

  • If the task runs on the default resource group, the log contains the following information: running in Pipeline[basecommon_ group_xxxxxxxxx]

  • If the task runs on an exclusive resource group for Data Integration, the log contains the following information: running in Pipeline[basecommon_S_res_group_xxx]

  • If the task runs on a Serverless resource group, the log contains the following information: running in Pipeline[basecommon_Serverless_res_group_xxx]

• If a scheduled task fails intermittently in the early morning but succeeds on a rerun, check the database load at the time of the error.

An offline sync task may fail intermittently if the whitelist configuration is incomplete. Check whether the database whitelist is fully configured.

When you use an exclusive resource group for Data Integration:

• If you added the elastic network interface (ENI) IP addresses of the exclusive resource group for Data Integration to the data source's whitelist, you must update the whitelist after you scale out the resource group. Add the ENI IP addresses of the scaled-out resource group to the data source's whitelist.

• To avoid updating the whitelist after scaling out the resource group, you can add the vSwitch CIDR block of the exclusive resource group for Data Integration to the database whitelist. For more information, see Add a whitelist.

When you use a Serverless resource group: For more information, see Add a whitelist to check the whitelist configuration of the resource group and ensure that the network settings are correct.

If the whitelist is configured correctly, check whether the database load is too high. A high load can cause the connection to be interrupted.

• Possible cause:

  The concurrency level is set too high, which results in insufficient resources.

• Solution:

  Reduce the concurrency of the offline sync task.

  • When configuring an offline sync task in the codeless UI, reduce the value of Maximum Concurrency in the channel control settings. For more information, see Configure a sync node in the codeless UI.

  • When configuring an offline sync task in the code editor, reduce the value of the concurrent parameter in the channel control settings. For more information, see Configure a sync node in the code editor.

To resolve this error, perform the following steps:

1. If the plugin configuration supports parameters such as `batchSize` or `maxFileSize`, reduce their values.

   To check which plugins support these parameters, go to Supported data sources and reader and writer plugins and click the relevant plugin to view its parameter details.

2. Reduce the concurrency.

   • When you configure an offline sync task in the codeless UI, reduce the value of Maximum Concurrency in the channel control settings. For more information, see Configure a sync node in the codeless UI.

   • If you configure an offline sync task in the code editor, reduce the value of the concurrent parameter in the channel control settings. For more information, see Configure a sync node in the code editor.

3. For file synchronization, such as synchronizing files from OSS, reduce the number of files to be read.

4. In the Running Resources section of the task configuration, increase the Resource Consumption (CU) value to an appropriate level to avoid affecting other running tasks.

• Error message: Error updating database. Cause: com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException: Duplicate entry 'cfc68cd0048101467588e97e83ffd7a8-0' for key 'uk_uk_op'.

• Possible cause: Data Integration does not allow different instances of the same sync task node to run concurrently. This means multiple sync tasks that have the same JSON configuration cannot run at the same time. For example, a sync task is scheduled to run every 5 minutes and experiences an upstream delay. At 00:05, both the instance for 00:00 and the instance for 00:05 are triggered. This can cause one instance to fail. Conflicts can also occur if a data backfill or rerun operation is performed while a task instance is running.

• Solution: Stagger the running times of the instances. For tasks that run every hour or minute, you can set a self-dependency. This ensures that the instance for the current cycle starts only after the instance for the previous cycle is complete. For the previous version of Data Development, see Self-dependency. For the new version of Data Development, see Select a dependency type (cross-cycle dependency).

• Error message: A data sync task fails with the error MongoDBReader$Task - operation exceeded time limitcom.mongodb.MongoExecutionTimeoutException: operation exceeded time limit.

• Possible cause: The amount of data being pulled is too large.

• Solution:

  • Increase the concurrency.

  • Decrease the BatchSize.

  • In the Reader parameter settings, add the cursorTimeoutInMs configuration. Try setting a larger value, such as 3,600,000 ms.

• Read error

  • Symptom:

    When data is read, the following error occurs: Communications link failure The last packet successfully received from the server was 7,200,100 milliseconds ago. The last packet sent successfully to the server was 7,200,100 milliseconds ago. - com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure

  • Possible cause:

    The database is executing the SQL query slowly, which causes a MySQL read timeout.

  • Solution:

    • Check whether a where filter condition is set and ensure that an index has been added to the filtered field.

    • Check whether the source data table contains too much data. If it does, split the task into multiple smaller tasks.

    • Find the blocking SQL statement in the logs and consult the database administrator (DBA) to resolve the issue.

• Write error

  • Symptom:

    When data is written, the following error occurs: Caused by: java.util.concurrent.ExecutionException: ERR-CODE: [TDDL-4614][ERR_EXECUTE_ON_MYSQL] Error occurs when execute on GROUP 'xxx' ATOM 'dockerxxxxx_xxxx_trace_shard_xxxx': Communications link failure The last packet successfully received from the server was 12,672 milliseconds ago. The last packet sent successfully to the server was 12,013 milliseconds ago. More...

  • Possible cause:

    A slow query is causing a socket timeout. The default socket timeout for TDDL connections is 12 seconds. If an SQL statement takes more than 12 seconds to execute on the MySQL side without returning a result, a 4614 error is reported. This error may occur intermittently when the data volume is large or the server is busy.

  • Solution:

    • Rerun the sync task after the database stabilizes.

    • Contact the database administrator to adjust the timeout period.

Possible cause 2: The task is waiting for Data Integration execution resources

Solution 2: If the log shows a prolonged `WAIT` status, the exclusive resource group for Data Integration that is used by the task does not have enough available concurrent resources to run the task. For more information about the cause and solution, see Why does a Data Integration task always show the "wait" status?.

• Scenario example

  The executed SQL statement is as follows.

  SELECT bid,inviter,uid,createTime FROM `relatives` WHERE createTime>='2016-10-2300:00:00' AND reateTime<'2016-10-24 00:00:00';

  Execution starts at 2016-10-25 11:01:24.875 and results start returning at 2016-10-25 11:11:05.489. The sync program waits for the database to return the SQL query results. As a result, MaxCompute must wait a long time before it can execute.

• Cause analysis

  When querying with the `WHERE` clause, the createTime column has no index, which leads to a full table scan.

• Solution

  Use indexed columns in the where clause to improve performance. You can also add indexes to the required columns.

For the previous version of Data Development:

You can modify the resource group for debugging on the offline sync task details page in DataStudio. You can also modify the Data Integration execution resource group for task scheduling in Operation Center. For more information, see Switch a Data Integration resource group.

For the new version of Data Development:

You can modify the resource group used for debugging Data Integration tasks in DataStudio. You can also modify the execution resource group for scheduled Data Integration tasks in Operation Center. For more information, see Resource group O&M.

Definition of dirty data: A single data record that causes an exception when it is written to the target data source is considered dirty data. Any data that fails to be written is classified as dirty data.

The number of dirty data records is accumulated during task execution. If this number exceeds the specified dirty data limit, the task is immediately aborted.

• Data retention: Data that was successfully written to the destination before the task was aborted is retained. No rollback operation is performed.

• Zero-tolerance policy: If the dirty data limit is set to 0, the system adopts a zero-tolerance policy. This means the task fails and stops immediately after it detects the first dirty data record.

• Symptom:

  If the data includes emojis, a dirty data error may be reported during synchronization: [13350975-0-0-writer] ERROR StdoutPluginCollector - Dirty data {"exception":"Incorrect string value: '\\xF0\\x9F\\x98\\x82\\xE8\\xA2...' for column 'introduction' at row 1","record":[{"byteSize":8,"index":0,"rawData":9642,"type":"LONG"}],"type":"writer"} .

• Possible cause:

  • The database encoding is not set to `utf8mb4`, which causes an error when synchronizing emojis.

  • The source data itself is garbled.

  • The database and the client use different character sets.

  • The browser uses a different character set, which causes a preview failure or garbled text.

• Solution:

  Choose the appropriate solution based on the cause of the garbled text:

  • If your raw data is garbled, process the raw data before you run the sync task.

  • If the database and client use inconsistent character sets, unify the character sets first.

  • If the browser uses a character set that is inconsistent with the database or client character set, unify the character sets first, and then preview the data.

  You can try the following operations:

  1. For a data source added using the Java Database Connectivity (JDBC) format, modify the connection string to use `utf8mb4`: jdbc:mysql://xxx.x.x.x:3306/database?com.mysql.jdbc.faultInjection.serverCharsetIndex=45.

  2. For a data source added using an instance ID, append the parameter after the database name: database?com.mysql.jdbc.faultInjection.serverCharsetIndex=45.

  3. Modify the relevant database encoding format to `utf8mb4`. For example, you can modify the RDS database encoding format in the RDS console.

     Note

     Command to set the RDS data source encoding format: set names utf8mb4. Command to view the RDS database encoding format: show variables like 'char%'.

When DataWorks creates a target table, it retains only the column names, data types, and comments from the source table. It does not retain the source table's default values or constraints, such as `NOT NULL` constraints and indexes.

No. Offline integration tasks do not support using a composite primary key as a shard key.

If you encounter data quality issues after data synchronization is complete, see Troubleshoot data quality issues in offline synchronization for detailed troubleshooting.

Q: How do I handle the "Task have SSRF attacks" error?

Cause: To ensure cloud security, DataWorks prohibits tasks from directly accessing internal network addresses in the cloud environment using public IP addresses. This security measure is triggered when a URL that points to an internal IP address or a VPC domain name is entered in a plugin configuration, such as HTTP Reader.

Solution:

• Do not use internal IP addresses such as 10.x.x.x or 192.168.x.x in the configuration.

• To securely access internal data sources, purchase a Serverless resource group (recommended) or an exclusive resource group for Data Integration and configure VPC network connectivity.

For tasks that run on internal data sources, stop using public resource groups. Instead, switch to a secure and reliable Serverless resource group (recommended) or an exclusive resource group for Data Integration.

After you switch the sync task to the code editor, add the following configuration item in the setting section of the task configuration page:

"common": {
  "column": {
    "dateFormat": "yyyyMMdd",
    "datetimeFormatInNanos": "yyyyMMdd HH:mm:ss.SSS"
  }
}

Where:

• `dateFormat` specifies the date format to use when a source `DATE` type (without time) is converted to text.

• `datetimeFormatInNanos` specifies the date format to use when a source `DATETIME` or `TIMESTAMP` type (with time) is converted to text, with precision up to the millisecond.

1. You can enter constants. The values must be enclosed in single quotation marks, such as 'abc' or '123'.

2. You can use them with scheduling parameters, such as '${bizdate}'. For more information about how to use scheduling parameters, see Supported formats of scheduling parameters.

3. You can enter the names of the partition columns that you want to synchronize, such as `pt`.

4. If the value that you enter cannot be parsed, the type is displayed as 'Custom'.

5. You cannot configure ODPS functions.

6. If a manually added column is displayed as custom, such as a MaxCompute partition column or a column that is not previewed in LogHub data, the task execution is not affected.

In the field mapping list, under Source table fields, click Add Row or Add Field. Enter the partition column name, such as pt, and configure its mapping to the target table field.

By configuring MaxCompute Writer, you can perform operations that MaxCompute does not support, such as column filtering, reordering, and null padding. For example, if the list of fields to be imported includes all fields, you can configure it as "column": ["*"].

If a MaxCompute table has three fields, `a`, `b`, and `c`, and you want to synchronize only fields `c` and `b`, you can configure the columns as "column": ["c","b"]. This means the first and second columns from the reader are imported into the `c` and `b` fields of the MaxCompute table, and the `a` field in the newly inserted row of the MaxCompute table is set to null.

To ensure the reliability of written data and avoid data quality issues caused by the loss of extra column data, MaxCompute Writer reports an error if it detects extra columns. For example, if a MaxCompute table has fields `a`, `b`, and `c`, and MaxCompute Writer attempts to write more than three columns, an error is reported.

MaxCompute Writer supports writing data only to the last-level partition. It does not support features such as partition routing based on a specific field. For example, if a table has three levels of partitions, the partition configuration must explicitly specify writing to a third-level partition. For example, to write data to the third-level partition of a table, you can configure it as pt=20150101, type=1, biz=2, but not as pt=20150101, type=1 or pt=20150101.

MaxCompute Writer ensures write idempotence when you configure "truncate": true. This means that if a write operation fails and the task is run again, MaxCompute Writer clears the previous data and imports the new data to ensure data consistency after each rerun. If the task is interrupted by other exceptions during execution, data atomicity cannot be guaranteed. The data is not rolled back or automatically rerun. You must use the idempotence feature and rerun the task to ensure data integrity.

• Error message:

  Code:DATAX_R_ODPS_005:Failed to read ODPS data, Solution:[Please contact the ODPS administrator]. RequestId=202012091137444331f60b08cda1d9, ErrorCode=StatusConflict, ErrorMessage=The download session is expired.

• Possible cause:

  Offline synchronization uses the MaxCompute Tunnel command to upload and download data. The lifecycle of a Tunnel session on the server is 24 hours. Therefore, if an offline sync task runs for more than 24 hours, the task fails and stops. For more information about Tunnel, see Instructions.

• Solution:

  Increase the concurrency of the offline sync task and plan the amount of data to be synchronized to ensure that the task can be completed within 24 hours.

• Error message:

  Code:[OdpsWriter-09], Description:[Failed to write data to the ODPS destination table.]. - ODPS destination table write block:0 failed, uploadId=[202012081517026537dc0b0160354b]. Please contact the ODPS administrator. - java.io.IOException: Error writing request body to server.

• Possible cause:

  • Possible cause 1: Data type exception. The source data does not conform to the ODPS data type specification. For example, you are trying to write the value `4.2223` to a field of the ODPS `decimal(18,10)` data type.

  • Possible cause 2: ODPS block or communication exception.

• Solution:

  Convert the data to a data type that conforms to the data type specification.

You can configure this by following the instructions in Synchronize sharded tables.

When you add a data source using a connection string, modify the JDBC format to: jdbc:mysql://xxx.x.x.x:3306/database?com.mysql.jdbc.faultInjection.serverCharsetIndex=45. For more information, see Configure a MySQL data source.

• Cause:

  • `net_read_timeout`: DataX splits the data from RDS for MySQL into several equal data retrieval SQL statements (`SELECT` statements) based on `SplitPk`. When the execution time of an SQL statement exceeds the maximum allowed running time on the RDS side, this error occurs.

  • `net_write_timeout`: The timeout period for waiting to send a block to the client is too short.

• Solution:

  Add the `net_write_timeout` or `net_read_timeout` parameter to the data source URL connection and set a larger value, or adjust this parameter in the RDS console.

• Suggestion:

  If the task can be rerun, set the task to automatically rerun on error.

For example: jdbc:mysql://192.168.1.1:3306/lizi?useUnicode=true&characterEncoding=UTF8&net_write_timeout=72000

Cause:

The default value for the wait_timeout parameter in MySQL is 8 hours. If data is still being fetched when this time is reached, the sync task is interrupted.

Solution:

Modify the MySQL configuration file my.cnf (or my.ini on Windows). Under the MySQL module, add the following parameters (in seconds): wait_timeout=2592000 interactive_timeout=2592000. Then, restart and log on to MySQL and run the following statement to check if the setting was successful: show variables like '%wait_time%'.

Normal CPU usage but high memory usage may cause the connection to be disconnected.

If you confirm that the task can be automatically rerun, set the task to Rerun upon Error. For more information, see Time property configuration.

• Scenario: When you synchronize PostgreSQL data using an offline sync tool, the following error occurs: org.postgresql.util.PSQLException: FATAL: terminating connection due to conflict with recovery

• Possible cause: This occurs because pulling data from the database takes a long time. Increase the values of the max_standby_archive_delay and max_standby_streaming_delay parameters. For more information, see Standby Server Events.

If connecting to Amazon RDS returns Host is blocked, you need to disable the Amazon load balancer health check. After you disable it, the block issue is no longer reported.

An error occurs when you add a MongoDB data source with the root user because you must use a username that is created in the database of the table you need to synchronize. You cannot use the root user.

For example, to import the `name` table, and the `name` table is in the `test` database, the database name here must be `test`, and you must use the username of a user created in the `test` database.

You can use an assignment node to first process the date type time into a timestamp and use this value as an input parameter for MongoDB data synchronization. For more information, see How to perform incremental synchronization on a timestamp type field in MongoDB?

You need to set the time zone in the MongoDB Reader configuration. For more information, see MongoDB Reader.

You can restart the task after a period of time with the query condition unchanged. This means delaying the execution time of the sync task without changing the configuration.

When reading data, the Column.name configured by the user is case-sensitive. If it is configured incorrectly, the read data is null. For example:

• MongoDB source data:

  {
      "MY_NAME": "zhangsan"
  }

• Sync task's Column configuration:

  {
      "column":
      [
          {
              "name": "my_name"
          }
      ]
  }

Because the case in the sync task's configuration does not match the source data, a data reading exception occurs.

The timeout configuration parameter is cursorTimeoutInMs, with a default value of 600,000 ms (10 minutes). This parameter represents the total time the MongoDB server takes to execute a query, not including data transmission time. If the amount of data read in a full pull is large, it may cause the error: MongoDBReader$Task - operation exceeded time limitcom.mongodb.MongoExecutionTimeoutException: operation exceeded time limit.

Currently, DataWorks sync tasks do not support reading data from a secondary node. If you configure reading from a secondary node, the error no master is reported.

• Cause analysis:

  This error is caused by a cursor timeout.

• Solution:

  Increase the value of the cursorTimeoutInMs parameter.

You need to increase the task concurrency and the `BatchSize` for reading.

• Possible cause:

  When a sync task runs, it uses the splitVector command for task sharding by default. Some MongoDB versions do not support the splitVector command, which leads to the no such cmd splitVector error.

• Solution:

  1. Go to the sync task configuration page and click the Convert to script button. Change the task to the code editor.

  2. In the MongoDB parameter configuration, add the parameter

     "useSplitVector" : false

     to avoid using splitVector.

• Symptom:

  This issue may occur in a sync task, such as in the codeless UI, if Write Mode (Overwrite) is set to Yes and a field other than _id is configured as the Business Primary Key.

• Possible cause:

  The data contains records whose _id does not match the configured Business Primary Key (such as my_id in the example configuration above).

• Solution:

  • Solution 1: Modify the offline sync task to ensure that the configured Business Primary Key is consistent with the _id.

  • Solution 2: Use _id as the business primary key for data synchronization.

• Cause:

  When Redis uses hash mode for storage, the hash's attribute and value must appear in pairs. For example: odpsReader: "column":[ "id", "name", "age", "address" ]. In the destination, RedisWriter is configured with `"keyIndexes":[ 0, 1]`. In Redis, `id` and `name` are used as the key, `age` as the attribute, and `address` as the value, stored in a hash type. If the ODPS source is configured with only two columns, you cannot use hash mode to store the Redis cache, and this exception is reported.

• Solution:

  If you want to use only two columns, configure Redis to store the information in String mode. If you must use hash mode, configure at least three columns in the source.

• Symptom:

  When you configure an offline sync task to read data from file storage such as OSS or FTP, if the file is in CSV format and uses multiple characters as a column delimiter (for example, |,, ##, or ;;), the task may fail with a "dirty data" error. In the task's run log, you will see a similar IndexOutOfBoundsException (array-index out of bounds) error, and dirty data is generated.

• Cause analysis:

  DataWorks' built-in csv reader ("fileFormat": "csv") has limitations when it handles delimiters that are composed of multiple characters, which leads to inaccurate column splitting of data rows.

• Solution:

  • Codeless UI: Switch the text type to text and explicitly specify your multi-character delimiter.

  • Code editor: Change "fileFormat": "csv" to "fileFormat": "text" and correctly set the delimiter: "fieldDelimiter":"<multi-delimiter>", "fieldDelimiterOrigin":"<multi-delimiter>".

Offline synchronization itself does not limit the number of files that the OSS reader plugin can read. The main limitation on file reading comes from the resource consumption (CU) of the task itself. Reading too many files at once can easily lead to an out-of-memory error. Therefore, we recommend that you do not set the object parameter to `*` to prevent the OutOfMemoryError: Java heap space error.

OSS Writer uses filenames to simulate a directory structure. OSS has the following restrictions on object names: if you use `"object": "datax"`, the written object starts with `datax`, and a random string is appended as a suffix. The number of files is determined by the actual number of sharded tasks.

If you do not want the random UUID suffix, set `"writeSingleObject" : "true"`. For more information, see the `writeSingleObject` description in the OSS data source documentation.

• Cause:

  The AccessKey account configured for the data source does not have the required permissions on the bucket.

• Solution:

  Grant the read permission on the bucket to the AccessKey account that is configured for the OSS data source.

• Cause analysis:

  The mapred.task.timeout parameter may be set to a value that is too short, which causes Hadoop to terminate the task and clean up the temporary directory. This results in the temporary data not being found.

• Solution:

  In the data source settings for an offline sync task, if you select Read Data Based On Hive JDBC (supports Conditional Filtering) for the Read Hive Method, you can set the mapred.task.timeout parameter in the Session Configuration field. For example: mapred.task.timeout=600000.

• Error message:

  ERROR JobContainer - Exception when job runcom.alibaba.datax.common.exception.DataXException: Code:[DatahubWriter-04], Description:[Write data failed.]. - com.aliyun.datahub.exception.DatahubServiceException: Record count 12498 exceed max limit 10000 (Status Code: 413; Error Code: TooLargePayload; Request ID: 20201201004200a945df0bf8e11a42)

• Possible cause:

  The error is caused because the amount of data submitted to DataHub by DataX in a single batch exceeds DataHub's limit. The configuration parameters that affect the amount of data submitted to DataHub are mainly:

  • maxCommitSize: The maximum size of the data buffer in megabytes (MB) before data is submitted to the destination in a batch. The default value is 1 MB (1,048,576 bytes).

  • batchSize: The number of data records that are accumulated in the DataX-On-Flume buffer. When the number of accumulated records reaches the specified `batchSize`, the records are submitted to the destination as a batch.

• Solution:

  Reduce the maxCommitSize and batchSize parameters.

This plugin is case-sensitive for field names. Check the column configuration of the LogHub reader.

Data Integration uses the time the data entered LogHub. In the LogHub console, check whether the `receive_time` metadata field is within the time range configured for the task.

If this occurs, manually edit the column in the interface.

The logic is the same as the API write method. Data in the same row and column is overwritten, while other data remains unchanged.

You can use a curl command to obtain the ES index mapping, and then extract all fields from the mapping.

• Query Shell command:

  //es7
  curl -u username:password --request GET 'http://esxxx.elasticsearch.aliyuncs.com:9200/indexname/_mapping'
  //es6
  curl -u username:password --request GET 'http://esxxx.elasticsearch.aliyuncs.com:9200/indexname/typename/_mapping'

• Obtain fields from the result:

  {
      "indexname": {
          "mappings": {
              "typename": {
                  "properties": {
                      "field1": {
                          "type": "text"
                      },
                      "field2": {
                          "type": "long"
                      },
                      "field3": {
                          "type": "double"
                      }
                  }
              }
          }
      }
  }

  In the returned result, the `properties` section contains all the fields and their property definitions for the index. The index above contains three fields: `field1`, `field2`, and `field3`.

You can add date scheduling variables to the index configuration. This allows the index string to be calculated based on different dates, which enables automatic changes to the Elasticsearch Reader's index name. The configuration process includes three steps: defining date variables, configuring the index variable, and publishing and executing the task.

1. Define date variables: In the sync task's scheduling configuration, select Add Parameter to define date variables. In the example below, `var1` is configured to represent the task execution time (current day), and `var2` represents the task's data timestamp (previous day).

2. Configure the index variable: Switch the task to the code editor and configure the Elasticsearch Reader's index. The configuration format is: `${variable_name}`, as shown in the figure below.

3. Publish and execute the task: After validation, submit and publish the task to the Operation Center, and run it using a recurring schedule or data backfill.

   1. Click the Run With Parameters button to run the task for validation. This action replaces the scheduling system parameters in the task configuration. After the task is complete, check the logs to verify that the synchronized index is as expected.

      Note

      When you run with parameters, directly enter the parameter values for testing.

      

   2. If the validation is successful, you can click Save and Submit to add the sync task to the production environment.

      For a standard project, click the Publish button to open the publishing center and publish the sync task to the production environment.

4. Result: The following figure shows the configuration and the actual index result from the run.

   Script index configuration: "index": "esstress_1_${var1}_${var2}".

   Running index replaced as: esstress_1_20230106_20230105.

   

To synchronize object field properties, you must use the code editor. In the code editor, configure `multi` as follows, and configure the column using the `property.sub-property` format.

"multi":{
   "multi":true 
 }

You can refer to the following example for configuration:

#Example:
##Data in ES
"hits": [
    {
        "_index": "mutiltest_1",
        "_type": "_doc",
        "_id": "7XAOOoMB4GR_1Dmrrust",
        "_score": 1.0,
        "_source": {
            "level1": {
                "level2": [
                    {
                        "level3": "testlevel3_1"
                    },
                    {
                        "level3": "testlevel3_2"
                    }
                ]
            }
        }
    }
]
##Reader configuration
"parameter": {
  "column": [
      "level1",
      "level1.level2",
      "level1.level2[0]"
  ],
  "multi":{
        "multi":true
    }
}
##Writer result: 1 row with 3 columns. The column order is the same as the reader configuration.
COLUMN              VALUE
level1:             {"level2":[{"level3":"testlevel3_1"},{"level3":"testlevel3_2"}]}
level1.level2:      [{"level3":"testlevel3_1"},{"level3":"testlevel3_2"}]
level1.level2[0]:   {"level3":"testlevel3_1"}

1. The extra double quotes around the characters are a display issue in the Kibana tool. The actual data does not have these surrounding double quotes. You can use a curl command or Postman to view the actual data. The curl command to obtain the data is as follows:

   //es7
   curl -u username:password --request GET 'http://esxxx.elasticsearch.aliyuncs.com:9200/indexname/_mapping'
   //es6
   curl -u username:password --request GET 'http://esxxx.elasticsearch.aliyuncs.com:9200/indexname/typename/_mapping'

   

2. You can configure the ES write field type as `nested` to synchronize JSON-type string data from ODPS to ES and store it in nested format. The following example synchronizes the `name` field to ES in nested format.

   • Sync configuration: Configure the type of `name` as nested.

   • Sync result: `name` is a nested object type.

There are two ways to configure writing to ES as an array type. You can choose the appropriate synchronization method based on the source data format.

• Write to ES as an array type by parsing the source data in JSON format. For example, if the source data is `"[1,2,3,4,5]"`, configure json_array=true to parse the source data and write it to the ES field as an array. Set ColumnList to json_array=true.

  • Codeless UI configuration:

  • Code editor configuration:

    "column":[
      {
        "name":"docs",
        "type":"keyword",
        "json_array":true
      }
    ]

• Write to ES as an array type by parsing the source data with a delimiter. For example, if the source data is `"1,2,3,4,5"`, configure the delimiter `splitter=","` to parse and write it to the ES field as an array.

  • Limitations:

    • Only one type of delimiter is supported per task. The splitter is globally unique. You cannot configure different delimiters for multiple Array fields. For example, if the source columns are col1="1,2,3,4,5" , col2="6-7-8-9-10", you cannot configure a separate splitter for each column.

    • The splitter can be a regular expression. For example, if the source column value is `"6-,-7-,-8+,*9-,-10"`, you can configure `splitter:".,."`. This is supported in the codeless UI.

  • Codeless UI configuration: splitter: Defaults to "-,-"

  • Code editor configuration:

    "parameter" : {
          "column": [
            {
              "name": "col1",
              "array": true,
              "type": "long"
            }
          ],
          "splitter":","
    }

• Cause analysis:

  HttpClient is pre-configured to first make a request without credentials each time a connection is established. After it receives the authorization requirement and determines the authentication method based on the response, it then makes a request with credentials. A connection needs to be established each time data is written to ES, so there is one request without credentials for each write. This causes each data write to be recorded in the audit log.

• Solution:

  You need to add the "preemptiveAuth":true configuration in the code editor.

There are two ways to configure date writing. You can choose as needed.

• Directly write the content of the field read by the reader to the ES Date field:

  • Configure `origin:true` to write the read content directly to ES.

  • Configure `"format"` to specify the format attribute for the field when you create the mapping using ES write.

    "parameter" : {
        "column": [
            {
                "name": "col_date",
                "type": "date",
                "format": "yyyy-MM-dd HH:mm:ss",
                "origin": true
            }
              ]
    }

• Time zone conversion: If you need Data Integration to perform time zone conversion, add the Timezone parameter.

  "parameter" : {
      "column": [
          {
              "name": "col_date",
              "type": "date",
              "format": "yyyy-MM-dd HH:mm:ss",
              "Timezone": "UTC"
          }
            ]
  }

• If you have configured `type:version`, ES currently does not support specifying an external version.

      "column":[
                              {
                                  "name":"id",
                                  "type":"version"
                              },
    ]

• Solution:

  You need to remove the `"type":"version"` configuration. Elasticsearch Writer does not currently support specifying an external version.

• Cause analysis:

  Elasticsearch Reader cannot parse the date format of a date type field because the mapping for the corresponding date field in the ES data source does not have a format configured.

• Solution:

  • Configure the `dateFormat` parameter with the same format as the ES date field's format, separated by "||". The format must include all date type formats. For example:

    "parameter" : {
          "column": [
         			"dateCol1",
            	"dateCol2",
              "otherCol"
          ],
         "dateFormat" : "yyyy-MM-dd||yyyy-MM-dd HH:mm:ss",
    }

  • Set the mapping for all date fields in the ES database to a format.

• Cause analysis:

  Due to fastjson keyword restrictions, there may be keywords like `$ref` in the index or column.

• Solution:

  Elasticsearch Reader does not currently support synchronizing indexes with the keyword `$ref` in their fields. For more information, see Elasticsearch Reader.

• Cause analysis:

  ES's optimistic locking mechanism was triggered. The current version number should be xxx, but the update command passed a different version number, which caused a version conflict. While data was being updated, another process was deleting index data.

• Solution:

  1. Check and confirm whether a data deletion behavior occurred.

  2. Change the task synchronization method from Update to Index.

• Cause analysis:

  When you configure advanced properties such as `similarity` or `properties` for a Column field, `other_params` is required for the plugin to recognize them.

• Solution:

  Configure other_params in the Column, and add `similarity` within other_params, as shown below:

  {"name":"dim2_name",...,"other_params":{"similarity":"len_similarity"}}

• Cause analysis:

  Currently, offline synchronization to Elasticsearch does not support the dense_vector type. Only the following types are supported:

  ID,PARENT,ROUTING,VERSION,STRING,TEXT,KEYWORD,LONG,
  INTEGER,SHORT,BYTE,DOUBLE,FLOAT,DATE,BOOLEAN,BINARY,
  INTEGER_RANGE,FLOAT_RANGE,LONG_RANGE,DOUBLE_RANGE,DATE_RANGE,
  GEO_POINT,GEO_SHAPE,IP,IP_RANGE,COMPLETION,TOKEN_COUNT,OBJECT,NESTED;

• Solution:

  To handle types not supported by Elasticsearch Writer:

  • Do not use Elasticsearch Writer to create the index mapping. Use a customer-created mapping instead.

  • Change the corresponding type to `NESTED`.

  • Modify the configuration to: dynamic = true and cleanup=false.

• Cause:

  #Incorrect configuration
  "settings": {
    "index": {
      "number_of_shards": 1,
      "number_of_replicas": 0
    }
  }
  #Correct configuration
  "settings": {
    "number_of_shards": 1,
    "number_of_replicas": 0
  }

• Solution:

  Settings take effect only when an index is created. Index creation occurs in two cases: when the index does not exist, and when cleanup=true. When cleanup=true, the Settings configuration does not need to use `"index"`.

#Original mappings
{
  "name":"box_label_ret",
  "properties":{
    "box_id":{
      "type":"keyword"
    }
}
#After cleanup=true rebuild, it becomes
{
    "box_label_ret": {
      "properties": {
        "box_id": {
          "type": "text",
          "fields": {
            "keyword": {
              "type": "keyword",
              "ignore_above": 256
            }}}}
}

• Cause analysis:

  For nested types, Elasticsearch Writer uses only the top-level mappings, which allows ES to adapt the composite types below. Changing the property type to `text` and adding `fields:keyword` is an adaptive behavior of ES and does not affect its use. If you are concerned about this mapping format, see Data Integration sync task capabilities.

• Solution:

  Before synchronization, create the expected ES index mappings, then set the ES sync task's cleanup parameter to false and execute the sync task.

Kafka Reader reads data in batches. If a batch of data contains records that are beyond the endDateTime, the synchronization stops, but this data that exceeds the endDateTime is also written to the destination data source.

• You can use the `skipExceedRecord` configuration item to specify whether to synchronize the excess data. For more information about how to use it, see Kafka Reader. [We recommend that you do not set this to not synchronize, as it may cause data loss.]

• You can configure Kafka's `max.poll.records` parameter to specify the amount of data to pull at one time. Combined with the concurrency, this can control the amount of potentially excess data. The amount of excess data is less than `max.poll.records` × concurrency.

• Cause analysis:

  A small amount of data or uneven data distribution causes some Kafka partitions to have no new data, or the new data has not reached the specified end offset for reading. Because the task's exit condition is that all partitions must reach the specified end offset, these "idle" partitions cannot meet the condition, which blocks the normal completion of the entire task.

• Solution:

  In this case, set the Synchronization End Policy to: Stop if no new data is read for 1 minute (in the code editor, set the stopWhenPollEmpty parameter to true and the stopWhenReachEndOffset parameter to true). This allows the task to exit immediately after reading the latest offset data from all partitions and avoids dry-runs. However, note that if records with timestamps earlier than the configured end offset are written to a partition after the task has finished, these records will not be consumed.

RestAPI Writer provides two write modes. When you synchronize multiple data records, you need to configure `dataMode` as `multiData`. For more information, see RestAPI Writer. You also need to add the parameter `dataPath:"data.list"` in the RestAPI Writer script.

1. The OTS Writer configuration must include the following two lines:

   "newVersion": "true",
   "enableAutoIncrement": "true",

2. The OTS Writer does not need to be configured with the name of the auto-increment primary key column.

3. The number of primaryKey entries + the number of column entries configured in OTS Writer must equal the number of columns in the upstream OTS Reader data.

Example: A data record has three tags: `[phone=Xiaomi, memory=8G, camera=Leica]`.

• Data exporting example (OTS Reader)

  • If you want to merge the above tags into a single column for export, configure as follows:

    "column": [
          {
            "name": "_tags",
          }
        ],

    DataWorks exports the tags as a single column of data, in the following format:

    ["phone=xiaomi","camera=LEICA","RAM=8G"]

  • If you want to export the phone and camera tags so that each tag is in a separate column, use the following configuration:

    "column": [
          {
            "name": "phone",
            "is_timeseries_tag":"true",
          },
          {
            "name": "camera",
            "is_timeseries_tag":"true",
          }
        ],

    The following two columns of data are obtained:

    xiaomi, LEICA

• Data import example (OTS Writer)

  Suppose the upstream data source (Reader) has two columns of data:

  • One column of data is: ["phone=xiaomi","camera=LEICA","RAM=8G"].

  • The other column of data is: 6499.

  If you want to add both of these columns to the tags, with the expected tag field format after writing as shown below: Then configure as follows:

  "column": [
        {
          "name": "_tags",
        },
        {
          "name": "price",
          "is_timeseries_tag":"true",
        },
      ],

  • The first column configuration imports ["phone=xiaomi","camera=LEICA","RAM=8G"] as a whole into the tag field.

  • The second column configuration imports price=6499 separately into the tag field.

If your table names follow a regular pattern, such as orders_20170310, orders_20170311, and orders_20170312, are distinguished by day, and have consistent table structures, you can combine scheduling parameters with code editor configuration to customize table names. This lets you automatically read the previous day's table data from the source database every morning.

In the code editor, change the source table name to a variable, for example, orders_${tablename}. Because the table names are distinguished by day and you need to read the previous day's data each day, in the task's parameter configuration, assign the variable as tablename=${yyyymmdd}.

Go to the sync task configuration page and modify the field mapping to update the changed fields in the task configuration. After that, you need to resubmit and execute the task for the changes to take effect.

When you configure an offline sync node, the Select Source area displays only the first 25 tables in the selected data source by default. If there are more tables, you can search by table name or use the code editor for development.

• Cause: The column contains a reserved word, or the column configuration contains a field that starts with a number.

• Solution: Switch the Data Integration sync task to the code editor and escape the special fields in the column configuration. For more information about how to configure a task in the code editor, see Configure a sync node in the code editor.

  • The escape character for MySQL is `keyword`.

  • The escape character for Oracle and PostgreSQL is "keyword".

  • The escape character for SQL Server is [keyword].

  MySQL example:

• Take a MySQL data source as an example:

  1. Run the following statement to create a new table named `aliyun`. create table aliyun (`table` int ,msg varchar(10));

  2. Run the following statement to create a view and give an alias to the `table` column. create view v_aliyun as select `table` as col1,msg as col2 from aliyun;

     Note

     • `table` is a MySQL keyword. The code generated during data synchronization causes an error. Therefore, you need to create a view to give an alias to the `table` column.

     • Do not use keywords as table column names.

  3. By running the above statement, you can assign an alias to the column that has a keyword. When you configure the sync task, select the `v_aliyun` view instead of the `aliyun` table.

This error may occur because the field mapping of the sync task is not configured correctly, or the plugin's column is not configured correctly.

1. Check whether field mapping is configured.

2. Check whether the plugin's column is configured correctly.

• Symptom:

  When you click Data Preview, the following message is displayed, indicating that a field exceeds the maximum byte size.

  

• Cause: To avoid an out-of-memory (OOM) error, the data source service checks the length of fields when it processes a data preview request. If a single column exceeds 1,000 bytes, the above message appears. This message does not affect the normal operation of the task. You can ignore this error and run the offline sync task directly.

  Note

  If the file exists and connectivity is normal, other reasons for data preview failure include the following:

  • The byte size of a single row in the file exceeds the limit of 10 MB. In this case, no data is displayed, similar to the message above.

  • The number of columns in a single row of the file exceeds the limit of 1,000. In this case, only the first 1,000 columns are displayed, and a message is shown at the 1001st column.

The time-to-live (TTL) is set on the table. There is no such option in the sync task configuration.

API synchronization does not support using source-side functions. You need to process the data using functions on the source side first, and then import the data.