通过搜索优化加速结构化数据查询¶

搜索优化服务可以提升 Snowflake 表中结构化数据的点查找和子字符串查询性能；即结构化 ARRAY、OBJECT 和 MAP 列中的数据。即使结构深度嵌套且频繁变更，您仍可对这些类型的列配置搜索优化。您还可以为结构化列中的特定元素启用搜索优化。

以下部分提供关于结构化数据查询搜索优化支持的更多信息：

为结构化数据查询启用搜索优化
支持对结构化类型进行点查找的谓词
结构化类型中的子字符串搜索
架构演化支持
当前对半结构化类型支持的限制

为结构化数据查询启用搜索优化¶

要提高表上结构化数据类型查询的性能，请在 :ref:` ALTER TABLE ... ADD SEARCH OPTIMIZATION 命令中使用子句 ON <label-alter_table_searchoptimizationaction_add>`，适用于特定列或列中的元素。如果省略 ON 子句，对 ARRAY、OBJECT 和 MAP 列的查询不会被优化。在表级别启用搜索优化不会自动为结构化数据类型的列启用搜索优化。

例如：

ALTER TABLE mytable ADD SEARCH OPTIMIZATION ON SUBSTRING(array_column);
ALTER TABLE mytable ADD SEARCH OPTIMIZATION ON EQUALITY(array_column[1]);

ALTER TABLE mytable ADD SEARCH OPTIMIZATION ON EQUALITY(object_column);
ALTER TABLE mytable ADD SEARCH OPTIMIZATION ON SUBSTRING(object_column:key);

ALTER TABLE mytable ADD SEARCH OPTIMIZATION ON EQUALITY(map_column);
ALTER TABLE mytable ADD SEARCH OPTIMIZATION ON EQUALITY(map_column:user.uuid);

Copy

以下规则适用于您在这些 ALTER TABLE ... ADD SEARCH OPTIMIZATION 命令中使用的关键字：

您可以将 EQUALITY 关键字用于任何内部元素或列本身。
您只能将 SUBSTRING 关键字用于具有文本字符串数据类型的内部元素。

有关更多信息，请参阅启用和禁用搜索优化。

结构化类型谓词中常量和类型转换支持的数据类型¶

搜索优化服务可以提升结构化数据点查找的性能，其中以下类型用于常量以及元素的隐式或显式类型转换：

FIXED（包括指定有效精度和小数点后位数的类型转换）
INTEGER （包括同义类型）
VARCHAR （包括同义类型）
DATE（包括指定小数点后位数的类型转换）
TIME（包括指定小数点后位数的类型转换）
TIMESTAMP、TIMESTAMP_LTZ、TIMESTAMP_NTZ、TIMESTAMP_TZ（包括指定小数点后位数的类型转换）

搜索优化服务支持使用以下转换函数进行类型转换：

支持对结构化类型进行点查找的谓词¶

搜索优化服务可以使用下列谓词类型改进点查找查询。在以下示例中，src 是结构化数据类型的列，path_to_element 是该列中元素的路径。

相等谓词的格式如下：

WHERE path_to_element[::target_data_type] = constant

在此语法中，target_data_type`（如果指定）和 :samp:`constant 的数据类型必须是支持的类型之一。

例如，搜索优化服务支持以下谓词：
- 将 OBJECT 或 MAP 元素与 NUMBER 常量进行比对，而不显式转换元素类型：
  WHERE src:person.age = 42;
  Copy
- 以指定精度和小数位显式将 OBJECT 或 MAP 元素转换为 NUMBER 类型：
  WHERE src:location.temperature::NUMBER(8, 6) = 23.456789;
  Copy
- 将 OBJECT 或 MAP 元素与 VARCHAR 常量进行比对，而不显式转换元素类型：
  WHERE src:sender_info.ip_address = '123.123.123.123';
  Copy
- 显式将 OBJECT 或 MAP 元素转换为 VARCHAR 类型：
  WHERE src:salesperson.name::VARCHAR = 'John Appleseed';
  Copy
- 显式将 OBJECT 或 MAP 元素转换为 DATE 类型：
  WHERE src:events.date::DATE = '2021-03-26';
  Copy
- 使用指定小数位显式将 OBJECT 或 MAP 元素转换为 TIMESTAMP 类型：
  WHERE src:event_logs.exceptions.timestamp_info(3) = '2021-03-26 15:00:00.123 -0800';
  Copy
- 将 ARRAY 元素与支持类型的值进行匹配，可带显式转换也可不带：
  WHERE my_array_column[2] = 5; WHERE my_array_column[2]::NUMBER(4, 1) = 5;
  Copy
- 将 OBJECT 或 MAP 元素与支持类型的值进行匹配，可带显式转换也可不带：
  WHERE object_column['mykey'] = 3; WHERE object_column:mykey = 3; WHERE object_column['mykey']::NUMBER(4, 1) = 3; WHERE object_column:mykey::NUMBER(4, 1) = 3;
  Copy
