OpenSearch:Configure a PolarDB data source

Prerequisites

Before you begin, make sure you have:

• A PolarDB for MySQL cluster running version 5.6, 5.7, or 8.0

• The cluster in the same region as your OpenSearch application

• The cluster created under the same Alibaba Cloud account you use to log in to the OpenSearch console (OpenSearch cannot access clusters owned by other accounts)

• Binary logging enabled on the cluster (disabled by default — see Enable binary logging below)

Connect a PolarDB for MySQL data source

Configure a PolarDB for MySQL data source when you create a new application. To add a data source to an existing application, use offline changes.

1. In the Select Data Source panel, select PolarDB and click Connect to Database.

2. Fill in the connection parameters and click Connect. If the connection fails, OpenSearch displays an error:

   Parameter	Description
   Cluster ID	The ID of your PolarDB for MySQL cluster. Find it in the PolarDB console. The ID is case-sensitive. Example: pc-uf6c056ny9tiaj1l7
   Database Name	The name of the database to connect. Not case-sensitive.
   Username	The database account for reading table schemas and full data. Case-sensitive.
   Password	The password for the database account. Must not contain the percent sign (%).

   Error	Resolution
   The PolarDB for MySQL cluster does not reside in the same region as the OpenSearch application.	Verify the cluster ID and confirm both the cluster and application are in the same region. If the region matches but the error persists, submit a ticket.
   The connection to a PolarDB for MySQL data source failed.	Check that the cluster ID, database name, username, and password are all correct.
   The table does not exist in the current PolarDB for MySQL cluster.	Verify the table name and confirm the table exists in the cluster.
   The configurations of the PolarDB for MySQL cluster are invalid.	Go to the parameter configuration page for this cluster in the PolarDB console and correct the settings.

3. Select the table to sync and click OK.

   • Table names are case-sensitive.

   • Use an asterisk (*) as a wildcard to match multiple tables with the same name prefix. For example, table_* matches table_a, table_b, and similar tables. All matched tables must share the same schema.

4. Configure field mappings. OpenSearch automatically loads source fields from the table. Map them to destination fields in your application. For details on transforming field values during sync, see Data processing plug-ins.

5. Specify filter conditions to control which rows are synced, then click Submit to save the application schema. For filter syntax details, see Configure conditions to filter fields in ApsaraDB RDS and PolarDB data sources.

   Filter conditions are not supported for standard applications.

How data sync works

Understanding how OpenSearch processes each row helps you predict index behavior and avoid data inconsistencies.

Full data sync

OpenSearch pulls all rows from the primary database. For each row:

• If the row does not exist in the index, OpenSearch creates a new document.

• If the row already exists in the index, OpenSearch replaces the existing document.

• Rows that do not meet filter conditions are excluded from the index. Rows whose primary key values match existing documents in the destination table are also excluded.

After full data sync completes, OpenSearch switches to incremental sync using binary logs to apply subsequent changes in near real time.

Incremental sync

OpenSearch captures row-level changes via binary logs. If no incremental data is generated at the source for 15 days or more, data synchronization may fail. Recover by triggering a manual offline change. For details, see Perform offline changes.

Supported features

• Scheduled or on-demand full data sync: Pull all data in the source database or table manually, or on an automatic schedule.

• Multi-table row merging: Merge rows from multiple tables across one or more data sources into a single table in your application. Source tables must share the same schema and the same data processing plug-in configuration. Duplicate primary key values can be overwritten.

• Field value transformation: Use data processing plug-ins to convert source field values before indexing.

• Filter conditions: Apply filter conditions to include only matching rows from full or incremental data.

• Wildcard table matching: Use * to match tables with a common name prefix.

• Cloned instances: PolarDB for MySQL clusters support cloned instances as a data source.

• Read/write splitting: PolarDB for MySQL clusters support read/write splitting.

Limitations

Usage notes

Data types

• Fields of type DATETIME or TIMESTAMP in the source table are converted to milliseconds. Set the corresponding destination field type to TIMESTAMP in your application.

• When filtering rows by a DATE or DATETIME field, use the full format: createtime>'2018-03-01 00:00:00'. A short format such as 2018-3-1 00:00:00' returns an error.

Multiple data sources

• Configure multiple data sources for a single application table, provided the source tables share the same schema and data processing plug-in configuration.

• If an ApsaraDB RDS for MySQL or PolarDB data source is attached to a PolarDB-X instance, specify the name of the database shard actually used on the PolarDB-X instance. After database sharding, each logical database on PolarDB-X has one shadow database and eight shards. Data is written to shards at random.

Full data import timing

• OpenSearch pulls full data from the primary database only. Schedule full data imports and index rebuilds during off-peak hours.

SSL encryption

• If SSL encryption is enabled on your PolarDB for MySQL cluster, keep SSL certificates valid. Expired certificates cause connection failures.

Troubleshooting

Reindexing stalls after the data source is configured

Binary logs may not be generating continuously. Create a test table in the source database and write or update one or two rows per minute until reindexing completes. This ensures a steady stream of binary log events.

Reindexing fails due to overdue payment

If an advanced application for which a PolarDB for MySQL data source is configured has overdue payments, pay the outstanding balance, then manually trigger reindexing from the OpenSearch console.

Duplicate primary keys when syncing from multiple tables

When you sync from multiple source tables with the same schema, duplicate primary key values across tables cause documents to overwrite each other. To create unique keys, use the StringCatenateExtractor plug-in to concatenate the original primary key with the table name.

Set the source fields to pk (replace with your actual primary key field name) and $table (a system variable that resolves to the table name), and use a hyphen (-) or another custom character as the separator.

For example, if the table is my_table_0 and the primary key value is 123456, the concatenated key becomes 123456-my_table_0.

What's next

• Build an application

• Perform offline changes

• Data processing plug-ins

• Configure conditions to filter fields in ApsaraDB RDS and PolarDB data sources