LookerのダッシュボードからSnowflakeのTime Travelを操作して過去のデータを可視化する #looker #SnowflakeDB

われらあくなきタイムトラベリンメン

「カスタマーストーリー」を支えるツール特集

#ビジネス

#Looker

#Snowflake

たまちゃん

2020.12.21

この記事は公開されてから1年以上経過しています。情報が古い可能性がありますので、ご注意ください。

大阪オフィスの玉井です。

当エントリは『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上で選ぶ日付と実データの日付がフィットすると思います。