#05 ： ハンズオンラボガイド「分析クエリ、リザルトキャッシュ、クローニング」 – Snowflake Advent Calendar 2019 #SnowflakeDB

こんにちは！DA事業本部の大高です。

当エントリは『Snowflake Advent Calendar 2019』の05日目のエントリです。

Snowflake Advent Calendar 2019 - Qiita
Snowflake Advent Calendar 2019 ｜ シリーズ ｜ Developers.IO

はじめに

この演習では「分析ユーザ」として、ワークシートとウェアハウスのANALYTICS_WHを利用して、Citi Bikeテーブルの分析を行う演習となります。

注意書きにもある通り、ロールとして通常SYSADMINは利用しませんが、この演習ではシンプルにするためにSYSADMINを利用しています。

また、クエリ実行も今回はワークシート経由で実行しますが、通常、「分析ユーザ」の場合にはTableau、Looker、PowerBIなどのBIツールからクエリを発行するのが一般的なユースケースとして想定されます。なお、さらに高度なデータサイエンス的な分析として、SparkやRなどからもクエリを発行することもできます。

前提条件

「Module 3: ロードするデータの準備」、「Module 4: データのロード」に引き続き「Module 5: 分析クエリ、リザルトキャッシュ、クローニング」のご紹介となります。「Module 5」では、「Module 3」、「Module 4」でロードしたデータやウェアハウス、データベース、テーブルなどを利用して進めていきますので、まだの方は先に「Module 3」、「Module 4」をお試しください。

また、ハンズオンラボガイドにある通り、演習の中のクエリは下記のファイルに記載されているので事前にダウンロードしてワークシートにロードしておくと便利です。

lab_scripts_free_trial.sql

それでは、張り切っていきましょう！

SELECTの実行とリザルトキャッシュ

5.1.1 ワークシートタブの表示

まずはワークシートタブに移動し、適切なコンテキストに設定されていることを確認します。

Role: SYSADMIN
Warehouse: ANALYTICS_WH (L)
Database: CITIBIKE
Schema = PUBLIC

上記のようになっていれば、問題ありません。

5.1.2 サンプルデータの表示

次に、下記のクエリを実行してtripsデータのサンプルを確認してみましょう。

SELECT * FROM trips LIMIT 20;

ちゃんとSELECTできていますね。

5.1.3 1時間ごとの情報を見てみる

最初に、Citi Bileの使用に関する基本的な1時間ごとの情報を見てみます。以下のクエリを実行して、1時間ごとに、走行回数、平均走行時間、および平均走行距離を見てみましょう。

SELECT
    DATE_TRUNC('hour', starttime) AS "date"
  , COUNT(*) AS "num trips"
  , AVG(tripduration)/60 AS "avg duration (mins)"
  , AVG(
      HAVERSINE(
          start_station_latitude
        , start_station_longitude
        , end_station_latitude
        , end_station_longitude
      )
    ) AS "avg distance (km)"
FROM
  trips
GROUP BY 1
ORDER BY 1
;

「深夜の時間帯は利用が少な目だな」とか「走行距離は大体２～３kmだな」とかが見て取れるかと思います。なお、HAVERSINEという見慣れない関数がありますが、これはヘルプによると、２点間の緯度経度から「地球の表面上の距離」をキロメートルで返してくれる関数だそうです。便利！

5.1.4 リザルトキャッシュ

Snowflakeには、過去24時間に実行されたすべてのクエリの結果を保持するリザルトキャッシュという機能があります。

リザルトキャッシュは同一のウェアハウス全体で共通利用されるため、あるユーザーに返されたクエリ結果は、基になるデータが変更されていない限り、同じクエリを実行した別ユーザーも利用することができます。また、非常に高速に返されるだけでなく、計算クレジットも使用しません！

実際に試してみましょう。先程と同じクエリを実行します。

SELECT
    DATE_TRUNC('hour', starttime) AS "date"
  , COUNT(*) AS "num trips"
  , AVG(tripduration)/60 AS "avg duration (mins)"
  , AVG(
      HAVERSINE(
          start_station_latitude
        , start_station_longitude
        , end_station_latitude
        , end_station_longitude
      )
    ) AS "avg distance (km)"
FROM
  trips
GROUP BY 1
ORDER BY 1
;

先程の907msから57msへと大幅に実行速度が向上していますね。

なお、クエリIDのリンクをクリックすることで、すぐにProfileの表示画面へ遷移することができます。ここからも、リザルトキャッシュを利用していることが良く分かります。

Historyウィンドウからも、結果がキャッシュされて実行速度が大幅に向上していることが確認できますね。

5.1.5 最も利用が多い曜日

次に、最も利用が多い曜日を確認してみます。

SELECT
    DAYNAME(starttime) AS "day of week"
  , COUNT(*) AS "num trips"
FROM
  trips
GROUP BY 1
ORDER BY 2 DESC
;

「水曜日」が最も利用が多く、逆に「日曜日」が最も少ないことが分かりますね。平日は仕事があって、土日は何となく「仕事が休みだからかな」というのが感じられます。

5.2 テーブルのクローン

Snowflakeの特徴でもある「ゼロコピークローン」の演習です。テーブル、スキーマ、データベースを数秒で作成することができ、ソースオブジェクトのスナップショットがクローン作成時に取得され、クローンオブジェクトで利用可能となります。

クローンオブジェクトは差分の書き込みとなり、ソースオブジェクトからは独立した状態になるため、ソースオブジェクトに影響を及ぼすことはありません。

一般的な利用例としては「本番環境」を複製した「開発環境」と「テスト環境」での利用があり、特徴としては下記が挙げられます。

1.)「本番環境」に影響を与えない
2.) 個別の設定と管理が不要

「ゼロコピークローン」の大きな利点としては、元データへのメタデータ、ポインタのみが変更されることで「元データのコピーがされない」ということです。従って、ストレージ要件が2倍になることもありません。

5.2.1 クローンの作成

では早速作成してみましょう。以下のコマンドで、tripsテーブルの、開発用テーブルを作成します。

TABLE trips_dev CLONE trips

5.2.2 クローンの確認

確認してみましょう。データベースオブジェクトブラウザが表示されていない場合には、ワークシート左側を展開して表示させます。

「更新」ボタンをクリックして、「CITIBIKE」データベースのオブジェクトツリーを展開すると、「PUBLIC」スキーマの配下に「TRIPS_DEV」という新しいテーブルが出来ていることが確認できますね。

まとめ

以上、「分析クエリ、リザルトキャッシュ、クローニング」のご紹介でした。「ゼロコピークローン」はとても便利なので、環境の複製やバックアップに活用したいですね。

明日の06日目は石川覚による「Module 6: 半構造化データの操作、ビュー、結合」の予定です。お楽しみに！