PolarDB:DDL change rules and best practices

Whether in-place changes are supported

The following table lists common search view or ETL stored procedure changes and whether they support in-place changes.

In-place changes

Modify the SQL logic in the existing link and start the link by using the existing link state. For more information, see Search views - Modify a search view and ETL stored procedures (sync_by_sql) - Modify a synchronization link.

• Search view syntax:

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

• ETL stored procedure syntax:

  SET esl_link_options = "<new_option_list>";
  -- If new_sync_sql is empty, the existing sync SQL is retained
  CALL dbms_etl.update_sync_link('<sync_id>', '<new_sync_sql>'); 

"New index + new link" changes

The "new index + new link" approach is the most versatile change method. The link rescans data and writes it to the PolarSearch node. After the new search view or ETL stored procedure completes data synchronization and verification, switch your application query traffic to the new index.

Example:

Add a field to the shop.user table and rebuild the search view. Assume the original search view synchronizes the shop.user table (with id, name, phone, gmt_create columns) to the user_v1 index. You need to add the membership_level column without disrupting online queries.

1. Create a new index: Create a new index named user_v2 on the PolarSearch node, and include the new membership_level field in its mapping.

   PUT user_v2
   {
     "mappings": {
       "properties": {
         "id":               { "type": "keyword" },
         "name":             { "type": "text", "fields": { "keyword": { "type": "keyword" } } },
         "phone":            { "type": "keyword" },
         "gmt_create":       { "type": "date" },
         "membership_level": { "type": "integer" }
       }
     }
   }

2. Modify the source table: Add the new column to the user table in the source PolarDB for MySQL.

   ALTER TABLE shop.user ADD COLUMN membership_level TINYINT NOT NULL DEFAULT 0 COMMENT 'Membership level';

3. Create a new search view: Create a new search view to synchronize data from the shop.user table to the user_v2 index.

   CREATE SEARCH VIEW user_v2 AS SELECT id, name, phone, gmt_create, membership_level FROM shop.user;

4. Verify and switch over: Run SHOW SEARCH VIEW STATUS to check the status of the new search view. After the synchronization latency drops to approximately 0 to 1 second, verify the data in the new index. After verification, switch your application query traffic to the new user_v2 index.

5. Clean up old resources：: After the new search view runs stably, drop the old search view and the user_v1 index。

   DROP SEARCH VIEW user_v1;