Lookerの派生テーブルを使ってみた
投稿者：Akihiko Ito
Looker Advent Calendar 2019 – Qiitaの18日目の記事です。
今回はLookerの魅力のひとつである派生テーブルについてご紹介したいと思います。
この派生テーブルは一般的なBI製品にはなかなかないユニークな機能で、単純にグラフを参照したりそのデータをExcelでダウンロードするといったシンプルな利用だけではないLookerの魅力的な機能の1つかなと思っています。
派生テーブルとは
まず派生テーブル（derived table）とは何かですが、簡単に説明するとデータベース内にまだ存在していない新たなテーブルを作成できる機能です。
もう少し詳細をお伝えすると、
- Lookerのデータモデリング言語であるLookMLで定義します。
- 他のテーブルやビューのように参照したり結合することが可能です。
- 一時的なテーブル（仮想）、永続的なテーブル（データベースの物理）の2種類があります。
次にどのような時に使用するかですが、例えばバスケット分析を行うためにいくつかのテーブルから発注や製品情報などのデータを結合・集計する必要がある場合や、複雑なクエリロジックを書く際に中間テーブルを使用したい時などに活用することで柔軟にデータを取り扱うことができます。
またクエリのパフォーマンスを向上させる重要な役割を担うこともあります。
2つの派生テーブル
上でも少し触れましたが、派生テーブルには下記の2つのタイプがあります。
この2つの派生テーブルについて詳細を見ていこうと思います。
■ 一時的な派生テーブル
- クエリ実行時に仮想的に生成されるテーブルでありデータベースには作成されません。
- 取得すべき内容がキャッシュ内に残っていればその結果を使用します。
■ 永続的な派生テーブル
- 実際にデータベース内に物理テーブルとして作成されるので、イメージとしてはデータベースのマテリアライズドビューが近いです。
BigQueryなどマテリアライズドビューの機能がないDBにとって非常に重宝する機能だと思います。 - 結合や集計された結果を物理テーブルで保持するのでクエリ時間とデータベースへの負荷が低減されます。
必要な事前設定（永続的）
ではLookerで実装していきたいと思いますが、永続的な派生テーブルはデータベース内に物理テーブルを作成するので管理メニューから下記2つの設定が必要です。（管理者が1度実施すればOK）
- Persistent Derived Tables のチェックを入れる
- 派生テーブルを作成するスキーマを指定する
設定画面は下記です。今回はスキーマ looker_test を使用します。
ちなみに上記を未設定で実行してみたら下記エラーが出力されました。（そりゃそうだ。）
※ 参照：[Lookerをデータベースに接続]
一時的な派生テーブル
まず、一時的な派生テーブルからです。
SQL Runnerで書いたSQLから派生テーブルを作成することも可能ですが、（SQL派生テーブル） LookMLで定義したディメンジョンやメジャーの項目を再利用でき、メンテナンス性や可読性の観点からネイティブ派生テーブルを使用して作成することをオススメします。
作成手順はとても簡単で下記です。
- Exploreから取得したい結果のクエリを実行する。
- Exploreのギアボタンをクリックして、
Get Derived Table LookMLを選択する。 - 表示されるコードをコピーする。
- LookMLで新規ビューを作成しコードをペーストする。
作成したLookMLの画面は下記です。
作成した一時的な派生テーブルを使用してExploreでレポート（Look）を実行し生成されたSQLを見てみます。
この状態では物理テーブルは作成されておらず、With句を使用して副問い合わせをしているのが確認できます。
Lookerではこのように生成されたSQLを必要に応じて確認できるので、結果がブラックボックスにならず非常に良いですね。これはとても気に入っている機能の1つです。
永続的な派生テーブル
次は永続的な派生テーブルで、今回は persist_for パラメーターを追加し永続的な派生テーブルにします。このパラメータでは派生テーブルを削除するまでの格納期間を設定することが可能です。
その他のパラメータは派生テーブルの使用に詳細な説明があります。
変更したLookMLの画面は下記です。
こちらも作成した永続的な派生テーブルを使用してExploreでレポート（Look）を実行し生成されたSQLを見てみます。
先ほどとは異なりWith句を使用せずに物理テーブルに対して直接Selectしているのが分かるかと思います。
このように用途に応じて2つの派生テーブルを簡単に使い分けることが可能です。
またデータベース（BigQuery）側を見ると、物理テーブルが作成されていることが確認できます。
まとめ
派生テーブルを利用すると、LookMLで定義したディメンジョンやメジャーの項目を再利用してデータベース内にまだ存在していない新たなテーブルを作成できます。これらは中間テーブルとしても活用できるため、より柔軟にデータを取り扱うことが可能となります。
またデータベース内の物理テーブルとして作成することもできるため、クエリのパフォーマンス向上やデータベースの負荷軽減としても活用することができますね。
ぜひご活用ください。
弊社では１月３０日(木)に「Lookerで実現する次世代BI」と題しセミナーを行います。
BigQuery・Redshift・Treasure DataなどのCloud DBをご利用もしくは、
導入をご検討中の皆様のご参加を心よりお待ち申し上げております。
