Synchronize PolarDB-X 1.0 Data to Elasticsearch - Data Transmission Service

This topic describes how to use Data Transmission Service (DTS) to synchronize data from PolarDB-X 1.0 to Elasticsearch.

Prerequisites

The source PolarDB-X 1.0 instance must use RDS MySQL as its storage type. This includes custom RDS for MySQL instances and standalone RDS for MySQL instances. PolarDB for MySQL is not supported.
You have created a destination Elasticsearch instance. For more information, see Create an Alibaba Cloud Elasticsearch Instance.
The storage space of the destination Elasticsearch instance must be larger than that of the source PolarDB-X 1.0 instance.

Important Notes

Type	Description
Source database limits	The tables to be synchronized must have PRIMARY KEY or UNIQUE constraints. Tables that have only UNIQUE constraints do not support schema synchronization. We recommend that you use PRIMARY KEY constraints. The fields in the constraints must be unique. Otherwise, duplicate data may exist in the destination database. If you select tables as the objects to be synchronized and need to edit them, such as mapping column names, you can synchronize a maximum of 1,000 tables in a single data synchronization task. If the number of tables exceeds the limit, a request error is reported after you submit the task. In this case, we recommend that you split the tables into multiple tasks or configure a task to synchronize the entire database. Binary logs of RDS MySQL instances attached to PolarDB-X 1.0: By default, binary logging is enabled. For more information about how to check whether the binlog_row_image parameter is set to full, see View instance parameters. If the parameter is not set to full, an error is reported during the precheck and the data synchronization task cannot be started. If you perform only incremental data synchronization, the binary logs of the source database must be retained for at least 24 hours. If you perform both full data synchronization and incremental data synchronization, the binary logs of the source database must be retained for at least seven days. You can set the retention period to more than 24 hours after full data synchronization is complete. Otherwise, DTS may fail to obtain the binary logs, which causes the task to fail or even leads to data inconsistency or data loss. Issues that are caused by a binary log retention period shorter than the required period are not covered by the DTS Service-Level Agreement (SLA). Limits on operations on the source database: If you change the network type of the PolarDB-X 1.0 instance during data synchronization, you must also modify the network connection information of the data synchronization link. During data synchronization, do not perform operations such as scaling out or scaling in the source instance, synchronizing hot spot tables, changing shard keys, or making DDL changes. Otherwise, the data synchronization task fails or data inconsistency occurs. If you perform only full data synchronization, do not write new data to the source instance. Otherwise, data inconsistency occurs between the source and destination instances. To ensure real-time data consistency, we recommend that you select schema synchronization, full data synchronization, and incremental data synchronization. Do not run DDL operations that change database or table schemas during schema synchronization or full synchronization. Otherwise, the synchronization task fails. Note During full synchronization, DTS queries the source database. This creates metadata locks that may block DDL operations on the source database. The version of the source PolarDB-X 1.0 instance must be 5.2 or later.
Other limits	DTS does not support synchronizing INDEX, PARTITION, VIEW, PROCEDURE, FUNCTION, TRIGGER, or FOREIGN KEY constraints. To add columns to tables in the source database, first modify the corresponding mapping in the Elasticsearch instance. Then run the DDL statement in the source database. Finally, pause and restart the synchronization task. You cannot synchronize data to Elasticsearch indexes that contain parent-child relationships or Join field types. Doing so may cause task errors or query failures in the destination. DTS ensures the data consistency of incremental synchronization tasks based on the continuity of XA transactions in the source PolarDB-X 1.0 instance. If the continuity of XA transactions is disrupted, for example, when you modify the synchronization objects or in disaster recovery scenarios for the incremental data collection module, uncommitted XA transactions may be lost. Before starting synchronization, evaluate the performance of both the source and destination databases. Run synchronization during off-peak business hours. Otherwise, full synchronization may consume significant read and write resources on both databases and increase database load. DTS attempts to recover failed synchronization tasks within seven days. Before switching traffic to the destination instance, end or release the synchronization task. Or use the `revoke` command to revoke the write permission of the DTS account on the destination instance. This prevents automatic recovery from overwriting destination data with source data. Development and test specifications of Elasticsearch instances are not supported. If a task fails, DTS support staff will attempt to restore it within eight hours. During restoration, they may restart the task or adjust its parameters. Note Only DTS task parameters are modified—not database parameters. Parameters that may be adjusted include those listed in Modify instance parameters.
Other notes	DTS periodically updates the `dts_health_check`.`ha_health_check` table in the source database to advance the binary log offset.

