[Looker]create_processで処理を作る時は必ず${SQL_TABLE_NAME}でテーブルを作りましょう #looker

さがらです。

LookerでSQL派生のPDTを生成する際の選択肢として、以下2つの方法が挙げられます。

• sqlパラメータにSELECT文を記述
• create_processとsql_stepでテーブルの作成からsqlで定義

この内、「create_processとsql_stepでテーブルの作成からsqlで定義」について、${SQL_TABLE_NAME}を使ってテーブル定義をしないとどうなるのか、を確認してみました。

その結果、タイトルの通り「create_processで処理を作る時は必ず${SQL_TABLE_NAME}で何かしらのテーブルを作りましょう」ということがわかりました。

特にBigQueryのオンデマンド契約の場合は手痛い出費に繋がる可能性があるため、ぜひご一読ください。

create_processってなんだっけ？

改めて、create_processとは何か触れておきたいと思います。

下記は公式DocのExampleですが、PDTを生成する時のクエリをDDL文から自前で定義したい場合に使える機能です。

view: ctasless_customer_order_facts {
  derived_table: {
    datagroup_trigger: some_datagroup
    create_process: {
      sql_step: CREATE TABLE ${SQL_TABLE_NAME} (
                         customer_id int(11),
                         lifetime_orders int(11)
                       ) ;;
      sql_step: INSERT INTO ${SQL_TABLE_NAME}(customer_id, lifetime_orders)
                        SELECT customer_id, COUNT(*) AS lifetime_orders
                         FROM order
                         GROUP BY customer_id ;;
    }
  }
}

このとき、${SQL_TABLE_NAME}をクエリの中に記述することでPDTとして定義・参照することができるのですが、自由にクエリを書くことが出来るので${SQL_TABLE_NAME}を書かずに別のテーブルを指定してしまうことが可能です。

これをすると、何が起きるのか…？次章で確認してみます。

様々なパターンでPDTを作って確かめてみる

ということで、「create_processで${SQL_TABLE_NAME}を書かずに別のテーブルを指定するとどうなるのか」を確かめるため、様々なパターンでPDTを生成して違いを見ていきましょう。

比較対象

以下の3パターンで、同じデータを表すようにクエリを定義して確かめてみます。

使用DWH

BigQueryを使用します。

共通のmodelファイル

## 共通のdatagroupだけ書いておきます。connectionなどは省略します。 ##
datagroup: default_datagroup {
  sql_trigger: SELECT EXTRACT(YEAR FROM CURRENT_DATE());;
  max_cache_age: "24 hour"
}

パターン1：sqlパラメータを使う

view: test_pdt_no1 {
  derived_table: {
    datagroup_trigger: default_datagroup
    sql: SELECT
          start_station_name
          ,sum(tripduration) AS tripduration
        FROM `sagara-satoshi.new_york_citibike_copy.citibike_trips_2016and2017`
        WHERE starttime = "2017-10-29T09:00:45"
        GROUP BY 1
       ;;
  }
  ## フィールド定義は省略します ##
}

パターン2：create_processを使いテーブル名は${SQL_TABLE_NAME}を用いる

view: test_pdt_no2 {
  derived_table: {
    datagroup_trigger: default_datagroup
    create_process: {
      sql_step:
      CREATE OR REPLACE TABLE ${SQL_TABLE_NAME} AS (
          SELECT
          start_station_name
          ,sum(tripduration) AS tripduration
        FROM `sagara-satoshi.new_york_citibike_copy.citibike_trips_2016and2017`
        WHERE starttime = "2017-10-29T09:00:45"
        GROUP BY 1
      )
      ;;
    }
  }
  ## フィールド定義は省略します ##
}

パターン3：create_processを使いテーブル名は独自に定義する

view: test_pdt_no3 {
  derived_table: {
    datagroup_trigger: default_datagroup
    create_process: {
      sql_step:
      CREATE OR REPLACE TABLE `sagara-satoshi.new_york_citibike_copy.test_pdt_no3` AS (
          SELECT
          start_station_name
          ,sum(tripduration) AS tripduration
        FROM `sagara-satoshi.new_york_citibike_copy.citibike_trips_2016and2017`
        WHERE starttime = "2017-10-29T09:00:45"
        GROUP BY 1
      )
      ;;
    }
  }
  ## フィールド定義は省略します ##
}

比較結果

※章のタイトルは「比較結果」としていますが、問題があるのはtest_pdt_no3だけですので、このtest_pdt_no3に関する結果を書き記していきます。

まず、Adminタブ➟Persistent Derived Tablesから各PDTの作成状況が確認できますが、下図の通り、test_pdt_no3だけ「未作成」となっています。

一方で、test_pdt_no3に関するPDT Activity Dashboardを見ると、一度も処理は失敗せずにPDT生成処理は成功していると…

この事象について、以下のように解釈しました。

• 「LookerのPDT」として判定されるには、${SQL_TABLE_NAME}として参照できるテーブルを定義しないといけない。そのため、create_processで${SQL_TABLE_NAME}を用いてテーブルを作成しないと、Lookerから「このviewでのPDTは未作成」と判定される。
• 一方でPDT Activity Dashboardにおける処理の成功可否は、create_processが問題なく回ったかどうか。

また、test_pdt_no3について、このviewを使ったExploreを立ち上げてクエリを発行すると下図のようにエラーが表示されます。

このエラーも、test_pdt_no3というviewにおけるSQL_TABLE_NAMEに該当するテーブルが作られていないから、つまりはPDTが作られていないから、ということが原因と考えられます。

「viewとして定義しているのに参照先のテーブルがなくてExploreに使用できない」という状況はLookMLの可読性も明らかに落ちるので、create_processを使う際は${SQL_TABLE_NAME}でテーブルを定義すべきだとこの検証を通して感じました。

最後に：${SQL_TABLE_NAME}が無いことによる一番のリスク

実は、本ブログで検証した事項について下記の公式Docにも注意書きが書いてあるのです。

create_process must create a table with the name indicated by the ${SQL_TABLE_NAME} substitution operator, or it will be rebuilt from scratch on every trigger check interval specified in a connection’s PDT and Datagroup Maintenance Schedule setting (the default is five minutes). This can cause unexpected query traffic on your database or data warehouse.

(DeepL翻訳)create_processは、${SQL_TABLE_NAME}置換演算子で示される名前のテーブルを作成しなければなりません。さもなければ、接続のPDTとデータグループのメンテナンススケジュール設定で指定されたトリガチェック間隔（デフォルトは5分）ごとに、ゼロから再構築されます。これにより、データベースやデータウェアハウスで予想外のクエリトラフィックが発生する可能性があります。

この注意書きの通り、5分置きにcreate_process処理が回ってしまう場合があります。（5分はPDT更新チェック間隔のデフォルト値です）

前述の私の検証では実はこの現象が確認できなかったのですが、もし数百GBスキャンするクエリをcreate_processで記述していて、BigQueryのオンデマンド契約で5分ごとに発行されたら…と考えると恐ろしいですね。

この事象を防ぐためにも、create_processで処理を作る時は必ず${SQL_TABLE_NAME}でテーブルを作ることを心がけたいですね。