LookerのExplore毎のクエリコスト出してみた

こんにちは、
[株式会社スマートドライブ](https://smartdrive.co.jp/)でモビリティデータに関わるコンサルティング
をしている[@zwt1n](https://twitter.com/zwt1n)です。(主な仕事内容は[こちら](https://prtimes.jp/story/detail/qb6DJDce2xG))

僭越ながら、[Looker Advent Calendar 2020](https://qiita.com/advent-calendar/2020/looker) の9日目を担当させて頂きます。
本記事ではLookerのExploreやPDT生成に掛かるクエリコストを把握する方法を紹介します。

##想定読者
  - Lookerを導入している
  - DWHにBigQueryを採用している
  - BigQueryの監査ログだけでは限界を感じている
  - コストという観点でExploreの最適化したいと少しでも思っている

##なぜ、こんなことをするのか？
  シンプルにコスト削減したい、というのが本音です。
  弊社データはGPSの地点情報をはじめ、多様に大量なデータをDWHに蓄積しております。
  スキャン量が３００GBを超えるようなクエリを投げるのは日常茶飯事です。

  また、弊社ではLookerで作成したダッシュボードをテンプレートとしてお客様に提供しており、お客様の増加と共に、弊社負担のクエリコストが増えていきます。

  そのため、類似のView・Exploreは極力集約・効率化していき、
  クエリあたりの提供価値を最大化していくのが望ましいと考えています。

##前提
以下の手順に移る前にLookerが提供するBlocks機能のデプロイが必要です。
Lookerは様々なタイプのテンプレートをBlocksという形で公開しています。([Looker Blocksについてはこちらから](https://ja.looker.com/platform/directory/home))

今回弊社で利用したBlocksが以下です。

- [google-bigquery-performance-by-datatonic](https://looker.com/platform/blocks/source/google-bigquery-performance-by-datatonic)

上記Blockのデプロイで、以下のような作業が一瞬で終わります。

- BigQuery監査ログの整形（各種Viewが自動で作成されます）
- Exploreの作成
- ダッシュボードの作成

以降の手順では上記Blockによる、ViewやExploreが作成されている前提で話を進めます。


##手順
[1. LookerのSystem Activityの取得](#1-lookerのsystem-activity取得)
[2. Schedule機能でGCSへの連携](#2-schedule機能でgcsへの連携)
[3. BigQuery Data Transfer](#3-bigquery-data-transfer)
[4. 新規View作成](#4-新規view作成)
[5. Viewファイルの修正(bigquery_data_access.view)](#5-viewファイル修正bigquery_data_accessview)
[6. モデルファイルの修正(gcp_bigquery_logs.model)](#6-モデルファイルの修正gcp_bigquery_logsmodel)
[7. Look作成](#7-look作成)


####0. 全体の流れ
![スクリーンショット 2020-12-10 0.06.36.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/878000/89208e2a-42d5-c21e-3e81-f4fb70d95fe7.png)


####1. LookerのSystem Activity取得
Lookerが公開しているSystem Activityに関するExploreの一つである、Historyを使います。
連携するディメンション、メジャーは任意ですが以下はあった方が良いでしょう。

- History ID
- Model(Query)
- Explore(Query)
- Title(Dashboard)
![スクリーンショット 2020-12-09 14.30.26.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/878000/0822a26c-5d78-b185-ecf5-0bad89d14c25.png)


####2. Schedule機能でGCSへの連携

Lookとして保存したら、スケジュールを使ってGCSに連携します。
※あらかじめ、GCSのActionを有効にしておく必要があります。

初期のBulk抽出以降は、HistoryのCreated Timeを利用して任意のタイミングでGCSに連携するよう設定して下さい。
![スクリーンショット 2020-12-09 14.35.05.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/878000/c0476103-93fa-eb73-7e85-8264081dc1bf.png)


####3. BigQuery Data Transfer

GCSへのパイプラインが整ったら、BigQuery Data Transferを使ってBigQueryへの転送設定を行います。
![スクリーンショット 2020-12-09 22.37.54.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/878000/aea55b28-3a1d-1c7c-a5ae-45f265747857.png)


####4. 新規View作成
BQ上に連携されたLookerのシステムアクティビティテーブルに対するViewを定義します。

```sql:looker_query_history.view.lookml
view: looker_query_history {
  sql_table_name: `sandbox_nishizawa.looker_query_history`
    ;;

  dimension: dashboard_title {
    type: string
    sql: ${TABLE}.Dashboard_Title ;;
  }
  dimension: history_approximate_web_usage_in_minutes {
    type: number
    sql: ${TABLE}.History_Approximate_Web_Usage_in_Minutes ;;
  }
  dimension: history_average_runtime_in_seconds {
    type: number
    sql: ${TABLE}.History_Average_Runtime_in_Seconds ;;
  }
  dimension: history_id {
    primary_key: yes
    type: string
    sql: cast(${TABLE}.History_ID as STRING) ;;
  }
  dimension: history_is_user_dashboard__yes___no_ {
    type: yesno
    sql: ${TABLE}.History_Is_User_Dashboard__Yes___No_ ;;
  }
  dimension: history_issuer_source {
    type: string
    sql: ${TABLE}.History_Issuer_Source ;;
  }
  dimension: history_max_runtime_in_seconds {
    type: number
    sql: ${TABLE}.History_Max_Runtime_in_Seconds ;;
  }
  dimension: history_min_runtime_in_seconds {
    type: number
    sql: ${TABLE}.History_Min_Runtime_in_Seconds ;;
  }
  dimension: history_rebuild_pdts__yes___no_ {
    type: yesno
    sql: ${TABLE}.History_Rebuild_PDTs__Yes___No_ ;;
  }
  dimension: history_result_source {
    type: string
    sql: ${TABLE}.History_Result_Source ;;
  }
  dimension: history_results_from_cache {
    type: number
    sql: ${TABLE}.History_Results_from_Cache ;;
  }
  dimension: history_results_from_database {
    type: number
    sql: ${TABLE}.History_Results_from_Database ;;
  }
  dimension: history_source {
    type: string
    sql: ${TABLE}.History_Source ;;
  }
  dimension: history_sql_runner_user {
    type: number
    sql: ${TABLE}.History_SQL_Runner_User ;;
  }
  dimension: history_status {
    type: string
    sql: ${TABLE}.History_Status ;;
  }
  dimension: history_total_runtime_in_seconds {
    type: number
    sql: ${TABLE}.History_Total_Runtime_in_Seconds ;;
  }
  dimension_group: history_created_at {
    type: time
    timeframes: [
      raw,
      time,
      date,
      week,
      month,
      quarter,
      year
    ]
    sql: ${TABLE}.History_Created_Time ;;
  }
  dimension: query_explore {
    type: string
    sql: ${TABLE}.Query_Explore ;;
  }
  dimension: query_model {
    type: string
    sql: ${TABLE}.Query_Model ;;
  }
  measure: count {
    type: count
  }
}
```

####5. Viewファイル修正（bigquery_data_access.view）

BigQuery監査ログからLooker発行のクエリを抽出するため、
bigquery_data_access.viewに以下を追記します。

```sql:bigquery_data_access.view.lkml
  dimension: looker_history_id {
    type: number
    sql:
      case when STARTS_WITH(bigquery_data_access_query.query, "-- Looker Query Context") = true
        then  SPLIT(SPLIT(bigquery_data_access_query.query, ':')[SAFE_OFFSET(2)],',')[SAFE_OFFSET(0)]
        else null
      end
    ;;
  }
  dimension: looker_pdt_name {
    type: string
    sql:
      case when STARTS_WITH(bigquery_data_access_query.query, "-- Building persistent derived table") = true
        then SPLIT(bigquery_data_access_query.query, ' ')[SAFE_OFFSET(5)]
        else null
      end
     ;;
  }
```


####6. モデルファイルの修正(gcp_bigquery_logs.model)
gcp_bigquery_logs.model内の、Explore(bigquery_data_access)を修正します。
[４](#4-新規view作成)で作成したViewをJOINします。

```sql:gcp_bigquery_logs.model
explore: bigquery_data_access {
  view_label: "BigQuery Data Access"
  label: "BigQuery Data Access Logs"

  always_filter: {
    filters: {
      field: bigquery_data_access_payload.service_name
      value: "bigquery.googleapis.com"
    }
    filters: {
      field: bigquery_data_access_payload.method_name
      value: "jobservice.jobcompleted"
    }
    filters: {
      field: bigquery_data_access_job_completed_event.event_name
      value: "query_job_completed"
    }
  }

  join: bigquery_data_access_payload {
    view_label: "BigQuery Data Access"
    sql: LEFT JOIN UNNEST([${bigquery_data_access.protopayload_auditlog}]) as bigquery_data_access_payload ;;
    relationship: one_to_one
  }
----------------------------------------------------------(中略)----------------------------------------------------------
----------------------------------------------------------以下追加---------------------------------------------------------
  join: looker_query_history {
    view_label: "Looker Query History"
    type: left_outer
    relationship: many_to_one
    sql_on:
      ${bigquery_data_access_query.looker_history_id} = ${looker_query_history.history_id}
    ;;
  }
}
```

####7. Look作成
Exploreが定義できたらあとはお好きなようにLook,tileを作って下さい。
ご覧の通り、一つのExploreだけで月1万円以上掛かっているExploreがあり、塵積で大きなコストになってくる可能性があります。
弊社では月に一度LookMLのリファクタリングDayを設けており、今回お見せしたようなExplore毎の料金も見ながら集約・効率化の対象を選定しています。

![ダウンロード.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/878000/4438a50c-d4e6-7b1c-af96-d78ed7e2a678.png)

##まとめ
ここまで目を通して頂きありがとうございます。
お気づきの方が多いと思いますが、今回やったことは
`Lookerのシステムアクティビティをいかに自社で自由に扱えるようにするか？`がポイントになっております。
その為に重要となった機能は以下です。

- [Looker Blocks](https://ja.looker.com/platform/directory/home)
- [Looker Action (GCS連携)](https://ja.looker.com/platform/actions/send-to-google-cloud-storage)
- [BigQuery Data Transfer Service](https://cloud.google.com/bigquery-transfer/docs/transfer-service-overview?hl=ja)

全部Googleで完結。
Googleさん、いつもありがとうございます。