Aceleração de consultas de dados semiestruturados com otimização de pesquisa

O serviço de otimização de pesquisa pode melhorar o desempenho de consultas de pesquisas pontuais e de subcadeia de caracteres em dados semiestruturados de tabelas do Snowflake (dados em colunas VARIANT, OBJECT e ARRAY).

Quando o suporte a VARIANT para o serviço de otimização de pesquisa é configurado para colunas em uma tabela, o serviço de otimização de pesquisa inclui automaticamente colunas VARIANT, OBJECT e ARRAY em um caminho de acesso de pesquisa. Isso se aplica até mesmo a colunas em que a estrutura está profundamente aninhada e em que a estrutura muda frequentemente. Você também pode ativar a otimização de pesquisa para campos específicos em uma coluna semiestruturada.

Nota

Você deve habilitar explicitamente este recurso para colunas ou campos específicos em colunas usando a cláusula ON no comando ALTER TABLE … ADD SEARCH OPTIMIZATION. (Ativar a otimização de pesquisa no nível da tabela não a ativa para colunas VARIANT.) Por exemplo:

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

Consultas em uma coluna VARIANT não são otimizadas se você omitir a cláusula ON.

As próximas seções fornecem mais detalhes sobre esse suporte:

Tipos de dados compatíveis para constantes e conversões em predicados para tipos de VARIANT

O serviço de otimização de pesquisa pode melhorar o desempenho de pesquisas pontuais de dados semiestruturados em que os seguintes tipos são usados para a constante e para a conversão implícita ou explícita do elemento:

  • FIXED (incluindo conversões que especificam uma precisão e escala válidas)

  • INTEGER

  • TEXT

  • DATE (incluindo conversões que especificam uma escala)

  • TIME (incluindo conversões que especificam uma escala)

  • TIMESTAMP, TIMESTAMP_LTZ, TIMESTAMP_NTZ, TIMESTAMP_TZ (incluindo conversões que especificam uma escala)

O serviço de otimização de pesquisa oferece suporte à conversão de tipos utilizando:

Suporte para valores de VARIANT convertidos em TEXT

O serviço de otimização de pesquisa também pode melhorar o desempenho das pesquisas pontuais nas quais as colunas VARIANT são convertidas em TEXT e são comparadas às constantes que são convertidas em TEXT.

Por exemplo, suponha que src seja uma coluna VARIANT contendo valores BOOLEAN, DATE e TIME que foram convertidos em VARIANT:

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

Para essa tabela, o serviço de otimização de pesquisa pode melhorar as seguintes consultas, que convertem a coluna VARIANT em TEXT e comparam a coluna com as constantes de cadeia de caracteres:

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

Predicados suportados para pesquisas pontuais em tipos VARIANT

O serviço de otimização de pesquisa pode melhorar as consultas de pesquisas pontuais com os tipos de predicado listados abaixo. Nos exemplos abaixo, src é a coluna VARIANT e path_to_variant_field é um caminho para um campo na coluna VARIANT.

  • Predicados de igualdade da seguinte forma:

    where path_to_variant_field[::target_data_type] = constant

    Nesta sintaxe, target_data_type (se especificado) e o tipo de dados de constant deve ser um dos tipos suportados.

    Por exemplo, o serviço de otimização de pesquisa oferece suporte para:

    • Corresponder um elemento com uma constante NUMBER sem converter explicitamente o elemento.

      where src:person.age = 42;
      Copy

    • Converter explicitamente um elemento em NUMBER com uma precisão e escala especificadas.

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

    • Corresponder um elemento com uma constante TEXT sem converter explicitamente o elemento.

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

    • Converter explicitamente um elemento em TEXT.

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

    • Converter explicitamente um elemento em DATE.

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

    • Converter explicitamente um elemento em TIME com uma escala especificada.

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

    • Converter explicitamente um elemento em TIMESTAMP com uma escala especificada.

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

  • Predicados que utilizam as funções ARRAY, como:


    • where ARRAY_CONTAINS(constant::VARIANT, path_to_variant_field)

      Nesta sintaxe, constant não deve ser NULL e o tipo de dados de constant deve ser um dos tipos compatíveis.

      Por exemplo:

      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)

      O tipo de dados de cada constante (constant_1, constant_2 etc.) deve ser um dos tipos compatíveis. O ARRAY construído pode incluir constantes NULL.

      Por exemplo:

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

  • Os seguintes predicados que verificam valores NULL:

    • where IS_NULL_VALUE(path_to_variant_field)

      Note que IS_NULL_VALUE se aplica a valores JSON null, e não a valores SQL NULL.

    • where path_to_variant_field IS NOT NULL

    • where variant_column IS NULL

      em que variant_column se refere à coluna, e não a um caminho para um elemento nos dados semiestruturados.

      Por exemplo, o serviço de otimização de pesquisa oferece suporte ao uso da coluna VARIANT src, mas não ao caminho para o campo src:person.age naquela coluna VARIANT.

