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.

Nota

É necessário habilitar explicitamente esse recurso para colunas ou elementos específicos em colunas usando a cláusula ON no comando ALTER TABLE … ADD SEARCH OPTIMIZATION. 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);
Copy

As consultas às colunas VARIANT, OBJECT e ARRAY não serão otimizadas se você omitir a cláusula ON.

As seções a seguir fornecem mais informações sobre esse suporte:

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

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

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 TEXT e comparadas a constantes convertidas para TEXT.

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);
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 = '2020-01-09 01:02:03.899';
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 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;
      Copy

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

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

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

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

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

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

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

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

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

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

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

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

      WHERE ARRAY_CONTAINS(300, my_array_column)
      Copy

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

      Neste exemplo, a matriz é uma coluna ARRAY:

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

  • 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 buscas com curingas ou expressões regulares em colunas semiestruturadas (ou seja, colunas VARIANT, OBJECT e ARRAY) ou elementos nessas colunas. Isso inclui os predicados que utilizam:

É 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);
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 elemento que foi 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

É possível especificar vários elementos a serem otimizados. Aqui, a otimização de pesquisa está ativada 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);
Copy

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

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 permite 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

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 (").

Após dividir a cadeia de caracteres em tokens, o serviço de otimização de pesquisa considera apenas os tokens com pelo menos cinco 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 usa os caminhos de acesso de pesquisa para o seguinte predicado 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 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 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 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 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:

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

  • 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.

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

Linguagem: Português