Beschleunigen von Abfragen auf semistrukturierten Daten durch die Suchoptimierung

Der Suchoptimierungsdienst kann die Leistung von Punktsuchabfragen und Abfragen mit Teilzeichenfolgen auf semistrukturierten Daten von Snowflake-Tabellen (Daten in VARIANT-, OBJECT- und ARRAY-Spalten) verbessern.

Wenn bei Spalten einer Tabelle die VARIANT-Unterstützung für den Suchoptimierungsdienst konfiguriert ist, schließt der Suchoptimierungsdienst automatisch VARIANT-, OBJECT- und ARRAY-Spalten in einen Suchzugriffspfad ein. Dies gilt sogar für Spalten, deren Struktur tief verschachtelt ist und sich häufig ändert. Sie können die Suchoptimierung auch für bestimmte Felder innerhalb einer semistrukturierten Spalte aktivieren.

Bemerkung

Sie müssen dieses Feature explizit für bestimmte Spalten oder bestimmte Felder in Spalten aktivieren, indem Sie die ON-Klausel im Befehl ALTER TABLE … ADD SEARCH OPTIMIZATION verwenden. (Wenn Sie die Suchoptimierung auf Tabellenebene aktivieren, wird sie nicht für VARIANT-Spalten aktiviert.) Beispiel:

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

Abfragen auf VARIANT-Spalten werden nur optimiert, wenn Sie die ON-Klausel verwenden.

In den nächsten Abschnitten finden Sie weitere Details dazu:

Unterstützte Datentypen für Konstanten und Umwandlungen in Prädikaten für VARIANT-Typen

Der Suchoptimierungsdienst kann die Leistung von Punktsuchabfragen auf semistrukturierten Daten verbessern, bei denen die folgenden Typen für Konstanten und für die implizite oder explizite Umwandlung von Elementen verwendet werden:

  • FIXED (einschließlich Umwandlungen, die eine gültige Genauigkeit und Skalierung angeben)

  • INTEGER

  • TEXT

  • DATE (einschließlich Umwandlungen, die eine Skalierung angeben)

  • TIME (einschließlich Umwandlungen, die eine Skalierung angeben)

  • TIMESTAMP TIMESTAMP_LTZ, TIMESTAMP_NTZ, TIMESTAMP_TZ (einschließlich Umwandlungen, die eine Skalierung angeben)

Der Suchoptimierungsdienst unterstützt die Umwandlung von Typen mit:

Unterstützung der Umwandlung von VARIANT-Werten in TEXT

Der Suchoptimierungsdienst kann auch die Leistung von Punktsuchabfragen verbessern, bei denen VARIANT-Spalten in TEXT umgewandelt und mit in TEXT umgewandelten Konstanten verglichen werden.

Angenommen, src ist eine VARIANT-Spalte, die Werte der Datentypen BOOLEAN, DATE und TIME enthält, die in VARIANT-Wert konvertiert worden sind:

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

Bei dieser Tabelle kann der Suchoptimierungsdienst die folgenden Abfragen verbessern, bei denen die VARIANT-Spalte in TEXT umgewandelt und die Spalte mit Zeichenfolgenkonstante verglichen wird:

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

Unterstützte Prädikate für Punktsuche auf VARIANT-Typen

Der Suchoptimierungsdienst kann Punktsuchabfragen mit den unten aufgeführten Typen von Prädikaten verbessern. In den folgenden Beispielen ist src die VARIANT-Spalte und path_to_variant_field ist ein Pfad zu einem Feld in der VARIANT-Spalte.

  • Gleichheitsprädikate im folgenden Format:

    where path_to_variant_field[::target_data_type] = constant

    Bei dieser Syntax müssen target_data_type (falls angegeben) und der Datentyp von constant zu den oben aufgeführten unterstützten Typen gehören.

    Der Suchoptimierungsdienst unterstützt beispielsweise Folgendes:

    • Abgleich eines Elements mit einer NUMBER-Konstante ohne explizite Umwandlung des Elements.

      where src:person.age = 42;
      
      Copy
    • Explizite Umwandlung eines Elements in NUMBER mit einer bestimmten Genauigkeit und Skalierung.

      where src:location.temperature::NUMBER(8, 6) = 23.456789;
      
      Copy
    • Abgleich eines Elements mit einer TEXT-Konstante ohne explizite Umwandlung des Elements.

      where src:sender_info.ip_address = '123.123.123.123';
      
      Copy
    • Explizite Umwandlung eines Elements in TEXT.

      where src:salesperson.name::TEXT = 'John Appleseed';
      
      Copy
    • Explizite Umwandlung eines Elements in DATE.

      where src:events.date::DATE = '2021-03-26';
      
      Copy
    • Explizite Umwandlung eines Elements in TIME mit einer bestimmten Skalierung.

      where src:events.time_info::TIME(6) = '01:02:03.456789';
      
      Copy
    • Explizite Umwandlung eines Elements in TIMESTAMP mit einer bestimmten Skalierung.

      where src:event_logs.exceptions.timestamp_info(3) = '2021-03-26 15:00:00.123 -0800';
      
      Copy
  • Prädikate, die ARRAY-Funktionen verwenden, wie z. B.:


    • where ARRAY_CONTAINS(constant::VARIANT, path_to_variant_field)

      Bei dieser Syntax darf constant nicht NULL sein, und der Datentyp von constant muss zu den oben aufgeführten unterstützten Typen gehören.

      Beispiel:

      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)

      Der Datentyp jeder Konstanten (constant_1, constant_2 usw.) muss einer der oben aufgeführten unterstützten Typen sein. Das konstruierte ARRAY kann NULL-Konstanten enthalten.

      Beispiel:

      where ARRAYS_OVERLAP(
          ARRAY_CONSTRUCT('122.63.45.75', '89.206.83.107'), src:senders.ip_addresses)
      
      Copy
  • Die folgenden Prädikate, die auf NULL-Werte prüfen:

    • where IS_NULL_VALUE(path_to_variant_field)

      Beachten Sie, dass IS_NULL_VALUE für JSON-Nullwerte und nicht für SQL-NULL-Werte gilt.

    • where path_to_variant_field IS NOT NULL

    • where variant_column IS NULL

      wobei sich variant_column auf die Spalte und nicht auf einen Pfad zu einem Element in den semistrukturierten Daten bezieht.

      So unterstützt der Suchoptimierungsdienst beispielsweise die Verwendung der VARIANT-Spalte src, nicht aber den Pfad zum Feld src:person.age in dieser VARIANT-Spalte.

