More than 3 years have passed since last update.

そんなデータで大丈夫か？〜LookerをRDB接続・DWH未整備で使って困ることと対策のまとめ〜

Last updated at 2022-07-06Posted at 2022-02-14

序段

久しぶりのQiita投稿になります。
現在、某自社サービス企業でデータエンジニアをしている者です。
社内におけるKPI抽出を管理し、BIツールの基盤構築・運用を行っています。

今回、社内でBIツールをChartioからLookerに移行する作業を行いましたが、
弊社ではBigQueryでなくRDBを使い、かつDWH（データウェアハウス）が未整備という装備で挑みました。
その結果、相応の苦労を味わいながらも、幾つかの対策を講じることで、比較的高額なライセンス代にも見合うメリットを実感できるようになってきたため、その内容をまとめていきたいと思います。

本記事の対象読者: Lookerユーザーで、接続データソースをRDBにしている人。一通りLookerの流儀に慣れたが、他社の使い方を知りたい人

背景

前述の通り、弊社ではこれまでKPIの分析基盤にChartioを使っていました。
しかし運営企業がAtlassianに買収されることにより、2022年3月にサービス終了ということに。。

社内で移行先が検討された結果、Lookerに白羽の矢が立ちました。
個人的にはちょっと懸念を表明していたんですが、ビジネス・マーケティングサイドの推しもあり、導入するに至りました。

懸念の理由は、前項で説明している通りです。
弊社ではAWSのRDS(postgres)を使っていますが、Chartioの接続先データベースのテーブル群は、ローデータのレプリカが主体で、ETL（加工、統合）が十分に行われていない状態でした。RDSサーバも一つではなく複数あるため、システム全体のデータは分散・孤立しており、BIツールの分析基盤であるDWH（データウェアハウス）構築が不十分といえました。
本来なら、その構築を進めてからLooker移行を進めるべきですが、色々と兼務している作業もあるため、移行期限までにそこに取り組む時間はありませんでした。

Lookerの強み・弱み

これが、思ったよりもはっきりしておりました。

Chartioは、フランクにSQLを書いて、データを並べて取り出し、それをグラフ化やCSV保存するツールとして使うことができました。そのおかげで、分析用のデータ基盤が弱いままでも、SQL上の工夫によってKPI分析を行うことができました。

一方、Lookerは、良くも悪くもBIツールとして扱われることを求めてきます。
基本的な使い方は、画面上の項目を選ぶこと = 分析軸を選ぶこととなり、この状態をSQL上で見ると、選択した分析軸でGROUP BYされています。
つまり、ある軸で集計して見れるように、あらかじめ整理してデータを持っておくことが前提のソフトウェア設計になっています。
ビューワーが、自分自身の画面操作で、色々な軸でデータを見れるようにできるのがLookerの強みの一つですので、多くの集計軸と従属するデータを持った、スタースキーマ等の専用のデータ構造で使用することが重要になるようです。

弊社のように、DWHの整わない環境で使用する場合、以下のような点がLookerの弱点として現れてきます。

クエリのパフォーマンス改善に工夫を必要とする
複数データソースからの結果マージ機能に制限がある
ETL・永続化機能がイマイチに感じる

Lookerは前述のGUIによる、分析者にとって自由度の高い分析ができること、外部アプリとの連携、MLモデルとGit連携によるチームでのコード管理といった機能的強みを持つのですが、それらを活かすためには、DWHを構築するのが前提といっても良いです。デファクトスタンダードは更に、「Google Big Query上で作ること」のように思えます。Googleに買収されてる背景から、無理ならないことですが...。
それらの、Lookerから暗に要求されることを満たせない場合、本来は他の、ETL機能の強力なBIツールを使う方が自然な気はします（例えばインメモリDB内でサイロ化データをマージできる、AWS QuickSightなどを）
ただ、やり方によってはローデータ・サイロ化データが多い環境でも、課題を少なくしてLookerの運用が可能という実感です。本記事は、そのために工夫したことをまとめていますので、同じような悩みを持つ運用者の方に、参考にして頂ければ幸いです。

対策

### GROUP BY除去の方法を学び、SQLそのままの結果が得られるようにする

前述の通り、LookerはSQLを書いたそのままの結果が得られるとは限りません。
むしろSQLを書くこと自体が、「派生テーブルの作成」となり、できるだけ控えることが公式で推奨されています。
SQLを書くとWith句としてサブクエリになり、そこから選択項目でGROPU BYされる動きになります。

