Zenn
🔗

【Looker】結合するときに考えたこと

2021/05/12に公開
Google Cloud
データ分析
BigQuery
join
Looker
tech

はじめに

皆さん、今日も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ファイルを格納するフォルダを作成しています。

LookMLのFileBrowser
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ファイルに下記のコネクション設定を書いてみました。

mst.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プロジェクト名>.<データセット名>.<テーブル名>で書くのが正解になります。

mst.view
view: mst {
+  sql_table_name: `project.mst.mst`
    ;;
(以下、省略)
web_log_a.view
view: web_log_a {
+  sql_table_name: `project.log.web_log_a`
    ;;
(以下、省略)
web_log_b.view
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つのカラムを持っているテーブルとなります。(サンプルです)

web_log.view
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句を記述しました。

web_log.model
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ファイルを書いてみましたが...

mst.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