Billing

Synchronization type	Pricing
Schema synchronization and full data synchronization	Free of charge.
Incremental data synchronization	Charged. For more information, see Billing overview.

SQL Operations Supported for Incremental Synchronization

Operation Type	SQL Operations
DML	INSERT, UPDATE, DELETE Note The UPDATE statement cannot be used to remove fields.

Database Account Permissions

Database	Required Permissions	How to Create Accounts and Grant Permissions
Source PolarDB-X 1.0 instance	Read permission on the synchronization objects.	Account Management
Destination Elasticsearch instance	The database account must have read and write permissions. The default account is elastic.

Data type mappings

Because source databases and Elasticsearch instances support different data types, data types cannot always be mapped directly. During initial schema synchronization, DTS maps data types based on the types that the destination Elasticsearch instance supports. For more information, see Data type mappings for initial schema synchronization.
Note
DTS does not set the mapping parameter in the dynamic during schema migration. The behavior of this parameter depends on your Elasticsearch instance settings. If your source data is in JSON format, ensure that the values for the same key have the same data type across all rows in a table. Otherwise, DTS may report synchronization errors. For more information, see dynamic.
The following table describes the mappings between Elasticsearch and relational databases.
Elasticsearch
Relational database
Index
Database
Type
Table
Document
Row
Field
Column
Mapping
Database schema

Procedure

Go to the data synchronization task list page in the destination region. You can do this in one of two ways.
DTS console
1. Log on to the DTS console.
2. In the navigation pane on the left, click Data Synchronization.
3. In the upper-left corner of the page, select the region where the synchronization instance is located.
DMS console
Note
The actual steps may vary depending on the mode and layout of the DMS console. For more information, see Simple mode console and Customize the layout and style of the DMS console.
1. Log on to the DMS console.
2. In the top menu bar, choose Data + AI > DTS (DTS) > Data Synchronization.
3. To the right of Data Synchronization Tasks, select the region of the synchronization instance.
Click Create Task to open the task configuration page.

Configure the source and destination databases.

Category	Configuration	Description
None	Task Name	DTS automatically generates a task name. We recommend that you specify a descriptive name for easy identification. The name does not need to be unique.
Source Database	Select Existing Connection	Select the registered database instance with DTS from the drop-down list. The database information below is automatically configured. Note In the DMS console, this configuration item is Select a DMS database instance. If you have not registered the database instance or do not need to use a registered instance, manually configure the database information below.
	Database Type	Select PolarDB-X 1.0.
	Access Method	Select Alibaba Cloud Instance.
	Instance Region	Select the region where the source PolarDB-X 1.0 instance resides.
	Replicate Data Across Alibaba Cloud Accounts	This example uses the same Alibaba Cloud account for both source and destination. Select No.
	Instance ID	Select the instance ID of the source PolarDB-X 1.0 instance.
	Database Account	Enter the database account for the source PolarDB-X 1.0 instance.
	Database Password	Enter the password for the specified database account.
Destination Database	Select Existing Connection	Select the registered database instance with DTS from the drop-down list. The database information below is automatically configured. Note In the DMS console, this configuration item is Select a DMS database instance. If you have not registered the database instance or do not need to use a registered instance, manually configure the database information below.
	Database Type	Select Elasticsearch.
	Access Method	Select Alibaba Cloud Instance.
	Instance Region	Select the region where the destination Elasticsearch instance resides.
	Type	Select Cluster Edition or Serverless as needed.
	Instance ID	Select the instance ID of the destination Elasticsearch instance.
	Database Account	Enter the account used to connect to the destination Elasticsearch instance. This is the username you entered when creating the Elasticsearch instance. The default account is elastic.
	Database Password	Enter the password for the specified database account.
	Encryption	Select HTTP or HTTPS as needed.

After completing the configuration, click Test Connectivity and Proceed at the bottom of the page.
Note
- Ensure that you add the CIDR blocks of the DTS servers (either automatically or manually) to the security settings of both the source and destination databases to allow access. For more information, see Add the IP address whitelist of DTS servers.
- If the source or destination is a self-managed database (i.e., the Access Method is not Alibaba Cloud Instance), you must also click Test Connectivity in the CIDR Blocks of DTS Servers dialog box.

Configure the task objects.

