LookerのダッシュボードからSnowflakeのTime Travelを操作して過去のデータを可視化する #looker #SnowflakeDB
大阪オフィスの玉井です。
当エントリは『Looker Advent Calendar 2020』21本目のエントリです。
クラウド型DWHのSnowflakeには、Time Travelという強力な機能が存在します。
（Editionにもよりますが）、いつでも、最大90日前の状態まで遡った状態のデータをクエリできるというものです。こちら、本来はクエリを書いて使用する機能ですが、Looker側のLookMLの作り方を工夫すれば、Lookやダッシュボードのフィルタを操作して、間接的にTime Travelを制御することができるのでは？って思ったので、実際にやってみました。
今回やることの概要
Time Travelを使ったクエリの構成はすごくシンプルです。
公式ドキュメントの例を載せます。下記は「現在から5分前の状態」のテーブルをクエリします。
select * from my_table at(offset => -60*5);
このoffsetという部分で、「現在時刻との差」を指定します。秒単位なので、上記は60×5秒 = 300秒 = 5分となり、「5分前の状態のmy_table」に対してSELECT文が発行されます。
ここで、視座をSnowflakeからLookerに変えてみます。LookerはLookMLを解釈して、最適なクエリをDWHに対して発行します。ですので、上記のクエリでいうと、offsetに渡す時間の値の部分を、Lookerのダッシュボードのフィルタで選んだ値が代入されるようにすれば、Lookerから、間接的に、好きな時間だけ遡れるようなダッシュボードが作れそうです。
もう一つの例を載せます（こちらも公式ドキュメントのもの）。
select * from my_table at(timestamp => 'Mon, 01 May 2015 16:20:00 -0700'::timestamp_tz);
先程のものが、現在時刻との相対的な差を指定するのに対して、こちらは絶対時間をTIMESTAMPで指定するものとなります。ですので、このクエリは「2015年5月1日16時20分時点のmy_table」に対してSELECT文を実行します。
ここでも、視座をLookerにうつして考えてみます。ここの日付部分を、Lookerの日付フィルタみたいなもので選んだ日付が入るようにすれば、間接的にTime Travelを使用することができそうです。
注意
- 1日以上遡るためには、SnowflakeのEditionがEnterprise以上である必要があります。
- 今回、タイムゾーンは考慮していません。
やってみた（offset編）
[Snowflake] テーブルを用意する
検証用のスキーマ下に、Snowflakeに最初から用意されているサンプルデータをコピってきます。ORDERSというサンプルデータの中から、注文日時が1993年のものだけ持ってきました。
CREATE TABLE "TAMAI_TEST_DB"."TMP"."orders" data_retention_time_in_days = 90 AS
SELECT
*
FROM
"SNOWFLAKE_SAMPLE_DATA"."TPCH_SF1"."ORDERS"
WHERE
YEAR(
o_orderdate
) = '1993';
ポイントはdata_retention_time_in_days = 90です。ここで、作成するテーブルに対してTime Travelを有効化しています。と同時に、Time Travelの保持期間を、最大の90日間に指定しています。
[Looker] 普通にModelを定義してみる
上記のテーブルをLookerで（LookMLで）定義します。このテーブルを定義したviewとexploreを作成します。ここは本当に何も特別なことはしないので詳細は省きます。
[Snowflake] テーブルのデータを全部消す
多少時間を空けて、ordersの中身を消します。
DELETE
FROM
"TAMAI_TEST_DB"."TMP"."orders";
このテーブルは0件ですので、普通にクエリしても、データは一切出てこない状態になりました。
[Looker] LookerのフィルタでTime Travelが切り替わるようにLookMLを実装する
ここからが本番です。
Time Travelの時間指定は、FROM句の後に記述します。そして、LookML上でFROM句に相当するのは、viewファイルのsql_table_nameです。
view: orders {
sql_table_name: "TMP"."orders";;
ここに、Lookやダッシュボードのフィルタで選んだ値が埋め込まれるような仕組みを作れば、今回やりたいことが実現できそうですね。
まず、単純にTime Travelのクエリを入れてみます。
view: orders {
sql_table_name: "TMP"."orders" at(offset => -60 * 5);;
時間値をベタ書きしているので、当然これではダメです。時間値が（フィルタの操作で）動的に変わる必要があります。こういう時はparameterを用意して、その値が入るようにLiquidを記述します。
view: orders {
sql_table_name: "TMP"."orders" at(offset => -60 * {% parameter timetravel %} );;
parameter: timetravel {
label: "タイムトラベルフィルタ"
description: "遡りたい時間を分単位で入力してください"
type: unquoted
}
...
これで、parameterをフィルタに設置して、そこに入力した値がTime Travelのクエリ部分に動的に入るようになりました。ただ、この方法でも、まだ不十分な部分があります。それは、parameterをフィルタに使わない場合、値が入らず、クエリが成立しないのでエラーになってしまいます。ですので、parameterがフィルタに使われている時だけ、Time Travelのクエリが入るようにします。
view: orders {
sql_table_name:
{% if timetravel._is_filtered %}
"TMP"."orders" at(offset => -60 * {% parameter timetravel %} )
{% else %}
"TMP"."orders"
{% endif %}
;;
parameter: timetravel {
label: "タイムトラベルフィルタ"
description: "遡りたい時間を分単位で入力してください"
type: unquoted
}
...
LiquidでIFロジックを組み込んで対応しました。実際にExploreで動きを確認しましょう。
[Looker] 動作確認してみる
まず、parameterを使用しない状態でクエリを実行します。エラーは発生せず、クエリが実行できました（FROM句に注目！）。そして、このテーブルはデータを消しているので、結果は0件です。
次に、parameterをフィルタに設定し、数値を入れてやってみます。（検証時）データを消したのが30分ほど前なので、40を入れて実行してみます。要するに40分前の状態のテーブルに対して、LookerのExploreからクエリを実行していることになります。
成功しました。LookMLとLiquidをうまく使うことで、分析画面からTime Travelを制御できました。繰り返しになりますが、このテーブルのデータ自体は空っぽの状態です。Time Travel機能が動いて、40分前の状態のデータが可視化されています。
やってみた（timestamp編）
[Snowflake] 時間をずらしてデータの内容を変える
Time Travelを使って内容の異なるデータを取得したいので、日にちをずらして、データの中身を変えます。
-- 1993年の分を1994年分に変更(2020/12/18)
DELETE
FROM
"TAMAI_TEST_DB"."TMP"."orders";
INSERT INTO "TAMAI_TEST_DB"."TMP"."orders"
SELECT
*
FROM
"SNOWFLAKE_SAMPLE_DATA"."TPCH_SF1"."ORDERS"
WHERE
YEAR(o_orderdate) = '1994';
-- 1994年の分を1995年分に変更(2020/12/20)
DELETE
FROM
"TAMAI_TEST_DB"."TMP"."orders";
INSERT INTO "TAMAI_TEST_DB"."TMP"."orders"
SELECT
*
FROM
"SNOWFLAKE_SAMPLE_DATA"."TPCH_SF1"."ORDERS"
WHERE
YEAR(o_orderdate) = '1995';
本当は変更した時間までメモっとくべきでしたが、忘れてしまいました…。
[Looker] Time Travelが切り替わるようにLookMLを実装する
今度はtimestampを指定するバージョンでやってみました。
view: orders_2 {
sql_table_name:
{% if timetravel._is_filtered %}
"TMP"."orders" before(timestamp => {% parameter timetravel %}::timestamp)
{% else %}
"TMP"."orders"
{% endif %}
;;
parameter: timetravel {
label: "タイムトラベルフィルタ"
description: "遡りたい日付を選んでください"
type: date_time
}
...
parameterのtypeをdate_timeにすることで、パラメータの値を日付型として渡すようにしました。パラメータをフィルタとして指定する際の型を、日付型の選択項目にすることができます。
[Looker] 動作確認してみる
パラメータ無しだと下記の通り。
今回はdate_timeなので、下記のように、カレンダーから選べるようになります。
カレンダーから日付を選んで実行すると、カレンダーで選んだ日付がTime Travelのクエリに入って、12月18日時点のordersテーブルに対してクエリがなされました。
[Looker] ダッシュボードを作ってみる
せっかくなので、今回は、このTime Travelを利用したダッシュボードを作ってみます。
完成版はこちら。一見、普通の棒グラフが2つ並んでいるだけに見えますが、上部のフィルタを選ぶと、右側のタイル（「比較用」という名前）だけ、Time Travelが動くようになっています。
テストデータがアレなので、実践的ではないですが、このダッシュボードは、Snowflakeに実際に入ってるデータは今年分しかないのに、フィルタ越しにTime Travelを操作して、過去と現在のデータを比較できるようになっています。
ダッシュボードフィルタは下記のように設定しています。
考慮事項
データとしての時間とTime Travelで指定する時間を混同しないようにする
実際にやってて思ったのですが、今回のテストデータは1993年とか1995年のものなのに、Time Travelで指定するのは2020年12月18日とかだったので、たまに混乱しました。当たり前なのですが、Time Travelは「Snowflake上の時間」を指定するので、実際のデータ側の時間は関係ありません。
理想は、その日発生したデータがその日にSnowflakeで更新されるようになっていることですね。2020年12月18日に「2020年12月18日分のデータ」が更新されるようになっていれば、Looker上で選ぶ日付と実データの日付がフィットすると思います。
タイムゾーン
冒頭でも述べたように、今回は完全に度外視しましたが、本当にちゃんとやるんだったら、タイムゾーンは気をつけないと、わけわかんないことになりそうな気がしました。
おわりに
LookMLを開発するときは、Lookerが生成するSQLをイメージしつつ取り組むと、いい感じになると思いました。
![[レポート]生成型AIのROIは？ 数字で語れるか？ #SEG208](https://images.ctfassets.net/ct0aopd36mqt/3IQLlbdUkRvu7Q2LupRW2o/edff8982184ea7cc2d5efa2ddd2915f5/reinvent-2024-sessionreport-jp.jpg)
