PolarDB:AutoETL parameter configuration and use cases

JSON field conversion

If a source table contains JSON fields, AutoETL stores them as strings in PolarSearch by default. You can use the esl_sink_options parameter to configure how these JSON fields are converted.

1. Prepare the data

   CREATE DATABASE IF NOT EXISTS db3;
   USE db3;
   CREATE TABLE IF NOT EXISTS t1 (
       id INT PRIMARY KEY,
       c1 JSON
   );
   INSERT INTO t1 VALUES (1, '{"name": "Alice", "age": 30}');
   INSERT INTO t1 VALUES (2, '{"name": "Bob", "age": 25}');

2. Default behavior (store as string)

   If you do not set any parameters, JSON fields are stored as strings in PolarSearch.

   CREATE SEARCH VIEW view_json_default AS SELECT * FROM db3.t1;

   Verify the data (partial results):

   {
       "hits": [
           {
               "_index": "view_json_default",
               "_id": "2",
               "_score": 1,
               "_source": {
                   "id": 2,
                   "c1": "{\"age\":25,\"name\":\"Bob\"}"
               }
           },
           {
               "_index": "view_json_default",
               "_id": "1",
               "_score": 1,
               "_source": {
                   "id": 1,
                   "c1": "{\"age\":30,\"name\":\"Alice\"}"
               }
           }
       ]
   }

3. Store as nested type

   Use the sink.json-flatten.fields parameter to specify which JSON fields to convert to the nested type. Separate multiple field names with a semicolon (;).

   SET esl_sink_options = "'sink.json-flatten.fields' = 'c1'";
   CREATE SEARCH VIEW view_json_nested AS SELECT * FROM db3.t1;

   Verify the data (partial results):

   {
       "hits": [
           {
               "_index": "view_json_nested",
               "_id": "1",
               "_score": 1,
               "_source": {
                   "id": 1,
                   "c1": {
                       "age": 30,
                       "name": "Alice"
                   }
               }
           },
           {
               "_index": "view_json_nested",
               "_id": "2",
               "_score": 1,
               "_source": {
                   "id": 2,
                   "c1": {
                       "age": 25,
                       "name": "Bob"
                   }
               }
           }
       ]
   }

4. Store in flatten mode

   Set the sink.json-flatten.mode parameter to flatten to flatten JSON fields into individual top-level fields.

   SET esl_sink_options = "'sink.json-flatten.fields' = 'c1', 'sink.json-flatten.mode' = 'flatten'";
   CREATE SEARCH VIEW view_json_flatten AS SELECT * FROM db3.t1;

   Verify the data (partial results): In flatten mode, the name and age keys from the JSON object are flattened into independent, top-level fields in the PolarSearch index.

   {
     "hits": [
         {
             "_index": "view_json_flatten",
             "_id": "2",
             "_score": 1,
             "_source": {
                 "id": 2,
                 "name": "Bob",
                 "age": 25
             }
         },
         {
             "_index": "view_json_flatten",
             "_id": "1",
             "_score": 1,
             "_source": {
                 "id": 1,
                 "name": "Alice",
                 "age": 30
             }
         }
     ]
   }

Search routing fields

Use the routing-fields parameter to specify one or more routing fields for a PolarSearch index. A document is stored on a shard determined by the routing field's value. This practice optimizes query performance when filtering by that field. Separate multiple fields with a semicolon (;).

Prepare the data

CREATE DATABASE IF NOT EXISTS db4;
USE db4;
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');

Example

Create a search view that uses the c1 field as the routing field:

SET esl_sink_options = "'routing-fields' = 'c1'";
CREATE SEARCH VIEW view_routing AS SELECT * FROM db4.t1;

Ignore delete operations

In multi-table join scenarios, a search view uses a delete-then-insert strategy to update the PolarSearch index. To prevent queries from failing to find data during this brief update, you can set the ignore-delete parameter to true to skip delete operations.

Example

SET esl_sink_options = "'ignore-delete' = 'true'";
CREATE SEARCH VIEW view_no_delete AS SELECT t1.id, t1.c1, t2.c2 FROM db3.t1 AS t1 JOIN db2.t2 AS t2 ON t1.id = t2.id;

Replacement write mode

By default, search views use the update mode to write data to PolarSearch, updating only modified fields. To use the replacement mode (also known as index mode), which replaces the entire document on each write, set the sink.force-index-request parameter to true.

Comparison of the two modes

Example

SET esl_sink_options = "'sink.force-index-request' = 'true'";
CREATE SEARCH VIEW view_replace AS SELECT * FROM db3.t1;