Databricksにおける準構造化データへのSQLクエリー

[Query semi\-structured data in SQL \| Databricks on AWS](https://docs.databricks.com/spark/latest/spark-sql/semi-structured.html) [2021/6/11時点]の翻訳です。

:::note warn
本書は抄訳であり内容の正確性を保証するものではありません。正確な内容に関しては原文を参照ください。
:::

:::note info
**注意**
Databricksランタイム8.1以降で使用できます。
:::

本書では、JSONとして格納されている準構造化データをクエリー、変換するために使用できるDatabricksのSQLオペレーターを説明します。

:::note info
**注意**
この機能を用いることでファイルをflattenすることなしに準構造化データを読み込むことができます。しかし、最適な読み込みクエリー性能を発揮するためには、ネストされたカラムを適切なデータ型で抽出することをお勧めします。
:::

# 構文

`<column-name>:<extraction-path>`構文を用いて、JSON文字列を含むフィールドからカラムを抽出します。ここで、`<column-name>`はカラム名の文字列であり、`<extraction-path>`は抽出するフィールドへのパスとなります。文字列が返却されます。

# サンプル

以下のサンプルでは、[サンプルデータ](#サンプルデータ)の文を用いて作成したデータを使用しています。

- [トップレベルカラムの抽出](#トップレベルカラムの抽出)
- [ネストされたフィールドの抽出](#ネストされたフィールドの抽出)
- [配列から値の抽出](#配列から値の抽出)
- [値のキャスト](#値のキャスト)
- [サンプルデータ](#サンプルデータ)

## トップレベルカラムの抽出

カラムを抽出するには、抽出のパスでJSONフィールド名を指定します。

括弧の中にカラム名を指定することができます。括弧内で参照されるカラムは大文字小文字を*区別します*。また、括弧内ではないカラム名は大文字小文字を*区別しません*。

```sql:SQL
SELECT raw:owner, RAW:owner FROM store_data
```

```
+-------+-------+
| owner | owner |
+-------+-------+
| amy   | amy   |
+-------+-------+
```

```sql:SQL
-- 括弧を使用した際の参照は大文字小文字を区別します
SELECT raw:OWNER case_insensitive, raw:['OWNER'] case_sensitive FROM store_data
```

```
+------------------+----------------+
| case_insensitive | case_sensitive |
+------------------+----------------+
| amy              | null           |
+------------------+----------------+
```

空白や特殊文字をエスケープするためにはバックティックスを使用します。フィールド名は大文字小文字を*区別しません*。

```sql:SQL
-- 特殊文字のエスケープにバックティックスを使用します。バックティックスを使用した際には大文字小文字を区別しません。
-- 大文字小文字を区別する際には括弧を使用します。
SELECT raw:`zip code`, raw:`Zip Code`, raw:['fb:testid'] FROM store_data
```

```
+----------+----------+-----------+
| zip code | Zip Code | fb:testid |
+----------+----------+-----------+
| 94025    | 94025    | 1234      |
+----------+----------+-----------+
```

:::note info
**注意**
大文字小文字を区別しないことにより、検索パスにマッチするカラムが複数JSONに含まれる場合、括弧を使うように指示するエラーが発生します。複数行に渡るカラムの一致があった場合にはエラーとはなりません。`{"foo":"bar", "Foo":"bar"}`はエラーとなりますが、以下の場合はエラーとなりません。

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


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

ドットと括弧を用いてネストされたフィールドを指定することができます。括弧を使用する際、カラムは大文字小文字を区別します。

```sql:SQL
-- ドット表記の使用
SELECT raw:store.bicycle FROM store_data
-- 返却カラムは文字列となります
```

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

```sql:SQL
-- 括弧の使用
SELECT raw:store['bicycle'], raw:store['BICYCLE'] FROM store_data
```

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

## 配列から値の抽出

括弧内配列の要素にインデックスを指定します。インデックスは0からスタートします。配列の全ての要素からサブフィールドを抽出するためにドットか括弧の後にアスタリスク(`*`)を使用することができます。

```sql:SQL
-- 配列のインデックス
SELECT raw:store.fruit[0], raw:store.fruit[1] FROM store_data
```

```
+------------------+-----------------+
| fruit            | fruit           |
+------------------+-----------------+
| {                | {               |
|   "weight":8,    |   "weight":9,   |
|   "type":"apple" |   "type":"pear" |
| }                | }               |
+------------------+-----------------+
```

```sql:SQL
-- 配列からサブフィールドを抽出
SELECT raw:store.book[*].isbn FROM store_data
```

```
+--------------------+
| isbn               |
+--------------------+
| [                  |
|   null,            |
|   "0-553-21311-3", |
|   "0-395-19395-8"  |
| ]                  |
+--------------------+
```

```sql:SQL
-- 配列内の配列、配列内の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](https://docs.databricks.com/spark/latest/spark-sql/language-manual/sql-ref-functions-builtin.html#json-functions)メソッドを使用することができます。

```sql:SQL
-- priceは文字列ではなくdoubleとして返却されます
SELECT raw:store.bicycle.price::double FROM store_data
```

```
+------------------+
| price            |
+------------------+
| 19.95            |
+------------------+
```

```sql:SQL
-- より複雑な型にキャストするために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"  |
| }                |
+------------------+
```

```sql:SQL
SELECT from_json(raw:store.basket[*], 'array<array<string>>') baskets FROM store_data
-- 返却されるカラムは文字列配列の配列となります
```

```
+------------------------------------------+
| basket                                   |
+------------------------------------------+
| [                                        |
|   ["1","2","{\"b\":\"y\",\"a\":\"x\"}]", |
|   ["3","4"],                             |
|   ["5","6"]                              |
| ]                                        |
+------------------------------------------+
```

## サンプルデータ

```sql:SQL
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`値を受け取ります。

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

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

### Databricks 無料トライアル

[Databricks 無料トライアル](https://databricks.com/jp/try-databricks)