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

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

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

为半结构化数据的查询启用搜索优化
半结构化类型的常量和谓词中的类型转换支持的数据类型
Support for semi-structured data type values cast to VARCHAR
支持的用于 VARIANT 类型点查找的谓词
VARIANT 类型中的子字符串搜索
当前对半结构化类型的支持存在的限制

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

为了提高表中半结构化数据的查询的性能，请为特定列或列元素使用 ON 子句，该子句位于 ALTER TABLE ...ADD SEARCH OPTIMIZATION 命令中。如果省略 ON 子句，则不会优化对 VARIANT、OBJECT 和 ARRAY 列的查询。在表级别启用搜索优化不会为半结构化数据类型的列启用搜索优化。

例如：

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

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

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

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

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

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

Support for semi-structured data type values cast to VARCHAR¶

The search optimization service can also improve the performance of point lookups in which columns with semi-structured data types are cast to VARCHAR and are compared to constants that are cast to VARCHAR.

例如，假设 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

For this table, the search optimization service can improve the following queries, which cast the VARIANT column to VARCHAR and compare the column to string constants:

SELECT * FROM test_table WHERE src::VARCHAR = 'true';
SELECT * FROM test_table WHERE src::VARCHAR = '2020-01-09';
SELECT * FROM test_table WHERE src::VARCHAR = '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
- Matching a VARIANT element against a VARCHAR constant without explicitly casting the element.
  WHERE src:sender_info.ip_address = '123.123.123.123';
  Copy
- Explicitly casting a VARIANT element to VARCHAR.
  WHERE src:salesperson.name::VARCHAR = '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 类型中的子字符串搜索¶

The search optimization service can optimize wildcard or regular expression searches in semi-structured columns --- that is, VARIANT, OBJECT, and ARRAY columns --- or elements in such columns.

The search optimization service can optimize predicates that use the following functions:

You can enable substring search optimization for a column or for multiple individual elements within a column. For example, the following statement enables substring search optimization for a nested element in a column:

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

Copy

After the search access path has been built, the following query can be optimized:

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

Copy

However, the following queries aren't optimized because the WHERE clause filters don't apply to the element that was specified when search optimization was enabled (col2:data.search):

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

Copy

You can specify multiple elements to be optimized. In the following example, search optimization is enabled for two specific elements in the column 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

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

For an example that enables FULL_TEXT search optimization on a VARIANT column in the car_sales table and its data, which is described in 查询半结构化数据, see 对 VARIANT 列启用 FULL_TEXT 搜索优化.

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

When it evaluates the constant string in a query --- for example, LIKE 'constant_string' --- the search optimization service splits the string into tokens by using the following characters as delimiters:

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

After it splits the string into tokens, the search optimization service considers only tokens that are at least five characters long. The following table explains how the search optimization service handles various predicate examples:

谓词示例	搜索优化服务如何处理查询
`LIKE '%TEST%'`	The search optimization service doesn't use search access paths for the following predicate because the substring is shorter than five characters.
`LIKE '%SEARCH%IS%OPTIMIZED%'`	The search optimization service can optimize this query, by using search access paths to search for `SEARCH` and `OPTIMIZED` but not `IS`. `IS` is shorter than five characters.
`LIKE '%HELLO_WORLD%'`	The search optimization service can optimize this query, by using search access paths to search for `HELLO_WORLD`.
`LIKE '%COL:ON:S:EVE:RYWH:ERE%'`	搜索优化服务将此字符串拆分为 `COL`、`ON`、`S`、`EVE`、`RYWH`、`ERE`。由于所有这些词元都短于五个字符，因此搜索优化服务无法优化此查询。
`LIKE '%{\"KEY01\":{\"KEY02\":\"value\"}%'`	The search optimization service splits this string into the tokens `KEY01`, `KEY02`, `VALUE` and uses the tokens when it optimizes the query.
`LIKE '%quo\"tes_and_com,mas,\"are_n\"ot\"_all,owed%'`	The search optimization service splits this string into the tokens `quo`, `tes_and_com`, `mas`, `are_n`, `ot`, `_all`, `owed`. The search optimization service can only use the tokens that are five characters or longer (`tes_and_com`, `are_n`) when it optimizes the query.

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

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

不支持格式为 path_to_element IS NULL 的谓词。
不支持常量是标量子查询结果的谓词。
不支持指定包含子元素的元素路径的谓词。
Predicates that use the XMLGET function aren't supported.

The current limitations of the search optimization service also apply to semi-structured types.