Abfragen von semistrukturierten Daten¶
Unter diesem Thema wird erklärt, wie spezielle Operatoren und Funktionen zur Abfrage komplexer hierarchischer Daten verwendet werden, die in einem VARIANT-Wert gespeichert sind.
(Einfache Beispiele zum Extrahieren von Werten aus ARRAYs und OBJECTs finden Sie unter Zugriff auf Elemente eines ARRAY per Index oder per Slice und Zugriff auf Elemente eines OBJECT nach Schlüssel).
Typischerweise werden hierarchische Daten aus einem der folgenden unterstützten Datenformate in einen VARIANT-Wert importiert:
JSON
Avro
ORC
Parquet
(Weitere Informationen zum Abfragen von XML-Daten, wie z. B. Daten, die im XML-Datenformat erstellt und durch den Aufruf von PARSE_XML in ein OBJECT-Format konvertiert wurden, finden Sie unter XMLGET.)
Tipp
Sie können den Suchoptimierungsdienst verwenden, um die Leistung von Abfragen zu verbessern. Weitere Details dazu finden Sie unter Suchoptimierungsdienst.
Unter diesem Thema:
In Beispielen verwendete Beispieldaten¶
Sofern nicht anders angegeben, beziehen sich die hier verwendeten Beispiele auf eine Tabelle mit dem Namen car_sales, die nur aus einer VARIANT-Spalte mit dem Namen src besteht: Dieser VARIANT-Wert enthält verschachtelte ARRAYs und OBJECTs.
Erstellen Sie die Tabelle, und laden Sie die Daten in die Tabelle:
CREATE OR REPLACE TABLE car_sales
(
src variant
)
AS
SELECT PARSE_JSON(column1) AS src
FROM VALUES
('{
"date" : "2017-04-28",
"dealership" : "Valley View Auto Sales",
"salesperson" : {
"id": "55",
"name": "Frank Beasley"
},
"customer" : [
{"name": "Joyce Ridgely", "phone": "16504378889", "address": "San Francisco, CA"}
],
"vehicle" : [
{"make": "Honda", "model": "Civic", "year": "2017", "price": "20275", "extras":["ext warranty", "paint protection"]}
]
}'),
('{
"date" : "2017-04-28",
"dealership" : "Tindel Toyota",
"salesperson" : {
"id": "274",
"name": "Greg Northrup"
},
"customer" : [
{"name": "Bradley Greenbloom", "phone": "12127593751", "address": "New York, NY"}
],
"vehicle" : [
{"make": "Toyota", "model": "Camry", "year": "2017", "price": "23500", "extras":["ext warranty", "rust proofing", "fabric protection"]}
]
}') v;
Wählen Sie die Daten aus:
SELECT * FROM car_sales;
+-------------------------------------------+
| SRC |
|-------------------------------------------|
| { |
| "customer": [ |
| { |
| "address": "San Francisco, CA", |
| "name": "Joyce Ridgely", |
| "phone": "16504378889" |
| } |
| ], |
| "date": "2017-04-28", |
| "dealership": "Valley View Auto Sales", |
| "salesperson": { |
| "id": "55", |
| "name": "Frank Beasley" |
| }, |
| "vehicle": [ |
| { |
| "extras": [ |
| "ext warranty", |
| "paint protection" |
| ], |
| "make": "Honda", |
| "model": "Civic", |
| "price": "20275", |
| "year": "2017" |
| } |
| ] |
| } |
| { |
| "customer": [ |
| { |
| "address": "New York, NY", |
| "name": "Bradley Greenbloom", |
| "phone": "12127593751" |
| } |
| ], |
| "date": "2017-04-28", |
| "dealership": "Tindel Toyota", |
| "salesperson": { |
| "id": "274", |
| "name": "Greg Northrup" |
| }, |
| "vehicle": [ |
| { |
| "extras": [ |
| "ext warranty", |
| "rust proofing", |
| "fabric protection" |
| ], |
| "make": "Toyota", |
| "model": "Camry", |
| "price": "23500", |
| "year": "2017" |
| } |
| ] |
| } |
+-------------------------------------------+
Durchsuchen semistrukturierter Daten¶
Fügen Sie einen Doppelpunkt : zwischen dem Namen der VARIANT-Spalte und einem Element erster Ebene ein: <Spalte>:<Ebene1-Element>.
Bemerkung
In den folgenden Beispielen wird die Abfrageausgabe in doppelte Anführungszeichen eingeschlossen, da die Anfrageausgabe vom Typ VARIANT und nicht vom Typ VARCHAR ist. (VARIANT-Werte sind keine Zeichenfolgen. Die VARIANT-Werte enthalten Zeichenfolgen.) Operator : und die nachfolgenden Operatoren . und [] geben standardmäßig immer VARIANT-Werte zurück, die Zeichenfolgen enthalten.
Rufen Sie beispielsweise eine Liste aller Händlernamen ab:
SELECT src:dealership
FROM car_sales
ORDER BY 1;
+--------------------------+
| SRC:DEALERSHIP |
|--------------------------|
| "Tindel Toyota" |
| "Valley View Auto Sales" |
+--------------------------+
Es gibt zwei Möglichkeiten, auf Elemente in einem JSON-Objekt zuzugreifen:
Punktierte Schreibweise (unter diesem Thema).
Klammernotation (unter diesem Thema).
Wichtig
Unabhängig davon, welche Notation Sie verwenden, ist die Groß-/Kleinschreibung bei Spaltennamen nicht relevant, bei Elementnamen dagegen schon. In der folgenden Liste sind beispielsweise die ersten beiden Pfade gleich, der dritte jedoch nicht:
src:salesperson.name
SRC:salesperson.name
SRC:salesperson.name
Punktierte Schreibweise¶
Verwenden Sie die Punktnotation, um einen Pfad in einem JSON-Objekt zu durchlaufen: <Spalte>:<Ebene1-Element>.<Ebene2-Element>.<Ebene3-Element>. Schließen Sie optional Elementnamen in doppelte Anführungszeichen ein: <Spalte>:"<Ebene1-Element>"."<Ebene2-Element>"."<Ebene3-Element>".
Bemerkung
Die Regeln für JSON-Schlüssel (Elementnamen) unterscheiden sich von den Regeln für Snowflake-SQL-Bezeichner.
Weitere Informationen zu den Regeln für Snowflake-SQL-Bezeichner finden Sie unter Anforderungen an Bezeichner.
Weitere Informationen zu JSON-Schlüsseln finden Sie unter http://json.org, insbesondere in der Beschreibung zu „string“ (Zeichenfolge).
Wenn ein Elementname nicht den Regeln für Snowflake-SQL-Bezeichner entspricht, z. B. wenn er Leerzeichen enthält, dann müssen Sie den Namen in doppelte Anführungszeichen setzen. Nachfolgend einige Beispiele (nicht alle stammen aus dem obigen „car_sales“-Beispiel) für gültige JSON-Elementnamen, die keine gültigen Snowflake-Bezeichnernamen sind, sofern sie nicht in doppelte Anführungszeichen eingeschlossen sind:
-- This contains a blank.
SELECT src:"company name" FROM partners;
-- This does not start with a letter or underscore.
SELECT zipcode_info:"94987" FROM addresses;
-- This contains characters that are not letters, digits, or underscores, and
-- it does not start with a letter or underscore.
SELECT measurements:"#sPerSquareInch" FROM english_metrics;
Abrufen der Namen aller Verkäufer, die Autos verkauft haben:
SELECT src:salesperson.name
FROM car_sales
ORDER BY 1;
+----------------------+
| SRC:SALESPERSON.NAME |
|----------------------|
| "Frank Beasley" |
| "Greg Northrup" |
+----------------------+
Klammernotation¶
Alternativ können Sie die Klammernotation verwenden, um den Pfad in einem Objekt zu durchlaufen: <Spalte>['<Ebene1-Element>']['<Ebene2-Element>']. Schließen Sie Elementnamen in einfache Anführungszeichen ein. Werte werden als Zeichenfolgen abgerufen.
Abrufen der Namen aller Verkäufer, die Autos verkauft haben:
SELECT src['salesperson']['name']
FROM car_sales
ORDER BY 1;
+----------------------------+
| SRC['SALESPERSON']['NAME'] |
|----------------------------|
| "Frank Beasley" |
| "Greg Northrup" |
+----------------------------+
Abrufen einer einzelnen Instanz eines sich wiederholenden Elements¶
Abrufen einer bestimmten nummerierten Instanz eines untergeordneten Elements in einem sich wiederholenden Array durch Hinzufügen eines nummerierten Prädikats (beginnend bei 0) zur Array-Referenz.
Beachten Sie, dass es zum Abrufen aller Instanzen eines untergeordneten Elements aus einem sich wiederholenden Array notwendig ist, das Array zu vereinfach (Flatten). Ein Beispiel dazu finden Sie unter Verwenden der FLATTEN-Funktion zum Parsen von Arrays unter diesem Thema.
Abrufen der Fahrzeugdetails für jeden Verkauf:
SELECT src:customer[0].name, src:vehicle[0]
FROM car_sales
ORDER BY 1;
+----------------------+-------------------------+
| SRC:CUSTOMER[0].NAME | SRC:VEHICLE[0] |
|----------------------+-------------------------|
| "Bradley Greenbloom" | { |
| | "extras": [ |
| | "ext warranty", |
| | "rust proofing", |
| | "fabric protection" |
| | ], |
| | "make": "Toyota", |
| | "model": "Camry", |
| | "price": "23500", |
| | "year": "2017" |
| | } |
| "Joyce Ridgely" | { |
| | "extras": [ |
| | "ext warranty", |
| | "paint protection" |
| | ], |
| | "make": "Honda", |
| | "model": "Civic", |
| | "price": "20275", |
| | "year": "2017" |
| | } |
+----------------------+-------------------------+
Abrufen des Preises von jedem verkauften Auto:
SELECT src:customer[0].name, src:vehicle[0].price
FROM car_sales
ORDER BY 1;
+----------------------+----------------------+
| SRC:CUSTOMER[0].NAME | SRC:VEHICLE[0].PRICE |
|----------------------+----------------------|
| "Bradley Greenbloom" | "23500" |
| "Joyce Ridgely" | "20275" |
+----------------------+----------------------+
Explizites Umwandeln von Werten¶
Wenn Sie Werte aus einem VARIANT extrahieren, können Sie diese Werte explizit in den gewünschten Datentyp umwandeln. So können Sie beispielsweise die Preise als numerische Werte extrahieren und Berechnungen damit ausführen:
SELECT src:vehicle[0].price::NUMBER * 0.10 AS tax
FROM car_sales
ORDER BY tax;
+--------+
| TAX |
|--------|
| 2027.5 |
| 2350.0 |
+--------+
Wenn VARCHARs, DATEs, TIMEs und TIMESTAMPs aus einer VARIANT-Spalte abgerufen werden, sind die Werte standardmäßig von doppelten Anführungszeichen umschlossen. Sie können die doppelten Anführungszeichen vermeiden, indem Sie die Werte explizit umwandeln. Beispiel:
SELECT src:dealership, src:dealership::VARCHAR
FROM car_sales
ORDER BY 2;
+--------------------------+-------------------------+
| SRC:DEALERSHIP | SRC:DEALERSHIP::VARCHAR |
|--------------------------+-------------------------|
| "Tindel Toyota" | Tindel Toyota |
| "Valley View Auto Sales" | Valley View Auto Sales |
+--------------------------+-------------------------+
Weitere Informationen zum Umwandeln von VARIANT-Werten finden Sie unter Verwenden von VARIANT-Werten.
Weitere allgemeine Informationen zum Umwandeln von Werten finden Sie unter Datentypkonvertierung.
Using FLATTEN to Filter the Results in a WHERE Clause¶
The FLATTEN function explodes nested values into separate columns. You can use the function to filter query results in a WHERE clause.
The following example returns key-value pairs that match a WHERE clause and displays them in separate columns:
CREATE TABLE pets (v variant);
INSERT INTO pets SELECT PARSE_JSON ('{"species":"dog", "name":"Fido", "is_dog":"true"} ');
INSERT INTO pets SELECT PARSE_JSON ('{"species":"cat", "name":"Bubby", "is_dog":"false"}');
INSERT INTO pets SELECT PARSE_JSON ('{"species":"cat", "name":"dog terror", "is_dog":"false"}');
SELECT a.v, b.key, b.value FROM pets a,LATERAL FLATTEN(input => a.v) b
WHERE b.value LIKE '%dog%';
+-------------------------+---------+--------------+
| V | KEY | VALUE |
|-------------------------+---------+--------------|
| { | species | "dog" |
| "is_dog": "true", | | |
| "name": "Fido", | | |
| "species": "dog" | | |
| } | | |
| { | name | "dog terror" |
| "is_dog": "false", | | |
| "name": "dog terror", | | |
| "species": "cat" | | |
| } | | |
+-------------------------+---------+--------------+
Using FLATTEN to List Distinct Key Names¶
When working with unfamiliar semi-structured data, you might not know the key names in an OBJECT. You can use the FLATTEN function with the RECURSIVE argument to return the list of distinct key names in all nested elements in an OBJECT:
SELECT REGEXP_REPLACE(f.path, '\\[[0-9]+\\]', '[]') AS "Path",
TYPEOF(f.value) AS "Type",
COUNT(*) AS "Count"
FROM <table>,
LATERAL FLATTEN(<variant_column>, RECURSIVE=>true) f
GROUP BY 1, 2 ORDER BY 1, 2;
The REGEXP_REPLACE function removes the array index values (e.g. [0]) and replaces them with brackets ([]) to group array elements.
For example:
{"a": 1, "b": 2, "special" : "data"} <--- row 1 of VARIANT column
{"c": 3, "d": 4, "normal" : "data"} <----row 2 of VARIANT column
Output from query:
+---------+---------+-------+
| Path | Type | Count |
|---------+---------+-------|
| a | INTEGER | 1 |
| b | INTEGER | 1 |
| c | INTEGER | 1 |
| d | INTEGER | 1 |
| normal | VARCHAR | 1 |
| special | VARCHAR | 1 |
+---------+---------+-------+
Using FLATTEN to List Paths in an OBJECT¶
Related to Using FLATTEN to List Distinct Key Names, you can use the FLATTEN function with the RECURSIVE argument to retrieve all keys and paths in an OBJECT.
The following query returns keys, paths, and values (including VARIANT „null“ values) for all data types stored in a VARIANT column. The code assumes that the VARIANT column contains an OBJECT in each row.
SELECT
t.<variant_column>,
f.seq,
f.key,
f.path,
REGEXP_COUNT(f.path,'\\.|\\[') +1 AS Level,
TYPEOF(f.value) AS "Type",
f.index,
f.value AS "Current Level Value",
f.this AS "Above Level Value"
FROM <table> t,
LATERAL FLATTEN(t.<variant_column>, recursive=>true) f;
The following query is similar to the first query, but excludes nested OBJECTs and ARRAYs:
SELECT
t.<variant_column>,
f.seq,
f.key,
f.path,
REGEXP_COUNT(f.path,'\\.|\\[') +1 AS Level,
TYPEOF(f.value) AS "Type",
f.value AS "Current Level Value",
f.this AS "Above Level Value"
FROM <table> t,
LATERAL FLATTEN(t.<variant_column>, recursive=>true) f
WHERE "Type" NOT IN ('OBJECT','ARRAY');
The queries return the following values:
- <variant_column>
OBJECT stored as a row in the VARIANT column.
- Seq
Unique sequence number associated with the data in the row.
- Key
String associated with a value in the data structure.
- Path
Path to the element within the data structure.
- Level
Level of the key-value pair within the data structure.
- Type
Data type for the value.
- Index
Index of the element in the data structure. Applies to ARRAY values only; otherwise NULL.
- Current Level Value
Value at the current level in the data structure.
- Above Level Value
Value one level higher in the data structure.
Verwenden der FLATTEN-Funktion zum Analysieren von Arrays¶
Analysieren Sie ein Array mit der FLATTEN-Funktion. FLATTEN ist eine Tabellenfunktion, mit der die laterale Ansicht einer VARIANT-, OBJECT- oder ARRAY-Spalte erstellt werden kann. Die Funktion gibt eine Zeile für jedes Objekt zurück, und der LATERAL-Modifikator verknüpft die Daten mit allen Informationen außerhalb des Objekts.
Abrufen der Namen und Adressen aller Kunden. Umwandeln der VARIANT-Ausgabe in Zeichenfolgenwerte:
SELECT
value:name::string as "Customer Name",
value:address::string as "Address"
FROM
car_sales
, LATERAL FLATTEN(INPUT => SRC:customer);
+--------------------+-------------------+
| Customer Name | Address |
|--------------------+-------------------|
| Joyce Ridgely | San Francisco, CA |
| Bradley Greenbloom | New York, NY |
+--------------------+-------------------+
Verwenden der FLATTEN-Funktion zum Analysieren geschachtelter Arrays¶
In den Beispieldaten ist das extras-Array innerhalb des vehicle-Arrays geschachtelt:
"vehicle" : [
{"make": "Honda", "model": "Civic", "year": "2017", "price": "20275", "extras":["ext warranty", "paint protection"]}
]
Fügen Sie eine zweite FLATTEN-Klausel hinzu, um das extras-Array innerhalb des bereits vereinfachten vehicle-Arrays zu vereinfachen und die für jedes verkaufte Auto erworbenen „Extras“ abzurufen:
SELECT
vm.value:make::string as make,
vm.value:model::string as model,
ve.value::string as "Extras Purchased"
FROM
car_sales
, LATERAL FLATTEN(INPUT => SRC:vehicle) vm
, LATERAL FLATTEN(INPUT => vm.value:extras) ve
ORDER BY make, model, "Extras Purchased";
+--------+-------+-------------------+
| MAKE | MODEL | Extras Purchased |
|--------+-------+-------------------|
| Honda | Civic | ext warranty |
| Honda | Civic | paint protection |
| Toyota | Camry | ext warranty |
| Toyota | Camry | fabric protection |
| Toyota | Camry | rust proofing |
+--------+-------+-------------------+
Analysieren von Text als VARIANT-Werte mit der PARSE_JSON-Funktion¶
Analysieren Sie mit der Funktion PARSE_JSON Text als JSON-Dokument.
Wenn der Eingabewert NULL ist, ist der Ausgabewert auch NULL. Wenn jedoch die Eingabezeichenfolge null ist, wird sie als VARIANT null-Wert interpretiert, d. h. das Ergebnis ist kein SQL NULL, sondern ein reeller Wert, der verwendet wird, um einen Nullwert in semistrukturierten Formaten darzustellen.
Ein Beispiel dazu finden Sie unter In Beispielen verwendete Beispieldaten unter diesem Thema.
Extrahieren von Werten mit der GET-Funktion¶
GET akzeptiert einen VARIANT-, OBJECT- oder ARRAY-Wert als erstes Argument und extrahiert den VARIANT-Wert des Elements in dem Pfad, der als zweites Argument angegeben ist.
Berechnen und extrahieren Sie das letzte Element jedes Arrays in einer VARIANT-Spalte mit den Funktionen GET und ARRAY_SIZE. ARRAY_SIZE gibt die Größe des Eingabearrays zurück:
Bemerkung
Dieses Beispiel weicht von der car_sales-Tabelle ab, die an anderer Stelle unter diesem Thema verwendet wird.
CREATE OR replace TABLE colors (v variant);
INSERT INTO
colors
SELECT
parse_json(column1) AS v
FROM
VALUES
('[{r:255,g:12,b:0},{r:0,g:255,b:0},{r:0,g:0,b:255}]'),
('[{c:0,m:1,y:1,k:0},{c:1,m:0,y:1,k:0},{c:1,m:1,y:0,k:0}]')
v;
SELECT *, GET(v, ARRAY_SIZE(v)-1) FROM colors;
+---------------+-------------------------+
| V | GET(V, ARRAY_SIZE(V)-1) |
|---------------+-------------------------|
| [ | { |
| { | "b": 255, |
| "b": 0, | "g": 0, |
| "g": 12, | "r": 0 |
| "r": 255 | } |
| }, | |
| { | |
| "b": 0, | |
| "g": 255, | |
| "r": 0 | |
| }, | |
| { | |
| "b": 255, | |
| "g": 0, | |
| "r": 0 | |
| } | |
| ] | |
| [ | { |
| { | "c": 1, |
| "c": 0, | "k": 0, |
| "k": 0, | "m": 1, |
| "m": 1, | "y": 0 |
| "y": 1 | } |
| }, | |
| { | |
| "c": 1, | |
| "k": 0, | |
| "m": 0, | |
| "y": 1 | |
| }, | |
| { | |
| "c": 1, | |
| "k": 0, | |
| "m": 1, | |
| "y": 0 | |
| } | |
| ] | |
+---------------+-------------------------+
Extrahieren von Werten nach Pfad mit der GET_PATH-Funktion¶
Extrahieren Sie einen Wert aus einer VARIANT-Spalte mit der Funktion GET_PATH, :. Die Funktion ist eine Variation von GET, mit der ein Wert unter Verwendung eines Pfadnamens extrahiert wird. GET_PATH entspricht einer Kette von GET-Funktionen.
Abrufen der Fahrzeugmarken der von den Kunden gekauften Autos:
SELECT GET_PATH(src, 'vehicle[0]:make') FROM car_sales;
+----------------------------------+
| GET_PATH(SRC, 'VEHICLE[0]:MAKE') |
|----------------------------------|
| "Honda" |
| "Toyota" |
+----------------------------------+
Unter Durchsuchen semistrukturierter Daten wird die Pfadsyntax beschrieben, mit der Elemente in einer VARIANT-Spalte abgerufen werden. Die Syntax ist für die Funktion GET oder GET_PATH, : kompakt. Im Gegensatz zur Pfadsyntax können diese Funktionen mit unregelmäßigen Pfaden oder Pfadelementen umgehen.
Die folgenden Abfragen führen zu den gleichen Ergebnissen:
SELECT GET_PATH(src, 'vehicle[0].make') FROM car_sales;
SELECT src:vehicle[0].make FROM car_sales;
Analysieren von Arrays direkt aus Staging-Datendateien¶
Angenommen, eine Stagingdatei namens contacts.json.gz enthält die folgenden Daten:
{
"root": [
{
"employees": [
{
"firstName": "Anna",
"lastName": "Smith"
},
{
"firstName": "Peter",
"lastName": "Jones"
}
]
}
]
}
Weiterhin angenommen, ein Dateiformat namens my_json_format enthält TYPE=JSON in seiner Definition.
Fragen Sie den Namen des ersten Mitarbeiters in der Stagingdatei ab. Im folgenden Beispiel befindet sich die Datei im Tabellen-Stagingbereich customers, aber sie könnte sich auch in jedem anderen internen (d. h. Snowflake) oder externen Stagingbereich befinden:
SELECT 'The First Employee Record is '||
S.$1:root[0].employees[0].firstName||
' '||S.$1:root[0].employees[0].lastName
FROM @%customers/contacts.json.gz (file_format => 'my_json_format') as S;
+----------------------------------------------+
| 'THE FIRST EMPLOYEE RECORD IS '|| |
| S.$1:ROOT[0].EMPLOYEES[0].FIRSTNAME|| |
| ' '||S.$1:ROOT[0].EMPLOYEES[0].LASTNAME |
|----------------------------------------------|
| The First Employee Record is Anna Smith |
+----------------------------------------------+