Looker×BigQuery パーティションテーブルの対応

この記事は Looker Advent Calendar 2021 の16日目の記事です。

##経緯

普段業務で使っているデータは、時系列系のテーブルで大量なデータのため、BigQueryでパーティションフィルタを必須にしているものが大半です。
BigQueryでパーティションフィルタを必須にしていると、Lookerで分析するにあたり、不便なことがあったので、その解決策を書こうと思います。



##パーティションフィルタの必須化とは
そもそも、BigQueryのパーティションフィルタの必須化とは何かについて、紹介します。

以下の図のように、パーティションフィルタが必須となっているテーブルを指します。
（例では、`timestamp`でをパーティションキーとなっています。）

<img src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2326670/3a20a5c8-585a-e9f5-ae6e-3d0f5351aff1.png" width=50%>

このテーブルの場合、SQLのWHERE句にパーティションキー（`timestamp`）を書かなければ、エラーとなってしまいます。
<img src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2326670/fc18ecbd-1e17-19dc-26c4-89ab5cfe57d8.png" width=100%>


##Lookerではどうなるのか（不便となる理由）
Lookerでも同じように、フィルタで`timestamp`を設定しなければエラーとなってしまいます。
<img src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2326670/4066f743-6bcc-8311-0862-a9c5f460e276.png" width=100%>
Lookerは、BigQueryよりも多くのユーザーが利用することになるので、エラー文だけでは、理解ができないユーザーがいるかもしれません。
また、物理名（英語）を論理名（日本語）に変更していれば、追加で調査が必要で、少しめんどくさいです。

##対策：always_filterを活用する
ユーザーが、フィルタを設定しなければならないことを意識させないように、`always_filter`を用いてフィルタを強制にします。詳細は、[公式ドキュメント](https://docs.looker.com/reference/explore-params/always_filter)を参照ください。
今回の例では、以下のようにLookMLを記載します。

```s
#hogehoge.model

explore: devicesensorvalue {
  label: "センサー情報"
  always_filter: {
    filters: [devicesensorvalue.timestamp_date: "7 days"]
  }
}
```

Exploreで操作する際に、フィルタが必須となっており、ユーザーは削除することができません。日付などの期間は変更可能です。
このように設定しておくことで、無駄なエラーを減らすことが可能です。

<img src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2326670/11ae9456-e7cc-4c55-6bc1-9bf69fa2aa94.png" width=100%>





##応用編：複数のViewを結合している場合
パーティション必須化のテーブル同士を結合した場合、同じ内容でフィルタしたいのに、それぞれのViewごとにフィルタを設定する必要が出てきます。

以降の例では、`devicesensorvalue.view（センサー情報）`と`deviceinfo.view（機器情報）`を結合しています。
<img src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2326670/d71c8449-dee3-30f5-2fee-78b718f8bec7.png" width=100%>


##対策：各Viewに同じフィルタを設定するようにLookMLを書く

次のLookMLを、結合するどちらかのviewに記載します。今回は、`devicesensorvalue.view（センサー情報）`に記載しています。

```s
#devicesensorvalue.view

  filter: date_filter {
    view_label: "共通フィルタ"
    description: "共通のフィルタです"
    label:"計測時間"
    type: date
  }
```

modelファイルのexploreには、以下のように記載します。
`always_filter`　`sql_always_where`を用います。

```s
#hogehoge.model

explore: devicesensorvalue {
  label: "センサー情報"
  view_label: "センサー情報"

  always_filter: {
    filters: [devicesensorvalue.date_filter: "7 days"]
  }

  sql_always_where:{% condition devicesensorvalue.date_filter %} ${devicesensorvalue.timestamp_raw} {% endcondition %}
  AND {% condition devicesensorvalue.date_filter %} ${deviceinfo.timestamp_raw} {% endcondition %};;

  join: deviceinfo{
    view_label: "機器情報"
    sql_on: ${devicesensorvalue."共通となるキー"} = ${deviceinfo."共通となるキー"} ;;
    type: left_outer
    relationship: many_to_one
    }
  }
```


以下のように、共通のフィルタを設定することができました。
`sql_always_where`の[テンプレートフィルタ](https://docs.looker.com/ja/data-modeling/learning-lookml/templated-filters)部分は、xxxx_**raw**でないとダメなようです。
さらに結合するviewを増やしたい場合は、結合したいViewと、`sql_always_where`に追加していくだけです。
<img src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2326670/0002a988-0dca-1c14-d132-22f33124d0a5.png" width=100%>

以下の記事に複数のViewに共通のフィルタを設定する方法が記載されているので、大変参考になるかと思います。
https://community.looker.com/%E3%82%B3%E3%83%A9%E3%83%A0-103/%E4%B8%80%E3%81%A4%E3%81%AE%E3%83%95%E3%82%A3%E3%83%AB%E3%82%BF%E3%83%BC%E3%82%92%E8%A4%87%E6%95%B0%E3%81%AEview%E3%81%AB%E9%81%A9%E7%94%A8%E3%81%99%E3%82%8B-19809

##最後に
Lookerは何でもできる便利なツールだからこそ、エンドユーザーのことを考えると、事前に対応しておくべきことは多々ありそうです。
他にも、多くのユーザーに使ってもらうためのTipsを書いていこうと思います。