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

搜索优化服务可以提高对 Snowflake 表中的半结构化数据（即 VARIANT、OBJECT 和 ARRAY 列中的数据）进行点查找和子字符串查询的性能。即使结构深度嵌套且经常更改，您也可以对这些类型的列配置搜索优化。您还可以对半结构化列中的特定元素启用搜索优化。

备注

必须使用 ON 子句为特定列或列中的元素显式启用此功能，该子句位于 ALTER TABLE ...ADD SEARCH OPTIMIZATION 命令中。在表级别启用搜索优化不会为半结构化数据类型的列启用搜索优化。例如：

ALTER TABLE mytable ADD SEARCH OPTIMIZATION ON EQUALITY(myvariantcol);
ALTER TABLE t1 ADD SEARCH OPTIMIZATION ON EQUALITY(c4:user.uuid);

ALTER TABLE mytable ADD SEARCH OPTIMIZATION ON SUBSTRING(myvariantcol);
ALTER TABLE t1 ADD SEARCH OPTIMIZATION ON SUBSTRING(c4:user.uuid);

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

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

Copy

如果省略 ON 子句，则不会优化对 VARIANT、OBJECT 和 ARRAY 列的查询。

以下部分提供了有关此支持的更多信息：

半结构化类型的常量和谓词中的类型转换支持的数据类型
支持半结构化数据类型值转换为 TEXT
支持的用于 VARIANT 类型点查找的谓词
VARIANT 类型中的子字符串搜索
当前对半结构化类型的支持存在的限制

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

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

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

搜索优化服务支持使用以下元素的类型转换类型：

支持半结构化数据类型值转换为 TEXT¶

搜索优化服务还可以提高点查找的性能，其中，半结构化数据类型的列的类型转换为 TEXT，并与类型转换为 TEXT 的常量进行比较。

例如，假设 src 是 VARIANT 列，其中包含已转换为 VARIANT 的 BOOLEAN、DATE 和 TIMESTAMP 值：

CREATE OR REPLACE TABLE test_table
(
  id INTEGER,
  src VARIANT
);

INSERT INTO test_table SELECT 1, TO_VARIANT('true'::BOOLEAN);
INSERT INTO test_table SELECT 2, TO_VARIANT('2020-01-09'::DATE);
INSERT INTO test_table SELECT 3, TO_VARIANT('2020-01-09 01:02:03.899'::TIMESTAMP);

Copy

对于此表，搜索优化服务可以改进以下查询，这些查询将列 VARIANT 转换为 TEXT,并将该列与字符串常量进行比较：

SELECT * FROM test_table WHERE src::TEXT = 'true';
SELECT * FROM test_table WHERE src::TEXT = '2020-01-09';
SELECT * FROM test_table WHERE src::TEXT = '2020-01-09 01:02:03.899';

Copy

支持的用于 VARIANT 类型点查找的谓词¶

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

相等谓词的格式如下：

WHERE path_to_element[::target_data_type] = constant

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

例如，搜索优化服务支持：
- 将 VARIANT 元素与 NUMBER 常量进行匹配，而不显式对元素进行类型转换。
  WHERE src:person.age = 42;
  Copy
- 以指定的精度和小数点后位数显式将 VARIANT 元素类型转换为 NUMBER。
  WHERE src:location.temperature::NUMBER(8, 6) = 23.456789;
  Copy
- 将 VARIANT 元素与 TEXT 常量进行匹配，而不显式对元素进行类型转换。
  WHERE src:sender_info.ip_address = '123.123.123.123';
  Copy
- 显式将 VARIANT 元素类型转换为 TEXT。
  WHERE src:salesperson.name::TEXT = 'John Appleseed';
  Copy
- 显式将 VARIANT 元素类型转换为 DATE。
  WHERE src:events.date::DATE = '2021-03-26';
  Copy
- 使用指定小数点后位数显式将 VARIANT 元素类型转换为 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 元素与支持的类型的值进行匹配，无论是否显式类型转换为相应类型。例如：
  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
  在以下示例中，该值是一个隐式类型转换为 VARIANT 的常量：
  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 常量。
  
  在以下示例中，数组位于 VARIANT 值中：
  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 semistructured_column IS NULL
  
  其中 semistructured_column 指的是列而不是半结构化数据中元素的路径。
  
  例如，搜索优化服务支持使用 VARIANT 列 src，但不支持使用该 VARIANT 列中元素 src:person.age 的路径。

VARIANT 类型中的子字符串搜索¶

搜索优化服务可以优化半结构化列。这包括使用以下元素的谓词：

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

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

如果为给定元素启用搜索优化，则会为任何嵌套元素启用该选项。下面的第二个 ALTER TABLE 语句是多余的，因为第一个语句启用了整个 data 元素（包括嵌套 search 元素）的搜索优化。

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

Copy

同样，为整个列启用搜索优化可以优化该列上的所有子字符串搜索，包括嵌套到其中任何深度的元素。

有关对 car_sales 表中的 VARIANT 列及其数据（查询半结构化数据中所述）启用 FULL_TEXT 搜索优化的示例，请参阅对 VARIANT 列启用 FULL_TEXT 搜索优化。

如何计算 VARIANT 子字符串搜索的常量¶

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

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

将字符串拆分为词元后，搜索优化服务仅考虑长度至少为五个字符的词元。

谓词示例	搜索优化服务如何处理查询
`LIKE '%TEST%'`	搜索优化服务不对以下谓词使用搜索访问路径，因为子字符串短于五个字符。
`LIKE '%SEARCH%IS%OPTIMIZED%'`	搜索优化服务可以优化此查询，使用搜索访问路径来搜索 `SEARCH` 和 `OPTIMIZED`，但不搜索 `IS`。`IS` 长度小于五个字符。
`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`。在优化查询时，搜索优化服务只能使用五个字符或更长的词元（`tes_and_com`、`are_n`）。

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

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

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

搜索优化服务的当前限制也适用于此功能。