#06：ハンズオンラボガイド「半構造化データの操作、ビュー、結合」| Snowflake Advent Calendar 2019 #SnowflakeDB
先日の#05 ： ハンズオンラボガイド「分析クエリ、リザルトキャッシュ、クローニング」に引き続き、本日は半構造化データ（JSON）のデータファイルをロードして、ビューを作成してクエリを実行する方法を解説します。
本日の内容
最初の手順は、前のModule3(データのロードの準備)およびModule4(データのロード)と同様ですが、時間を節約するために、UIではなくワークシート内のSQLを使用して設定します。
Citi Bikeの分析チームは、ラボ「Story」に戻って、天候が乗車率に与える影響を調べたいと考えています。そのために、このモジュールでは次の操作を行います。
- 公開されているS3バケットに保持されているJSONフォーマットで気象データをロードする
- ビューを作成し、SQLドット表記を使用して半構造化データをクエリーします
- このガイドの前のモジュールのJSONデータをTRIPSデータに結合するクエリーを実行します。
- 天候が自転車に乗る回数に与える影響を見る
JSONデータは、2016年7月5日から2019年6月25日までのニューヨーク市の歴史的条件を詳述するOpenWeatherMapによって提供される気象情報で構成されます。57.9k行、61個のオブジェクト、および圧縮された合計2.5MBのサイズのデータがAmazon S3に配置されています。
SEMI-STRUCTURED DATA とは
Snowflakeは、JSON、Parquet、Avroなどの半構造化データを、変換せずに簡単にロードしてクエリを実行できます。これが重要な理由は、日々増加するビジネス関連の半構造化データに対して、従来のデータウェアハウスでは容易にロードしてクエリできませんでした。
6.1 データベースとテーブルの作成
6.1.1 データベースの作成
ワークシートを使用して、半構造化データの保存に使用するWEATHERというデータベースを作成します。
create database weather;
6.1.2 コンテキストの指定
SQLをワークシート内で実行する際のコンテキストを適切に設定します。
use role sysadmin; use warehouse compute_wh; use database weather; use schema public;
6.1.3 テーブルの作成
ワークシートを使用して、JSONデータのロードに使用するJSON_WEATHER_DATAというテーブルを作成します。ワークシートで、以下のSQLテキストを実行します。SnowflakeにはVARIANTという特殊なデータ型があり、JSONオブジェクト全体を保存し、最終的には直接クエリーすることができます。
create table json_weather_data (v variant);
※ Semi-Structured Data Magic
SnowflakeのVARIANTデータ型を使用すると、スキーマを事前に定義しなくても、半構造化データを取り込むことができます。Snowflakeでは半構造化データのテーブル作成時に悩むことはありません。
6.1.4 テーブルの作成結果
テーブルJSON_WEATHER_DATAテーブルが作成されたことを確認します。ワークシートの下部に「Table JSON_WEATHER_DATA successfully created」というメッセージが表示されます。
6.1.5 テーブル定義の確認
ページ上部のWEATHERデータベースリンクをクリックします。新しく作成されたJSON_WEATHER_DATAテーブルが表示されます。
6.2 External Stage の作成
6.2.1 Amazon S3 の External Stage の作成
ワークシートを使用して、半構造化データがAmazon S3に格納されるStageを作成します。
create stage nyc_weather url = 's3://snowflake-workshop-lab/weather-nyc';
6.2.2 nyc_weather Stage のデータファイルの確認
nyc_weatherStageに指定したデータファイルを見てみましょう。ページ上部の [ワークシート]タブをクリックします。ワークシートで、LISTコマンドを使用して次のステートメントを実行し、ファイルのリストを表示します。
list @nyc_weather;
6.3 半構造化データのロードと検証
このセクションでは、Warehouseを使用してS3バケットから作成したSnowflakeのテーブルにデータをロードします。
6.3.1 JSON_WEATHER_DATAテーブルにデータをロード
ワークシートを使用してCOPYコマンドを実行し、前に作成したJSON_WEATHER_DATAテーブルにデータをロードします。
ここのSQLでは、FILE FORMATオブジェクトを(type=json)のようにインラインで指定できます。構造化データをロードした前のモジュールでは、ファイルフォーマットを詳細に定義する必要がありました。しかし、ここのJSONデータは適切にフォーマットされているため、デフォルト設定を使用してJSONタイプを指定するだけです。
copy into json_weather_data from @nyc_weather file_format = (type=json);
6.3.2 ロードデータの確認
ロードされたデータを確認します。
select * from json_weather_data limit 10;
6.3.3 JSONの内容を確認
いずれかの値をクリックします。データが生のJSONフォーマットで保存されていることに注目してください。
6.4 ビューの作成と半構造化データのクエリ
Snowflakeでビューを作成し、SQLを使ってJSONデータを直接クエリーする方法を解説します。
ビューとマテイアライズドビューの違い
ビューはテーブルであるかのようにクエリの結果にアクセスできます。ビューはエンドユーザーにクリーンな方法でデータを表示したり、プライバシーやセキュリティ上の理由からソーステーブルでエンドユーザーが表示できるものを制限したり、よりモジュール型のSQLを記述したりする場合に役立ちます。
一方、SQL結果がテーブルのように格納されるマテリアライズドビューもあります。これにより、より高速なアクセスが可能になりますが、ストレージ領域が必要になります。マテリアライズド・ビューにはSnowflake Enterprise Edition以上が必要です。
6.4.1 ビューの作成
「ワークシート」 タブからワークシートに移動し、次のコマンドを実行します。構造化されていないJSONの気象データのビューをカラム形式のビューで作成することでアナリストが理解やクエリの実行が容易になります。参考までに、ニューヨーク市のcity_idは5128638です。
create view json_weather_data_view as select v:time::timestamp as observation_time, v:city.id::int as city_id, v:city.name::string as city_name, v:city.country::string as country, v:city.coord.lat::float as city_lat, v:city.coord.lon::float as city_lon, v:clouds.all::int as clouds, (v:main.temp::float)-273.15 as temp_avg, (v:main.temp_min::float)-273.15 as temp_min, (v:main.temp_max::float)-273.15 as temp_max, v:weather[0].main::string as weather, v:weather[0].description::string as weather_desc, v:weather[0].icon::string as weather_icon, v:wind.deg::float as wind_dir, v:wind.speed::float as wind_speed from json_weather_data where city_id = 5128638;
6.4.2 SQLのドット表記によるJSON階層の下位レベルの値を取り出し
前のステップでは、SQLのドット表記(v.city.coord.lat)を使用して、JSON階層の下位レベルの値を取り出す方法を説明しました。これにより、各フィールドをリレーショナルテーブルの列のように扱うことができます。
6.4.3 作成したビューの表示
新しいビューがjson_weather_dataテーブルのすぐ下に表示されるUIの左上のビューを確認します。表示するには、データベースオブジェクトブラウザを展開したり更新する必要があります。
6.4.4 クエリでビューの確認
ワークシートを使用して、次のクエリでビューを確認します。結果は、通常の構造化データソース(注:結果セットのobservation_time値が異なる場合があります)のように表示されます。
select * from json_weather_data_view
where date_trunc('month',observation_time) = '2018-01-01'
limit 20;
6.5 データを結合してデータの相関を確認する
次に、JSONの気象データをCITIBIKE.PUBLIC.TRIPSデータに結合して、天候が乗り物の数にどのように影響するかという最初の疑問に対する答えを得たいと思います。
6.5.1 WEATHERとTRIPSのデータを結合する
次のコマンドを実行して、WEATHERをTRIPSに結合し、特定の気象条件に関連するトリップの数をカウントします。
ワークシート内のコンテキストのデフォルトは、WEATHERデータベースであるため、TRIPSテーブルへの参照はデータベース名とスキーマ名を指定します。
select weather as conditions ,count(*) as num_trips
from citibike.public.trips
left outer join json_weather_data_view
on date_trunc('hour', observation_time) = date_trunc('hour', starttime)
where conditions is not null
group by 1
order by 2 desc;
6.5.2 データの相関
Citi Bikeの当初の目標は、自転車に乗る回数と天気の間に相関関係があるかどうかを、乗客数と天気データの両方を分析することで確かめることでした。ご想像の通り、天候が良ければ、旅行の回数はかなり多くなることが確認できました。
まとめ
Snowflakeでは、半構造化データをVARIANTにそのままの形式でロードするため、テーブル定義が容易で、JSONフォーマットの変更が柔軟な特長があり、階層化したデータもSQLのドット表記で簡単に取り出すことも可能です。また、半構造化データはビューを用いることで構造化データのように簡単に扱う事ができます。ビューによるパフォーマンスの課題が懸念される場合は、マテリアライズドビューの導入をご検討ください。
明日は、#07 ： ハンズオンラボガイド「Using Time Travel」について説明します。
![[新機能]dbtのdata testで任意の設定をconfigで指定できるようになり、各data testでSnowflakeのウェアハウスを指定できるようになりました](https://images.ctfassets.net/ct0aopd36mqt/wp-thumbnail-f846b6052d1f7b983172226c6b1ed44b/6b4729aabfb5b7a3359acc27dc6b5453/dbt-1200x630-1.jpg)
