Accélérer les requêtes de données semi-structurées grâce à l’optimisation de la recherche¶

Le service d’optimisation de la recherche peut améliorer les performances des recherches ponctuelles et des requêtes de sous-chaînes sur des données semi-structurées dans des tables Snowflake (données dans les colonnes VARIANT, OBJECT et ARRAY).

Lorsque la prise en charge de VARIANT du service d’optimisation des recherches est configuré pour les colonnes d’une table, le service d’optimisation des recherches inclut automatiquement les colonnes VARIANT, OBJECT et ARRAY dans un chemin d’accès de recherche. Cela s’applique même aux colonnes où la structure est profondément imbriquée et où la structure change fréquemment. Vous pouvez également activer l’optimisation de la recherche pour des champs spécifiques dans une colonne semi-structurée.

Note

Vous devez activer de façon explicite cette fonctionnalité pour des colonnes ou des champs spécifiques dans des colonnes à l’aide de la clause ON dans la commande ALTER TABLE … ADD SEARCH OPTIMIZATION. (L’activation de l’optimisation de la recherche au niveau de la table ne l’active pas pour les colonnes VARIANT.) Par exemple :

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

Les requêtes sur une colonne VARIANT ne sont pas optimisées si vous omettez la clause ON.

Les sections suivantes fournissent plus de détails sur cette prise en charge :

Types de données pris en charge pour les constantes et les conversions dans les prédicats pour les types de VARIANT
Prise en charge de la conversion de valeurs VARIANT en TEXT
Prédicats pris en charge pour les recherches ponctuelles sur les types VARIANT
Recherche par sous-chaîne dans les types VARIANT
Limites actuelles de la prise en charge pour les types de VARIANT

Types de données pris en charge pour les constantes et les conversions dans les prédicats pour les types de VARIANT¶

Le service d’optimisation de la recherche peut améliorer les performances des recherches ponctuelles de données semi-structurées où les types suivants sont utilisés pour la constante et la conversion implicite ou explicite pour l’élément :

FIXED (y compris des conversions qui spécifient une précision et une échelle valides)
INTEGER
TEXT
DATE (y compris les conversions qui précisent une échelle)
TIME (y compris les conversions qui précisent une échelle)
TIMESTAMP, TIMESTAMP_LTZ, TIMESTAMP_NTZ, TIMESTAMP_TZ (y compris les conversions qui spécifient une échelle)

Le service d’optimisation des recherches prend en charge la conversion des types en utilisant :

Prise en charge de la conversion de valeurs VARIANT en TEXT¶

Le service d’optimisation de la recherche peut également améliorer les performances des recherches ponctuelles dans lesquelles les colonnes VARIANT sont converties en TEXT et sont comparées à des constantes converties en TEXT.

Par exemple, supposons que src est une colonne VARIANT contenant des valeurs BOOLEAN, DATE et TIME qui ont été converties en 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

Pour cette table, le service d’optimisation des recherches peut améliorer les requêtes suivantes, qui convertissent la colonne VARIANT en TEXT et comparent la colonne à des constantes de type chaîne :

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

Prédicats pris en charge pour les recherches ponctuelles sur les types VARIANT¶

Le service d’optimisation de la recherche peut optimiser les recherches ponctuelles avec les types de prédicats énumérés ci-dessous. Dans les exemples ci-dessous, src est la colonne VARIANT, et path_to_variant_field est un chemin d’accès à un champ de la colonne VARIANT.

Prédicats d’égalité de la forme suivante :

where path_to_variant_field[::target_data_type] = constant

Dans cette syntaxe, target_data_type (s’il est spécifié) et le type de données de constant doivent être l’un des types pris en charge.

Par exemple, le service d’optimisation de la recherche prend en charge les points suivants :
- Mise en correspondance d’un élément avec une constante NUMBER sans convertir explicitement l’élément.
  where src:person.age = 42;
  Copy
- Conversion explicite d’un élément en NUMBER avec une précision et une échelle spécifiées.
  where src:location.temperature::NUMBER(8, 6) = 23.456789;
  Copy
- Mise en correspondance d’un élément avec une constante TEXT sans convertir explicitement l’élément.
  where src:sender_info.ip_address = '123.123.123.123';
  Copy
- Conversion explicite d’un élément en TEXT.
  where src:salesperson.name::TEXT = 'John Appleseed';
  Copy
- Conversion explicite d’un élément en DATE.
  where src:events.date::DATE = '2021-03-26';
  Copy
- Conversion explicite d’un élément en TIME avec une échelle spécifiée.
  where src:events.time_info::TIME(6) = '01:02:03.456789';
  Copy
- Conversion explicite d’un élément en TIMESTAMP avec une échelle spécifiée.
  where src:event_logs.exceptions.timestamp_info(3) = '2021-03-26 15:00:00.123 -0800';
  Copy
Les prédicats qui utilisent les fonctions ARRAY, tels que :
- where ARRAY_CONTAINS(constant::VARIANT, path_to_variant_field)
  
  Dans cette syntaxe, constant ne doit pas être NULL, et le type de données de constant doit être l’un des types pris en charge.
  
  Par exemple :
  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)
  
  Le type de données de chaque constante (constant_1, constant_2, etc.) doit être l’un des types pris en charge. L’ARRAY construit peut inclure des constantes NULL.
  
  Par exemple :
  where ARRAYS_OVERLAP( ARRAY_CONSTRUCT('122.63.45.75', '89.206.83.107'), src:senders.ip_addresses)
  Copy
