Lookerの派生テーブルを永続化する

俺のターン！永続派生テーブルを発動！

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

#ビッグデータ

#ビジネス・アナリティクス

#Looker

たまちゃん

2020.04.16

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

奈良県でリモートワーク中の玉井です。

今回はLookerの永続派生テーブルという機能を使ってみました。

公式情報

そもそも派生テーブルとは

下記をご覧ください。

一言でいうと、Looker側で中間テーブル的なものを定義できる機能です。

派生テーブルの永続化とは

派生テーブルはSQLクエリでいうところのWITHで作成されます。要するに「実体」は無いテーブルなんですね。

Lookerを使い続けていると、派生テーブルの中でも、頻繁に使われたり処理が重かったりするものが出てきます。そういう時「すげーよく使うテーブルだから、いっそのこと実テーブルとして用意してえ…」と思う時があります。

この派生テーブルを「実テーブルとして作成しちゃう」機能が、派生テーブルの永続化です。Looker側からデータソース側に対して、実際にテーブルを作成するものになります。実テーブルになるので、パフォーマンスの向上が期待できますし、クエリもシンプルになる（ごっついWITH句が無くなる）ので、DBに対する負荷も軽減できるのではないでしょうか。

永続化する方法

どうやって派生テーブルを永続化するん？という話。

まず、Connectionで永続派生テーブルに関する設定を行います。

Connectionで各種設定

Connection単位で「永続化」に関する設定を行う部分はあります。というのも、前述したように、永続派生テーブルはLookerが実際にテーブルを作るので、Connectionのところで「永続派生テーブルを作成するスキーマ」等を設定する必要があるからです。ちなみに、永続化自体をできないようにすることも可能です。

設定のスクリーンショットは本記事の下部にあります。

派生テーブル部分にパラメータを定義する

ちょっとややこしいのですが、明示的に「この派生テーブルを永続化する！！」みたいな設定は、実はありません。派生テーブルを定義している部分に、後述するパラメータ（のいくつか）を追加で定義することで、任意の派生テーブルを永続化することができます。

`datagroup_trigger`

永続派生テーブルの作成ポリシー（永続派生テーブルを再作成する条件や保持時間）を指定します。指定するのはdatagroupです。datagroupというのは、キャッシュのポリシーを定義する時に使用するパラメータです（下記参照）。

つまりどういうことか。キャッシュのポリシーを永続派生テーブルのポリシーにも使用できるということなんですね。逆にいえば、（キャッシュとは別に定義したい時でも）永続派生テーブルの作成ポリシーもdatagroupを使用して定義するということになります。

ちなみに、datagroup_triggerを定義した時点で、その派生テーブルは永続化されます。「明示的に永続化をON/OFFする設定はない」とはいいましたが、事実上、datagroup_triggerの定義がスイッチになっているとも言えるでしょう。

また、datagroup_triggerを定義した場合、後述するパラメータの指定は不要です。

view: Viewの名前 {
  derived_table: {
    datagroup_trigger: 適用したいdatagroupの名前
    …
  }
}

`sql_trigger_value`

永続派生テーブルを更新する条件（となるSQLクエリ）を定義します。datagroupのsql_triggerと同じですね。クエリ結果の1列目1行目の値が変わったタイミングで永続派生テーブルを再作成します。

定義したクエリの定期実行間隔はこちらを参照（デフォルトは5分間隔で実行される）。

view: Viewの名前 {
  derived_table: {
    sql_trigger_value: SELECT CURDATE() ;;
    …
  }
}

`persist_for`

永続派生テーブルを保持する期間を定義します。定義した期間が過ぎた後で、この永続派生テーブルに対してクエリが実行された場合は、永続派生テーブル自体が再作成されてからのクエリ実行となります。これ、要するに、定義した期間が経過すると永続派生テーブルは削除されます（次にクエリが実行されるまで再作成されない。クエリが来たタイミングで再作成）。ですので、persist_forを定義する場合、sql_trigger_valueで定期的な再作成ルールもあわせて定義しておくのが良いです。

view: Viewの名前 {
  derived_table: {
    persist_for: "24 hours"
    …
  }
}

インデックス

永続派生テーブルは実際にテーブルを作るため、（DBによっては）インデックスを指定する必要があります（必須ではないが定義することを推奨）。DBによって事情が異なってくるため（例えば、Amazon Redshiftの場合、ここで指定するのはINTERLEAVED SORTKEY。そして別途distkeyやsortkeyも定義したほうがよい）、詳細は下記のドキュメントを参照。

view: Viewの名前 {
  derived_table: {
    indexes: ["カラム名"]
    …
  }
}

どれで永続化するのがいいのか（個人の感想）

ケースバイケース…と言ってしまえばそれまでなのですが、ドキュメントを読む感じ、個人的にはdatagroup_triggerで永続化するのがいいのかな、と感じました。永続派生テーブルを複数用意する時に、定義は1回で済むからです（derived_table下に個別にsql_trigger_valueとか書いてるとメンテがめんどくさそう）。

やってみた

下記で作成した派生テーブルを永続化してみます。

Connectionの設定を確認

永続派生テーブルの設定許可がONになっていることを確認します。今回は設定しませんが、PDT Overridesという部分で、永続派生テーブルを作成するユーザーを（既存ユーザーとは別に）別途指定することもできます。

派生テーブルを定義しているviewファイルを確認

一時派生テーブルになっていますね。

派生テーブルを永続化する

今回は「とりあえずやってみた」ということですし、派生テーブルも1個あるだけなので、sql_trigger_valueで永続化します。ちなみに、データソースはSnowflakeなので、インデックスは指定しません（Snowflakeにはインデックスを指定するという設定自体が無い）。

derived_table: {
    sql: with tmp_desire as(SELECT value as raw_status
      FROM "TAMAI_TEST_DB"."SPOTIFY"."NAKAMORI_AKINA"
      ,LATERAL FLATTEN
      (input => RAW_STATUS:sections))

      select
      raw_status:start::float as start_
      ,raw_status:duration::float as duration
      ,raw_status:confidence::float as confidence
      ,raw_status:loudness::float as loudness
      ,raw_status:tempo::float as tempo
      ,raw_status:tempo_confidence::float as tempo_confidence
      ,raw_status:key::integer::float as key_
      ,raw_status:key_confidence::float as key_confidence
      ,raw_status:mode::integer as mode
      ,raw_status:mode_confidence::float as mode_confidence
      ,raw_status:time_signature::integer as time_signature
      ,raw_status:time_signature_confidence::float as time_signature_confidence
      from tmp_desire
       ;;
      sql_trigger_value: SELECT CURRENT_DATE() ;;
  }

クエリ自体はかなり適当です。