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

O serviço de otimização de pesquisa pode melhorar o desempenho das consultas de pesquisa de pontos e de substring em dados semiestruturados nas tabelas Snowflake (ou seja, dados nas colunas VARIANT, OBJECT e ARRAY). É possível configurar a otimização de pesquisa em colunas desses tipos, mesmo quando a estrutura está profundamente aninhada e muda com frequência. Você também pode ativar a otimização de pesquisa para elementos específicos em uma coluna semiestruturada.

As seções a seguir fornecem mais informações sobre o suporte de otimização de pesquisa para consultas de dados semiestruturados:

Como ativar a otimização de pesquisa para consultas de dados semiestruturados

Para melhorar o desempenho das consultas de dados semiestruturados em uma tabela, use a cláusula ON no comando ALTER TABLE … ADD SEARCH OPTIMIZATION para colunas específicas ou elementos em colunas. As consultas às colunas VARIANT, OBJECT e ARRAY não serão otimizadas se você omitir a cláusula ON. A ativação da otimização de pesquisa no nível da tabela não a habilita para colunas com tipos de dados semiestruturados.

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

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

Para obter mais informações, consulte Habilitação e desabilitação da otimização de pesquisa.

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

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 (incluindo tipos de sinônimos)

  • VARCHAR (incluindo tipos de sinônimos)

  • 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 a valores de tipos de dados semiestruturados convertidos em VARCHAR

O serviço de otimização de pesquisa também pode melhorar o desempenho de pesquisas pontuais nas quais as colunas com tipos de dados semiestruturados são convertidas para VARCHAR e comparadas a constantes convertidas para VARCHAR.

Por exemplo, suponha que src seja uma coluna VARIANT contendo valores BOOLEAN, DATE e TIMESTAMP 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);
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);

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

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';

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 com um tipo de dados semiestruturados e path_to_element é um caminho para um elemento na coluna com um tipo de dados semiestruturados.

  • Predicados de igualdade da seguinte forma:

    WHERE path_to_element[::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:

    • Correspondência de um elemento VARIANT com uma constante NUMBER sem converter explicitamente o elemento.

      WHERE src:person.age = 42;

    • Transmissão explícita de um elemento VARIANT para NUMBER com precisão e escala especificadas.

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

    • Correspondência de um elemento VARIANT com uma constante VARCHAR sem converter explicitamente o elemento.

      WHERE src:sender_info.ip_address = '123.123.123.123';

    • Conversão explícita de um elemento VARIANT para VARCHAR.

      WHERE src:salesperson.name::VARCHAR = 'John Appleseed';

    • Conversão explícita de um elemento VARIANT para DATE.

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

    • Conversão explícita de um elemento VARIANT em TIMESTAMP com uma escala especificada.

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

    • Correspondência de um elemento ARRAY com um valor de um tipo compatível, com ou sem conversão explícita para o tipo. Por exemplo:

      WHERE my_array_column[2] = 5;

WHERE my_array_column[2]::NUMBER(4, 1) = 5;

    • Correspondência de um elemento OBJECT com um valor de um tipo compatível, com ou sem conversão explícita para o tipo. Por exemplo:

      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;

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

    • WHERE ARRAY_CONTAINS(value_expr, array)

      Nessa sintaxe, value_expr não pode ser NULL e deve ser avaliado como VARIANT. O tipo de dados do valor deve ser um dos tipos compatíveis.

      Por exemplo:

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

      Neste exemplo, o valor é uma constante que é implicitamente convertida em VARIANT:

      WHERE ARRAY_CONTAINS(300, my_array_column)

    • WHERE ARRAYS_OVERLAP(ARRAY_CONSTRUCT(constant_1, constant_2, .., constant_N), array)

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

      Neste exemplo, a matriz está em um valor VARIANT:

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

      Neste exemplo, a matriz é uma coluna ARRAY:

      WHERE ARRAYS_OVERLAP(
  ARRAY_CONSTRUCT('a', 'b'), my_array_column)

  • Os seguintes predicados que verificam valores NULL:

    • WHERE IS_NULL_VALUE(path_to_element)

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

    • WHERE path_to_element IS NOT NULL

    • WHERE semistructured_column IS NULL

      em que semistructured_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 aceita o uso da coluna VARIANT src, mas não o caminho para o elemento src:person.age nessa coluna VARIANT.

Pesquisa de subcadeia de caracteres em tipos VARIANT

O serviço de otimização de pesquisa pode otimizar as pesquisas com curingas ou expressões regulares em colunas semiestruturadas, ou seja, colunas VARIANT, OBJECT e ARRAY, ou elementos nessas colunas.

O serviço de otimização de pesquisa pode otimizar predicados que utilizam as seguintes funções:

É possível ativar a otimização de pesquisa de substring para uma coluna ou para vários elementos individuais em uma coluna. Por exemplo, a instrução a seguir permite a otimização de pesquisa de substring para um elemento aninhado em uma coluna:

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

Após a criação do caminho de acesso de pesquisa, a seguinte consulta poderá ser otimizada:

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

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

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

É possível especificar vários elementos a serem otimizados. No exemplo a seguir, a otimização de pesquisa está habilitada para dois elementos 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);

Se você ativar a otimização de pesquisa para um determinado elemento, ela será ativada para todos os elementos aninhados. A segunda instrução ALTER TABLE abaixo é redundante porque a primeira instrução permite a otimização de pesquisa para todo o elemento data, incluindo o elemento 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);

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

Para ver um exemplo que habilita a otimização de pesquisa FULL_TEXT em uma coluna VARIANT na tabela car_sales e seus dados, descritos em Consulta de dados semiestruturados, consulte Habilitação da otimização de pesquisa FULL_TEXT em uma coluna VARIANT.

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

Quando avalia 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 os tokens com pelo menos cinco caracteres. A tabela a seguir explica como o serviço de otimização de pesquisa lida com vários exemplos de predicado:

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 usa os caminhos de acesso de pesquisa para o predicado a seguir porque a substring é menor que cinco caracteres.

LIKE '%SEARCH%IS%OPTIMIZED%'

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

LIKE '%HELLO_WORLD%'

O serviço de otimização de pesquisa pode otimizar essa consulta usando caminhos de acesso de pesquisa para pesquisar por 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 cinco caracteres, o serviço de otimização de pesquisa não pode otimizar essa consulta.

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

O serviço de otimização de pesquisa a divide 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 os tokens com cinco caracteres ou mais (tes_and_com, are_n) ao otimizar a consulta.

Limitações atuais no suporte a tipos semiestruturados

O suporte a tipos semiestruturados no serviço de otimização de pesquisa é limitado das seguintes maneiras:

  • Os predicados do formulário path_to_element IS NULL não são compatíveis.

  • Predicados em que as constantes são resultados de subconsultas escalares não são compatíveis.

  • Predicados que especificam caminhos para elementos que contêm subelementos não são compatíveis.

  • Predicados que usam a função XMLGET não são compatíveis.

As limitações atuais do serviço de otimização de pesquisa também se aplicam aos tipos semiestruturados.