Lookerで中間テーブルを運用管理する

[Looker Advent Calendar 2020](https://qiita.com/advent-calendar/2020/looker)の23日目の記事になります。

ZOZOテクノロジーズでLookerに触れている[データエンジニア 遠藤](https://qiita.com/tsucasa)です。

結局、昨年末から1年ぶりの投稿になってしまいました。。。

# はじめに

ZOZOテクノロジーズではLookerをデータガバナンスツールとして使用しています。
（弊社におけるLooker利用法の詳細は**[こちらからご覧ください](https://techblog.zozo.com/entry/looker-data-processing-improvement) ![](https://b.hatena.ne.jp/entry/s/techblog.zozo.com/entry/looker-data-processing-improvement)**）

とくに、弊社ではLookerをELTツールとしても活用していて、ELTの結果を"中間テーブル"として格納しています。

中間テーブルの生成は、[@hase-ryoさんのLooker Advent Calendar 2019 5日目の記事](https://qiita.com/hase-ryo/items/b403f5653a7935535a97)にもあるとおり、
Looker利用のパフォーマンス向上においてとても有用です。

そこで、本記事では、Looker上で中間テーブル生成をどのように柔軟に管理していくか？について報告します。

# PDT機能

Lookerには、**PDT（永続的な派生テーブル）**という、viewで設定したderived_tableのアウトプットを物理データとして好きなタイミングで保存する機能が存在します。

PDTの概要・基本は以下の記事で参照できます。

- [公式情報](https://docs.looker.com/ja/data-modeling/learning-lookml/derived-tables#%E6%B0%B8%E7%B6%9A%E6%80%A7%E3%82%92%E8%BF%BD%E5%8A%A0%E3%81%99%E3%82%8B)
- [Lookerのエキスパート クラスメソッドさんのテックブログ記事](https://dev.classmethod.jp/articles/looker-pdt/)

例えば、PDT生成の基本例の1つとして、以下のようなdatagroupを使用したviewが挙げられます。[^1]

```yaml:pdt_temp_table.view
view: pdt_temp_table {
  derived_table: {
    datagroup_trigger: temp_datagroup
    explore_source: temp_table {
      column: id {}
      column: column_1 {}
      column: column_2 {}
      column: column_3 { field: temp_table.temp_column_a }
      column: update_timestamp { field: temp_table.update_timestamp_time }
    }
  }
  （中略）
}

datagroup: temp_datagroup {
  sql_trigger: SELECT FORMAT_TIMESTAMP('%F',CURRENT_TIMESTAMP(),'Asia/Tokyo') ;;
  max_cache_age: "24 hours"
}
```
まず、`datagroup: temp_datagroup`を定義して、定義したdatagroupをPDTとして生成したいviewの`derived_table`内に`datagroup_trigger: temp_datagroup`と記述します。

すると、datagroup内の`sql_trigger`で設定したクエリの値が変化した際に、中間テーブルが自動的にCREATEされます。
（以下の図は*pdt_temp_table.view*において自動的に生成されたBigQuery中間テーブルの例です）

![BigQueryTable.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/533552/348a98fe-7bdb-a70a-c3dd-a5e4f34ab8a7.png)

PDTで生成された中間データは、所定のlook・dashboardのデータ元として自動的にキャッシュのように用いられます。

# 中間テーブルを柔軟に作成する

基本的なPDT機能を用いた中間テーブル作成は以下の懸念点があります。

- 中間データの出力方法はテーブルが新規にCREATEされるのみ（＝テーブルが乱立する）
- 中間データ格納先のDBスキーマとテーブル名はLookerの規則で決められてしまう[^2]

これらの懸念点を踏まえて柔軟に中間テーブルを作成するには、**新たに中間テーブルを作成するためのダミーのviewを設定する**ことで解決します。

```yaml:pdt_temp_table_dummy.view
view: pdt_temp_table_basics {
  derived_table: {
    explore_source: temp_table {
      column: id {}
      column: column_1 {}
      column: column_2 {}
      column: column_3 { field: temp_table.temp_column_a }
      column: update_timestamp { field: temp_table.update_timestamp_time }
    }
  }
}

view: pdt_temp_table_dummy {
  derived_table: {
    datagroup_trigger: temp_datagroup
    create_process: {
      sql_step: CREATE OR REPLACE TABLE default_dataset.pdt_temp_table AS 
                ${pdt_temp_table_basics.SQL_TABLE_NAME} ;;
      sql_step: CREATE OR REPLACE TABLE ${SQL_TABLE_NAME} AS
                SELECT 1 AS dummy ;;
    }
  }
  dimension: dummy {}
}
```

*pdt_temp_table_dummy.view*では、`create_process`・`sql_step`という項目が追加されました。

また、`view: pdt_temp_table_dummy`内での記述`${pdt_temp_table_basics.SQL_TABLE_NAME} `は、`view: pdt_temp_table_basics`におけるderived_tableのクエリ文を参照するという意味を表しています。（[これはカスケードと呼ばれる機能です](https://docs.looker.com/ja/data-modeling/learning-lookml/derived-tables#%E4%BB%96%E3%81%AE%E6%B4%BE%E7%94%9F%E3%83%86%E3%83%BC%E3%83%96%E3%83%AB%E3%81%AB%E3%81%8A%E3%81%91%E3%82%8B%E6%B4%BE%E7%94%9F%E3%83%86%E3%83%BC%E3%83%96%E3%83%AB%E3%81%AE%E5%8F%82%E7%85%A7)）

なお、上記の`view: pdt_temp_table_dummy`のexploreを実行すると、Looker UI側のexplore画面は以下のように意味を持たない出力結果になります。

![Looker_explore.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/533552/dc4b1fe5-4697-b864-822e-7ac5d8decb96.png)

ただし、`view: pdt_temp_table_dummy`のexploreの実行中に`create_process`内の`sql_step`のDDL文が順に実行され、
以下のように、DDL文で指定した名称（＝名称が「LR_●●●...」ではない）中間テーブル「temp_table」がCREATEされます。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/533552/d2513f1b-7957-43bc-a2e9-fe0b3599e171.png)

もちろん`sql_step`では、INSERT・DELETE文も記述することも可能で、一部データのみ更新するといったことも可能になります[^3]（＝かなり柔軟な中間テーブル運用が可能になります）。

# 中間テーブル自動更新を設定する際の注意点

Lookerでは、先述のとおり、`datagroup_trigger`において更新するトリガーを設定することで、中間テーブルデータを自動的に更新できるようになりますが、その際に、注意点が2点あります。

### user_attributesの取り扱いに注意

トリガー由来でPDTを生成する実行ユーザーはLookerのシステムユーザーになります。

（以下のクエリ実行ログ画面によると`User: None`と表されている）

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/533552/513b4c8c-6e47-7111-0b2a-7be50bb95267.png)

一方、Lookerにはユーザーアカウントごとに引数を設定できる「user_attributes」という機能がありますが、現在、Lookerのシステムユーザーにuser_attributesの値の割り当てを設定することは不可能です。

そのため、datagroupの項目を含むviewに対してuser_attributesの記述（*{{ _user_attributes['●●●'] }}*）はできません。

（Lookerのシステムユーザーにおけるuser_attributesの引数は全て空値になります）

ただし、それを逆手にとって、*pdt_temp_table_dummy.view(version2)*では、自動更新と手動更新の処理分岐をLiquid記法のif文で記述することで同一view内で実現しています。

```yaml:pdt_temp_table_dummy.view(version2)
view: pdt_temp_table_filtered {
  derived_table: {
    explore_source: temp_table {
      column: id {}
      column: column_1 {}
      column: column_2 {}
      column: column_3 { field: temp_table.temp_column_a }
      column: update_timestamp { field: temp_table.update_timestamp_time }

      filters: {
        field: temp_table.update_timestamp_time
        value: "{{ _user_attributes['start_date'] }} to {{ _user_attributes['end_date'] }}, {{ _user_attributes['end_date'] }} for 1 days"
      }
    }
  }
}

view: pdt_temp_table_dummy {
  derived_table: {
    datagroup_trigger: temp_datagroup
    create_process: {
      sql_step: {% if _user_attributes['start_date'] == '' %}
                  CREATE OR REPLACE TABLE default_dataset.pdt_temp_table AS 
                  ${pdt_temp_table_basics.SQL_TABLE_NAME} ;;
                {% else %}
                  INSERT default_dataset.pdt_temp_table
                       (id,
                        column_1,
                        column_2,
                        column_3,
                        update_timestamp)
                  ${pdt_temp_table_filtered.SQL_TABLE_NAME}
                {% endif %} ;;
      sql_step: CREATE OR REPLACE TABLE ${SQL_TABLE_NAME} AS
                SELECT 1 AS dummy ;;
    }
  }
  dimension: dummy {}
}
```

### sql_triggerのクエリ設定

datagroupで設定する`sql_trigger`のクエリは、connection内の「PDT And Datagroup Maintenance Schedule」でcron設定することで、定期的に実行されます。
（デフォルトでは5分）

その定期的なクエリ実行を繰り返す中で、クエリの値に変化が生じた場合にPDTの自動作成がLookerシステム内で起動します。

Looker公式では、以下のようなsql_triggerのクエリ設定方法を提示してくれています。

- [TIMESTAMP関数を用いたクエリ（例：hourly, 毎日●:00のdaily）](https://docs.looker.com/ja/reference/view-params/sql_trigger_value#examples)
- [データ差分を示すカラムに対する集計クエリ（例：idの最大値, update_timestampの最大値, テーブル全体のレコード数）](https://docs.looker.com/reference/model-params/datagroup)

しかし、このクエリ設定で重いクエリを設定すると、PDT自動生成を行う前の段階でDBリソースに影響を与えかねません。
（とくに、データ差分をトリガにしたい場合はよく起こりうる話です）

そこで、テーブルデータの更新・追加状況をログとして記録するテーブル（例：以下の図のような*pdt_update_log*）の作成をお薦めします。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/533552/1ad52536-4221-efcd-e015-bb1dfab97088.png)

*pdt_update_log*テーブルを使用したクエリをsql_triggerに設定することで、PDT作成に依存する元テーブルのupdate状況を低コストのクエリで監視できます。

このように、DBリソースに優しい適切なsql_triggerのクエリ設定の工夫はとても重要です。

# おわりに

本記事では、Lookerを用いた中間テーブル管理法の例・注意点を紹介させていただきました。

なお、中間テーブルの運用は、DBリソースに負荷をかけるので、DB管理者の方とよく相談しながら進めていただければと思います。

今回も小技を紹介してしまいましたが、このような小技を
[先々月リニューアルされた日本語版のLooker Forum](https://community.looker.com/japanese-161)に
Lookerユーザーの皆さんにとって充実することを願って。。。

# まとめ

- Lookerを用いた中間テーブルの生成・管理はPDT機能を用いる
- create_process・sql_stepでDDL文を発行することで中間テーブルを柔軟に作成・更新できる
- PDTの自動更新ではuser_attributesは使用不可
- PDT自動実行トリガー設定内のsql_triggerクエリは低コストを心がける（デバッグ実行ログをテーブルとして展開してそのテーブルにクエリを投げるのも手）

# さらにお知らせ

***私が現在所属している ZOZOテクノロジーズ 推薦基盤チームでは一緒に楽しく働くデータエンジニアを募集しています！***

***ご興味のある方は[ぜひこちらをご覧ください！](https://www.wantedly.com/projects/503761)***

[^1]: Looker 7.0からは[publish_as_viewを用いて作成する方法](https://docs.looker.com/reference/view-params/publish_as_db_view?version=7.20&lookml=new)もあります
[^2]: Looker公式ドキュメントによると、[scratch schema name].[connection registration key]\_[model_name]\_[view_name]となるように格納するDB(データセット)とテーブル名が決まります
[^3]: sql_stepに記述できるDDL文はDBエンジンの制約やLookerで設定したDB実行ユーザの権限によって依存します