Variantendaten abfragen

Wichtig

Dieses Feature befindet sich in der Public Preview.

VARIANT ist ein halbstrukturierter Datentyp, der JSON-ähnliche Daten in einem selbst beschreibenden Binärformat speichert. Der Datentyp VARIANT ist in Databricks Runtime 15.3 und höher verfügbar.

Azure Databricks empfiehlt die Verwendung VARIANT über JSON-Zeichenfolgen für halbstrukturierte Daten. 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?.

Informationen zum Abfragen halbstrukturierter Daten, die als JSON-Zeichenfolgen gespeichert sind, finden Sie unter JSON-Abfragezeichenfolgen.

Hinweis

VARIANT Spalten können nicht für Clusterschlüssel, Partitionen oder Z-Reihenfolge-Schlüssel verwendet werden. Der VARIANT Datentyp kann nicht für Vergleiche, Gruppierung, Sortierung und Festlegen von Vorgängen verwendet werden. Eine vollständige Liste der Einschränkungen finden Sie unter Einschränkungen.

Erstellen einer Tabelle mit einer Variantenspalte

Verwenden Sie zum Erstellen einer Variant-Spalte die parse_json Funktion (SQL oder Python).

Führen Sie Folgendes aus, um eine Tabelle mit stark verschachtelten Daten zu erstellen, die als VARIANT gespeichert werden. (Diese Daten werden in anderen Beispielen auf dieser Seite verwendet.)

Python

# Create a table with a variant column
store_data='''
{
  "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"
}
'''

# Create a DataFrame
df = spark.createDataFrame([(store_data,)], ["json"])

# Convert to a variant
df_variant = df.select(parse_json(col("json")).alias("raw"))

# Alternatively, create the DataFrame directly
# df_variant = spark.range(1).select(parse_json(lit(store_data)))

df_variant.display()

# Write out as a table
df_variant.write.saveAsTable("store_data")

SQL

-- Create a table with a variant column
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

SELECT * FROM store_data

Abfragen von Feldern in einer Variantenspalte

Verwenden Sie zum Extrahieren von Feldern aus einer Variant-Spalte die variant_get Funktion (SQL oder Python), die den Namen des JSON-Felds in Ihrem Extraktionspfad angibt. Feldnamen sind immer case-sensitiv.

Python

# Extract a top-level field
df_variant.select(variant_get(col("raw"), "$.owner", "string")).display()

SQL

-- Extract a top-level field
SELECT variant_get(store_data.raw, '$.owner') AS owner FROM store_data

Sie können auch SQL-Syntax verwenden, um Felder in einer Variant-Spalte abzufragen. Siehe SQL Shorthand für variant_get.

SQL-Kurzform für variant_get

Die SQL-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.
SELECT raw:owner FROM store_data

+-------+
| owner |
+-------+
| "amy" |
+-------+

-- Enclose a field name that contains special characters in single quotes inside square brackets.
SELECT raw:['zip code'], raw:['fb:testid'] FROM store_data

+----------+-----------+
| zip code | fb:testid |
+----------+-----------+
| "94025"  | "1234"    |
+----------+-----------+

Die Syntax ['<field>'] maskiert jedes Sonderzeichen in einem Feldnamen, einschließlich Leerzeichen, Punkten (.), Doppelpunkten (:) und eckigen Klammern ([ ]). Azure Databricks empfiehlt diese Syntax für jeden Feldnamen, der ein Sonderzeichen enthält. Verwenden Sie raw:['zip.code'] z. B. zum Auswählen eines Felds mit dem Namen zip.codeoder raw:['A[1]'] zum Auswählen eines Felds mit dem Namen A[1].

Graviszeichen maskieren auch Feldnamen, die Leerzeichen oder Doppelpunkte enthalten, sie maskieren jedoch keine Punkte oder eckigen Klammern. Ein Feldname, der einen Punkt oder eckige Klammern enthält, gibt NULL zurück, wenn er mit Graviszeichen maskiert wird. Verwenden Sie daher für diese Namen die Syntax ['<field>'].

Verwenden Sie zum Extrahieren von Feldern, die Sonderzeichen in PySpark enthalten, dieselbe Klammernsyntax im variant_get Extraktionspfad:

# Escape special characters in the extraction path
df_variant.select(variant_get(col("raw"), "$['zip.code']", "string"))
df_variant.select(variant_get(col("raw"), "$['A[1]']", "string"))

Extrahieren von geschachtelten Variantenfeldern