Les prédicats suivants qui recherchent la présence de valeurs NULL :
- where IS_NULL_VALUE(path_to_variant_field)
  
  Notez que IS_NULL_VALUE s’applique aux valeurs nulles JSON et non aux valeurs SQL NULL.
- where path_to_variant_field IS NOT NULL
- where variant_column IS NULL
  
  où variant_column fait référence à la colonne et non à un chemin vers un élément des données semi-structurées.
  
  Par exemple, le service d’optimisation des recherches prend en charge l’utilisation de la colonne VARIANT src mais pas le chemin d’accès au champ src:person.age dans cette colonne VARIANT.

Recherche par sous-chaîne dans les types VARIANT¶

Le service d’optimisation de la recherche peut optimiser les recherches par caractères génériques ou expressions régulières dans les colonnes semi-structurées (y compris les colonnes ARRAY, OBJECT et VARIANT) ou les champs de ces colonnes. Cela inclut les prédicats qui utilisent :

Vous pouvez activer l’optimisation de la recherche de sous-chaîne pour une colonne ou pour plusieurs champs individuels au sein d’une colonne. Par exemple, l’instruction suivante permet d’optimiser la recherche de sous-chaîne pour un champ imbriqué dans une colonne.

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

Copy

Une fois le chemin à la recherche construit, la requête suivante peut être optimisée :

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

Copy

Cependant, les requêtes suivantes ne sont pas optimisées, car les filtres de la clause WHERE ne s’appliquent pas au champ spécifié lors de l’activation de l’optimisation de la recherche (col2:data.search).

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

Copy

Vous pouvez spécifier plusieurs champs à optimiser. Ici, l’optimisation de la recherche est activée pour deux champs spécifiques dans la colonne 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

Si vous activez l’optimisation de la recherche pour un champ donné, elle est activée pour tous les sous-champs. La deuxième instruction ALTER TABLE ci-dessous est redondante, car la première instruction permet d’optimiser la recherche pour l’ensemble du champ data , y compris le champ imbriqué 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

De même, l’activation de l’optimisation de la recherche pour une colonne entière permet d’optimiser toutes les recherches de sous-chaînes sur cette colonne, y compris les champs imbriqués à n’importe quelle profondeur à l’intérieur de celle-ci.

Comment les constantes sont évaluées pour les recherches de sous-chaînes VARIANT¶

Lors de l’évaluation de la chaîne constante dans une requête (par exemple LIKE 'constant_string'), le service d’optimisation de la recherche divise la chaîne en jetons en utilisant les caractères suivants comme délimiteurs :

Crochets ([ et ]).
Accolades ({ et }).
Deux points (:).
Virgules (,).
Guillemets doubles (").

Après avoir divisé la chaîne en jetons, le service d’optimisation de la recherche ne prend en compte que les jetons d’au moins 5 caractères.

Exemple de prédicat	Comment le service d’optimisation de la recherche traite la requête
`LIKE '%TEST%'`	Le service d’optimisation de la recherche n’utilise pas les chemins d’accès à la recherche pour le prédicat suivant, car la sous-chaîne fait moins de cinq caractères :
`LIKE '%SEARCH%IS%OPTIMIZED%'`	Le service d’optimisation de la recherche peut optimiser cette requête, en utilisant les chemins d’accès pour rechercher `SEARCH` et `OPTIMIZED` mais pas `IS`. `IS` fait moins de cinq caractères.
`LIKE '%HELLO_WORLD%'`	Le service d’optimisation de la recherche peut optimiser cette requête, en utilisant les chemins d’accès à la recherche pour `HELLO_WORLD`.
`LIKE '%COL:ON:S:EVE:RYWH:ERE%'`	Le service d’optimisation de la recherche divise cette chaîne en `COL`, `ON`, `S`, `EVE`, `RYWH`, `ERE`. Comme tous ces jetons sont inférieurs à cinq caractères, le service d’optimisation de la recherche ne peut pas optimiser cette requête.
`LIKE '%{\"KEY01\":{\"KEY02\":\"value\"}%'`	Le service d’optimisation de la recherche divise cette requête en jetons `KEY01`, `KEY02`, `VALUE` et utilise ces jetons lors de l’optimisation de la requête.
`LIKE '%quo\"tes_and_com,mas,\"are_n\"ot\"_all,owed%'`	Le service d’optimisation de la recherche divise cette chaîne en jetons `quo`, `tes_and_com`, `mas`, `are_n`, `ot`, `_all`, `owed`. Le service d’optimisation de la recherche ne peut utiliser que les jetons de cinq caractères ou plus (`tes_and_com`, `are_n`) lors de l’optimisation de la requête.

Limites actuelles de la prise en charge pour les types de VARIANT¶

Actuellement, la prise en charge des types VARIANT dans le service d’optimisation des recherches présente les limitations suivantes :

Les prédicats qui utilisent XMLGET ne sont pas pris en charge.
Les prédicats de la forme variant_field IS NULL ne sont pas pris en charge.
Les prédicats où les constantes sont des résultats de sous-requêtes scalaires ne sont pas pris en charge.
Les prédicats qui spécifient des chemins vers des éléments qui contiennent des sous-éléments ne sont pas pris en charge.

Les limites actuelles du service d’optimisation de la recherche s’appliquent également à cette fonction.