OpenSearch:Configure a PolarDB data source

Things to know before purchasing PolarDB

PolarDB is a ready-to-use, stable, reliable, and elastically scalable online database service offered by Alibaba Cloud (Learn about PolarDB).

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

• The PolarDB cluster must belong to the currently logged-in Alibaba Cloud account to be accessible.

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

• Binary logging is disabled by default after a PolarDB cluster is created, which causes data source registration to fail. You must enable it as follows: loose_polar_log_bin defaults to OFF and must be set to ON_WITH_GTID. binlog_row_image defaults to FULL and does not need to be changed.

• Cloning instances is supported.

• The PolarDB cluster must be set to a read-write cluster.

Supported features

• Supports (manual/scheduled) full data pulls from specified database tables.

• Supports the horizontal merging of data from one or more data source tables. These source tables must have identical structures and data source plug-in configurations, and their primary key values must not be duplicated (duplicate primary key values are overwritten). The following two scenarios are supported:

  • The application table is configured with one data source that contains multiple source tables.

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

• Supports data source column transformation plug-ins.

• Supported data synchronization methods:

  • Automatic synchronization.

  • Self-purchased DTS real-time synchronization.

  • No automatic synchronization.

• Supports (full) filter conditions.

• Supports matching database table names using the wildcard *.

Related limits

• Only the full mode of binary logging is supported for PolarDB clusters. Enable it as follows: loose_polar_log_bin defaults to OFF and must be set to ON_WITH_GTID. binlog_row_image defaults to FULL and does not need to be changed.

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

• The PolarDB cluster must belong to the currently logged-in Alibaba Cloud account to be accessible.

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

• After a Standard Edition application is configured with a PolarDB data source, pushing incremental data (via SDK/API) is not supported.

• Data source 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.

• The PolarDB access password cannot contain the % character, as this causes the index reindexing task to fail.

• Merging columns across source tables of different databases is not supported.

• We recommend setting both loose_max_statement_time and connect_timeout to 0. After reindexing or an offline change triggers a full synchronization, you can change them back to their normal values.

Notes

• If a data source (RDS/PolarDB) mounted behind DRDS is connected to OpenSearch, you must specify the name of the actual physical database under DRDS when configuring the data source (a database under DRDS is split into one shadow database and 8 actual physical databases, and written data is randomly written to the physical databases).

• PolarDB clusters support switching between internal and public network domains. OpenSearch does not charge any traffic fees for retrieving PolarDB data.

• OpenSearch only supports pulling full data from the primary database. Based on how busy your workloads are, we recommend reindexing to import full data during off-peak hours.

• For time types such as datetime and timestamp in PolarDB cluster tables, the system automatically converts them to milliseconds. Set the corresponding application table column type to TIMESTAMP.

• (Full) documents that do not meet the data source filter conditions are filtered out. In addition, if a document with the same primary key value exists in the corresponding application table, it is also deleted.

• If the data source side has no incremental data for a long time (15 days or more), data synchronization exceptions may occur. If this happens, manually perform reindexing to resolve the issue.

• If SSL certificate encryption is enabled for PolarDB, make sure the certificate has not expired. An expired certificate causes connection exceptions, so update the SSL certificate validity period in a timely manner.

• Configuring a PolarDB data source is not supported in the China (Qingdao) region.

• When synchronizing PolarDB data source data through OpenSearch, you must add the IP address ranges of the OpenSearch servers to the corresponding security settings in PolarDB. Refer to the following list for the IP address allowlists of each region:

  Region	IP address
  China (Hangzhou)	100.104.190.128/26,100.104.241.128/26
  China (Beijing)	100.104.16.192/26,100.104.179.0/26
  China (Shanghai)	100.104.37.0/26,100.104.46.0/26
  China (Shenzhen)	100.104.87.192/26,1100.104.132.192/26
  China (Zhangjiakou)	100.104.155.192/26,100.104.238.64/26
  Germany	100.104.127.0/26,100.104.35.192/26
  US	100.104.193.128/26,100.104.119.128/26
  Singapore	100.104.58.192/26,100.104.74.192/26

Account authorization issues

• When connecting PolarDB, you must grant access to the cluster and provide the account and password. Choose the account and password carefully during the initial connection.

