Interroger des données de variante

Important

Cette fonctionnalité est disponible en préversion publique.

Cet article explique comment 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.3 et versions ultérieures.

Databricks recommande d’utiliser VARIANT sur les chaînes JSON. Pour les utilisateurs qui utilisent actuellement des chaînes JSON qui cherchent à migrer, consultez Comment la variante diffère-t-elle des chaînes JSON ?.

Si vous souhaitez voir des exemples d’interrogation de données semi-structurées stockées avec des chaînes JSON, consultez Chaînes JSON de requête.

Remarque

VARIANT les colonnes ne peuvent pas être utilisées pour les clés de clustering, les partitions ou les clés de classement Z. Le type de données VARIANT ne peut pas être utilisé pour les comparaisons, le regroupement, l’ordre et les opérations de définition. Pour obtenir la liste complète des limitations, consultez Limitations.

Créer une table avec une colonne de variante

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

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

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

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

Interroger des champs dans une colonne de variantes

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

SQL

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

Vous pouvez également utiliser la syntaxe SQL pour interroger des champs dans une colonne variant. Consultez le raccourci SQL pour variant_get.

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 de chaînes JSON et d’autres types de données complexes sur Azure Databricks s’applique aux VARIANT données, notamment les suivantes :

Permet : de sélectionner des champs de niveau supérieur.
Utilisez . ou [<key>], pour sélectionner des champs imbriqués avec des clés nommées.
Utilisez [<index>] pour sélectionner des valeurs dans des tableaux.

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

Si un nom de champ contient un point (.), vous devez l’échapper entre crochets ([ ]). Par exemple, la requête suivante sélectionne un champ nommé zip.code :

SELECT raw:['zip.code'] FROM store_data

Extraire des champs imbriqués variants

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

SQL

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

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

Si un chemin d’accès est introuvable, le résultat est NULL de type VARIANT.

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

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

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

Extraire des valeurs à partir de tableaux de variantes

Pour extraire des éléments à partir de tableaux, index avec crochets. Les indices sont de base 0.

SQL

-- Index elements
SELECT raw:store.fruit[0], raw:store.fruit[1] FROM store_data

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 d’accès est introuvable ou si l’index de tableau est hors limites, le résultat est null.

Utilisation de variantes dans Python

Vous pouvez extraire des variantes de DataFrames Spark en Python avec VariantVal et les utiliser individuellement grâce aux méthodes toPython et toJson.

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

Sortie sous la forme d’une VariantVal chaîne JSON :

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

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

Convertissez un VariantVal en un objet Python :

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

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

electronic

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

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

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

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

Imprimez la variante sous forme de chaîne JSON :

print(variant.toJson())

{"a":1}

Convertissez la variante en objet Python et imprimez une valeur :

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

Retourner le schéma d’une variante

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

SQL

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

Python

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

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

Les exemples suivants retournent le schéma, puis le schéma combiné pour les exemples de données json_data.

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;

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

SQL

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

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 tableaux de variantes

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

SQL

Étant donné que variant_explode est une fonction générateur, vous l’utilisez dans le cadre de la clause FROM plutôt que dans la liste SELECT, comme illustré dans les exemples suivants.

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

Python

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

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

Règles de distribution des types de variantes

Vous pouvez stocker des tableaux et des scalaires en utilisant le type VARIANT. Lorsque vous essayez 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 additionnelles suivantes.

Remarque

variant_get et try_variant_get prenez des arguments de type et suivez ces règles de distribution.

Type de source	Comportement
`VOID`	Le résultat est un `NULL` de type `VARIANT`.
`ARRAY<elementType>`	`elementType` doit être un type pouvant être converti en `VARIANT`.

Lors de l’inférence de type avec schema_of_variant ou schema_of_variant_agg, les fonctions reviennent au type VARIANT plutôt qu’au type STRING lorsque des conflits de type sont présents et ne peuvent pas être résolus.

SQL

Utilisez la fonction try_variant_get pour effectuer une conversion :

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

Vous pouvez également utiliser :: ou cast convertir des valeurs en types de données pris en charge :

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

Python

Utilisez la fonction try_variant_get (Python) pour effectuer un cast :

# 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 try_variant_get fonction (SQL ou Python) pour gérer les échecs de cast :

SQL

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

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 null de variante

Utilisez la is_variant_null fonction (SQL ou Python) pour déterminer si la valeur variant est une valeur null variant.

SQL

Les variantes peuvent contenir deux types de valeurs Null :

SQL NULL : SQL NULLindique que la valeur est manquante. Il s’agit des mêmes NULLque lors de la gestion des données structurées.
Variante NULL : les variantes NULL indiquent que la variante contient explicitement une valeur NULL. Elles ne sont pas identiques aux NULL de SQL, car la valeur NULL est stockée dans les données.

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

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

Commentaires

Cette page a-t-elle été utile ?

Last updated on 2025-12-02

Partager via

Interroger des données de variante

Créer une table avec une colonne de variante

SQL

Python

Interroger des champs dans une colonne de variantes

SQL

Python

Raccourci SQL pour variant_get

Extraire des champs imbriqués variants

SQL

Python

Extraire des valeurs à partir de tableaux de variantes

SQL

Python

Utilisation de variantes dans Python

Retourner le schéma d’une variante

SQL

Python

SQL

Python

SQL

Python

Aplatir les objets et tableaux de variantes

SQL

Python

Règles de distribution des types de variantes

SQL

Python

SQL

Python

Règles null de variante

SQL

Python

Commentaires

Ressources supplémentaires