Remarque
L’accès à cette page nécessite une autorisation. Vous pouvez essayer de vous connecter ou de modifier des répertoires.
L’accès à cette page nécessite une autorisation. Vous pouvez essayer de modifier des répertoires.
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 des performances de requête de lecture optimales, Databricks recommande d’extraire des colonnes imbriquées avec les types de données appropriés.
Vous extrayez une colonne à partir 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> 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 toutes référence à ce tableau.
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 fournir des noms de colonnes entre crochets. Les colonnes référencées entre crochets sont sensibles à la casse. Le nom de colonne est également référencé de manière non sensible à 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 |
+----------+----------+-----------+
Remarque
Si un enregistrement JSON contient plusieurs colonnes qui peuvent correspondre à votre chemin d’extraction en raison d’une correspondance non sensible à la casse, vous recevrez une erreur vous demandant d’utiliser des crochets. Si vous avez des correspondances de colonnes sur plusieurs lignes, vous ne recevrez aucune erreur. Les éléments suivants lèvent une erreur : {"foo":"bar", "Foo":"bar"}et les éléments suivants ne lèvent 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 correspondent à 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 à partir de 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"} | | |
| ] | ] | ] | | |
+----------------------------+------------------+---------------------+---------------------------------+----------+
Valeurs de distribution
Vous pouvez utiliser :: pour convertir des 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 existe avec une null valeur, vous recevrez une valeur SQL null pour cette colonne, et non une null valeur de texte.
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 de commande supérieure 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.