とあるお客様のエンゲージメントで、ダッシュボードのコストを3分の1に減らすことができたサクセスストーリー

この記事でわかること

Google CloudでLooker/Data/AI(!)のコンサルタントをしています。Lookerに入って気づいたら5年、#stilllovelookerlove

お客様とのエンゲージメントでパフォーマンス改善とかしたりするんですが、今回はとある日本のお客様(一緒にプロジェクト取り組めてすごく楽しかったです！ありがとうございます！)で、実際に、現状のダッシュボードのパフォーマンス分析から改善案のリコメンド、そしてリコメンドの実装をした結果、ダッシュボードのコストが３分の１まで削減できたという、なんとも技術コンサルタントとして大変嬉しいプロジェクトがあったので、ここで何をしたかを共有したいと思います。

分析からスタート

とにかく、初見のプラットフォームで何が起きてるかをまず把握するために、System Activityをゴリゴリに使っていきます。見るべき観点は、それこそハイレベルなところから詳細レベルまでありますが、まず切り分けしたいのは、

• 大量のロードが発生した故の、リソース枯渇なのか（一時的な遅さか）
• それとも恒常的に遅いのか

を見ていきたいので、私はよく下のようなダッシュボードをあらかじめ作って調べます。この手のダッシュボードは一度使っておしまいではなく、今後のモニタリング用として、引き続き使っていけるので、作っておくことをおすすめします。

a) ハイレベルな状態を把握するためのダッシュボード
b) 個々のダッシュボードのパフォーマンスを見るためのダッシュボード

aのダッシュボードで見ているのは、

• 時系列での全体の平均クエリ時間、同時実行クエリ数
• Usage Pattern(例えば、Embedとそうでないか、クエリはダッシュボードからか、Exploreからか、みたいなパターン)
• 平均クエリ時間が遅いTop10のダッシュボード

ざっくり、どの時間が一番忙しくて、どういう使われ方をしていて、どのダッシュボードが特に遅いのかがわかればいい、っていうノリです。

bはもっと細かく、ダッシュボードを絞った上での

• 時系列での平均クエリ時間
• より細かいクエリのステップごとの時間
• タイル別クエリの平均クエリ時間
• ユーザごとの平均実行時間

なんかを出しています。ただのダッシュボード全体の平均クエリ時間を見ていたとて、なかなかそこから原因や、実際の改善策にはたどり着かないので、上の情報から、

• どのタイルのクエリが遅いのか
• ユーザレベルで見たときの実行時間
• どのクエリステップが一番時間かかっているのか

みたいなところを見て、なんでこのクエリが遅いのかってところを仮説を立てて調べていきます。

ありうる仮説としては、（上のリソース枯渇ではなさそうっていう前提で）

クエリベースだと

• パーティション、クラスタリングがないテーブルをクエリしている
• フィルターを一切かけていないで、大量のデータをクエリしている
• ジョインがめちゃくちゃ多い
• LookMLが複雑すぎる

ダッシュボードレベルだと

• タイルの数が多い（タイルの数が多いと、同時実行できるクエリ数の上限を超えるせいで、キューするクエリが出てきます、もったいない）
• 行合計とか列合計、ピボットが駆使された非常に重いタイル
• 表示しようとしているデータ量が多い

なんかがよくある理由です。

フィルタとか、ジョインといった、個々のクエリの中身を見る前におすすめなのは、一旦、今回のクエリの対象となっているテーブルのデータ量だとか、パーティションもしくはクラスタリングされているかをまず見たほうが作業の効率としてはよいので、そこはBigQueryのInformation Schemaを活用してざっとテーブル情報をゲットしておきます。

個々のクエリを見ていく必要があるときも、System Activityを基本は使っていきます。フィルタがされているかどうか、とか、どのフィールドがクエリされてるかまで見ることができますが、もし実際のクエリを見ないとだめな場合でも、history viewのslugというdimensionに実はリンクがあり、実際に発行されたクエリの中身を見ることができます。このあたりの作業はひたすら仮説とその証明の繰り返しとスクリプト書いたりで、地味な分析作業をします、と思っていただければ。

原因

今回のお客様は、平均クエリ時間ベースで見ると、数秒と、ものすごく成績のよい感じなのですが、よく詳細を見てみると、20秒とか、それ以上かかっているケースもあり、上の仮説を検証していった結果、

• パーティション、クラスタリングが入ってないテーブルがほとんど
• 適切なフィルタがかかってないことで、特にダッシュボードを開いた瞬間の初クエリが重い
• ダッシュボードのタイル数が多い

ことでした。

改善策

以下の改善策を提案し、実装していただきました。

• テーブルに、パーティション、クラスタリングをよくフィルタされるカラムに対して設定
• パーティション、クラスタリングを設定したカラムで、ダッシュボード上で必須フィルターとする
• フィルタのデフォルト値を設定

少しトリッキーなのは、クラスタリングを設定した際に、Looker上でフィルタがかかる順番をクラスタリングキーの指定順になるように強制したいので、そこはsql_always_whereを使って、順番を指定する仕込みが必要です。