• [Ensure account permissions] The account must have permission to view all tables in the database (a limit of the upstream DTS service), so that it can correctly run show create table *. *. Otherwise, issues may occur with real-time service synchronization.

• [Minimize account permission changes] Account changes prevent the current real-time task from consuming data normally, and creating new versions is also affected. If you change the account or password, you must delete the instance and reconnect the database.

Frequently asked questions

• If reindexing stalls after you configure a PolarDB data source, create a test table in the database where the data table resides and write or update 1 to 2 rows of data per minute to ensure that continuous binary logs are generated during reindexing.

• If the PolarDB cluster of a Professional Edition application became overdue but the overdue payment was later settled, you can directly trigger a manual reindexing.

• The PolarDB cluster access password cannot contain the % character; otherwise, the reindexing task fails. (Error message: Illegal hex characters in escape (%) pattern).

• The system requires that primary key values in the application table not be duplicated. If primary key values are duplicated in a sharded scenario, they are overwritten. You can use the StringCatenateExtractor data source plug-in to merge multiple column values. Set the source columns to pk,$table (replace pk with the primary key column of the PolarDB cluster table; $table is a default system variable that represents the corresponding database table name), and set the concatenation character to - (customizable).

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

• To filter data based on the date or datetime column type in a database table, assuming the database column name is createtime, the time format in the data source filter condition must be createtime>'2018-03-01 00:00:00'. Using a format such as createtime>'2018-3-1 00:00:00' causes an error.

Configure a PolarDB data source

• Configure the PolarDB data source during the application creation process.

• For a created application, you can modify it on the application details page via "Edit application structure".

Console configuration steps and notes

1. When creating or modifying an application, in step 3, Data Source, add or edit a data source, select Polardb Data Source, and click Create Database.

2. After you fill in the PolarDB data source information, click Connect.

   In the Connect Database dialog, the data source information you need to fill in includes the cluster ID, database name (for example, opensearch), username, and password.

Parameter	Description
Cluster ID	The PolarDB cluster ID, which can be obtained from the PolarDB console (case-sensitive). Refer to the following format for the cluster ID: pc-uf6c056ny9tiaj1l7.
Database name	The name of the database to connect to under this instance (case-insensitive).
Username	The database account, used to obtain the database table schema and full data (case-sensitive).
Password	The password for the account.

OpenSearch attempts to connect and provides a result prompt based on the specific situation:

Prompt message	Solution
This PolarDB cluster does not exist in the current region for the current user	Check whether the cluster ID is correct and make sure the region of the PolarDB cluster matches the region of the OpenSearch application. If the conditions are met but the error persists, submit a ticket for feedback.
Failed to connect to the database service	Check whether the PolarDB connection string is correct, including the cluster ID, database name, username, and password.
This table does not exist under the current PolarDB cluster	Check whether the table name is filled in correctly and whether the table actually exists in the PolarDB database.
Issue with configuration items of the PolarDB cluster	Go to the Parameter Configuration page in the PolarDB console, modify the corresponding configuration items, and then retry.

3. After the PolarDB data source information is connected, select the data table. The interface with an established data source connection is shown below. Select the corresponding table and click OK.

   After selecting the target table, click the >> button in the middle to move it to the Selected panel on the right.

• Select or enter the name of the table to access under this database (case-sensitive).

• The sharding rule table_* is supported, for example, table_a, table_b, and so on.

4. If the connection is successful, proceed with column configuration. OpenSearch automatically obtains the table columns. For information about data source plug-ins, see Data source plug-ins.

   In the column mapping table, confirm the correspondence between OpenSearch table columns and POLARDB source columns (for example, id maps to id, and text maps to title). To add a data source plug-in, click the + button in the corresponding column row. After confirming that the mapping is correct, click OK.

5. Configure the PolarDB data source filter conditions (not supported in Standard Edition). After configuring the data source, click Submit to complete the application structure configuration.

• You can also configure multiple data sources in an OpenSearch application table, but ultimately these table structures and configurations must be identical.

• The filter conditions configured for the PolarDB data source can extract only records that meet the conditions. For detailed configuration, see Configure filter conditions.