選択項目で集計されない結果が欲しいとき
GROUP BYが余計となり、パフォーマンス懸念があるとき

これらの場合は、Lookerが付与するGROUP BYを除去する方法を学ぶ必要があります。

追記
現在（2022年7月）振り返ると、この項目を強調しすぎてしまったと思います。
ユニークな列をディメンジョンに含められており、かつ十分に結果行数を絞り込めている場合は、
RDBであっても、GROUP BY指定によって問題（集計の誤りやパフォーマンス低下）が出ることがありませんでした。

そのことは可能なのですが、仕組みがそう単純ではなく、考える要素がいくつかあります。
Lookerコミニュティで説明されていることになりますが、

viewの項目を全てdimensionで定義すること（measureを定義しない）
viewの項目の一つにprimary keyを付与すること
結合したとき、primary keyの一意性が守られていること

こうすることで、GROUP BYが除かれます。
しかし、3.が守れないことによってGROUP BYがついてしまうことがあるので、
若干の違和感を醸しますが、viewの項目を全てmeasureにしてしまうことでも除去が可能です。

その他、ExploreのMLモデルに、「cancel_grouping_fields」という属性があるのですが
これを指定したdimension項目がビューワーに選択されると、全てのGROUP BYが除去されます。
便利ではあるのですが、項目が選択されない場合は除去されないこと、dimension項目結合のリレーションシップにone_to_many指定があると動作しないといった注意すべき点があります。

また、テーブルの全列をビューワーが自由に選んで見れるようにしたいケースでは、cancel_grouping_fieldsに記述する量も増え、コード管理的にもあまりよろしくありません。上記の公式ドキュメントにある通り、行ユニークになるdimensionによってGROUP BYされる状況での、無駄なGROUP BYコストの削減といった用途に使うべきかと思います。

まとめると

選択項目で集計されない結果が欲しいとき

→ primary key指定+ measureを作らない or 全てをmeasureにする

GROUP BYの有無による結果が同じで、パフォーマンス懸念があるとき

→ cancel_grouping_fields指定

となります。

こうしたGROUP BYの挙動の理解は、自由にGROUP BYしてよいデータ = 分析用のDWHが作れていない環境では必要になる認識です。この理解に加えて、ExploreのMLモデルの「fileds」を使い、GROUP BYしてもよい項目だけをdimensionとして見せることで、ETL前のローデータでも分析に活用することができます。

継承を使った派生テーブルVIEWで事前絞り込みし、パフォーマンスの課題を解決する

前項で、GROUP BYを除去するかどうか選べるようになり、社内のSQL資産をそのまま使うことができるとが分かりました。しかしこの場合、SQL派生テーブルとしての記述になり、クエリの結果は、WITH句によるサブクエリとして作成されます。

派生テーブル（サブクエリ）の結果に対してWHERE句、JOIN句、GROUP BY句にRDBのインデックスは利用できないので、派生テーブルの中で適切な絞り込みを行わないと、参照データのサイズによっては、パフォーマンス問題を引き起こすおそれがあります。
Lookerではキャッシュが使えるとはいえ、弊社の環境では、マスタDBサーバからのレプリケーション遅延の抑止を優先するため、クエリに高負荷は許容できない状態でした。

そこで、弊社では行サイズの大きいテーブルを継承した、期間などの条件で絞り込めるようにしたViewを/common/narrowed/といったディレクトリを掘って、そこに作成しました。

include: "/views/base/sales.view.lkml"

view:narrowed_sales {
    extends: [sales]

    derived_table: {
        sql:
        SELECT * FROM sales
        WHERE
        {% if sales.created_date_time._is_filtered or sales.created_date_time._is_filtered %}
            sales.created_date_time >= {% date_start sales.created_date_time %}
            and sales.created_date_time < {% date_end sales.created_date_time %}
        {% endif %}
        ;;
    }
}

適当な例ではありますが、何となく雰囲気は伝わるでしょうか？
こうしておくと、Lookerの任意の派生テーブル内に、以下のように埋め込むだけで
salesテーブルの日付による期間指定・データの絞り込みが完了します。

include: "/common/narrowed/narrowed_sales.view.lkml"

derived_table: {
    sql:
    ...
    ${narrowed_sales.SQL_TABLE_NAME} AS sales
    ...
    ;;
}

