Snowflake 半構造化データサポートの解説 | Snowflake Advent Calendar 2019 #SnowflakeDB

本日は、Snowflakeの強力な半構造化データのサポートと簡単なデータの取扱について解説します。

• Snowflake Advent Calendar 2019 - Qiita
• Snowflake Advent Calendar 2019 ｜ シリーズ ｜ DevelopersIO

JSONデータのロードからクエリを実行する方法については、#06：ハンズオンラボガイド「半構造化データの操作、ビュー、結合」をご覧ください。

#06：ハンズオンラボガイド「半構造化データの操作、ビュー、結合」| Snowflake Advent Calendar 2019 #SnowflakeDB

Snowflakeがサポートする半構造化フォーマット

• 多くの半構造化データをサポート
  • 機器によって生成されたデータ
  • センサーIoTによるデータ
  • 半構造化データ由来のデータ形式（JSON、Avro、ORC、Parquet、またはXML）
• 第1段階の新しいデータ型
  • クエリアクセスを高速化するためのサブフィールドやパフォーマンスのための効果的なパーティションプルーニングなど、統計が収集および維持される
• すべてのSQL操作（結合、グループ化、順序付け等）のサポート
  • 半構造化フィールドでもデータにアクセスするためのネイティブ構文利用できる

全てのデータを１つのデータベースで

Snowflakeは、構造化・半構造化データ問わず、すべての共通のストレージに格納します。データをロードする際には、配列のような繰り返される要素を透過的に検出して、ストレージを最適化して格納します。そのため、クエリの実行時には半構造化データについてもプルーニングやフィルタリングといったデータベースが持つ最適化が活かされます。

半構造化のデータ型

• VARIANT：標準SQLの型、配列、およびオブジェクトの値を保持する
  • 非ネイティブ値（日付やタイムスタンプなど）は、VARIANT列に文字列として保存されます
  • 対応するデータ型としてリレーショナル列に保存されている場合よりも操作が遅くなる可能性があります
• OBJECT：キーと値のペアのコレクション
  • 値はVARIANT
• ARRAY：可変長の配列
  • 値はVARIANT
• Snowflakeは、これらの列のメタデータを収集し、オプティマイザーがプルーニングに使用します

半構造化の関数

Snowflakeは、半構造化データに対しても結合、集計、ソートといったSQL操作に加えて、半構造化データ用の関数が提供されています。

• 配列の作成と操作
• オブジェクトの作成と操作
• JSONおよびXML解析
• 抽出
• 変換
• キャスト
• 型チェック

参考： Semi-structured Data Functions

半構造化データへの値のアクセス方法

VARIANT型の値のキーを指定して要素にアクセスできます。アクセスする方法は、コロン:もしくは、ブランケットにキーを指定（連想配列のようなして方法）の２つの方法があります。

• コロンでアクセスする方法：column:key

SELECT 
value:humidity 
AS pct_humidity
FROM
  my_json_table;

...
PCT_HUMIDITY
=========
50

• ブランケットでアクセスする方法：column[‘key’]

SELECT 
value['humidity']
AS pct_humidity
FROM
  my_json_table;

...
PCT_HUMIDITY
=========
50

VARIANT型のデータのキャスト

• VARIANTは、多くの場合、単なる文字列、配列、または数字です。
• キャストなしでは、VARIANTオブジェクトタイプのままです
• ::オペレータを使用してSQLデータ型にキャストし、その後に変換する型を指定します

SELECT
   value:humidity::number(10,2)
AS pct_humidity
FROM
   daily_14_total dt,
   LATERAL FLATTEN(input=> dt.v:data)
LIMIT 3;

配列データのフラット化（FLATTEN）

• VARIANTには、ネストされた要素（データを含む配列）が含まれる場合があります
• FLATTEN()はネストされた配列からデータを取り出します
• ほとんどの場合、LATERAL結合で使用されます（他のテーブル、ビュー、またはテーブル関数の列を参照するため）
• LATERAL FLATTEN (input => expression [options]) input => 行に固定されない式または列 データは、VARIANT、OBJECT、またはARRAYタイプでなければなりません

配列をフラット化する

以下の例では、横持ちのデータを縦持ちに変換して列の固定セットを返します。THIS列のようにFLATTENのソーステーブルの列にもアクセスできます。

SELECT * FROM 
TABLE (FLATTEN
(input => PARSE_JSON('[1,2,3]')));

SEQ	KEY	PATH	INDEX	VALUE	THIS
1	NULL	[0]	0	1	[1,2,3]
1	NULL	[1]	1	2	[1,2,3]
1	NULL	[2]	2	3	[1,2,3]

FLATTENが返すカラムデータ

• SEQ
  • 入力レコードに関連付けられた一意のシーケンス番号
  • 隙間がないことや特定の方法で注文されたことを保証するものではありません
• KEY
  • マップまたはオブジェクトの場合、この列には展開値のキーが含まれます
• PATH
  • FLATTENする必要があるデータ構造内の要素へのパス
• INDEX
  • 配列の場合、要素のインデックス。 それ以外の場合はNULL
• VALUE
  • FLATTENされた配列またはオブジェクトの要素の値
• THIS
  • FLATTENされる要素（再帰的FLATTENするのに有用）

まとめ

単に半構造化データに対してクエリを実行できるデータウェアハウスは数多くあります。Snowflakeは、VARIANT型を用いることで半構造化データを簡単にテーブル定義できますが、データロード時に配列のような繰り返される要素を透過的に検出して、ストレージを最適化して格納します。そのため、クエリの実行時には半構造化データについてもプルーニングやフィルタリングといったデータベースが持つ最適化が活かされます。

JSONをはじめとする半構造化データにおいて配列データなどの繰り返すデータの取扱は煩雑になりがちですが、クエリで簡単にフラット化できます。Snowflakeは半構造化データのように刻々と変化するデータでもとらわれることなく柔軟に分析できる点が強みといえます。