PolarDB:Search views and ETL stored procedures

Create a search view

Syntax

CREATE SEARCH VIEW view_name [(column_list, PRIMARY KEY (pk_column_list))]
 [WITH (option_list)]
 AS select_statement;

Parameters

Usage limits and notes

• The source table must contain a primary key or a unique key.

• You must have the ALTER permission on all source tables in the search view, and the SELECT permission on the related columns or the entire table.

• After a search view is created, new columns added to the source table are not automatically synchronized. To synchronize new columns, see Modify a search view.

• To use a custom destination index configuration, you can first create the index on the PolarSearch node and manually create the index and define its configuration, and then create a search view. If the destination index does not exist at creation time, the system creates it automatically.

• To configure advanced synchronization parameters for multi-table aggregation or complex queries, such as JSON field conversion and routing fields, see AutoETL parameter configuration and best practices.

Data preparation

You can create the test data used in the following examples by executing the following SQL statements inPolarDB for MySQL.

CREATE DATABASE IF NOT EXISTS db1;
CREATE DATABASE IF NOT EXISTS db2;

USE db1;
CREATE TABLE IF NOT EXISTS t1 (
 id INT PRIMARY KEY,
 c1 VARCHAR(100),
 c2 VARCHAR(100)
);
INSERT INTO t1(id, c1, c2) VALUES
(1, 'apple', 'red'),
(2, 'banana', 'yellow'),
(3, 'grape', 'purple');

USE db2;
CREATE TABLE IF NOT EXISTS t2 (id INT PRIMARY KEY, c2 INT);
INSERT INTO t2(id, c2) VALUES (1, 111), (2, 222), (4, 444);

Examples

• Full table synchronization: Joindb1.t1 to PolarSearch. The view nameview_test is also the target index name in PolarSearch.

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

• Specified column synchronization: Synchronize only the c1andc2 columns, and manually define column types and the primary key.

  CREATE SEARCH VIEW view_test1 AS SELECT c1, c2 FROM db1.t1;

• Specified synchronization parameters: Specify synchronization configuration through the WITH clause when creating a search view. The following example synchronizes the db1.t1 c1andc2 columns to PolarSearch and sets the synchronization parallelism to 2.

  CREATE SEARCH VIEW view_test6 WITH ('parallelism' = '2') AS SELECT c1, c2 FROM db1.t1;

• Conditional filter synchronization: Synchronize only data that meets the WHERE condition.

  CREATE SEARCH VIEW view_test2 AS SELECT id, c1, c2 FROM db1.t1 WHERE c1 > 10;

• Multi-table JOIN: Joindb1.t1anddb2.t2throughid and synchronize the result to PolarSearch.

  CREATE SEARCH VIEW view_test3(id, c1, c2) AS SELECT t1.id, t1.c1, t2.c2 FROM db1.t1 AS t1 LEFT JOIN db2.t2 AS t2 ON t1.id = t2.id;

• Multi-table UNION: Merge multiple tables with the same structure and synchronize. EachSELECT statement must have the same number and types of columns.

  CREATE SEARCH VIEW view_test4(id, c2) AS SELECT id, c2 FROM db1.t1 UNION ALL SELECT id, c2 FROM db2.t2;

• Group aggregation: Synchronize data after group aggregation. When usingGROUP BY, you must manually define columns and the primary key.

  CREATE SEARCH VIEW view_test5 (id, max_c) AS SELECT t1.id, MAX(t1.c1) AS max_c FROM db1.t1 GROUP BY t1.id;

Verify data

Check the synchronization status of the search view:

SHOW SEARCH VIEW STATUS;

When the status is active, the search view data synchronization is running normally. Connect to the PolarSearch node, using an Elasticsearch-compatible REST API to verify data:

# Replace <user>:<password> with the PolarSearch node credentials and <polarsearch_endpoint> with the PolarSearch node endpoint and port
curl -u <user>:<password> -X GET "http://<polarsearch_endpoint>/view_test/_search"

Manage search views

You can use the following commands to view created search views, or to stop, restart, or rebuild data synchronization. All commands must be executed in a database client after connecting to the cluster.

SHOW SEARCH VIEW STATUS;

+------------+--------+----------+---------+---------------------+---------------------+
| View Name | Type | Status | Message | Created_at | Updated_at |
+------------+--------+----------+---------+---------------------+---------------------+
| view_test | search | active | | 2026-03-18 18:44:12 | 2026-03-18 18:51:37 |
+------------+--------+----------+---------+---------------------+---------------------+

SHOW CREATE SEARCH VIEW view_test;

+-----------+------------------------------------------------------+
| View Name | Create Search View |
+-----------+------------------------------------------------------+
| view_test | CREATE SEARCH VIEW view_test AS SELECT * FROM db1.t1 |
+-----------+------------------------------------------------------+

ALTER SEARCH VIEW view_test STOP;

ALTER SEARCH VIEW view_test RESTART;

ALTER SEARCH VIEW view_test REBUILD;

Delete a search view

DROP SEARCH VIEW view_name;

The system handles deletion differently depending on the search view status:

• active search view: The status first changes todropping. After the system completes resource cleanup and target index data deletion, the status changes todropped.

