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);
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
Unterstützung der Umwandlung von semistrukturierten Datentypwerten in TEXT
Derzeitige Einschränkungen bei der Unterstützung von semistrukturierten Typen
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);
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';
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 vonconstant
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 TEXT-Konstanten ohne explizite Umwandlung des Elements.
WHERE src:sender_info.ip_address = '123.123.123.123';
Explizite Umwandlung eines VARIANT-Elements in TEXT.
WHERE src:salesperson.name::TEXT = '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 Elementsrc: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);
Sobald 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. 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);
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 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 |
---|---|
|
Der Suchoptimierungsdienst verwendet keine Suchzugriffspfade für das folgende Prädikat, da die Teilzeichenfolge kürzer als fünf Zeichen ist. |
|
Der Suchoptimierungsdienst kann diese Abfrage optimieren, indem er die Suchzugriffspfade verwendet, um nach |
|
Der Suchoptimierungsdienst kann die Abfrage optimieren, indem Suchzugriffspfade für die Suche nach |
|
Der Suchoptimierungsdienst zerlegt diese Zeichenfolge in |
|
Der Suchoptimierungsdienst zerlegt diese Zeichenfolge in die Token |
|
Der Suchoptimierungsdienst zerlegt diese Zeichenfolge in die Token |
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.