こんにちは、donutです。
今回は、Lookerを利用する際に便利な機能である派生テーブルについてご紹介したいと思います。
派生テーブルは、データベース内にまだ存在していない新しいテーブルを作成するための手法です。
LookMLで作成しているテーブルとはまた別に、Looker上でテーブルを作成できる機能が
派生テーブルになります。
主な用途としては以下の2つになります。
①テーブル同士を結合させてもできないような集計をする場合
→集計の例として、「顧客ごとの集計情報」を顧客マスタへ挿入したり、
RFM分析用に顧客ランクを実装するため、一度集計の上でランク付けを行う等があります。②パフォーマンス向上の場合
→テーブルから取得した粒度でダッシュボードに使用しない場合、
細かい粒度でSQLを実行してもリソースが勿体ない、あるいはデータ量が多いと集計が
重くなる...ということになりかねないため、事前に派生テーブルとして集計しておく、
といった用途があります。
※Qlikでいうと中間テーブルを作成したり、TableauではLOD分析に近しい機能となります。※Lookerでの使用例※
＜参考＞
◆Lookerの派生テーブル(Google Cloudより)
派生テーブルの代表的なものとして、以下の2種類があります。
①一時的な派生テーブル(標準派生テーブル)
→クエリの結果が消えてしまうテーブルです。
※一時的な派生テーブルの場合、実行(グラフ表示やフィルターを
切り替えたらクエリが実行される)を行う度にクエリが実行されてしまうため、
注意が必要です。
②永続的な派生テーブル
→クエリの結果をデータベースに保存できるテーブルです。
Lookerでは永続的な派生テーブルを「PDT(Persistent Derived Tables)」と
呼んでいます。
永続的な派生テーブルを使うことで、データマートをLookerで作成することもできます。
また、クエリもLookMLでバージョン管理ができるため、とても便利です。
今回はSnowflakeと接続して作成を行いたいと思います。サンプルとしてオフィスオアシスの
利用データを使用します。
なお、派生テーブルの作成方法として「①SQL派生テーブル」と「②ネイティブ派生テーブル」の
2パターンがあり、主な特徴は以下となります。
順を追って見ていきましょう。
①Lookerの[開発]→[SQL Runner]を選択します。
②クエリを記載していきます。
→今回は「ユーザーの注文あたりの購入数」と、「購入金額集計用」のクエリを作成します。
SELECT
SEND_TIME ,
"USER" ,
COUNT(*) AS order_cnt ,
SUM(PRICE) AS total_sales
FROM
"SLACK"."DM_PURCHASE"
GROUP BY
1,2
ORDER BY
3 DESC
LIMIT
10 ;
③結果に問題がなければProjectへ追加します。
→右上の歯車マークをクリックし、[Add to Project]を選択します。
④対象のプロジェクトとView名を記載します。(セキュリティの観点から一部マスクを行っています)
あわせて、Limit句の削除を行っておきます。
⑤[Add to Project]の下にある[Get Derived Table LookML]をクリックすると、
ポップアップ形式でLookMLの内容を確認することができます。
⑥作成されたViewを確認してみると、通常通り作成したViewと比較して
[derived_table]というパラメータで作成されていることが確認できます。
→こちらのSQLで出力された結果をベースに、対象のViewが作成されるようになります。
SQL派生テーブルの作成手順は以上となります。
ネイティブ派生テーブルは、既に定義されたExploreを利用して作成できる派生テーブルです。
既にmodelファイルまで用意している場合はこちらの方法でも作成することができます。
その他の特徴としては以下となります。
引き続き作成してみたいと思います。
①Exploreから作成したmodelファイルを選択します。
→SQL派生テーブルと同様に、「ユーザーの注文あたりの購入数」と「購入金額集計用」
クエリを作成していきます。
※もし、クエリ内でWhere句を記載している場合は、フィルターを設定してください。
②右上の歯車マークをクリックして[LookMLを取得する]をクリックします。
③[派生テーブル]を選択し、表示されたLookMLを保存します。
④Viewを新規作成しコピーしたLookMLを貼り付け、includeの部分をコメントアウトしてください。 ※View名は任意で設定します。
※ネイティブ派生テーブル内で使用されているパラメータの説明としては以下を参照ください。
ネイティブ派生テーブルの作成手順は以上となります。
PDTについては、前述した手順で作成した派生テーブルの設定を変更するだけで作成が可能です。
主に必要な設定としては以下となります。
①更新タイミング
→データベース側にデータを保存するためのタイミング設定を行います。
主に以下の3パターンが存在します。
(1)datagroup_trigger(設定されたキャッシュポリシーを元に更新)
(2)sql_trigger_value(記載したSQLクエリの結果が変更されたら更新)
※定期的に実行する必要がある場合は(2)が良いと思います。
(3)persist_for(期間で更新)②データベース上でIndexを設定する
→MysqlやPosgreなどIndexを設定するデータベースには必要となりますが、
Snowflakeの場合は不要です。
なお、Redshiftの場合はインデックスが存在しないため、
ソートキーと分散キーの登録が必要です。
今回は[persist_for(期間で更新)]を設定して実際にSnowflake上でデータが作成されるか
確認してみたいと思います。
※なお、接続先の設定としてPDTの設定が必要です。(一部マスクを行っています)
①前述した内容で作成した派生テーブルのViewに[persist_for]パラメータを追加します。
以上でPDTの準備が完了しました。
上記Viewを使用したmodelファイルを作成し、Explore上で実行してみます。
Snowflake側も確認したところ、作成されていたことを確認できました。
今回は派生テーブルの作成についてご紹介しました。
派生テーブルはLookerの分析度向上に貢献してくれる便利な機能でもあるため、
試してみてはいかがでしょうか。
なお、弊社ではLookerに関する資料を公開中です。
もしご興味がありましたら、下記ページよりダウンロードが可能ですのであわせてご参照ください。
＜参考＞
◆Looker概要資料