Wenn Sie geschachtelte Felder aus einer Variantenspalte extrahieren möchten, geben Sie diese mithilfe von Punktnotation oder eckigen Klammern an. Feldnamen sind immer case-sensitiv.

Python

# Use dot notation
df_variant.select(variant_get(col("raw"), "$.store.bicycle", "string")).display()

# Use brackets
df_variant.select(variant_get(col("raw"), "$.store['bicycle']", "string")).display()

Wenn kein Pfad gefunden werden kann, ist das Ergebnis null vom Typ VariantVal.

SQL

-- Use dot notation
SELECT raw:store.bicycle FROM store_data

-- Use brackets
SELECT raw:store['bicycle'] FROM store_data

Wenn kein Pfad gefunden werden kann, ist das Ergebnis NULL vom Typ VARIANT.

+-----------------+
| bicycle         |
+-----------------+
| {               |
| "color":"red",  |
| "price":19.95   |
| }               |
+-----------------+

Werte aus Variantenarrays extrahieren

Um Elemente aus Arrays zu extrahieren, verwenden Sie den Index mit Klammern. Indizes basieren auf 0.

Python

# Index elements
df_variant.select((variant_get(col("raw"), "$.store.fruit[0]", "string")),(variant_get(col("raw"), "$.store.fruit[1]", "string"))).display()

SQL

-- 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, ist das Ergebnis NULL.

Arbeiten mit Varianten in Python

Sie können Varianten aus Spark DataFrames in Python als VariantVal extrahieren und einzeln unter Verwendung der Methoden toPython und toJson mit ihnen arbeiten.

# toPython
data = [
    ('{"name": "Alice", "age": 25}',),
    ('["person", "electronic"]',),
    ('1',)
]

df_person = spark.createDataFrame(data, ["json"])

# Collect variants into a VariantVal
variants = df_person.select(parse_json(col("json")).alias("v")).collect()

Geben Sie die VariantVal JSON-Zeichenfolge aus:

print(variants[0].v.toJson())

{"age":25,"name":"Alice"}

Konvertiere VariantVal in ein Python-Objekt:

# First element is a dictionary
print(variants[0].v.toPython()["age"])

25

# Second element is a List
print(variants[1].v.toPython()[1])

electronic

# Third element is an Integer
print(variants[2].v.toPython())

1

Sie können auch VariantVal unter Verwendung der VariantVal.parseJson-Funktion konstruieren.

# parseJson to construct VariantVal's in Python
from pyspark.sql.types import VariantVal

variant = VariantVal.parseJson('{"a": 1}')

Drucken Sie die Variante als JSON-Zeichenfolge:

print(variant.toJson())

{"a":1}

Konvertieren Sie die Variante in ein Python-Objekt, und drucken Sie einen Wert:

print(variant.toPython()["a"])

1

Zurückgeben des Schemas einer Variante

Verwenden Sie die schema_of_variant Funktion (SQL oder Python), um das Schema einer Variante zurückzugeben.

Python

# Return the schema of the variant
df_variant.select(schema_of_variant(col("raw"))).display()

SQL

-- Return the schema of the variant
SELECT schema_of_variant(raw) FROM store_data;

Um die kombinierten Schemas aller Varianten in einer Gruppe zurückzugeben, verwenden Sie die schema_of_variant_agg Funktion (SQL oder Python).

In den folgenden Beispielen wird das Schema und dann das kombinierte Schema für die Beispieldaten json_datazurückgegeben.

Python


json_data = [
    ('{"name": "Alice", "age": 25}',),
    ('{"id": 101, "department": "HR"}',),
    ('{"product": "Laptop", "price": 1200.50, "in_stock": true}',)
]

df_item = spark.createDataFrame(json_data, ["json"])

# Return the schema
df_item.select(parse_json(col("json")).alias("v")).select(schema_of_variant(col("v"))).display()

SQL

CREATE OR REPLACE TEMP VIEW json_data AS
SELECT '{"name": "Alice", "age": 25}' AS json UNION ALL
SELECT '{"id": 101, "department": "HR"}' UNION ALL
SELECT '{"product": "Laptop", "price": 1200.50, "in_stock": true}';

-- Return the schema
SELECT schema_of_variant(parse_json(json)) FROM json_data;

