LookerでUNNESTを活用するもsuggestionsが機能しない時の処方箋

Lookerのsuggestionsについて

2回連続でLookerネタです。今回はドキュメントにも載っていない話ですのでかなり有用かと思います。

Lookerでは、 type: string なdimensionに対してのfilterをかけるとき、フィルタ条件の欄をクリックすると、suggestが出ます。

要するに、 ここに入りうる値を選択できるようにしてくれる というなかなかCoolでNiceな補助機能です。

ちなみに、LookMLで定義した候補しか出さないこともできます。suggestionsの公式ドキュメントを参照されると良いかと思います。

ちなみに、 case を使用している場合そのロジック内で定義された値のみが候補として表示されるなど、なかなかにイケています。

このあたりは、フィルタの候補の変更の公式ドキュメントが非常に参考になります。そうだったのか、となる内容も多いのでLooker使いの皆様におかれましてはご一読をオススメします。

UNNESTしたテーブルのJOIN時は機能していない…だと

さて今回の本題です。

前回、Lookerで配列データや階層化データを扱う方法 の記事で、配列データ・階層化データ(ネストされたデータ)を扱うためにUNNESTしてJOIN、というのを扱っていました。

前回同様にこんな感じのテーブル purchases を用意してみます

これに対してのLookMLはシンプルに下記のように書けます。

explore: purchases {
  view_label: "購買データ"
  
  join: purchases__item_ids {
    view_label: "購買データ"
    sql: LEFT JOIN UNNEST(${purchases.item_ids}) as purchases__item_ids WITH OFFSET as purchases__item_ids_offset ;;
    relationship: one_to_many
  }
}

view: purchases {
  sql_table_name: [テーブル名] ;;

  dimension: item_ids {
    hidden: yes
    sql: ${TABLE}.item_ids ;;
  }

  dimension: member_id {
    type: number
    sql: ${TABLE}.member_id ;;
  }

  dimension: transaction_id {
    type: number
    sql: ${TABLE}.transaction_id ;;
  }

  measure: count {
    type: count
  }
}

view: purchases__item_ids {
  dimension: id {
    primary_key: yes
    hidden: yes
    sql: CONCAT(CAST(${purchases.transaction_id} AS STRING), '|', ${purchases__item_ids},'|', CAST(${purchases__item_ids_offset} AS STRING)) ;;
  }

  dimension: purchases__item_ids {
    type: string
    sql: purchases__item_ids ;;
  }

  dimension: purchases__item_ids_offset {
    hidden: yes
    type: number
    sql: purchases__item_ids_offset ;;
  }
}

細かい話ですが、一応 item_ids に複数同じidが登場する可能性を考えてJOIN時に WITH OFFSET を活用して primary_key を立てています。

具体的には

このようにIDが振られます。

こちらのデータに対してフィルタのsuggestを試してみましょう…すると

No suggestions found. と出てしまいます。 本来的には、 foo bar baz の3つが候補として出て欲しいのに出ません！

困りました…というケースが実際に弊社にてあったのでその対応方法の話です。(いつもに増して前置きが長い)

処方箋としてのfull_suggestions

というわけで、早速処方箋ですが、 full_suggestions: yes です。

先ほどのLookMLで

dimension: purchases__item_ids {
    type: string
    full_suggestions: yes
    sql: purchases__item_ids ;;
  }

とするだけです。

ホントに…？と感じられている方を一撃で黙らせるスクショです。

やはりホントでした。 はじめて気付いたときは結構興奮しました。

full_suggestionsとは？

好奇心旺盛なLooker使いの皆様におかれましては、なんでこうなるのか？が気になって眠れなくなると思います。おそらくこんな対処法だけ学んでサヨウナラ、とはいかないと思います。

では、そもそも full_suggestions とは何なのでしょうか？

まずは full_suggestionsの公式ドキュメント を参照してみます。下記の記述があります。

Whenever sql_always_where or access_filter are used on an Explore, Looker applies those restrictions to the filter suggestions it makes for fields of type: string. This prevents users from seeing a filter suggestion that does not apply to them.
For example, you use access_filter to limit users to their company's data. If one of those users added a Project Name filter, you might not want them to see the names of projects from other companies.

このドキュメントによると、通常は sql_always_where ないしは access_filter による制限をかけた上で候補を表示しているということです。

…と、一見関係なさそうなことが書いてあります。

これをもう少し紐解いた上での私の理解では、

full_suggestions: yes は結局、Exploreのクエリベースで候補一覧を出すもので、

full_suggestions: no の場合(これがLookerではデフォルト)、単純にテーブルから DISTINCT な値を全て抜いてこようとするので、UNNESTした内容をJOINするような今回の文脈ではNGとなっていた… ということだと思っています。

このあたりは色々なデータとLookMLで試行錯誤すると見えてくるかと思いますが、差し当たっての私の見解としては以上です。

まとめ

今回は前回の記事に続いて配列データ・階層化データをLookerで取り扱う話でした。その際のハマりポイントである「suggestions」について扱いました。

Lookerのsuggestions機能は現場ユーザに卸していく上で必須の機能とも言えるかと思いますので、可能な限りLookMLを複雑にせず対応していきたいですね。ちなみにどうしても簡易に対応するのが難しそうな場合には、 suggest_explore や suggest_dimensionというオプションもあり、柔軟に対応出来ます。

Lookerはとても良いプロダクトですが、まだまだ普及が進みきっていないのでこういったハマりポイントでドキュメントが少ないことがややネックになるときがあります。更なるLookerのドキュメント充実・コミュニティ発展を祈って締めたいと思います。🙏