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

搜索优化服务可以提高对 Snowflake 表（VARIANT、OBJECT 和 ARRAY 列 中的数据）中的半结构化数据进行点查找查询和子字符串查询的性能。

当为表中的列配置了对搜索优化服务的 VARIANT 支持时，搜索优化服务会自动在搜索访问路径中包含 VARIANT、OBJECT 和 ARRAY 列。这甚至适用于结构深度嵌套且结构频繁更改的列。您还可以为半结构化列中的特定字段启用搜索优化。

备注

必须使用 ON 子句为特定列或列中的字段显式启用此功能，该子句位于 ALTER TABLE ...ADD SEARCH OPTIMIZATION 命令中。（在表级别启用搜索优化不会为 VARIANT 列启用它。例如：

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);
Copy

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

以下各节提供有关此支持的更多详细信息：

VARIANT 类型谓词中针对常量和类型转换支持的数据类型

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

  • FIXED（包括指定有效精度和小数点后位数的类型转换）

  • INTEGER

  • TEXT

  • DATE（包括指定小数点后位数的类型转换）

  • TIME（包括指定小数点后位数的类型转换）

  • TIMESTAMP、TIMESTAMP_LTZ、TIMESTAMP_NTZ、TIMESTAMP_TZ（包括指定小数点后位数的类型转换）

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

对将 VARIANT 值类型转换为 TEXT 的支持

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

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

create or replace TABLE test_table
(
    ID INTEGER,
    SRC VARIANT
);

insert into test_table select 1, to_variant('true'::boolean);          -- BOOLEAN
insert into test_table select 2, to_variant('2020-01-09'::date);       -- DATE
insert into test_table select 3, to_variant('01:02:03.899213'::time);  -- TIME
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 = '01:02:03.899213';
Copy

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

搜索优化服务可以使用下面列出的谓词类型改进点查找查询。在下面的示例中， src 是 VARIANT 列， path_to_variant_field 是 :ref:` VARIANT 列中字段的路径 <label-traversing_semistructured_data>`。

  • 相等谓词的格式如下：

    where path_to_variant_field[::target_data_type] = constant

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

    例如，搜索优化服务支持：

    • 将元素与 NUMBER 常量进行比对，而不显式对元素进行类型转换。

      where src:person.age = 42;
      Copy

    • 以指定的精度和小数点后位数显式将元素类型转换为 NUMBER。

      where src:location.temperature::NUMBER(8, 6) = 23.456789;
      Copy

    • 将元素与 TEXT 常量进行比对，而不显式对元素进行类型转换。

      where src:sender_info.ip_address = '123.123.123.123';
      Copy

    • 显式将元素类型转换为 TEXT。

      where src:salesperson.name::TEXT = 'John Appleseed';
      Copy

    • 显式将元素类型转换为 DATE。

      where src:events.date::DATE = '2021-03-26';
      Copy

    • 使用指定小数点后位数显式将元素类型转换为 TIME。

      where src:events.time_info::TIME(6) = '01:02:03.456789';
      Copy

    • 使用指定小数点后位数显式将元素类型转换为 TIMESTAMP。

      where src:event_logs.exceptions.timestamp_info(3) = '2021-03-26 15:00:00.123 -0800';
      Copy

  • 使用 ARRAY 函数的谓词，例如：


    • where ARRAY_CONTAINS(constant::VARIANT, path_to_variant_field)

      在此语法中，constant 不能是 NULL，而且 constant 的数据类型必须是 支持的类型 之一。

      例如：

      where ARRAY_CONTAINS('77.146.211.88'::VARIANT, src:logs.ip_addresses)
      Copy

    • where ARRAYS_OVERLAP(ARRAY_CONSTRUCT(constant_1, constant_2, .., constant_N), path_to_variant_field)

      每个常量（constant_1、constant_2 等）的数据类型必须是 受支持的类型 之一。构造 ARRAY 可以包含 NULL 常量。

      例如：

      where ARRAYS_OVERLAP(
    ARRAY_CONSTRUCT('122.63.45.75', '89.206.83.107'), src:senders.ip_addresses)
      Copy

  • 以下谓词用于检查 NULL 值：

    • where IS_NULL_VALUE(path_to_variant_field)

      请注意， IS_NULL_VALUE 适用于 JSON null 值，而不适用于 SQL NULL 值。

    • where path_to_variant_field IS NOT NULL

    • where variant_column IS NULL

      其中 variant_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

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

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

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

  • 方括号（[ 和 ]）。

  • 花括号（{ 和 }）。

  • 冒号 (:)。

  • 逗号 (,)。

  • 双引号 (")。

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

谓词示例

搜索优化服务如何处理查询

LIKE '%TEST%'

搜索优化服务 不对以下谓词使用 搜索访问路径，因为子字符串短于 5 个字符。

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。由于所有这些词元都短于 5 个字符，因此搜索优化服务无法优化此查询。

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）。

对 VARIANT 类型的支持的当前限制

目前，对搜索优化服务中的 VARIANT 类型的支持具有以下限制：

  • 不支持使用 XMLGET 的谓词。

  • 不支持该 variant_field IS NULL 形式的谓词。

  • 不支持常量是标量子查询结果的谓词。

  • 不支持指定包含子元素的元素路径的谓词。

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

语言: 中文