Aller au contenu principal
Dernière mise à jour le

query variant data

Cet article décrit comment vous pouvez interroger et transformer des données semi-structurées stockées en tant que VARIANT. Le type de données VARIANT est disponible dans Databricks Runtime 15,4 et versions ultérieures.

Databricks recommande d'utiliser VARIANT plutôt que des chaînes JSON pour les données semi-structurées. Pour les utilisateurs utilisant actuellement des chaînes JSON et cherchant à migrer, consultez En quoi le variant est-il différent des chaînes JSON ?.

Pour interroger les données semi-structurées stockées sous forme de chaînes JSON, consultez Interroger les chaînes JSON.

remarque

VARIANT les colonnes ne peuvent pas être utilisées pour les clés de clustering, les partitions ou les clés de Z-order. Le type de données VARIANT ne peut pas être utilisé pour les comparaisons, les regroupements, le tri et les opérations d'ensemble. Pour obtenir la liste complète des limitations, consultez Limitations.

Créez une table avec une colonne variante.​

Pour créer une colonne variante, utilisez la fonction parse_json (SQL ou Python).

Exécutez ce qui suit pour créer une table avec des données très imbriquées stockées en tant que VARIANT. (Ces données sont utilisées dans d'autres exemples sur cette page.)

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

Champs de query dans une colonne variante​

Pour extraire des champs d’une colonne variante, utilisez la fonction variant_get (SQL ou Python) en spécifiant le nom du champ JSON dans votre chemin d’extraction. Les noms de champ sont toujours sensibles à la casse.

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

Raccourci SQL pour variant_get​

La syntaxe SQL pour l'interrogation des chaînes JSON et d'autres types de données complexes sur Databricks s'applique aux données VARIANT, y compris les suivantes :

  • Utilisez : pour sélectionner les champs de niveau supérieur.
  • Utilisez . ou [<key>] pour sélectionner les champs imbriqués avec des clés nommées.
  • Utilisez [<index>] pour sélectionner des valeurs dans les tableaux.
SQL
SELECT raw:owner FROM store_data
+-------+
| owner |
+-------+
| "amy" |
+-------+
SQL
-- 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"    |
+----------+-----------+

La syntaxe ['<field>'] échappe tout caractère spécial dans un nom de champ, y compris les espaces, les points (.), les deux points (:) et les crochets ([ ]). Databricks recommande cette syntaxe pour chaque nom de champ qui contient un caractère spécial. Par exemple, utilisez raw:['zip.code'] pour sélectionner un champ nommé zip.code, ou raw:['A[1]'] pour sélectionner un champ nommé A[1].

Les apostrophes inversées échappent également les noms de champs qui contiennent des espaces ou des deux-points, mais elles n'échappent pas les points ou les crochets. Un nom de champ qui contient un point ou des crochets retourne NULL lorsqu'il est échappé avec des apostrophes inversées, utilisez donc la syntaxe ['<field>'] pour ces noms.

Pour extraire les champs qui contiennent des caractères spéciaux dans PySpark, utilisez la même syntaxe de crochets dans le chemin d'extraction variant_get :

Python
# 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"))

Extraire les champs imbriqués de variantes​

Pour extraire des champs imbriqués d’une colonne variante, spécifiez-les à l’aide de la notation par points ou de crochets. Les noms de champs sont toujours sensibles à la casse.

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

Si un chemin d'accès est introuvable, le résultat est null de type VariantVal.

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

Extraction de valeurs de tableaux de variants​

Pour extraire les éléments des tableaux, utilisez les crochets comme index. Les index commencent à 0.

Python
# Index elements
df_variant.select((variant_get(col("raw"), "$.store.fruit[0]", "string")),(variant_get(col("raw"), "$.store.fruit[1]", "string"))).display()
+-------------------+------------------+
| fruit             | fruit            |
+-------------------+------------------+
| {                 | {                |
|   "type":"apple", |   "type":"pear", |
|   "weight":8      |   "weight":9     |
| }                 | }                |
+-------------------+------------------+

Si le chemin est introuvable, ou si l’index du tableau est hors limites, le résultat est null.

Utilisation des variantes en Python​

Vous pouvez extraire des variantes des Spark DataFrames dans Python en tant que VariantVal et travailler avec elles individuellement en utilisant les méthodes toPython et toJson.

Python
# 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()

Affichez VariantVal en tant que chaîne JSON :

Python
print(variants[0].v.toJson())
{"age":25,"name":"Alice"}

Convertir un VariantVal en objet Python :

Python
# First element is a dictionary
print(variants[0].v.toPython()["age"])
25
Python
# Second element is a List
print(variants[1].v.toPython()[1])
electronic
Python
# Third element is an Integer
print(variants[2].v.toPython())
1

Vous pouvez également construire VariantVal à l'aide de la fonction VariantVal.parseJson.

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

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

Imprimez la variante en tant que chaîne JSON :

Python
print(variant.toJson())
{"a":1}

Convertissez la variante en objet Python et imprimez une valeur :

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

Retourner le schéma d'une variante​

Pour renvoyer le schéma d'une variante, utilisez la fonction schema_of_variant (SQL ou Python).

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

Pour renvoyer les schémas combinés de toutes les variantes d'un groupe, utilisez la fonction schema_of_variant_agg (SQL ou Python).

Les exemples suivants renvoient le schéma, puis le schéma combiné pour les données d'exemple json_data.

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

Aplatir les objets et les tableaux variants​

La fonction génératrice à valeur de table variant_explode (SQL ou Python) peut être utilisée pour aplatir les tableaux et les objets variants.

Utilisez l'API DataFrame de fonction à valeur de table (TVF) pour développer une variante en plusieurs lignes :

Python
spark.tvf.variant_explode(parse_json(lit(store_data))).display()
Python
# 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()

Règles de transtypage de type Variant​

Vous pouvez stocker des tableaux et des scalaires à l'aide du type VARIANT. Lorsque vous tentez de convertir des types de variantes en d'autres types, les règles de conversion normales s'appliquent aux valeurs et champs individuels, avec les règles supplémentaires suivantes.

remarque

variant_get et try_variant_get acceptent les arguments de type et suivent ces règles de transtypage.

Type de source

Comportement

VOID

Le résultat est un NULL de type VARIANT.

ARRAY<elementType>

Le elementType doit être un type qui peut être converti en VARIANT.

Type de source

Comportement

VOID

Le résultat est un NULL de type VARIANT.

ARRAY<elementType>

Le elementType doit être un type qui peut être converti en VARIANT.

Lors de l'inférence de type avec schema_of_variant ou schema_of_variant_agg, les fonctions se rabattent sur le type VARIANT plutôt que sur le type STRING lorsque des types conflictuels sont présents et ne peuvent pas être résolus.

Utilisez la fonction try_variant_get (Python) pour caster :

Python
# 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            |
+------------------+

Utilisez également la fonction try_variant_get (SQL ou Python) pour gérer les échecs de conversion :

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

Règles de nullité de variant​

Utilisez la fonction is_variant_null (SQL ou Python) pour déterminer si la valeur de variante est une valeur nulle de variante.

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

Ressources supplémentaires​

Sur cette page