Beschleunigen von Abfragen auf semistrukturierten Daten durch die Suchoptimierung

Der Suchoptimierungsdienst kann die Leistung von Punktsuchabfragen und Abfragen mit Teilzeichenfolgen auf semistrukturierte Daten in Snowflake-Tabellen (d. h. Daten in den Spalten VARIANT, OBJECT und ARRAY) verbessern. Sie können die Suchoptimierung für Spalten dieser Arten konfigurieren, selbst wenn die Struktur tief verschachtelt ist und sich häufig ändert. Sie können auch die Suchoptimierung für bestimmte Elemente innerhalb einer semistrukturierten Spalte aktivieren.

In den folgenden Abschnitten finden Sie weitere Informationen zur Unterstützung der Suchoptimierung bei Abfragen von semistrukturierten Daten:

Suchoptimierung für Abfragen von semistrukturierten Daten

Um die Leistung bei Abfragen von semistrukturierten Daten in einer Tabelle zu verbessern, verwenden Sie die Klausel ON in dem Befehl ALTER TABLE … ADD SEARCH OPTIMIZATION für bestimmte Spalten oder Elemente in Spalten. Abfragen für die Spalten VARIANT, OBJECT und ARRAY werden nicht optimiert, wenn Sie die Klausel ON weglassen. Wenn Sie die Suchoptimierung auf Tabellenebene aktivieren, wird sie nicht für Spalten mit semistrukturierten Daten 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);

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

Weitere Informationen dazu finden Sie unter Aktivieren und Deaktivieren der Suchoptimierung.

Unterstützte Datentypen für Konstanten und Umwandlungen in Prädikaten für semistrukturierte 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 (einschließlich synonymer Typen)

  • VARCHAR (einschließlich synonymer Typen)

  • 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 semistrukturierten Datentypwerten in VARCHAR

Der Suchoptimierungsdienst kann auch die Leistung von Punktsuchen verbessern, bei denen Spalten mit semistrukturierten Datentypen in VARCHAR umgewandelt und mit Konstanten verglichen werden, die in VARCHAR umgewandelt werden.

Angenommen, src ist eine VARIANT-Spalte, die Werte der Datentypen BOOLEAN, DATE und TIMESTAMP 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);
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);

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

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

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 Spalte mit einem semistrukturierten Datentyp, und path_to_element ist ein Pfad zu einem Element in der Spalte mit einem semistrukturierten Datentyp.

  • Gleichheitsprädikate im folgenden Format:

    WHERE path_to_element[::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 VARIANT-Elements mit einer NUMBER-Konstanten ohne explizite Umwandlung des Elements.

      WHERE src:person.age = 42;

    • Explizite Umwandlung eines VARIANT-Elements in NUMBER mit einer bestimmten Genauigkeit und Skalierung.

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

    • Abgleich eines VARIANT-Elements mit einer VARCHAR-Konstanten ohne explizite Umwandlung des Elements.

      WHERE src:sender_info.ip_address = '123.123.123.123';

    • Explizite Umwandlung eines VARIANT-Elements in VARCHAR.

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

    • Explizite Umwandlung eines VARIANT-Elements in DATE.

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

    • Explizite Umwandlung eines VARIANT-Elements in TIMESTAMP mit einem bestimmten Maßstab.

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

    • Abgleich eines ARRAY-Elements mit dem Wert eines unterstützten Typs, mit oder ohne explizite Umwandlung in den Typ. Beispiel:

      WHERE my_array_column[2] = 5;

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

    • Abgleich eines OBJECT-Elements mit dem Wert eines unterstützten Typs, mit oder ohne explizite Umwandlung in den Typ. Beispiel:

      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;

  • Prädikate, die ARRAY-Funktionen verwenden, wie z. B.:

    • WHERE ARRAY_CONTAINS(value_expr, array)

      In dieser Syntax darf value_expr nicht NULL sein und muss VARIANT ergeben. Der Datentyp des Wertes muss einer der unterstützten Typen sein.

      Beispiel:

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

      In diesem Beispiel ist der Wert eine Konstante, die implizit in ein VARIANT umgewandelt wird:

      WHERE ARRAY_CONTAINS(300, my_array_column)

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

      Der Datentyp jeder Konstanten (constant_1, constant_2 und so weiter) muss einer der von unterstützten Typen sein. Das konstruierte ARRAY kann NULL-Konstanten enthalten.

      In diesem Beispiel befindet sich das Array in einem VARIANT-Wert:

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

      In diesem Beispiel ist das Array eine ARRAY-Spalte:

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

  • Die folgenden Prädikate, die auf NULL-Werte prüfen:

    • WHERE IS_NULL_VALUE(path_to_element)

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

    • WHERE path_to_element IS NOT NULL

    • WHERE semistructured_column IS NULL

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

      Zum Beispiel unterstützt der Suchoptimierungsdienst die Verwendung der Spalte VARIANT src, aber nicht den Pfad zum Element src:person.age in dieser Spalte VARIANT.

Suche nach Teilzeichenfolgen in VARIANT-Typen

Der Suchoptimierungsservice kann Platzhalter oder reguläre Ausdrücke in semistrukturierten Spalten (d. h. VARIANT-, OBJECT- und ARRAY-Spalten) oder Elemente in solchen Spalten optimieren.

Der Suchoptimierungsservice kann Prädikate optimieren, die die folgenden Funktionen verwenden:

Sie können die Optimierung der Suche nach Teilzeichenfolgen für eine Spalte oder für mehrere einzelne Elemente innerhalb einer Spalte aktivieren. Die folgende Anweisung aktiviert zum Beispiel die Optimierung der Suche nach Teilzeichenfolgen für ein verschachteltes Element in einer Spalte:

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

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

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

Die folgenden Abfragen werden jedoch nicht optimiert, da die Filter der WHERE-Klausel nicht auf das Element angewendet werden, das bei der 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%';

Sie können mehrere Elemente angeben, die optimiert werden sollen. Im folgenden Beispiel ist die Suchoptimierung für zwei bestimmte Elemente 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);

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

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

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