一旦この仕組みを作ってしまえば、LookerのMLコードの再利用性は強力なので、
サイズが巨大なテーブルを参照するとき、毎回おまじないのように書けます。

### Liquidパラメーターを結合View間で共通化する

先ほどの例では、あくまでsalesテーブルの絞り込みしかできません。
複雑なクエリになると、GUI上の日付指定ひとつで、別テーブルの絞り込みも同時にしたい気持ちになります。
その対応として、期間での絞り込み条件を専用のViewで管理させるという方法が思いつきます。
こんな感じに、GUI上で一つにしたい→

そこで/common/parameters/といったディレクトリを掘って、フィルタ・パラメータ管理用のViewを作成しました。

view: period_parameters {
  label: "期間指定"
  derived_table: {
    sql:
        select target_period from 
        generate_series(
　　　　　  DATE({% date_start period_parameters.start_period %}),/*タイムゾーン設定に注意*/
　　　　　  DATE({% date_end period_parameters.end_period %}),
        '1 day') as target_date
    ;;
  }
　dimension_group: doril {
    type: time
    timeframes: [
      date,
      week,
      month,
    ]
    sql: ${TABLE}."target_period" ;;
  }
  parameter: start_period {
    label: "From"
    type: date
  }
  parameter: end_period {
    label: "To"
    type: date
  }
　...
}

このviewのLiquidパラメーターを、他の派生テーブルVIEWで参照することができます。
例えば先ほどの例のView、narrowed_salesの絞り込み箇所では、そのView内に定義したLiquidパラメーターに依存していましたが

{% if sales.created_date_time._is_filtered or sales.created_date_time._is_filtered %}
  sales.created_date_time >= {% date_start sales.created_date_time %}
  and sales.created_date_time < {% date_end sales.created_date_time %}
{% endif %}

次のように置き換えることができ、他のViewと共通のLiquidパラメーターを見るようにすることができます。

{% if period_parameters._is_filtered or period_parameters._is_filtered %}
  sales.created_date_time >= {% date_start period_parameters.created_date_time %}
  and sales.created_date_time < {% date_end period_parameters.created_date_time %}
{% endif %}

これは、Explore上で作成した期間ViewをJOINをしてやると上手くいきます。


explore: sales {

  #（パターン１）ダミーのJOINでも動かせる
  join: period_parameters {
    relationship: many_to_one
    sql_on:
    1=0
    ;;
    type: left_outer
  }

　#（パターン２）期間viewのdimensionで集計したいときは、実際にJOINする
  join: period_parameters {
    relationship: many_to_one
    sql_on:
      ${period_parameters.doril_date} = ${sales.created_date}
    ;;
    type: inner
  }
}

※なお、同じExplore内でJOINさせないとLookerはパラメータを認識できないため、
他のExploreの結果を元にしているネイティブ派生テーブルでは、このテクニックは使えません

前項の「絞り込み」でLookerに接続したRDBの抱えるパフォーマンス問題の大部分を解消することができ、
「パラメーター共通化」によって、その絞り込みの記述を再利用できます。

### 複数データソースからのマージを、データベースリンクで補完する

Lookerは異なるサーバにあるデータソースでのExplore結果を結合できるMergeResultという機能を備えています。
ただし残念ながら、この結合は画面表示されるデータ間だけで行われるらしく、Looker全体の制限であるExploreごとに画面表示は5000行までという内容により、大量データの結合はできない仕様になっています。また、結合方法はLEFT JOINだけです。
「何遍言わせるの？あらかじめデータを統合してよ」というLookerの声が聞こえてきそうですが、この記事の主旨は、逆張りの方法を紹介することですので...。それ以外の方法で、弱点を補完できる方法を探します。

弊社が試した補完の手段としては、Postgresのデータベースリンク(Foreign Data Wrapper)の利用でした。実装にあたってはこちらの記事などを参考にさせて頂きました。

Foreign Data Wrapper（fdw）では、ローカル側からリモート側のテーブルを同じデータベースにあるテーブルのように参照することができます。またプッシュダウンという機能を持ち、通信を行う前にWHERE句の条件でリモート側で絞り込みを行うことができます。

完全に課題解決までには至らず、複雑なSQLだとプッシュダウンされないことや、結果行の通信量が数百万行などになると応答が返らないこともあるのですが、MergeResultに比べて多くの結果行を処理してくれます。

### PDT機能を応用してETL作業を行う