• dropped search view: The system completely removes the search view information.

• Search views in other statuses: The system does not support deletion.

Modify a search view

After a search view is created, you can adjust its runtime parameters or modify its synchronization logic, such as adding synchronization fields or changing query conditions. AutoETL provides three modification methods:

• To adjust only runtime parameters such as parallelism and compute resource usage, use Parameter modification. No changes to the synchronization SQL are required.

• When modifying the synchronization logic, preferIn-place SQL modification. If the SQL definitions are compatible, synchronization continues from the original checkpoint without full re-synchronization. You can refer toDDL change rules and best practices to determine whether the SQL definitions are compatible.

• If the SQL definitions are incompatible and in-place modification is not possible, use “new index + new search view”modification to rebuild.

ALTER SEARCH VIEW view_name UPDATE WITH (new_option_list);

ALTER SEARCH VIEW view_test UPDATE WITH ('parallelism' = '8', 'link.tm.cpu' = '4', 'link.tm.slots' = '8');

ALTER SEARCH VIEW view_name UPDATE
 [TO (column_list, PRIMARY KEY (pk_column_list))]
 AS new_select_statement;

Create a synchronization link

Syntax

CALL dbms_etl.sync_by_sql("search", "<sync_sql>");

Examples

CALL dbms_etl.sync_by_sql("search", "

-- Step 1: Define the PolarDB source table
CREATE TEMPORARY TABLE `db1`.`t1` (
 `id` BIGINT,
 `c1` STRING,
 PRIMARY KEY (`id`) NOT ENFORCED
) WITH (
 'connector' = 'mysql',
 'database-name' = 'db1',
 'table-name' = 't1'
);

-- Step 2: Define the PolarSearch destination table
CREATE TEMPORARY TABLE `dest` (
 `id` BIGINT,
 `max_c` STRING,
 PRIMARY KEY (`id`) NOT ENFORCED
) WITH (
 'connector' = 'opensearch',
 'index' = 'dest'
);

-- Step 3: Define the computation and insert logic
INSERT INTO `dest`
SELECT
 `t1`.`id`,
 MAX(`t1`.`c1`)
FROM `db1`.`t1` AS `t1`
GROUP BY `t1`.`id`;
");

Verify data

Connect to the PolarSearch node, using an Elasticsearch-compatible REST APIto query, verify that data has been synchronized.

# Replace <polarsearch_endpoint> with the PolarSearch node endpoint
curl -u <user>:<password> -X GET "http://<polarsearch_endpoint>/dest/_search"

Manage synchronization links

You can use the following commands to view created synchronization links, or to stop, restart, or rebuild data synchronization. All commands must be executed in a database client after connecting to the cluster.

CALL dbms_etl.show_sync_link();

CALL dbms_etl.show_sync_link_by_id('<sync_id>')\G

*************************** 1. row ***************************
 SYNC_ID: crb5rmv8rttsg
 NAME: crb5rmv8rttsg
 SYSTEM: search
SYNC_DEFINITION: db1.t1 -> dest
 SOURCE_TABLES: db1.t1
 SINK_TABLES: dest
 STATUS: active -- Link status. active indicates normal operation
 MESSAGE: -- If an error occurs, the error message is displayed here
 CREATED_AT: 2024-05-20 11:55:06
 UPDATED_AT: 2024-05-20 17:28:04
 OPTIONS:...

CALL dbms_etl.stop_sync_link('<sync_id>');

CALL dbms_etl.restart_sync_link('<sync_id>');

CALL dbms_etl.rebuild_sync_link('<sync_id>');

Delete a synchronization link

This operation stops data synchronization and cleans up related resources.

CALL dbms_etl.drop_sync_link('<sync_id>');

The system handlesdrop_sync_link deletion differently depending on the link status:

• active link: The status first changes todropping. After the system completes link resource and target index data cleanup, the status changes todropped.

• dropped link: The system completely removes the link information.

• Links in other statuses: The system does not support deletion.

Modify a synchronization link

After a synchronization link is created, you can adjust its runtime parameters or modify its synchronization SQL. Similar to search views, AutoETL provides three modification methods:

• To adjust only runtime parameters such as parallelism and compute resource usage, use Parameter modification. No changes to the synchronization SQL are required.

• When modifying the synchronization SQL, preferIn-place SQL modification. If the SQL definitions are compatible, synchronization continues from the original checkpoint without full re-synchronization. You can refer toDDL change rules and best practices to determine whether the SQL definitions are compatible.

• If the SQL definitions are incompatible and in-place modification is not possible, use “new index + new link”modification to rebuild.

SET esl_link_options = "<new_option_list>";
CALL dbms_etl.update_sync_link('<sync_id>', '');

SET esl_link_options = "'parallelism' = '8', 'link.tm.cpu' = '4', 'link.tm.slots' = '8'";
CALL dbms_etl.update_sync_link('8f4228x2uq12z', '');

CALL dbms_etl.update_sync_link('<sync_id>', '<new_sync_sql>');

CALL dbms_etl.update_sync_link('8f4228x2uq12z', "

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 `id`, `c1` FROM `db1`.`t1` WHERE `c1` > 20;
");