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.

Bemerkung

Sie müssen dieses Feature für bestimmte Spalten oder Elemente in Spalten explizit aktivieren, indem Sie die Klausel ON im Befehl ALTER TABLE. … ADD SEARCH OPTIMIZATION verwenden. 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);
Copy

Abfragen für die Spalten VARIANT, OBJECT und ARRAY werden nicht optimiert, wenn Sie die Klausel ON weglassen.

In den folgenden Abschnitten finden Sie weitere Informationen zu dieser Unterstützung:

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

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

Der Suchoptimierungsdienst kann auch die Leistung von Punktsuchen verbessern, bei denen Spalten mit semistrukturierten Datentypen in TEXT umgewandelt und mit Konstanten verglichen werden, die in TEXT 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);
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 = '2020-01-09 01:02:03.899';
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 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;
      Copy

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

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

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

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

    • Explizite Umwandlung eines VARIANT-Elements in TEXT.

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

    • Explizite Umwandlung eines VARIANT-Elements in DATE.

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

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

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

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

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

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

      WHERE ARRAY_CONTAINS(300, my_array_column)
      Copy

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

      In diesem Beispiel ist das Array eine ARRAY-Spalte:

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

  • 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 Suchoptimierungsdienst kann Platzhalter oder reguläre Ausdrücke in semistrukturierten Spalten (d. h. VARIANT-, OBJECT- und ARRAY-Spalten) oder Elemente in solchen Spalten optimieren. Dazu zählen Prädikate, die Folgendes 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);
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 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%';
Copy

Sie können mehrere Elemente angeben, die optimiert werden sollen. Hier 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);
Copy

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

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 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 fünf Zeichen lang sind.

Beispiel für ein Prädikat

Wie der Suchoptimierungsdienst die Abfrage verarbeitet

LIKE '%TEST%'

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

LIKE '%SEARCH%IS%OPTIMIZED%'

Der Suchoptimierungsdienst 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 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 fünf 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 fünf Zeichen oder länger 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, die XMLGET verwenden, werden nicht unterstützt.

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

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

Sprache: Deutsch