Abfragen von Variantendaten
Wichtig
Dieses Feature befindet sich in der Public Preview.
In diesem Artikel werden die Databricks-SQL-Operatoren beschrieben, die Sie zum Abfragen und Transformieren von als VARIANT gespeicherten teilstrukturierten Daten verwenden können. Der Datentyp VARIANT ist in Databricks Runtime 15.3 und höher verfügbar.
Databricks empfiehlt die Verwendung von VARIANT anstatt JSON-Zeichenfolgen. Informationen zu Benutzerinnen und Benutzern, die derzeit JSON-Zeichenfolgen verwenden und migrieren möchten, finden Sie unter Wie unterscheidet sich eine Variante von JSON-Zeichenfolgen?.
Wenn Sie Beispiele zum Abfragen halbstrukturierter Daten anzeigen möchten, die mit JSON-Zeichenfolgen gespeichert sind, finden Sie weitere Informationen unter Abfragen von JSON-Zeichenfolgen.
Erstellen einer Tabelle mit einer Variantenspalte
Führen Sie die folgende Abfrage aus, um eine Tabelle mit hochgradig geschachtelten Daten, die als VARIANT gespeichert sind, zu erstellen. In den Beispielen dieses Artikels wird immer auf diese Tabelle verwiesen.
CREATE TABLE store_data AS
SELECT parse_json(
'{
"store":{
"fruit": [
{"weight":8,"type":"apple"},
{"weight":9,"type":"pear"}
],
"basket":[
[1,2,{"b":"y","a":"x"}],
[3,4],
[5,6]
],
"book":[
{
"author":"Nigel Rees",
"title":"Sayings of the Century",
"category":"reference",
"price":8.95
},
{
"author":"Herman Melville",
"title":"Moby Dick",
"category":"fiction",
"price":8.99,
"isbn":"0-553-21311-3"
},
{
"author":"J. R. R. Tolkien",
"title":"The Lord of the Rings",
"category":"fiction",
"reader":[
{"age":25,"name":"bob"},
{"age":26,"name":"jack"}
],
"price":22.99,
"isbn":"0-395-19395-8"
}
],
"bicycle":{
"price":19.95,
"color":"red"
}
},
"owner":"amy",
"zip code":"94025",
"fb:testid":"1234"
}'
) as raw
Abfragen von Feldern in einer Variantenspalte
Die Syntax zum Abfragen von JSON-Zeichenfolgen und anderen komplexen Datentypen in Azure Databricks gilt für VARIANT-Daten, einschließlich der folgenden:
- Verwenden Sie
:, um Felder der obersten Ebene auszuwählen. - Verwenden Sie
.oder[<key>], um geschachtelte Felder mit benannten Schlüsseln auszuwählen. - Verwenden Sie
[<index>], um Werte aus Arrays auszuwählen.
Extrahieren eines Variant-Felds auf oberster Ebene
Um ein Feld zu extrahieren, geben Sie den Namen des JSON-Felds in Ihrem Extraktionspfad an. Bei Feldnamen wird immer zwischen Groß- und Kleinschreibung unterschieden.
SELECT raw:owner FROM store_data
+-------+
| owner |
+-------+
| "amy" |
+-------+
-- Use backticks to escape special characters.
SELECT raw:`zip code`, raw:`fb:testid` FROM store_data
+----------+-----------+
| zip code | fb:testid |
+----------+-----------+
| "94025" | "1234" |
+----------+-----------+
Wenn kein Pfad gefunden werden kann, ist das Ergebnis NULL vom Typ VARIANT.
Extrahieren von geschachtelten Variantenfeldern
Geschachtelte Felder werden über Punktnotation oder mithilfe von eckigen Klammern angegeben. Bei Feldnamen wird immer zwischen Groß- und Kleinschreibung unterschieden.
-- Use dot notation
SELECT raw:store.bicycle FROM store_data
+------------------+
| bicycle |
+------------------+
| { |
| "color":"red", |
| "price":19.95 |
| } |
+------------------+
-- Use brackets
SELECT raw:store['bicycle'] FROM store_data
+------------------+
| bicycle |
+------------------+
| { |
| "color":"red", |
| "price":19.95 |
| } |
+------------------+
Wenn kein Pfad gefunden werden kann, ist das Ergebnis NULL vom Typ VARIANT.
Extrahieren von Werten aus Variantenarrays
Elemente in Arrays werden mit Klammern indiziert. Indizes basieren auf 0.
-- Index elements
SELECT raw:store.fruit[0], raw:store.fruit[1] FROM store_data
+-------------------+------------------+
| fruit | fruit |
+-------------------+------------------+
| { | { |
| "type":"apple", | "type":"pear", |
| "weight":8 | "weight":9 |
| } | } |
+-------------------+------------------+
Wenn der Pfad nicht gefunden werden kann oder der Arrayindex außerhalb der Grenzen liegt, lautet das Ergebnis NULL.
Vereinfachen von Variantenobjekten und -arrays
Die Tabellenwert-Generatorfunktion variant_explode kann verwendet werden, um VARIANT-Arrays und -Objekte zu vereinfachen.
Da es sich bei variant_explode um eine Generatorfunktion handelt, verwenden Sie sie als Teil der FROM-Klausel und nicht in der SELECT-Liste, wie in den folgenden Beispielen:
SELECT key, value
FROM store_data,
LATERAL variant_explode(store_data.raw:store);
+-------+--------------------+
| key| value|
+-------+--------------------+
| basket|[[1,2,{"a":"x","b...|
|bicycle|{"color":"red","p...|
| book|[{"author":"Nigel...|
| fruit|[{"type":"apple",...|
+-------+--------------------+
SELECT pos, value
FROM store_data,
LATERAL variant_explode(store_data.raw:store.basket[0]);
+---+-----------------+
|pos| value|
+---+-----------------+
| 0| 1|
| 1| 2|
| 2|{"a":"x","b":"y"}|
+---+-----------------+
Umwandlungsregeln für Variant-Typen
Sie können Arrays und Skalare mit dem Typ VARIANT speichern. Beim Versuch, Variantentypen in andere Typen zu umwandeln, gelten normale Umwandlungsregeln für einzelne Werte und Felder mit den folgenden zusätzlichen Regeln.
Hinweis
variant_get und try_variant_get nehmen Eingabeargumente auf und befolgen diese Umwandlungsregeln.
| Quellentyp | Behavior |
|---|---|
VOID |
Das Ergebnis ist ein NULL vom Typ VARIANT. |
ARRAY<elementType> |
Der elementType muss ein Typ sein, der in VARIANT umgewandelt werden kann. |
Beim Ableiten des Typs mit schema_of_variant oder schema_of_variant_agg, fallen Funktionen auf den Typ VARIANT zurück, anstatt auf Typ STRING, wenn widersprüchliche Typen vorhanden sind, die nicht aufgelöst werden können.
Sie können :: oder cast verwenden, um Werte in unterstützte Datentypen umzuwandeln.
-- price is returned as a double, not a string
SELECT raw:store.bicycle.price::double FROM store_data
+------------------+
| price |
+------------------+
| 19.95 |
+------------------+
-- cast into more complex types
SELECT cast(raw:store.bicycle AS STRUCT<price DOUBLE, color STRING>) bicycle FROM store_data;
-- `::` also supported
SELECT raw:store.bicycle::STRUCT<price DOUBLE, color STRING> bicycle FROM store_data;
+------------------+
| bicycle |
+------------------+
| { |
| "price":19.95, |
| "color":"red" |
| } |
+------------------+
NULL-Regeln für Varianten
Varianten können zwei Arten von Nullen enthalten:
- SQL
NULL: SQLNULL-Werte deuten darauf hin, dass der Wert fehlt. Dies sind die gleichenNULL-Werte wie bei der Arbeit mit strukturierten Daten. - Variante
NULL: VarianteNULL-Werte geben an, dass die Variante explizit einenNULL-Wert enthält. Diese sind nicht identisch mit SQLNULL-Werten, da der WertNULLin den Daten gespeichert wird.
Verwenden Sie die Funktion is_variant_null, um zu ermitteln, ob der Variant-Wert ein Variante NULL-Wert ist.
SELECT
is_variant_null(parse_json(NULL)) AS sql_null,
is_variant_null(parse_json('null')) AS variant_null,
is_variant_null(parse_json('{ "field_a": null }'):field_a) AS variant_null_value,
is_variant_null(parse_json('{ "field_a": null }'):missing) AS missing_sql_value_null
+--------+------------+------------------+----------------------+
|sql_null|variant_null|variant_null_value|missing_sql_value_null|
+--------+------------+------------------+----------------------+
| false| true| true| false|
+--------+------------+------------------+----------------------+