2021.07.28 SnowflakeTableauTech Blog　

Snowflakeで半構造データのクエリを書いてみよう！

1 概要
2 Snowflakeにおける基本的なクエリの書き方
3 FLATTEN関数
4 LATERALについて
5 LATERAL FLATTENについて
6 OBJECT_KEYS関数
7 GET関数
8 まとめ

概要

DATUM STUDIOのデータサイエンティストの真崎です。
Snowflakeではjsonやparquetなどの半構造化データもSQLで呼び出すことができます。
以前研修で学んだときにすごく分かりやすくて使いやすいなーと感動したので記事で取り上げてみました。
今回はjsonデータを用いて説明していきます。

Snowflakeにおける基本的なクエリの書き方

まずはSnowflakeにおける基本的なクエリの書き方をご紹介します。
まず、jsonテーブルを作成します。

```
create or replace table car_sales
(
  src variant
)
as
select parse_json(column1) as src
from values
('{
    "date" : "2021-06-20",
    "dealership" : "Valley View Auto Sales",
    "salesperson" : {
      "id": "55",
      "name": "Frank Beasley"
    },
    "customer" : [
      {
        "name": "Joyce Ridgely",
        "phone": "16504378889",
        "address": "San Francisco, CA"
      }
    ],
    "vehicle" : [
      {
        "make": "Honda",
        "model": "Civic",
        "year": "2017",
        "price": "20275",
        "extras":["ext warranty", "paint protection"]
        }
    ]
}'),
('{
    "date" : "2021-06-20",
    "dealership" : "Tindel Toyota",
    "salesperson" : {
      "id": "274",
      "name": "Greg Northrup"
    },
    "customer" : [
      {
        "name": "Bradley Greenbloom",
        "phone": "12127593751",
        "address": "New York, NY"
        }
    ],
    "vehicle" : [
      {
        "make": "Toyota",
        "model": "Camry", 
        "year": "2017", 
        "price": "23500", 
        "extras":["ext warranty", "rust proofing", "fabric protection"]
      }
    ]
}') v;
```

このテーブルを確認してみます。

```
select
*
from
car_sales;
```

当然json形式で格納されたデータが得られます。

keyである列名”src”に対応するvalueの”dealership”の間にコロンを入れることで”dealership”内のデータを表形式で参照することができます。（今回のセル内の出力は VARIANTなのでダブルクオートで囲まれています）

```
select
src:dealership
from
car_sales;
```

入れ子構造のjsonも扱うことができます。
以下のクエリを叩くとsalesperson配下のvalueが得られます。

```
select
src:salesperson
from
car_sales;
```

例えばこのjsonのkey:nameに対応するvalueを得たい場合以下のクエリを書きます。

```
select
src:salesperson.name
from
car_sales;
```

もしくは以下のように括弧とシングルクオートで同様の結果を得ることができます。
※ダブルクオートではエラーになるので注意

```
select src['salesperson']['name'] from car_sales;
```

データ型を事前に宣言しておくこともできます。

```
select src:salesperson.id::string from car_sales;
```

FLATTEN関数

また半構造化データをより柔軟に扱うために、FLATTEN関数が用意されています。

FLATTEN関数に関しての公式ドキュメントの記述は以下のようになっています。

<引用文>
複合値を複数の行にフラット化（展開）します。

FLATTEN は、VARIANT、 OBJECT、または ARRAY 列を取り、側面ビュー（ FROM 句でその前にある他のテーブルを参照する相関を含むインラインビュー）を生成するテーブル関数です。

FLATTEN は、半構造化データをリレーショナル表現に変換するために使用できます。
<引用文>

やはり公式ドキュメントを一読しただけで内容を理解することは難しいですが、
要するにFLATTEN関数を用いることで、JSON 内の配列や ARRAY 型の値を行に展開してテーブルとして返してくれるということです。

LATERALについて

FLATTEN関数の典型的な使用方法として、LATERAL FLATTENという書き方をよく用います。
LATERALはLateral Join (ラテラル結合)のことを指します。
CROSS JOINのようなもので左側のテーブルから 1 行ずつ取り出して、その行を使ってサブクエリを実行し、そのサブクエリの結果行を右側として、左側の行と結合していく、ということを行います。
例えば、以下のクエリを実行して結果を表示してみます。

```
create or replace table x1 (c1 int) as
select * from values (1), (3), (5);

select
*
from x1,
lateral (select x1.c1*2);
```

左側のテーブル（t1）の各行に対してサブクエリselect t1.c1+1を実行した結果をt1に結合していった結果になります。
つまり、左側のテーブルに演算処理をした結果を新たに右側のテーブルとしてくっつけたい場合にLATERALを使います。

LATERAL FLATTENについて

相関サブクエリだけでなくテーブルを右側に持ってきても同様の処理ができます。
以下のテーブルを作ってjsonの最深部の各要素（value）を取り出すことを考えてみます。

``` create or replace table x1 (c1 variant) as select parse_json('{"id": 1, "foo":[{"bar":[0, 1]}, {"bar": [2, 3]}]}') union select parse_json('{"id": 2, "foo":[{"bar":[4, 5]}, {"bar": [6, 7]}]}'); select * from x1; ```

まずは、前の項で覚えたコロンで展開してみます。

``` select c1, c1:foo, c1:foo:bar from x1; ```

この場合2層目のkeyに当たるbarが２つあるのでエラーになってしまいます。
続いてLATERAL FLATTENを用いて各要素を取り出してみます。

``` select x1.c1, f1.value, f2.value from x1, lateral flatten(x1.c1:foo) f1, lateral flatten(f1.value:bar) f2; ```

異なる結果が得られました。
barに対応するvalueの配列から各要素が取り出されて新たにvalue列に格納されています。
flatten(t1.c1)のようにテーブル関数を引数に取ることで配列であるt1.c1の各要素をそれぞれ格納した行に展開することができます。

OBJECT_KEYS関数

続いてはOBJECT_KEYS関数についてです。
公式DOCを挿入（https://docs.snowflake.com/ja/sql-reference/functions/object_keys.html）
以下のようにOBJECT_KEYS()で指定したjsonオブジェクトからkeyを取得することができます。

``` select f1.value AS "f1_object", OBJECT_KEYS("f1_object") AS "f1_key" from x1, lateral flatten(x1.c1:foo) f1; ```

GET関数

GET関数を用いることでjsonオブジェクトからvalueを取得することができます。
公式DOCを挿入（https://docs.snowflake.com/ja/sql-reference/functions/get.html）
第一引数にjsonが入っているカラム（f1_object）、第二引数に取得したいvalueに対応したstring型のkey名の入ったカラム（f1_ks）を指定します。

``` select f1.value AS "f1_object", OBJECT_KEYS("f1_object") AS "f1_key", "f1_key"[0]::string AS "f1_ks", GET("f1_object", "f1_ks") AS "f1_val" from x1, lateral flatten(x1.c1:foo) f1; ```

まとめ

半構造化データを扱うクエリの書き方と便利な関数を紹介しました。
SnowflakeならSQLで扱いづらいイメージのある半構造化データでも便利な関数を組み合わせることで、簡単に扱うことができそうです。

Snowflakeの導入をご検討のお客様は、お問い合わせフォームよりお気軽にお問い合わせください。

このページをシェアする：

DATUM STUDIOは、クライアントの事業成長と経営課題解決を最適な形でサポートする、データ・ビジネスパートナーです。
データ分析の分野でお客様に最適なソリューションをご提供します。まずはご相談ください。

Contact

サービス・研修・アドバイザリーに関するお問い合わせはこちら

Explore Jobs

採用に関する情報はこちら

前へ次へ