こんにちは、
株式会社スマートドライブでモビリティデータに関わるコンサルティング
をしている@zwt1nです。(主な仕事内容はこちら)
僭越ながら、Looker Advent Calendar 2020 の9日目を担当させて頂きます。
本記事ではLookerのExploreやPDT生成に掛かるクエリコストを把握する方法を紹介します。
##想定読者
- Lookerを導入している
- DWHにBigQueryを採用している
- BigQueryの監査ログだけでは限界を感じている
- コストという観点でExploreの最適化したいと少しでも思っている
##なぜ、こんなことをするのか？
シンプルにコスト削減したい、というのが本音です。
弊社データはGPSの地点情報をはじめ、多様に大量なデータをDWHに蓄積しております。
スキャン量が３００GBを超えるようなクエリを投げるのは日常茶飯事です。
また、弊社ではLookerで作成したダッシュボードをテンプレートとしてお客様に提供しており、お客様の増加と共に、弊社負担のクエリコストが増えていきます。
そのため、類似のView・Exploreは極力集約・効率化していき、
クエリあたりの提供価値を最大化していくのが望ましいと考えています。
##前提
以下の手順に移る前にLookerが提供するBlocks機能のデプロイが必要です。
Lookerは様々なタイプのテンプレートをBlocksという形で公開しています。(Looker Blocksについてはこちらから)
今回弊社で利用したBlocksが以下です。
上記Blockのデプロイで、以下のような作業が一瞬で終わります。
- BigQuery監査ログの整形（各種Viewが自動で作成されます）
- Exploreの作成
- ダッシュボードの作成
以降の手順では上記Blockによる、ViewやExploreが作成されている前提で話を進めます。
##手順
1. LookerのSystem Activityの取得
2. Schedule機能でGCSへの連携
3. BigQuery Data Transfer
4. 新規View作成
5. Viewファイルの修正(bigquery_data_access.view)
6. モデルファイルの修正(gcp_bigquery_logs.model)
7. Look作成
####0. 全体の流れ
####1. LookerのSystem Activity取得
Lookerが公開しているSystem Activityに関するExploreの一つである、Historyを使います。
連携するディメンション、メジャーは任意ですが以下はあった方が良いでしょう。
- History ID
- Model(Query)
- Explore(Query)
- Title(Dashboard)
####2. Schedule機能でGCSへの連携
Lookとして保存したら、スケジュールを使ってGCSに連携します。
※あらかじめ、GCSのActionを有効にしておく必要があります。
初期のBulk抽出以降は、HistoryのCreated Timeを利用して任意のタイミングでGCSに連携するよう設定して下さい。
####3. BigQuery Data Transfer
GCSへのパイプラインが整ったら、BigQuery Data Transferを使ってBigQueryへの転送設定を行います。
####4. 新規View作成
BQ上に連携されたLookerのシステムアクティビティテーブルに対するViewを定義します。
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に以下を追記します。
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)を修正します。
４で作成したViewをJOINします。
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毎の料金も見ながら集約・効率化の対象を選定しています。
##まとめ
ここまで目を通して頂きありがとうございます。
お気づきの方が多いと思いますが、今回やったことは
Lookerのシステムアクティビティをいかに自社で自由に扱えるようにするか？がポイントになっております。
その為に重要となった機能は以下です。
全部Googleで完結。
Googleさん、いつもありがとうございます。