我流LookMLテクニック集 (coolな方法求む)

Looker Advent Calendar2021の15日目の記事です。
https://qiita.com/advent-calendar/2021/looker

Lookerを使い始めて半年が経ち、やりたいことはできるようになってきましたが…我流なのが気になっています。

集合知のようなものを知りたいのですが、onboarding期間が終わっていて質問できないし、LookerCommunityにあるのかもしれませんが英語を探しきれない…。

ので、ちょっとここに悩み事を書いてみようと思います。
皆様の「こうしたらいいよ」をお待ちしております！

我流テクニック集

10個くらいあるんですが、一般化しやすい5つを選んでみました。

1.分析関数を使うときは derived_tableにする

通常のSQL関数を使うときには、各dimensionのsqlに TIMESTAMP_MILLIS(${TABLE}.received_milllis) などと記載するのですが… 分析関数を使うときだけはエラーになるので sql_table_name から derived_table に変更しています。

下記のような、「sql_table_nameやっぱやめたview」がだんだんと増えていっています。*に加えているから既存のdimensionなどに影響がなく、お手軽。

view: payment {
  derived_table: {
    sql:
      SELECT
      *,
      row_number() over (partition by shop_id,user_id order by created) as repeat_count
      FROM payment
    ;;
  }
  # sql_table_name: `payment`
}

「テーブルそのままのviewはそのままで残しつつ、新たに"ネイティブ派生テーブル"を作り、そこに derived_column として記載する」という手もあるのですが、view増やしたいわけじゃないんだよな…。

2.区分値があるものはCASE文を使う

status 0:無効 1:有効 みたいな、int型に対して意味が割り振られているもの。
0,1ではLookerを使う人がわからないので、全て下記のようにCASE文を使っています。また、filterで使うときに文字列だとメンテナンス性が怖いので、「statusをhiddenで作りつつ stringようにdimensionを作る」としています。

これって普通なのか？ みんなこうしているの…？ もっとcoolな方法があればおしえてください！

dimension: status {
    hidden: yes
    type: number
    sql: ${TABLE}.status ;;
}

dimension: status_str {
    label: "ステータス"
    sql: CASE ${status}
            WHEN 0 THEN "無効"
            WHEN 1 THEN "有効"
            ELSE "不明"
          END;;
  }

3.リンクテーブルのあるmany_to_manyはすべてhiddenにして消す

通常は マスタA - リンクテーブル - マスタB という作りで many_to_many を実装していると思います。
（lookerのreferenceにある many_to_many ( https://docs.looker.com/ja/reference/explore-params/relationship#many_to_many ) はあまり見ないような…）

→ 実際のexploreのjoin条件は 「one_to_many」と「many_to_one」になるはず。

explore: As {

  join: A_Bs {
    sql_on: ${A.id} = ${A_Bs.a_id} ;;
    type: left_outer
    relationship: one_to_many
  }

  join: Bs {
    sql_on: ${A_Bs.b_id} = ${Bs.id} ;;
    type: left_outer
    relationship: many_to_one
   }
}

が、リンクテーブル A_Bs は非ITユーザーにとっては意味不明なviewなので、Exploreの画面で出したくない…
という場合、A_Bsのdimensionをすべて hidden:yes にすると出なくなったのでそうしています。

labelを使ってグルーピングを変えるテクニックと同様、こちらもハック的で気に入っています。

4.「ステータス履歴」に分かれている場合にderived_tableを使った

「A:注文table」 1-n 「B:注文ステータス履歴table」というDB構造になっていて、「現在のステータスはBのcreatedが最も新しいものである」とき。Aに「ステータス」dimensionをつけたくなります。
derived_tableを使ってしまったのですが、coolな方法を募集中です。

view: As {
  derived_table: {
    sql:
    with sub as (
     select distinct 
        B.order_id,
        first_value(B.status) over (partition by B.order_id order by B.created desc)
     from B)
    select
        A.*,
        sub.status
    from
        A
        left outer join sub on sub.order_id =  A.id
    ;;
 }
}

なお…
Bのほうのviewに何か仕掛けをしておいて、Aのviewから呼び出す形式にする ようなアイデアはある気がするのですが、
「leaf側のviewに依存するdimension」を作るのは嫌い、という理由で 見向きもしませんでした。
(BがいらないExploreにはBをjoinさせたくない)

5.多言語用マスタのときもderived_tableを使った

shops テーブルがあるが、多言語を表現するために店舗名は shop_i18n などという別テーブルにある場合。
shop_i18n のviewを単純につなげると、めちゃくちゃ使いづらい。

ユーザーから見たら 「なんでshopsにショップ名が無いんだよ」となります
また、ほしいのはショップ名だけではなく説明文とかも欲しいのでその分viewが増えます
なお、labelを「shops」にすると contentsをshopsの中に入れられますが、「ccontents? はて？」となってしまいます…

結局、 shops のviewを derived_tableにして、つなげてしまいました

view: shops {
  derived_table: {
    sql:
      with a as (select shop_id,content as shop_name from shop_i18n
      where lang = "ja" and key = "shop_name")
      select 
        s.*,
        a.shop_name 
      from shops s 
        left outer join a on a.shop_id =  s.id
    ;;
  }

これも、頑張れば「shopsのviewからshop_i18nを呼び出す」形にできる気がするんですが…
やっぱり4と同じで、modelファイル上でjoinが必要になるわExploreのUI上でshop_i18nが見えちゃうわ…

おわりに

書いてて思いましたが、derived_tableを使っちゃった懺悔 ってのばかりですね
（「Lookerでderived_tableを使ったらダメ！DRY原則！」という自己制約をかけちゃってるのかも）

地獄に落ちないcoolな方法とか、「それをやっちゃうとこういうとき困るよ」みたいなアドバイスがあればぜひください！

あと、似たような不安を抱えながらlookML書いている方がいたら記事書いてほしいです、仲間がいると安心できます。

ではでは。