On the Configure Objects page, specify the objects to synchronize.

Configuration	Description
Synchronization Types	DTS always selects Incremental Data Synchronization. By default, you must also select Schema Synchronization and Full Data Synchronization. After the precheck, DTS initializes the destination cluster with the full data of the selected source objects, which serves as the baseline for subsequent incremental synchronization.
Processing Mode of Conflicting Tables	Precheck and Report Errors: Checks whether an index with the same name exists in the destination database. If no such index exists, the check passes. If a matching index exists, the precheck reports an error and the synchronization job does not start. Note If you cannot delete or rename the conflicting index in the destination database, you can set the name of the synchronization object in the destination instance to avoid naming conflicts. Ignore Errors and Proceed: Skips the check for indexes that have the same name in the destination database. Warning Selecting Ignore Errors and Proceed may cause data inconsistency and put your business at risk. For example: If the mapping structures match, records with the same primary key value as in the source database are kept in the destination database during initialization. During incremental synchronization, these records are overwritten. If the mapping structures do not match, initialization may fail, only some columns may synchronize, or synchronization may fail entirely.
Index Name	Select Table Name to create an index in the destination Elasticsearch instance that has the same name as the table. Select Database Name_Table Name to create an index in the destination Elasticsearch instance. The index name is generated by combining the database name, an underscore (_), and the table name. Note The index name mapping applies to all tables.
Capitalization of Object Names in Destination Instance	Configure the case-sensitivity policy for database, table, and column names in the destination instance. By default, the DTS default policy is selected. You can also choose to use the default policy of the source or destination database. For more information, see Case policy for destination object names.
Source Objects	In the Source Objects box, select the objects to synchronize, then click to move them to the Selected Objects box. Note We recommend that you select objects at the table level. If you select an entire database as a synchronization object, changes to add or delete tables in the source database are not synchronized to the destination database.
Selected Objects	To rename a single object in the destination instance, right-click the object in the Selected Objects box. For more information, see Map a single object name. To rename multiple objects in bulk, click Batch Edit in the upper-right corner of the Selected Objects box. For more information, see Map multiple object names in bulk. Note Only underscores (_) are allowed as special characters in index and type names. To filter data using a WHERE clause, right-click the table to synchronize in the Selected Objects box. A dialog box appears where you can set the filter condition. For more information, see Set Filter Conditions. Using the object name mapping feature may cause synchronization failures for other objects that depend on the mapped object.

Click Next: Advanced Settings.

Configuration	Description
Dedicated Cluster for Task Scheduling	By default, DTS uses a shared cluster for tasks, so you do not need to make a selection. For greater task stability, you can purchase a dedicated cluster to run the DTS synchronization task. For more information, see What is a DTS dedicated cluster?.
Retry Time for Failed Connections	If the connection to the source or destination database fails after the synchronization task starts, DTS reports an error and immediately begins to retry the connection. The default retry duration is 720 minutes. You can customize the retry time to a value from 10 to 1,440 minutes. We recommend a duration of 30 minutes or more. If the connection is restored within this period, the task resumes automatically. Otherwise, the task fails. Note If multiple DTS instances (e.g., Instance A and B) share a source or destination, DTS uses the shortest configured retry duration (e.g., 30 minutes for A, 60 for B, so 30 minutes is used) for all instances. DTS charges for task runtime during connection retries. Set a custom duration based on your business needs, or release the DTS instance promptly after you release the source/destination instances.
Retry Time for Other Issues	If a non-connection issue (e.g., a DDL or DML execution error) occurs, DTS reports an error and immediately retries the operation. The default retry duration is 10 minutes. You can also customize the retry time to a value from 1 to 1,440 minutes. We recommend a duration of 10 minutes or more. If the related operations succeed within the set retry time, the synchronization task automatically resumes. Otherwise, the task fails. Important The value of Retry Time for Other Issues must be less than that of Retry Time for Failed Connections.
Enable Throttling for Full Data Synchronization	During full data synchronization, DTS consumes read and write resources from the source and destination databases, which can increase their load. To mitigate pressure on the destination database, you can limit the migration rate by setting Queries per second (QPS) to the source database, RPS of Full Data Migration, and Data migration speed for full migration (MB/s). Note This parameter is available only if Synchronization Types is set to Full Data Synchronization. You can also adjust the rate of full data synchronization when the synchronization instance is running.
Enable Throttling for Incremental Data Synchronization	You can also limit the incremental synchronization rate to reduce pressure on the destination database by setting RPS of Incremental Data Synchronization and Data synchronization speed for incremental synchronization (MB/s).
Environment Tag	Select an environment tag to identify the instance.
Shard Configuration	Set the number of primary shards and replica shards for the index, based on the maximum shard configuration in the destination Elasticsearch instance.
String Index	How strings are indexed in the destination Elasticsearch instance. analyzed: Analyze the string before indexing. You must also select an analyzer. For more information about analyzers, see Analyzers. not analyzed: Index the raw value without performing analysis. no: You can choose not to index the string.
Time Zone	When DTS synchronizes time-type data (such as DATETIME or TIMESTAMP) to the destination Elasticsearch instance, select the time zone to use. Note If time-type data in the destination instance should not include time zones, set the document type (type) for that data in the destination instance beforehand.
DOCID	By default, DOCID is the table's primary key. If the table has no primary key, DOCID is the ID column auto-generated by the Elasticsearch instance.
Configure ETL	Choose whether to enable the extract, transform, and load (ETL) feature. For more information, see What is ETL? Valid values: Yes: Enables the ETL feature. Enter data processing statements in the code editor. For more information, see Configure ETL in a data migration or data synchronization task. No: Disables the ETL feature.
Monitoring and Alerting	Choose whether to set up alerts. If the synchronization fails or the latency exceeds the specified threshold, DTS sends a notification to the alert contacts. No: No alerts are configured. Yes: Configures alerts. You must also set the alert threshold and alert notifications. For more information, see Configure monitoring and alerting during task configuration.