使用 ARRAY 函数的谓词，例如以下谓词：
- WHERE ARRAY_CONTAINS(value_expr, array)
  
  在这种语法中，value_expr 不能是 NULL，必须求值到 VARIANT。值的数据类型必须是支持的类型之一。
  WHERE ARRAY_CONTAINS('77.146.211.88'::VARIANT, src:logs.ip_addresses)
  Copy
  在以下示例中，该值是一个隐式转换为 OBJECT 类型的常量：
  WHERE ARRAY_CONTAINS(300, my_array_column)
  Copy
- WHERE ARRAYS_OVERLAP(ARRAY_CONSTRUCT(constant_1, constant_2, .., constant_N), array)
  
  每个常量（constant_1、constant_2 等）的数据类型必须是支持的类型之一。构造 ARRAY 可以包含 NULL 常量。
  
  在以下示例中，数组位于 OBJECT 值中：
  WHERE ARRAYS_OVERLAP( ARRAY_CONSTRUCT('122.63.45.75', '89.206.83.107'), src:senders.ip_addresses)
  Copy
  在以下示例中，数组位于 ARRAY 列中：
  WHERE ARRAYS_OVERLAP( ARRAY_CONSTRUCT('a', 'b'), my_array_column)
  Copy
以下谓词用于检查 NULL 值：
- WHERE IS_NULL_VALUE(path_to_element)
  
  备注
  
  IS_NULL_VALUE 适用于 JSON null 值，但不适用于 SQL NULL 值。
- WHERE path_to_element IS NOT NULL
- WHERE structured_column IS NULL
  
  其中 structured_column 指的是列，而不是结构化数据中元素的路径。
  
  例如，搜索优化服务支持使用 OBJECT 列 src，但不支持使用该 OBJECT 列中元素 src:person.age 的路径。

结构化类型中的子字符串搜索¶

仅当目标结构化元素的数据类型为文本字符串时，才能启用子字符串搜索。

例如，考虑下表：

CREATE TABLE t(
  col OBJECT(
    a INTEGER,
    b STRING,
    c MAP(INTEGER, STRING),
    d ARRAY(STRING)
  )
);

Copy

对于此表，可以在以下目标结构化元素上添加 SUBSTRING 搜索的优化：

col:b 因为其类型是 STRING。
col:c[value] – 例如，col:c[0]、col:c[100]– 如果其值为文本字符串类型。

对于此表，以下目标结构化元素不能添加 SUBSTRING 搜索的优化：

col 因为其类型是结构化 OBJECT。
col:a 因为其类型是 INTEGER。
col:c 因为其类型是 MAP。
col:d 因为其类型是 ARRAY。

搜索优化服务可以优化使用以下函数的谓词：

您可以为列或一个列中的多个单独元素启用子字符串搜索优化。例如，以下语句可为列中的嵌套元素启用子字符串搜索优化：

ALTER TABLE test_table ADD SEARCH OPTIMIZATION ON SUBSTRING(col2:data.search);

Copy

构建搜索访问路径后，可优化以下查询：

SELECT * FROM test_table WHERE col2:data.search LIKE '%optimization%';

Copy

但是，以下查询未被优化，因为 WHERE 子句筛选器不适用于启用搜索优化时指定的元素 (col2:data.search)。

SELECT * FROM test_table WHERE col2:name LIKE '%simon%parker%';
SELECT * FROM test_table WHERE col2 LIKE '%hello%world%';

Copy

您可以指定多个要优化的元素。在以下示例中，为列 col2 中的两个特定元素启用了搜索优化：

ALTER TABLE test_table ADD SEARCH OPTIMIZATION ON SUBSTRING(col2:name);
ALTER TABLE test_table ADD SEARCH OPTIMIZATION ON SUBSTRING(col2:data.search);

Copy

如果为指定元素启用搜索优化，则文本字符串类型的任何未嵌套元素也会启用搜索优化。嵌套元素或非文本字符串类型的元素不会启用搜索优化。

结构化子字符串搜索中常量的计算方式¶

在查询中（例如 LIKE 'constant_string'）计算常量字符串时，搜索优化服务使用以下字符作为分隔符将字符串拆分为词元：

