Query semi-structured data in SQL | Databricks on AWS [2021/6/11時点]の翻訳です。
本書は抄訳であり内容の正確性を保証するものではありません。正確な内容に関しては原文を参照ください。
注意
Databricksランタイム8.1以降で使用できます。
本書では、JSONとして格納されている準構造化データをクエリー、変換するために使用できるDatabricksのSQLオペレーターを説明します。
注意
この機能を用いることでファイルをflattenすることなしに準構造化データを読み込むことができます。しかし、最適な読み込みクエリー性能を発揮するためには、ネストされたカラムを適切なデータ型で抽出することをお勧めします。
構文
<column-name>:<extraction-path>構文を用いて、JSON文字列を含むフィールドからカラムを抽出します。ここで、<column-name>はカラム名の文字列であり、<extraction-path>は抽出するフィールドへのパスとなります。文字列が返却されます。
サンプル
以下のサンプルでは、サンプルデータの文を用いて作成したデータを使用しています。
トップレベルカラムの抽出
カラムを抽出するには、抽出のパスでJSONフィールド名を指定します。
括弧の中にカラム名を指定することができます。括弧内で参照されるカラムは大文字小文字を区別します。また、括弧内ではないカラム名は大文字小文字を区別しません。
SELECT raw:owner, RAW:owner FROM store_data
+-------+-------+
| owner | owner |
+-------+-------+
| amy | amy |
+-------+-------+
-- 括弧を使用した際の参照は大文字小文字を区別します
SELECT raw:OWNER case_insensitive, raw:['OWNER'] case_sensitive FROM store_data
+------------------+----------------+
| case_insensitive | case_sensitive |
+------------------+----------------+
| amy | null |
+------------------+----------------+
空白や特殊文字をエスケープするためにはバックティックスを使用します。フィールド名は大文字小文字を区別しません。
-- 特殊文字のエスケープにバックティックスを使用します。バックティックスを使用した際には大文字小文字を区別しません。
-- 大文字小文字を区別する際には括弧を使用します。
SELECT raw:`zip code`, raw:`Zip Code`, raw:['fb:testid'] FROM store_data
+----------+----------+-----------+
| zip code | Zip Code | fb:testid |
+----------+----------+-----------+
| 94025 | 94025 | 1234 |
+----------+----------+-----------+
注意
大文字小文字を区別しないことにより、検索パスにマッチするカラムが複数JSONに含まれる場合、括弧を使うように指示するエラーが発生します。複数行に渡るカラムの一致があった場合にはエラーとはなりません。{"foo":"bar", "Foo":"bar"}はエラーとなりますが、以下の場合はエラーとなりません。
{"foo":"bar"}
{"Foo":"bar"}
ネストされたフィールドの抽出
ドットと括弧を用いてネストされたフィールドを指定することができます。括弧を使用する際、カラムは大文字小文字を区別します。
-- ドット表記の使用
SELECT raw:store.bicycle FROM store_data
-- 返却カラムは文字列となります
+------------------+
| bicycle |
+------------------+
| { |
| "price":19.95, |
| "color":"red" |
| } |
+------------------+
-- 括弧の使用
SELECT raw:store['bicycle'], raw:store['BICYCLE'] FROM store_data
+------------------+---------+
| bicycle | BICYCLE |
+------------------+---------+
| { | null |
| "price":19.95, | |
| "color":"red" | |
| } | |
+------------------+---------+
配列から値の抽出
括弧内配列の要素にインデックスを指定します。インデックスは0からスタートします。配列の全ての要素からサブフィールドを抽出するためにドットか括弧の後にアスタリスク(*)を使用することができます。
-- 配列のインデックス
SELECT raw:store.fruit[0], raw:store.fruit[1] FROM store_data
+------------------+-----------------+
| fruit | fruit |
+------------------+-----------------+
| { | { |
| "weight":8, | "weight":9, |
| "type":"apple" | "type":"pear" |
| } | } |
+------------------+-----------------+
-- 配列からサブフィールドを抽出
SELECT raw:store.book[*].isbn FROM store_data
+--------------------+
| isbn |
+--------------------+
| [ |
| null, |
| "0-553-21311-3", |
| "0-395-19395-8" |
| ] |
+--------------------+
-- 配列内の配列、配列内のstructへのアクセス
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"} | | |
| ] | ] | ] | | |
+----------------------------+------------------+---------------------+---------------------------------+----------+
値のキャスト
基本的なデータ型にキャストするために::を使用することができます。ネストされた結果を配列やstructのように、より複雑なデータ型にキャストするためにfrom_jsonメソッドを使用することができます。
-- priceは文字列ではなくdoubleとして返却されます
SELECT raw:store.bicycle.price::double FROM store_data
+------------------+
| price |
+------------------+
| 19.95 |
+------------------+
-- より複雑な型にキャストするためにfrom_jsonを使用します
SELECT from_json(raw:store.bicycle, 'price double, color string') bicycle FROM store_data
-- 返却されるカラムはpriceとcolorを含むstructになります
+------------------+
| bicycle |
+------------------+
| { |
| "price":19.95, |
| "color":"red" |
| } |
+------------------+
SELECT from_json(raw:store.basket[*], 'array<array<string>>') baskets FROM store_data
-- 返却されるカラムは文字列配列の配列となります
+------------------------------------------+
| basket |
+------------------------------------------+
| [ |
| ["1","2","{\"b\":\"y\",\"a\":\"x\"}]", |
| ["3","4"], |
| ["5","6"] |
| ] |
+------------------------------------------+
サンプルデータ
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
NULL値の挙動
JSONフィールドにnull値がある場合、nullというテキストの値ではなく、当該カラムに対するSQLのnull値を受け取ります。
select '{"key":null}':key is null sql_null, '{"key":null}':key == 'null' text_null
+-------------+-----------+
| sql_null | text_null |
+-------------+-----------+
| true | null |
+-------------+-----------+