【Looker】結合するときに考えたこと
はじめに
皆さん、今日もLooker使ってますかー？ ^^
今回は、BigQuery(以降、BQ)で複数データセットに格納されているテーブルを使って
結合する場合、LookMLをどう書くのがいい感じになるのかまとめてみました。
結合している様子
これが正解！というものは無いと思います。
もっと良い方法があればコメント頂けると嬉しいです^^
今回書きたかったこと
複数のデータセットに格納されているテーブルを参照する場合の
コネクション設定方法やLookMLの書き方について、色々と考えることがありました。
そのポイントについて解説ができたら幸いです。
最初にふと思ったことは、
BQの接続設定で指定するコネクションの単位がBQのデータセット単位なので
複数データセットの場合は、Modelファイルに複数コネクションを書くのかな？
というところでした。
Connection Settings
利用環境
| Product | version |
|---|---|
| Looker | 21.4.17 |
| BigQuery | 2021年3月15日時点 |
| Region | asia-northeast1 |
構成情報
今回の投稿で利用しているBQとLookerの環境の構成情報になります。
全体構成
BQ
1つのBQプロジェクトに2つのデータセットを作成しています。
マスター系テーブルを格納するmstと履歴系テーブルを格納するlogになります。
今回の例では、UNIONとJOINを説明するため、履歴系テーブルを2つ用意しています。
project #プロジェクト
├ mst #データセット
│ └ mst #テーブル
└ log #データセット
├ web_log_a #テーブル
└ web_log_b #テーブル
Looker
頂点にlooker_devフォルダを作成し、その直下にマスター系と履歴系のオブジェクトを格納するフォルダを作成しています。
マスター系と履歴系の各フォルダ配下には、ModelファイルとViewファイルを格納するフォルダを作成しています。
looker_dev
├ 01_master
│ ├ 01_model
│ │ └ mst.model
│ └ 02_view
│ └ mst.view
└ 02_log
├ 01_model
│ └ web_log.model
└ 02_view
├ 01_raw
│ ├ web_log_a.view
│ └ web_log_b.view
└ web_log.view
複数コネクションを書いてみた
まず、マスター用のModelファイルに下記のコネクション設定を書いてみました。
connection: "bq_mst" #1行目
connection: "bq_log" #2行目
するとThis "connection" replaces the "connection" declared on line 1
というメッセージがインフォメーションで表示されました。
コネクションの定義で記載されている通り
1つのModelファイルで定義できるコネクションは1つで
2つ目に書いたものが1つ目のコネクションよりも優先されるようです。
A model may only use one connection.
また、Code Validationで以下のModel Errorsとなります。
ここで開発 > LookMLプロジェクトの管理を確認します。
LookMLプロジェクトの管理
LookMLプロジェクトのModelのmstでConfigureを押すと、以下の画面になります。
Edit Model Configuration
すると、新規にModelファイルを作成した時にデフォルトでセットされていた
コネクションのみが許可されており、他のコネクションが許可されていませんでした。
これが原因でエラーになっていたことが分かります。
bq_logにもチェックを入れれば、エラー回避することは可能ですが
2つ目に設定したコネクションしか参照しないので、どうしてこうなっているのか不明でした。
正しいコネクションの指定方法とは
Database ConnectionでBQとの接続を設定していますが
実はデータセット単位ではなく、指定したGCPのサービスアカウントで参照可能な
データセットにアクセスすることができました。
Database Connectionで指定していたデータセットはあくまでデフォルト値とのことです。
なので、作成するコネクションは1つになり、マスター用とWebログ用のModelファイルでは
同じコネクション設定を書くのが正解でした。
そして、Viewファイルの中でテーブルを指定するViewの設定において
sql_table_nameを<BQプロジェクト名>.<データセット名>.<テーブル名>で書くのが正解になります。
view: mst {
+ sql_table_name: `project.mst.mst`
;;
(以下、省略)
view: web_log_a {
+ sql_table_name: `project.log.web_log_a`
;;
(以下、省略)
view: web_log_b {
+ sql_table_name: `project.log.web_log_b`
;;
(以下、省略)
【補足】
sql_table_nameは、Database Connectionで設定しているコネクションの
データセットを利用してView名とテーブル名が一致している場合は省略可です。
今回のような複数データセットを参照して結合するケースでは
1つのデータセットでは省略可能でコネクション設定で指定していない
もう1つのデータセットでは省略が不可となり、コードの統一性がなくなることが考えられます。
そのため、自分は省略せずにいずれのView設定においてもsql_table_nameを書くよう
規約化しています。
結合する場合のLookMLの書き方
LooKMLにおけるUNIONとJOINの書き方についても少し触れたいと思います。
UNIONの場合
2つのサイトのWebログを縦結合した場合のLookMLは下記のようになりました。
カラムはそれぞれ同じで、3つのカラムを持っているテーブルとなります。(サンプルです)
view: web_log {
extends: [
web_log_a,
web_log_b
]
sql_table_name:
(select column1,
column2,
column3
from ${web_log_a.SQL_TABLE_NAME}
union all select column1,
column2,
column3
from ${web_log_b.SQL_TABLE_NAME}
JOINの場合
縦結合したWebログテーブルに対して、マスターテーブルを横結合(Left Outer Join)しています。
今回は、Webログ用のViewファイル(Webログをベースに)でJOIN句を記述しました。
connection: "bq_log"
include: "/looker_dev/02_log/02_view/web_log.view.lkml"
include: "/looker_dev/01_master/02_view/mst.view.lkml"
explore: web_log {
join: mst {
relationship: many_to_one
type: left_outer
sql_on: ${web_log.column1} = ${mst.column1}
;;
}
}
余談ですが...
Modelファイル名はLookMLプロジェクトをまたいでも一意である必要がありますが
異なるデータセットに同じテーブル名のテーブルがあると仮定して
それらのテーブルを結合できるのか確認するため、まず同じViewファイル名を認識できるのか
下記のようにModelファイルを書いてみましたが...
connection: "bq_mst"
include: "/view/*/*.view.lkml"
explore: bq_mst {
+ view_name: test_table
}
explore: bq_log {
+ view_name: test_table
}
結論としては、当然エラーになりました。
ModelファイルでincludeされるViewファイル名が重複して存在しているよ！
と怒られるだけでした。
まとめ
さて、いかがでしたでしょうか？
やや複雑で分かりにくい表現もあったかと思いますが
改めてちゃんとLookMLの仕様を把握する良い機会となりました。
また、意外な発見も多くありました。
皆様にとっても役に立つナレッジになると幸いです^^
Discussion