方括号（[ 和 ]）。
花括号（{ 和 }）。
冒号 (:)。
逗号 (,)。
双引号 (")。

将字符串拆分为词元后，搜索优化服务仅考虑长度至少为 5 个字符的词元。下表说明了搜索优化服务如何处理各种谓词示例：

谓词示例	搜索优化服务如何处理查询
`LIKE '%TEST%'`	搜索优化服务不对以下谓词使用搜索访问路径，因为子字符串少于五个字符：
`LIKE '%SEARCH%IS%OPTIMIZED%'`	搜索优化服务可以优化此查询，使用搜索访问路径搜索 `SEARCH` 和 `OPTIMIZED`，但不搜索 `IS`。`IS` 少于 5 个字符。
`LIKE '%HELLO_WORLD%'`	搜索优化服务可通过搜索访问路径搜索 `HELLO_WORLD` 来优化此查询。
`LIKE '%COL:ON:S:EVE:RYWH:ERE%'`	搜索优化服务将此字符串拆分为 `COL`、`ON`、`S`、`EVE`、`RYWH`、`ERE`。由于所有这些词元都短于五个字符，因此搜索优化服务无法优化此查询。
`LIKE '%{\"KEY01\":{\"KEY02\":\"value\"}%'`	搜索优化服务会将该字符串拆分为词元 `KEY01`、`KEY02`、`VALUE`，并在优化查询时使用这些词元。
`LIKE '%quo\"tes_and_com,mas,\"are_n\"ot\"_all,owed%'`	搜索优化服务将此字符串拆分为词元 `quo`、`tes_and_com`、`mas`、`are_n`、`ot`、`_all`、`owed`。在优化查询时，搜索优化服务只能使用长度为 5 个字符或更长的词元（`tes_and_com`、`are_n`）。

架构演化支持¶

结构化列的架构可以随时间演变。有关架构演化的详细信息，请参阅 ALTER ICEBERG TABLE ... ALTER COLUMN ... SET DATA TYPE（结构化类型）。

作为单次架构演化操作的一部分，可能会发生以下修改：

类型扩展
元素重新排序
添加元素
移除元素
重命名元素

搜索优化服务不会因架构演化操作而失效。相反，搜索优化服务通过以下方式处理操作：

类型扩展（例如，INT 到 NUMBER）

搜索优化访问路径不受影响。

添加元素

新添加的元素会自动在现有搜索优化访问路径中体现。

移除元素

从结构化列中移除元素时，搜索优化服务会自动删除以该元素为前缀的访问路径。

例如，创建一个包含 OBJECT 类型列的表，然后插入数据：

CREATE OR REPLACE TABLE test_struct (
  a OBJECT(
    b INTEGER,
    c OBJECT(
      d STRING,
      e VARIANT
      )
  )
);

INSERT INTO test_struct (a) SELECT
  {
    'b': 100,
    'c': {
        'd': 'value1',
        'e': 'value2'
  }
  }::OBJECT(
    b INTEGER,
    c OBJECT(
        d STRING,
        e VARIANT
    )
);

Copy

要查看数据，请查询该表：

SELECT * FROM test_struct;

Copy

+--------------------+
| A                  |
|--------------------|
| {                  |
|   "b": 100,        |
|   "c": {           |
|     "d": "value1", |
|     "e": "value2"  |
|   }                |
| }                  |
+--------------------+

以下语句从对象中移除元素 c：

ALTER TABLE test_struct ALTER COLUMN a
  SET DATA TYPE OBJECT(
    b INTEGER);

Copy

执行此语句时，位于 a、a:c、a:c:d 和 a:c:e 的访问路径将被删除。

重命名元素

重命名元素时，搜索优化服务会自动删除以该元素为前缀的访问路径，并用新名称重新添加这些路径。此操作会产生额外维护成本，用于处理搜索优化服务中新添加的路径。

例如，创建一个包含 OBJECT 类型列的表，然后插入数据：

CREATE OR REPLACE TABLE test_struct (
  a OBJECT(
    b INTEGER,
    c OBJECT(
      d STRING,
      e VARIANT
      )
  )
);

INSERT INTO test_struct (a) SELECT
  {
    'b': 100,
    'c': {
        'd': 'value1',
        'e': 'value2'
  }
  }::OBJECT(
    b INTEGER,
    c OBJECT(
        d STRING,
        e VARIANT
    )
);

Copy

要查看数据，请查询该表：

SELECT * FROM test_struct;

Copy

+--------------------+
| A                  |
|--------------------|
| {                  |
|   "b": 100,        |
|   "c": {           |
|     "d": "value1", |
|     "e": "value2"  |
|   }                |
| }                  |
+--------------------+

以下语句将对象中的元素 c 重命名为 c_new：

ALTER TABLE test_struct ALTER COLUMN a
  SET DATA TYPE OBJECT(
    b INTEGER,
    c_new OBJECT(
      d STRING,
      e VARIANT
    )
  ) RENAME FIELDS;

Copy

位于 a、a:c、a:c:d、a:c:e 的访问路径被删除，并重新添加为 a、a:c_new、a:c_new:d、a:c_new:e。

元素重新排序

搜索优化访问路径不受影响。

当前对半结构化类型支持的限制¶

搜索优化服务对半结构化类型的支持在以下方面有限：

不支持格式为 path_to_element IS NULL 的谓词。
不支持常量是标量子查询结果的谓词。
不支持指定包含子元素的元素路径的谓词。
使用 XMLGET 函数的谓词不受支持。

使用 MAP_CONTAINS_KEY 函数的谓词不受支持。

搜索优化服务的当前限制同样适用于半结构化类型。