クエリー半構造化データ in Databricks

この記事では、JSON として格納されている半構造化データのクエリと変換に使用できる Databricks SQL 演算子について説明します。

注

この機能を使用すると、ファイルをフラット化せずに半構造化データを読み取ることができます。ただし、読み取りクエリのパフォーマンスを最適化するには、Databricks では、正しいデータ型の入れ子になった列を抽出することをお勧めします。

JSON 文字列を含むフィールドから列を抽出するには、構文 <column-name>:<extraction-path>を使用します。ここで、 <column-name> は文字列列名、 <extraction-path> は抽出するフィールドへのパスです。返される結果は文字列です。

高度にネストされたデータを含むテーブルを作成する

次のクエリーを実行して、高度にネストされたデータを含むテーブルを作成します。この記事の例はすべて、この表を参照しています。

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

最上位の列を抽出する

列を抽出するには、抽出パスの JSON フィールドの名前を指定します。

列名は角かっこで囲んで指定できます。角かっこで囲まれて参照される列は、大文字と小文字 が区別されます。列名も大文字と小文字を区別せずに参照されます。

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

バッククォートを使用して、スペースと特殊文字をエスケープします。フィールド名は大文字と小文字 を区別しません。

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

注

大文字と小文字を区別しない一致のために抽出パスに一致する可能性のある複数の列が JSON レコードに含まれている場合、角かっこを使用するように求めるエラーが表示されます。行間で列が一致している場合、エラーは発生しません。以下はエラーをスローします: {"foo":"bar", "Foo":"bar"}、そして以下はエラーをスローしません:

{"foo":"bar"}
{"Foo":"bar"}

ネストされたフィールドの抽出

ネストされたフィールドは、ドット表記または角かっこを使用して指定します。角かっこを使用すると、列では大文字と小文字が区別されます。

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

配列から値を抽出する

配列内の要素は角かっこで囲んでインデックスを付けます。インデックスは 0 から始まります。アスタリスク (*) の後にドットまたは括弧表記を使用して、配列内のすべての要素からサブフィールドを抽出できます。

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

キャスト値

:: を使用して、値を基本データ型にキャストできます。from_json メソッドを使用して、入れ子になった結果を配列や構造体などのより複雑なデータ型にキャストします。

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

NULL 動作

null 値を持つ JSON フィールドが存在する場合、 null テキスト値ではなく、その列の SQL null 値を受け取ります。

select '{"key":null}':key is null sql_null, '{"key":null}':key == 'null' text_null

+-------------+-----------+
| sql_null    | text_null |
+-------------+-----------+
| true        | null      |
+-------------+-----------+

Spark SQL 演算子を使用して入れ子になったデータを変換する

Apache Spark には、複雑なデータや入れ子になったデータを操作するための組み込み関数が多数あります。次のノートブックには例が含まれています。

さらに、高階関数には、組み込みの Spark 演算子を使用してデータを希望どおりに変換できない場合に、多くの追加オプションが用意されています。

複雑な入れ子になったデータノートブック

ノートブックを新しいタブで開く

クエリー 半構造化データ in Databricks

高度にネストされたデータ を含むテーブルを作成する

最上位の列 を抽出する

ネストされたフィールドの 抽出

配列 から値を抽出する