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

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

今回は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() ;;
  }

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

永続派生テーブルを確認

この状態でExploreを使用（クエリ発行）すると、クエリは下記のような形になっていました。

Snowflake側を見てみると、Connectionで指定したスキーマ下に、永続派生テーブルが作成されていました。

おわりに

非常に便利な機能ですが、実際にDBにテーブルを作成する以上、DB管理者とよく相談する必要があると思いました。個人的には、永続派生テーブルを使わないで済むならそれに越したことはなく、一時派生テーブル等ではにっちもさっちもいかない場合に使用するのがいいのかなと感じました。