Ein Beispiel für eine FULL_TEXT-Suchoptimierung für eine VARIANT-Spalte in der Tabelle car_sales und deren Daten (beschrieben in Abfragen von semistrukturierten Daten) finden Sie unter FULL_TEXT-Suchoptimierung in einer VARIANT-Spalte aktivieren.

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 Suchoptimierungsservice die Zeichenfolge in Token auf und verwendet dabei die folgenden Zeichen als Trennzeichen:

  • Eckige Klammern ([ und ])

  • Geschweifte Klammern ({ und })

  • Doppelpunkte (:)

  • Kommas (,)

  • Doppelte Anführungszeichen (")

Nachdem die Zeichenfolge in Token aufgeteilt wurde, berücksichtigt der Suchoptimierungsservice nur Token, die mindestens fünf Zeichen lang sind. In der folgenden Tabelle wird erläutert, wie der Suchoptimierungsservice verschiedene Beispiele für Prädikate behandelt:

Beispiel für ein Prädikat

Wie der Suchoptimierungsdienst die Abfrage verarbeitet

LIKE '%TEST%'

Der Suchoptimierungsservice verwendet keine Suchzugriffspfade für das folgende Prädikat, da die Teilzeichenfolge kürzer als fünf Zeichen ist.

LIKE '%SEARCH%IS%OPTIMIZED%'

Der Suchoptimierungsservice kann diese Abfrage optimieren, indem er die Suchzugriffspfade verwendet, um nach SEARCH und OPTIMIZED, aber nicht nach IS zu suchen. IS ist kürzer als fünf Zeichen.

LIKE '%HELLO_WORLD%'

Der Suchoptimierungsservice 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 fünf Zeichen sind, kann der Suchoptimierungsdienst diese Abfrage nicht optimieren.

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

Der Suchoptimierungsservice 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 Suchoptimierungsservice kann bei der Optimierung der Abfrage nur die Token verwenden, die mindestens fünf Zeichen lang sind (tes_and_com, are_n).

Derzeitige Einschränkungen bei der Unterstützung von semistrukturierten Typen

Die Unterstützung für semistrukturierte Typen im Suchoptimierungsdienst ist auf folgende Weise eingeschränkt:

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

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

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

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

Die aktuellen Einschränkungen des Suchoptimierungsservice gelten auch für semistrukturierte Typen.