Interroger des chaînes JSON
Cet article décrit les opérateurs SQL Databricks que vous pouvez utiliser pour interroger et transformer des données semi-structurées stockées sous forme de chaînes JSON.
Remarque
Cette fonctionnalité vous permet de lire des données semi-structurées sans avoir à aplatir les fichiers. Toutefois, pour optimiser les performances des requêtes de lecture, Databricks vous recommande d’extraire les colonnes imbriquées avec les types de données corrects.
Vous extrayez une colonne de champs contenant des chaînes JSON à l’aide de la syntaxe <column-name>:<extraction-path>, où <column-name> est le nom de la colonne de chaîne et <extraction-path> est le chemin d’accès au champ à extraire. Les résultats renvoyés sont des chaînes.
Créer une table avec des données hautement imbriquées
Exécutez la requête suivante pour créer une table avec des données hautement imbriquées. Les exemples de cet article font tous référence à cette table.
CREATE TABLE store_data AS SELECT
'{
"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
Extraire une colonne de niveau supérieur
Pour extraire une colonne, spécifiez le nom du champ JSON dans votre chemin d’extraction.
Vous pouvez saisir les noms de colonne entre crochets. Les colonnes référencées entre crochets sont mises en correspondance en respectant la casse. Le nom de colonne est également référencé sans respect de la casse.
SELECT raw:owner, RAW:owner FROM store_data
+-------+-------+
| owner | owner |
+-------+-------+
| amy | amy |
+-------+-------+
-- References are case sensitive when you use brackets
SELECT raw:OWNER case_insensitive, raw:['OWNER'] case_sensitive FROM store_data
+------------------+----------------+
| case_insensitive | case_sensitive |
+------------------+----------------+
| amy | null |
+------------------+----------------+
Utilisez des backticks pour éviter les espaces et les caractères spéciaux. Les noms de champs sont mis en correspondance sans respect de la casse.
-- Use backticks to escape special characters. References are case insensitive when you use backticks.
-- Use brackets to make them case sensitive.
SELECT raw:`zip code`, raw:`Zip Code`, raw:['fb:testid'] FROM store_data
+----------+----------+-----------+
| zip code | Zip Code | fb:testid |
+----------+----------+-----------+
| 94025 | 94025 | 1234 |
+----------+----------+-----------+
Notes
Si un enregistrement JSON contient plusieurs colonnes qui peuvent correspondre à votre chemin d’extraction en raison de la mise en correspondance qui ne respecte pas la casse, vous recevez un message d’erreur vous demandant d’utiliser des crochets. En cas de correspondances entre les colonnes et les lignes, vous ne recevrez aucune erreur. La commande suivante génère une erreur : {"foo":"bar", "Foo":"bar"}, et les éléments suivants ne génèrent pas d’erreur :
{"foo":"bar"}
{"Foo":"bar"}
Extraire les champs imbriqués
Vous spécifiez des champs imbriqués à l’aide de la notation par points ou entre crochets. Lorsque vous utilisez des crochets, les colonnes sont mises en correspondance en respectant la casse.
-- Use dot notation
SELECT raw:store.bicycle FROM store_data
-- the column returned is a string
+------------------+
| bicycle |
+------------------+
| { |
| "price":19.95, |
| "color":"red" |
| } |
+------------------+
-- Use brackets
SELECT raw:store['bicycle'], raw:store['BICYCLE'] FROM store_data
+------------------+---------+
| bicycle | BICYCLE |
+------------------+---------+
| { | null |
| "price":19.95, | |
| "color":"red" | |
| } | |
+------------------+---------+
Extraire des valeurs des tableaux
Vous indexez des éléments dans des tableaux à l’aide de crochets. Les indices sont de base 0. Vous pouvez utiliser un astérisque (*) suivi d’une notation sous forme de points ou de crochets pour extraire des sous-champs de tous les éléments d’un tableau.
-- Index elements
SELECT raw:store.fruit[0], raw:store.fruit[1] FROM store_data
+------------------+-----------------+
| fruit | fruit |
+------------------+-----------------+
| { | { |
| "weight":8, | "weight":9, |
| "type":"apple" | "type":"pear" |
| } | } |
+------------------+-----------------+
-- Extract subfields from arrays
SELECT raw:store.book[*].isbn FROM store_data
+--------------------+
| isbn |
+--------------------+
| [ |
| null, |
| "0-553-21311-3", |
| "0-395-19395-8" |
| ] |
+--------------------+
-- Access arrays within arrays or structs within arrays
SELECT
raw:store.basket[*],
raw:store.basket[*][0] first_of_baskets,
raw:store.basket[0][*] first_basket,
raw:store.basket[*][*] all_elements_flattened,
raw:store.basket[0][2].b subfield
FROM store_data
+----------------------------+------------------+---------------------+---------------------------------+----------+
| basket | first_of_baskets | first_basket | all_elements_flattened | subfield |
+----------------------------+------------------+---------------------+---------------------------------+----------+
| [ | [ | [ | [1,2,{"b":"y","a":"x"},3,4,5,6] | y |
| [1,2,{"b":"y","a":"x"}], | 1, | 1, | | |
| [3,4], | 3, | 2, | | |
| [5,6] | 5 | {"b":"y","a":"x"} | | |
| ] | ] | ] | | |
+----------------------------+------------------+---------------------+---------------------------------+----------+
Convertir les valeurs
Vous pouvez utiliser :: pour convertir les valeurs en types de données de base. Utilisez la méthode from_json pour convertir des résultats imbriqués en types de données plus complexes, tels que des tableaux ou des structs.
-- price is returned as a double, not a string
SELECT raw:store.bicycle.price::double FROM store_data
+------------------+
| price |
+------------------+
| 19.95 |
+------------------+
-- use from_json to cast into more complex types
SELECT from_json(raw:store.bicycle, 'price double, color string') bicycle FROM store_data
-- the column returned is a struct containing the columns price and color
+------------------+
| bicycle |
+------------------+
| { |
| "price":19.95, |
| "color":"red" |
| } |
+------------------+
SELECT from_json(raw:store.basket[*], 'array<array<string>>') baskets FROM store_data
-- the column returned is an array of string arrays
+------------------------------------------+
| basket |
+------------------------------------------+
| [ |
| ["1","2","{\"b\":\"y\",\"a\":\"x\"}]", |
| ["3","4"], |
| ["5","6"] |
| ] |
+------------------------------------------+
Comportement NULL
Lorsqu’un champ JSON avec une valeur null est présent, vous recevez une valeur SQL correspondant à null pour cette colonne, et non une valeur texte correspondant à null.
select '{"key":null}':key is null sql_null, '{"key":null}':key == 'null' text_null
+-------------+-----------+
| sql_null | text_null |
+-------------+-----------+
| true | null |
+-------------+-----------+
Transformer des données imbriquées à l’aide d’opérateurs Spark SQL
Apache Spark dispose d’un certain nombre de fonctions intégrées permettant d’utiliser des données complexes et imbriquées. Le notebook suivant contient des exemples.
En outre, les fonctions d’ordre supérieur fournissent de nombreuses options supplémentaires lorsque les opérateurs Spark intégrés ne sont pas disponibles pour transformer les données comme vous le souhaitez.