+-----------------------------------------------------------------+
| schema_of_variant(v)                                            |
+-----------------------------------------------------------------+
| OBJECT<age: BIGINT, name: STRING>                               |
| OBJECT<department: STRING, id: BIGINT>                          |
| OBJECT<in_stock: BOOLEAN, price: DECIMAL(5,1), product: STRING> |
+-----------------------------------------------------------------+

Python

# Return the combined schema
df.select(parse_json(col("json")).alias("v")).select(schema_of_variant_agg(col("v"))).display()

SQL

-- Return the combined schema
SELECT schema_of_variant_agg(parse_json(json)) FROM json_data;

+----------------------------------------------------------------------------------------------------------------------------+
| schema_of_variant(v)                                                                                                       |
+----------------------------------------------------------------------------------------------------------------------------+
| OBJECT<age: BIGINT, department: STRING, id: BIGINT, in_stock: BOOLEAN, name: STRING, price: DECIMAL(5,1), product: STRING> |
+----------------------------------------------------------------------------------------------------------------------------+

Entflachen von Variantenobjekten und -arrays

Die variant_explode Tabellenwert-Generatorfunktion (SQL oder Python) kann verwendet werden, um Variablenarrays und Objekte abzuflachen.

Python

Verwenden Sie die Table-Valued Function (TVF) DataFrame API, um eine Variante in mehrere Zeilen zu erweitern.

spark.tvf.variant_explode(parse_json(lit(store_data))).display()

# To explode a nested field, first create a DataFrame with just the field
df_store_col = df_variant.select(variant_get(col("raw"), "$.store", "variant").alias("store"))

# Perform the explode with a lateral join and the outer function to return the new exploded DataFrame
df_store_exploded_lj = df_store_col.lateralJoin(spark.tvf.variant_explode(col("store").outer()))
df_store_exploded = df_store_exploded_lj.drop("store")
df_store_exploded.display()

SQL

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

SELECT pos, value
  FROM store_data,
  LATERAL variant_explode(store_data.raw:store.basket[0]);

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 akzeptieren Typargumente und befolgen diese Umwandlungsregeln.

Quellentyp Verhalten
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 den Typ STRING, wenn konfliktierende Typen vorhanden sind, die nicht aufgelöst werden können.

Python

Verwenden Sie die try_variant_get-Funktion (Python) zum Umwandeln:

# price is returned as a double, not a string
df_variant.select(try_variant_get(col("raw"), "$.store.bicycle.price", "double").alias("price"))

+------------------+
| price            |
+------------------+
| 19.95            |
+------------------+

SQL

Verwenden Sie die try_variant_get Funktion (SQL) zum Casten:

-- price is returned as a double, not a string
SELECT try_variant_get(raw, '$.store.bicycle.price', 'double') as price FROM store_data

+------------------+
| price            |
+------------------+
| 19.95            |
+------------------+

Sie können auch :: oder cast verwenden, um Werte in unterstützte Datentypen zu konvertieren.

-- 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"  |
| }                |
+------------------+

Verwenden Sie auch die try_variant_get-Funktion (SQL oder Python), um Umwandlungsfehler zu behandeln:

Python

spark.range(1).select(parse_json(lit('{"a" : "c", "b" : 2}')).alias("v")).select(try_variant_get(col('v'), '$.a', 'boolean')).display()

SQL

SELECT try_variant_get(
  parse_json('{"a" : "c", "b" : 2}'),
  '$.a',
  'boolean'
)

Varianten-Null-Regeln

Verwenden Sie die is_variant_null Funktion (SQL oder Python), um zu bestimmen, ob der Variant-Wert eine Variante null ist.

Python

data = [
    ('null',),
    (None,),
    ('{"field_a" : 1, "field_b" : 2}',)
]

df = spark.createDataFrame(data, ["null_data"])
df.select(parse_json(col("null_data")).alias("v")).select(is_variant_null(col("v"))).display()

+------------------+
|is_variant_null(v)|
+------------------+
|              true|
+------------------+
|             false|
+------------------+
|             false|
+------------------+

SQL

Varianten können zwei Arten von Nullen enthalten:

  • SQL NULL: SQL NULL-Werte deuten darauf hin, dass der Wert fehlt. Dies sind die gleichen NULL-Werte wie bei der Arbeit mit strukturierten Daten.
  • Variante NULL: Variante NULL-Werte geben an, dass die Variante explizit einen NULL-Wert enthält. Diese sind nicht identisch mit SQL NULL-Werten, da der Wert NULL in den Daten gespeichert wird.
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|
+--------+------------+------------------+----------------------+

Weitere Ressourcen

  • Last updated on