[Tableau]PostgreSQLのマテリアライズドビューを使用する

はじめに

みなさんこんにちは、yokatsukiです。今回のお題は、PostgreSQLのマテリアライズドビューをTableau Desktopから使用する方法について、ちょっとした注意点があったのでお伝えします。

その前にちょっと前置きを…。

マテリアライズドビューとは

マテリアライズドビューとは、PostgreSQL 9.3から実装されたデータを持つビューです。詳細は以下のSlideshareのスライドが詳しいのでご覧下さい。

• Postgre sql9.3新機能紹介(Slideshare)

マテリアライズドビューを使用するメリット

マテリアライズドビューは、通常のビューと違いデータを持つビューなので、元テーブルへの検索負荷を減らす事ができます。

同様の仕組みは手作業でサマリテーブルを作成することで実現できますが、マテリアライズドビューの場合は、リフレッシュ操作(SQL文は後述)だけでデータを更新できるので、メンテナンスが楽です。

マテリアライズドビュー使用上の注意点

ただし、現状のPostgreSQLのマテリアライズドビューは、リフレッシュの最中、ロックによる待ちが発生する事が知られています。これは9.4の新機能で改善しています。

• 「もうMATERIALIZED VIEWの挙動でやきもきしない」 9.4での設定・運用の改善 (3/4)

マテリアライズドビューはTableau Desktopから使えない!?

さて、ここからが本題です。

Tableau Desktopで検索速度を上げる為の基本中の基本は、検索対象の母集団を減らせすことです。当たり前ですね。この為に、データベース側でマテリアライズドビューを使用するアイデアが出てくる訳ですが、残念ながらTableau Desktopではマテリアライズドビューがメニューに現れません。

具体的にどういうことか、実証します。PostgreSQL 9.4.1上のマテリアライズドビューにTableau Desktop 9.0.0でアクセスする形で確認します。

テーブルおよびマテリアライズドビューは、本家PostgreSQL 9.4.0文書の38.3. マテリアライズドビュー記載のINVOICEと、SALES_SUMMARYを使用します。

テーブルと、マテリアライズドビューの作成

クライアントから、以下SQL文を実行して、テーブルを作成します。

CREATE TABLE invoice (
    invoice_no    integer        PRIMARY KEY,
    seller_no     integer,       -- 販売員のID
    invoice_date  date,          -- 販売日
    invoice_amt   numeric(13,2)  -- 販売量
);

テーブルの型に合う適当なデータをExcel等で作成して、データのロードも行っておきます。

\COPY invoice FROM 'invoice.csv' WITH csv header

次に、INVOICEの販売量を販売員、販売日毎に集計するマテリアライズドビュー(ついでにマテリアライズドビュー用インデックス)を作成します。

CREATE MATERIALIZED VIEW sales_summary AS
  SELECT
      seller_no,
      invoice_date,
      sum(invoice_amt)::numeric(13,2) as sales_amt
    FROM invoice
    WHERE invoice_date < CURRENT_DATE
    GROUP BY
      seller_no,
      invoice_date
    ORDER BY
      seller_no,
      invoice_date;

CREATE UNIQUE INDEX sales_summary_seller
  ON sales_summary (seller_no, invoice_date);  

念の為、マテリアライズドビューをリフレッシュしておきます。

REFRESH MATERIALIZED VIEW sales_summary;

これでマテリアライズドビューが完成しました。こちらをTableau Desktopから参照すると…。

INVOICEテーブルと共に表示されることを期待していたマテリアライズドビューSALES_SUMMARYが表示されません。

解決法

Tableau Desktopからマテリアライズドビューを使用する方法として、以下2点を紹介します。

カスタムSQLを使用する

Talbeau Desktopのデータソースの設定画面には、"新しいカスタム SQL"のリンクがあります。これを使用して、直接マテリアライズドビューを指定する方法があります。

• 利点：マテリアライズドビューを直接呼び出せるので、不要なオーバーヘッドが無い
• 欠点：事前にマテリアライズドビューの名前を知っておく必要がある

マテリアライズドビューを参照するビューを作成する

上記実験で確認した通り、Tableau Desktopはデータソースとしてマテリアライズドビューを参照できません。しかし通常のビューは参照できるので、マテリアライズドビューを参照するビューを作成することで対応する、というアイデアです。

CREATE VIEW sales_summary_view AS
  SELECT *
    FROM sales_summary;

再びTableau Desktopのデータソースを確認すると、ビューを参照することで、マテリアライズドビューが利用できるようになります。

• 利点：GUIで参照できる形で公開できる(ユーザに優しい)
• 欠点：ビューを経由するので、SQL処理がひとつ余計に掛かる

まとめ

PostgreSQLのマテリアライズドビューは、そのままではTableau Desktopから参照できないということで、いくつかの対応方法を検討しました。

他にも方法があると思いますが、今後Tableau Desktopでマテリアライズドビューが直接参照できるようになったらありがたいなと思います。それでは、また。