Pesquisa de subcadeia de caracteres em tipos VARIANT

O serviço de otimização de pesquisa pode otimizar pesquisas com caracteres curinga ou expressões regulares em colunas semiestruturadas (incluindo colunas ARRAY, OBJECT e VARIANT) ou campos nessas colunas. Isso inclui os predicados que utilizam:

Você pode ativar a otimização de pesquisa de subcadeia de caracteres para uma coluna ou para vários campos individuais em uma coluna. Por exemplo, a instrução a seguir permite a otimização de pesquisa de subcadeia de caracteres para um campo aninhado em uma coluna.

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

Uma vez criado o caminho de acesso à pesquisa, a seguinte consulta pode ser otimizada:

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

No entanto, as consultas a seguir não são otimizadas porque os filtros da cláusula WHERE não se aplicam ao campo especificado quando a otimização de pesquisa foi ativada (col2:data.search).

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

Você pode especificar vários campos a serem otimizados. Aqui, a otimização de pesquisa está habilitada para dois campos específicos na coluna 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

Se você ativar a otimização de pesquisa para um determinado campo, ela será ativada para todos os subcampos. A segunda instrução ALTER TABLE abaixo é redundante porque a primeira instrução permite a otimização de pesquisa para todo o campo data, incluindo o campo search aninhado.

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

Da mesma forma, habilitar a otimização de pesquisa para uma coluna inteira permite que todas as pesquisas de subcadeia de caracteres nessa coluna sejam otimizadas, incluindo campos aninhados em qualquer profundidade dentro dela.

Como as constantes são avaliadas para pesquisas de cadeia de caracteres VARIANT

Ao avaliar a cadeia de caracteres constante em uma consulta (por exemplo, LIKE 'constant_string'), o serviço de otimização de pesquisa divide a cadeia de caracteres em tokens usando os seguintes caracteres como delimitadores:

  • Colchetes ([ e ]).

  • Chaves ({ e }).

  • Dois pontos (:).

  • Vírgulas (,).

  • Aspas duplas (").

Depois de dividir a cadeia de caracteres em tokens, o serviço de otimização de pesquisa considera apenas tokens com pelo menos 5 caracteres.

Exemplo de um predicado

Como o serviço de otimização de pesquisa trata a consulta

LIKE '%TEST%'

O serviço de otimização de pesquisa não utiliza caminhos de acesso de pesquisa para os seguintes predicados porque a subcadeia de caracteres tem menos de 5 caracteres.

LIKE '%SEARCH%IS%OPTIMIZED%'

O serviço de otimização de pesquisa pode otimizar essa consulta usando caminhos de acesso de pesquisa para procurar SEARCH e OPTIMIZED, mas não IS. IS tem menos de 5 caracteres.

LIKE '%HELLO_WORLD%'

O serviço de otimização de pesquisa pode otimizar essa consulta usando caminhos de acesso de pesquisa para HELLO_WORLD.

LIKE '%COL:ON:S:EVE:RYWH:ERE%'

O serviço de otimização de pesquisa divide essa cadeia de caracteres em COL, ON, S, EVE, RYWH, ERE. Como todos esses tokens têm menos de 5 caracteres, o serviço de otimização de pesquisa não pode otimizar esta consulta.

LIKE '%{\"KEY01\":{\"KEY02\":\"value\"}%'

O serviço de otimização de pesquisa divide isso em tokens KEY01, KEY02, VALUE e usa os tokens ao otimizar a consulta.

LIKE '%quo\"tes_and_com,mas,\"are_n\"ot\"_all,owed%'

O serviço de otimização de pesquisa divide essa cadeia de caracteres em nos tokens quo, tes_and_com, mas, are_n, ot, _all, owed. O serviço de otimização de pesquisa só pode usar tokens com 5 caracteres ou mais (tes_and_com, are_n) ao otimizar a consulta.

Limitações atuais no suporte para tipos de VARIANT

Atualmente, o suporte para tipos de VARIANT no serviço de otimização de pesquisa tem as seguintes limitações:

  • Predicados que utilizam XMLGET não têm suporte.

  • Predicados da forma variant_field IS NULL não têm suporte.

  • Predicados em que as constantes são resultados de subconsultas escalares não têm suporte.

  • Predicados que especificam caminhos para elementos que contêm subelementos não têm suporte.

As limitações atuais do serviço de otimização de pesquisa também se aplicam a esse recurso.

Linguagem: Português