Suche nach Teilzeichenfolgen in VARIANT-Typen

Der Suchoptimierungsdienst kann Platzhalter oder reguläre Ausdrücke in semistrukturierten Spalten (einschließlich ARRAY-, OBJECT- und VARIANT-Spalten) oder in Feldern in solchen Spalten optimieren. Dazu zählen Prädikate, die Folgendes verwenden:

Sie können die Suchoptimierung nach Teilzeichenfolgen für eine Spalte oder für mehrere einzelne Felder innerhalb einer Spalte aktivieren. Die folgende Anweisung aktiviert beispielsweise die Suchoptimierung nach Teilzeichenfolgen für ein verschachteltes Feld in einer Spalte.

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

Sobald der Suchzugriffspfad erstellt wurde, kann die folgende Abfrage optimiert werden:

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

Die folgenden Abfragen werden jedoch nicht optimiert, da die Filter der WHERE-Klausel nicht für das Feld gelten, das bei Aktivierung der Suchoptimierung angegeben wurde (col2:data.search).

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

Sie können mehrere Felder angeben, die optimiert werden sollen. Hier ist die Suchoptimierung für zwei bestimmte Felder in der Spalte col2 aktiviert.

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

Wenn Sie die Suchoptimierung für ein bestimmtes Feld aktivieren, wird sie auch für alle Unterfelder aktiviert. Die zweite ALTER TABLE-Anweisung unten ist redundant, da die erste Anweisung die Suchoptimierung für das gesamte data-Feld ermöglicht, einschließlich des verschachtelten search-Feldes.

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

Wenn Sie die Suchoptimierung für eine ganze Spalte aktivieren, werden alle Suchläufe nach Teilzeichenfolgen in dieser Spalte optimiert, einschließlich für Felder, die in beliebiger Tiefe in der Spalte verschachtelt sind.

Auswertung der Konstanten für die Suche von VARIANT-Teilzeichenfolgen

Bei der Auswertung der konstanten Zeichenfolge in einer Abfrage (z. B. LIKE 'constant_string') teilt der Suchoptimierungsdienst die Zeichenfolge in Token auf und verwendet dabei die folgenden Zeichen als Trennzeichen:

  • Eckige Klammern ([ und ])

  • Geschweifte Klammern ({ und })

  • Doppelpunkte (:)

  • Kommas (,)

  • Doppelte Anführungszeichen (")

Nach der Aufteilung der Zeichenfolge in Token berücksichtigt der Suchoptimierungsdienst nur Token, die mindestens 5 Zeichen lang sind.

Beispiel für ein Prädikat

Wie der Suchoptimierungsdienst die Abfrage verarbeitet

LIKE '%TEST%'

Der Suchoptimierungsdienst verwendet für das folgende Prädikat keine Suchzugriffspfade, da die Teilzeichenfolge kürzer als 5 Zeichen ist:

LIKE '%SEARCH%IS%OPTIMIZED%'

Der Suchoptimierungsdienst kann diese Abfrage optimieren, indem er Suchzugriffspfade für die Suche nach SEARCH und OPTIMIZED verwendet, aber nicht nach IS, denn IS ist kürzer als 5 Zeichen.

LIKE '%HELLO_WORLD%'

Der Suchoptimierungsdienst kann die Abfrage optimieren, indem Suchzugriffspfade für die Suche nach HELLO_WORLD verwendet werden.

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

Der Suchoptimierungsdienst zerlegt diese Zeichenfolge in COL, ON, S, EVE, RYWH, ERE. Da alle diese Token kürzer als 5 Zeichen sind, kann der Suchoptimierungsdienst diese Abfrage nicht optimieren.

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

Der Suchoptimierungsdienst zerlegt diese Zeichenfolge in die Token KEY01, KEY02 und VALUE und verwendet dann diese Token bei der Optimierung der Abfrage.

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

Der Suchoptimierungsdienst zerlegt diese Zeichenfolge in die Token quo, tes_and_com, mas, are_n, ot, _all, owed. Der Suchoptimierungsdienst kann bei der Optimierung der Abfrage nur die Token verwenden, die mindestens 5 Zeichen haben (tes_and_com, are_n).

Aktuelle Einschränkungen bei der Unterstützung von VARIANT-Typen

Die Unterstützung von VARIANT-Typen im Suchoptimierungsdienst unterliegt derzeit den folgenden Einschränkungen:

  • Prädikate, die XMLGET verwenden, werden nicht unterstützt.

  • Prädikate der Form variant_field IS NULL werden nicht unterstützt.

  • Prädikate, bei denen die Konstanten die Ergebnisse von skalaren Unterabfragen sind, werden nicht unterstützt.

  • Prädikate, die Pfade zu Elementen angeben, die Unterelemente enthalten, werden nicht unterstützt.

Die aktuell für den Suchoptimierungsdienst geltenden Einschränkungen gelten auch für dieses Feature.