結果

結果はタイトルにもある通りです。Lookerの良いところは、BigQueryでできることをやったら、それを必ずLooker側のダッシュボードないし、LookMLの設計に反映させておくことで、最大限の効果が得られるところだと思います。

番外編

他のお客様でもよくあるクエリ遅い問題。とあるお客様でJoinが鬼のように多いクエリがありました。数で言うたら、50-100ぐらい。もちろん、Exploreの定義だけでジョインが多いのもわかるし、なおかつ、それぞれのJoinされたViewのフィールドをこれでもかとクエリしてるのも目に見えてわかるんですが、それでもクエリをまじまじと見てみたら、無駄なジョインがありました。

何が起きていたかというと、よくあるparameterを使って、動的にdimensionをダッシュボード上でフィルタ使って変えたいっていう実装。これが、悪さをしていました。どういうことかと言うと、例えば、以下のOKケースのような動的に変える対象のdimensionがすべて同じViewから来てるのであればいいものの、

OKケース

view: order_items {
  parameter: time_granularity {
    type: unquoted
    allowed_value: {
      value: "daily"
      label: "daily"
    }
    allowed_value: {
      value: "weekly"
      label: "weekly"
    }
    allowed_value: {
      value: "monthly"
      label: "monthly"
    }

    default_value: "daily"
  }

  dimension: dynamic_time_granularity {
    label_from_parameter: time_granularity
    type: string
    sql: {% if time_granularity._parameter_value == 'daily' %}
         ${created_at_date}
         {% elsif time_granularity._parameter_value == 'weekly' %}
         ${created_at_week}
         {% elsif time_granularity._parameter_value == 'monthly' %}
         ${created_at_month}
         {% endif %}

    ;;
  }

下のコードのように、他のViewsにあるフィールドを参照していると、このdynamic_dimensionを使用しているタイルでは、常にorder_items,users,inventory_itemsのViewsのジョインが、選択した値に関わらず、発生します。例えば、statusを選んだとしても、常にusers, inventory_itemsがジョインされるようになります。（補足:基本的に、LookerはExploreにJoinが定義されていても、常にJoinが発生するわけではなく、選択されたフィールドによってJoinするしないが決まります。）

無駄なJoinが発生するケース

view: order_items {
  parameter: dimension_selector {
    type: unquoted
    allowed_value: {
      value: "city"
      label: "city"
    }
    allowed_value: {
      value: "brand"
      label: "brand"
    }
    allowed_value: {
      value: "status"
      label: "status"
    }
    default_value: "status"
  }
  
  dimension: dynamic_dimension {
    type: string
    sql: {% if dimension_selector._parameter_value == 'city' %}
    ${users.city}
    {% elsif dimension_selector._parameter_value == 'brand' %}
    ${inventory_items.product_brand}
    {% elsif dimension_selector._parameter_value == 'status' %}
    ${status}
    {% endif %}
    
    ;;
  }

なので、やらないほうがいいと思うんですけど、どうしても、、っていう場合は、

Option1: Joinの実装も動的にする（ハードコア）

要は、そのparameterの選択肢となるフィールドの定義に沿って、Joinするしないをliquidを使ってゴリゴリ書くのです。Liquid便利（白目）

以下の例みたいな感じです。

explore: order_items {
  join: users {
    sql:
    {% if order_items.filter_dynamic_dimension._parameter_value == 'city' %}
    LEFT JOIN `thelook.users` as users on order_items.user_id = users.id
    {% elsif
      order_items.filter_dynamic_dimension._parameter_value == 'brand'
      or order_items.filter_dynamic_dimension._parameter_value == 'status'
    %}
    {% else %}
    LEFT JOIN `thelook.users` as users on order_items.user_id = users.id
    {% endif %}
    ;;
    relationship: many_to_one
  }
join: inventory_items {
  sql:
    {% if
      order_items.filter_dynamic_dimension._parameter_value == 'brand'
    %}
    LEFT JOIN `thelook.inventory_items` as inventory_items on inventory_items.id = order_items.inventory_item_id
    {% elsif
      order_items.filter_dynamic_dimension._parameter_value == 'city'
      or order_items.filter_dynamic_dimension._parameter_value == 'status'
    %}
    {% else %}
    LEFT JOIN `thelook.inventory_items` as inventory_items on inventory_items.id = order_items.inventory_item_id
    {% endif  %}
  ;;
  relationship: many_to_one
}
}

Option2: テーブルの再設計を考慮、ジョインをしなくてもよい設計にする

が、あるかと思います。ここは、違うトピックになるので、この記事では割愛します。

締め

思ったより長くなりましたが、この記事が少しでも役に立てば幸いです。また、もしパフォーマンスでお悩みの場合は、ぜひ弊社のコンサルティングサービスの活用も検討いただければと思います。パフォーマンス改善以外にも、設計支援等々諸々行っていますので、お気軽にお問い合わせいただいたければと思います。

完