After completing the preceding configurations, click Next: Configure Database and Table Fields at the bottom of the page to set the _routing strategy and _id value for tables to be synchronized in the destination Elasticsearch instance.

Type	Description
Set _routing	Configuring _routing routes documents to specific shards in the destination Elasticsearch instance. For more information, see _routing. Select Yes to define a custom column for routing. Select No to route using _id. Note If the destination Elasticsearch instance runs version 7.x, select No.
_routing Column	Select the column to use for routing. Note This parameter is required only when Set _routing is set to Yes.
Value of _id	Select the column to use as the document ID.

Save the task and perform a precheck.
- To view the parameters for configuring this instance via an API operation, hover over the Next: Save Task Settings and Precheck button and click Preview OpenAPI parameters in the tooltip.
- If you have finished viewing the API parameters, click Next: Save Task Settings and Precheck at the bottom of the page.
Note
- Before a synchronization task starts, DTS performs a precheck. You can start the task only if the precheck passes.
- If the precheck fails, click View Details next to the failed item, fix the issue as prompted, and then rerun the precheck.
- If the precheck generates warnings:
  For non-ignorable warning, click View Details next to the item, fix the issue as prompted, and run the precheck again.
  For ignorable warnings, you can bypass them by clicking Confirm Alert Details, then Ignore, and then OK. Finally, click Precheck Again to skip the warning and run the precheck again. Ignoring precheck warnings may lead to data inconsistencies and other business risks. Proceed with caution.

Purchase the instance.

When the Success Rate reaches 100%, click Next: Purchase Instance.

On the Purchase page, select the billing method and link specifications for the data synchronization instance. For more information, see the following table.

Category	Parameter	Description
New Instance Class	Billing Method	Subscription: You pay upfront for a specific duration. This is cost-effective for long-term, continuous tasks. Pay-as-you-go: You are billed hourly for actual usage. This is ideal for short-term or test tasks, as you can release the instance at any time to save costs.
	Resource Group Settings	The resource group to which the instance belongs. The default is default resource group. For more information, see What is Resource Management?.
	Instance Class	DTS offers synchronization specifications at different performance levels that affect the synchronization rate. Select a specification based on your business requirements. For more information, see Data synchronization link specifications.
	Subscription Duration	In subscription mode, select the duration and quantity of the instance. Monthly options range from 1 to 9 months. Yearly options include 1, 2, 3, or 5 years. Note This option appears only when the billing method is Subscription.

Read and select the checkbox for Data Transmission Service (Pay-as-you-go) Service Terms.
Click Buy and Start, and then click OK in the OK dialog box.
You can monitor the task progress on the data synchronization page.

Elasticsearch	Relational database
Index	Database
Type	Table
Document	Row
Field	Column
Mapping	Database schema