Use AutoETL to synchronize data to PolarSearch nodes on clusters across regions - PolarDB

PolarDB supports Global Database Network (GDN) for data synchronization across PolarDB clusters in multiple regions. Each PolarDB cluster in a GDN can have its own PolarSearch node to provide cross-region search services. This topic describes how to use AutoETL to synchronize data to PolarSearch nodes on clusters in other regions.

Background information

In a GDN, data is replicated from the primary cluster to each secondary cluster. Each cluster can independently serve read requests. To use PolarSearch for search and analytics on a secondary cluster, you can create a cross-region synchronization pipeline with AutoETL to synchronize data to the PolarSearch node on the target secondary cluster.

Cross-region synchronization pipelines are created and managed from the primary cluster. You can specify the target secondary cluster by setting the gdn-cluster parameter in the esl_link_options session variable.

The following figure shows the overall architecture of AutoETL cross-region data synchronization.

Synchronize data to the PolarSearch node on the primary cluster

If the gdn-cluster parameter is not specified, AutoETL synchronizes data to the PolarSearch node on the primary cluster by default.

Example

Create a search view to synchronize data from the db1.t1 table to the PolarSearch node on the primary cluster.

CREATE SEARCH VIEW view_cluster_master AS SELECT * FROM db1.t1;

Synchronize data to the PolarSearch node on a secondary cluster

After you set the gdn-cluster parameter, AutoETL synchronizes data to the PolarSearch node on the specified secondary cluster.

Synchronize by using a search view

Create a search view to synchronize data to the PolarSearch node on the secondary cluster instance-1.

SET esl_link_options="'gdn-cluster'= 'instance-1'";
CREATE SEARCH VIEW view_cluster_slave AS SELECT * FROM db1.t1;

Synchronize by using an ETL stored procedure

Create an ETL stored procedure to synchronize data to the PolarSearch node on the secondary cluster instance-1.

SET esl_link_options="'gdn-cluster'= 'instance-1'";
CALL dbms_etl.sync_by_sql("search", "CREATE TEMPORARY TABLE `db1`.`t1` (
  `id`   BIGINT,
  `c1`   STRING,
  PRIMARY KEY (`id`) NOT ENFORCED
) WITH (
  'connector' = 'mysql',
  'database-name' = 'db1',
  'table-name' = 't1'
);
CREATE TEMPORARY TABLE `dest` (
  `id`  BIGINT,
  `c1` STRING,
  PRIMARY KEY (`id`) NOT ENFORCED
) WITH (
  'connector' = 'opensearch',
  'index' = 'dest'
);
INSERT INTO `dest` SELECT * FROM `db1`.`t1`;
");

View the cluster that a pipeline belongs to

You can view the CREATE statement of a search view to check which cluster the synchronization pipeline belongs to.

SHOW CREATE SEARCH VIEW view_cluster_slave;

The following sample result is returned. The Options field shows the cluster that the pipeline belongs to.

+-----------------------+--------------------------------------------------------------------+---------------------------------+
| View Name             | Create view                                                        | Options                         |
+-----------------------+--------------------------------------------------------------------+---------------------------------+
| view_cluster_slave    | create search view `view_cluster_slave` as select * from `db1`.`t1`| 'gdn-cluster'='instance-1'      |
+-----------------------+--------------------------------------------------------------------+---------------------------------+

Monitor a synchronization pipeline

To monitor a cross-region synchronization pipeline, go to the console of the secondary cluster.

Log on to the PolarDB console, click Clusters in the navigation pane on the left, select the Region where the cluster is deployed, and then click the cluster ID to open the cluster details page.
In the left-side navigation pane, choose Performance Monitoring > Search Clusters > Sync Links to view the running status of synchronization pipelines.

Limits

A search view can synchronize data to the PolarSearch node on only one secondary cluster.
The cluster specified by the gdn-cluster parameter must have been created and added to a GDN.
esl_link_options is a connection-level session variable. The gdn-cluster value remains in effect until you disconnect.

PolarDB:Use AutoETL to synchronize data to PolarSearch nodes on clusters across regions