何度も書いておりますが、データをETLし、BIツールと相性のいい構造にすることは、Lookerを使う上で必須になってきます。実はLookerでは、PDTというクエリ結果の永続化機能を使うことで、データをETLし、DWH用のデータ構築を行うことができます。本記事では詳しい説明は割愛し、その使用感のみ書かせて頂きます。

PDTで保存するデータは、デフォルトだとdatagroupのスケジュール毎に、全データをDELETE・INSERTする動きをします。DWHとして使うには更新データのみ積んでいきたいところですが、増分PDTという設定を加えることで、それが実現できます。
しかし使いづらい点としては、viewの内容を少しでも編集するとテーブル名に含まれるハッシュ値が変わり、別テーブルとして生成されてしまいます。publish_as_db_viewというパラメータを設定することにより、参照用のビューを作成し、常に新しいテーブルを見ることができるため、大きな問題は出ないのですが、

新しい名前でPDTテーブルが生成されたとき、過去のテーブルが残ってしまう
参照用のビューの名前にもハッシュ名が含まれ、管理しにくい
スキーマの権限的に、外部システム等からビューを参照しにくい

一つ設定するまで手間がかかるのと、こうした細かい部分での課題が残るため、PDTをDWH用のテーブルとして運用していくには、少々躊躇うものがありました。
そこで代替手段として、SQLをスケジュール実行する機能だけ使わせてもらうことにしました。RDBと接続して運用する上で、実はこちらの方が、かなり応用範囲が広いように思います。
以下が例となります。

view: src_order_details {
  derived_table: {
    label: "受注詳細"
    sql_trigger_value: SELECT CURRENT_DATE ;;
      create_process: {
        sql_step:
　　　　　　#公開スキーマにテーブルを作りにいく
          CREATE TABLE IF NOT EXISTS public.order_details  (
　　　　　　　　　 order_id uuid,
                user_id uuid,
                item_id uuid,
                status  int2 NOT NULL,
                amount int8 DEFAULT 0,
                created_at timestamp(0) without time zone not null,
                updated_at timestamp(0) without time zone,
                PRIMARY KEY (order_id)
            );
        ;;
　　　　　#VACCUMメンテ実行
        sql_step: 
          VACUUM ANALYZE public.order_details;;

        #クエリの結果を登録、または更新する
        sql_step:
          INSERT INTO public.order_details
          (
            SELECT FROM items inner join users ...
          ) ON CONFLICT (order_id) do update set
            user_id = EXCLUDED.user_id,
            item_id = EXCLUDED.item_id,
            ....
        ;;

このような形で、create_process〜sql_stepを使えば、公開領域のスキーマに自由なテーブル名でETL結果を保存できますし、SQLステートメントの記述も自由で、上記のVACUUMのようなメンテ処理を挟むこともできます。WITH RECURSIVE句や、SET句など、通常Looker上では発行できないステートメントも実行できます。
CREATE TABLE IF NOT EXISTS, ON CONFLICT句を使うことで、エラーも起こりません。

ただし、RDBがレプリカDBの場合はこれも行えず...。弊社では、一部の書き込み可能なRDSでの利用に止まりました。中々、儘ならないものです。

まとめ

今回はLookerが重視していない点にフォーカスし、補う方法をご紹介しました。記事の主旨上、Lookerの良い点についてあまり触れられませんでしたが、現在はChartioに比較しても満足しています。特にMLモデルによるコード管理の部分で、SQLコードの再利用性を高め、属人性も減らして管理できるようになったことは大きなメリットに感じております。

今後はLookerが勧告するところに従い、より分析に適したDWH構築を進めていく予定です。
Chartioは直感的に使いやすい、親切なUIを備えており、使う人に合わせてくれるタイプのBIツールでしたが、Lookerは一貫した美学をもとに使う人が合わせることを求めてきて、あえて擬人化して言うなら気位が高く、性格キツめの美人というキャラです。そんなLookerちゃんともっと仲良くなりたいと思っております。

後からの設計見直しがしづらいツールとも感じており、一旦MLモデルを作って社内に公開してしまうと、後からの大きな変更は既に作成されたLookやダッシュボードに影響が出てしまうため、「こういう便利な使い方があることに気づいたけど、使えない」ということはよくあります。今回の記事によるナレッジを通して、そうした後悔を多少でも減らすヒントになることができれば幸いです。

You get articles that match your needs
You can efficiently read back useful information
You can use dark theme

What you can do with signing up