OpenSearch:PolarDB data source configuration

PolarDB is a fully managed, high-availability cloud database service provided by Alibaba Cloud that supports elastic scaling. For more information, see What is PolarDB.

Before you purchase a PolarDB cluster

• OpenSearch supports PolarDB for MySQL 5.6, 5.7, and 8.0.

• The PolarDB cluster must belong to your current Alibaba Cloud account.

• The PolarDB cluster must be in the same region as the OpenSearch application.

• Binary logging (binlog) is disabled by default on new PolarDB clusters, which causes data source registration to fail. You must enable binlog by setting the loose_polar_log_bin parameter to ON_WITH_GTID. The binlog_row_image parameter defaults to FULL and does not need to be changed.

• Cloned instances are supported.

• The PolarDB cluster must be a read/write cluster.

Supported features

• Pull full data from a specified database table manually or on a schedule.

• Merge data from multiple source tables. The source tables must share the same schema and data source plugin configurations. Primary key values must be unique — duplicate values cause new data to overwrite existing data. This feature supports the following scenarios:

  • The application table is configured with a data source and includes multiple source tables.

  • An application table is configured with multiple data sources, and each data source contains one or more source tables.

• Use data source plugins to transform field values.

• Supported data synchronization methods:

  • Automatic synchronization

  • Self-purchased DTS for real-time synchronization

  • No automatic synchronization

• Specify filter conditions for full data.

• Use the wildcard character * to match database table names.

Limits

• The binlog mode for the PolarDB cluster must be set to full. Set the loose_polar_log_bin parameter to ON_WITH_GTID. The binlog_row_image parameter defaults to FULL and does not need to be changed.

• Only PolarDB for MySQL 5.6, 5.7, and 8.0 are supported.

• The PolarDB cluster must belong to the same Alibaba Cloud account that you use to access the OpenSearch console.

• The PolarDB cluster must be in the same region as the OpenSearch application.

• After you configure a PolarDB data source for a Standard Edition application, you cannot push incremental data by using an SDK or API.

• Filter conditions are not supported for PolarDB data sources of Standard Edition applications.

• The REPLACE INTO syntax is not supported.

• The TRUNCATE and DROP commands are not supported. Use the DELETE command to delete data instead.

• The access password for the PolarDB cluster cannot contain the % character. Otherwise, reindexing fails.

• You cannot merge columns from source tables that have different schemas.

• You can set both the loose_max_statement_time and connect_timeout parameters to 0 during reindexing or offline changes. After the full data synchronization is complete, you can revert these values.

Notes

• If a data source such as RDS or PolarDB is attached to a DRDS instance, specify the actual database shard name in the DRDS instance when configuring the data source. A DRDS database is split into one shadow database and eight shards, and data is randomly written to these shards.

• You can switch between the internal and public endpoints of a PolarDB cluster. OpenSearch does not charge traffic fees for data retrieval from PolarDB.

• OpenSearch pulls full data only from the primary database. We recommend that you perform reindexing and full data imports during off-peak hours.

• The system automatically converts datetime and timestamp values in PolarDB tables to milliseconds. Set the corresponding field type in the application schema to TIMESTAMP.

• Documents that do not meet the filter conditions are filtered out during full data synchronization. If documents in the application table have duplicate primary key values, they are also deleted.

• If no incremental data is generated in the data source for 15 days or more, data synchronization may become abnormal. To resolve this issue, perform a manual reindex or an offline change.

• If SSL encryption is enabled for the PolarDB cluster, ensure the SSL certificate is valid. An expired certificate causes connection errors. Update the certificate before it expires.

• PolarDB data sources are not available in the Qingdao region.

• To synchronize data from a PolarDB data source, add the IP address CIDR blocks of the OpenSearch servers to the whitelist of your PolarDB instance. The following table lists the required IP addresses for each region.

  Region	IP Address
  Hangzhou	100.104.190.128/26,100.104.241.128/26
  Beijing	100.104.16.192/26,100.104.179.0/26
  Shanghai	100.104.37.0/26,100.104.46.0/26
  Shenzhen	100.104.87.192/26,100.104.132.192/26
  Zhangjiakou	100.104.155.192/26,100.104.238.64/26
  Germany	100.104.127.0/26,100.104.35.192/26
  United States	100.104.193.128/26,100.104.119.128/26
  Singapore	100.104.58.192/26,100.104.74.192/26

Account authorization

• To connect to a PolarDB cluster, you must provide an account and password with the required access permissions. Choose the account carefully for the initial data source connection.

• Ensure account permissions: The account must have permissions to query all tables in the source database (required by the upstream DTS service) and to correctly execute SHOW CREATE TABLE *.*. Insufficient permissions cause real-time synchronization to fail.

• Minimize permission changes: Modifying the account can disrupt running real-time tasks and affect the creation of new application versions. If you change the account password, you must delete the instance and reconnect to the database.

FAQ

• If reindexing becomes stuck after you configure a PolarDB data source, create a test table in the database that contains the source table, and then write or update one or two records every minute. This ensures continuous binlog generation during reindexing.

• If a Premium Edition application with a PolarDB data source has overdue payments, you can trigger a manual reindex after settling the balance.

• The access password for the PolarDB cluster cannot contain the % character. Otherwise, reindexing fails with the error message: `Illegal hex characters in escape (%) pattern`.

• Primary key values in an application table must be unique. With table sharding, duplicate primary key values may cause data to be overwritten. To prevent this, use the StringCatenateExtractor plugin to merge multiple fields. Set the source fields to pk,$table, where `pk` is the primary key field of the PolarDB table and `$table` is a system variable representing the table name (available only when table sharding uses a wildcard). Use a hyphen (-) or a custom character as the separator.

For example, if the PolarDB table is named my_table_0 and the primary key value is 123456, the new primary key value after concatenation is 123456-my_table_0.

• To filter data by a date or datetime field, use the correct format in the filter condition. For example, if the field name is `createtime`, the filter condition must be createtime>'2018-03-01 00:00:00'. Formats such as createtime>'2018-3-1 00:00:00' cause errors.

Configure a PolarDB data source

• You can configure a PolarDB data source when you create an application.

• For an existing application, go to the product page and perform an offline change to modify the data source.

Procedure in the console

1. When creating or modifying an application, go to the Data Source step. Add or edit a data source, select PolarDB, and then click New Database.

2. Enter the PolarDB data source information and click Connect.

OpenSearch attempts to connect and displays a message based on the result:

3. After the connection is established, select a table from the list and click OK.

• You can select or enter the name of the table to access. The name is case-sensitive.

• Table sharding rules such as table_* are supported. For example, table_* matches tables such as table_a and table_b.

4. After a successful connection, configure the fields. OpenSearch automatically retrieves the table fields. For more information about data source plugins, see this document.

5. Configure filter conditions for the PolarDB data source (not supported for Standard Edition applications). After configuring the data synchronization method, click Finish to complete the application schema configuration.

• You can configure multiple data sources for a table in an OpenSearch application, but the table schemas and configurations must be identical.

• If a message indicates that automatic synchronization is not supported, use DTS for real-time synchronization.

• The filter conditions only pull records that meet the specified criteria. For more information, see Filter conditions for a data source.