経緯
最近、私はデータウェアハウスの構築や選定について色々と調査している。
時間を経て成長してきた組織では、年月を経て様々なデータベースが存在することが普通である。
それらのデータベースには特性や得手不得手や所在の差異 (OLAP, OLTP, KVS, ドキュメントストア, クラウド, オンプレミス...) があり、それぞれの差異には合理的な意味がある。
一方で、データ分析者にとっては、それら各データベースに存在するデータをあたかも 1 つのデータベースとみなして分析できるほうが都合が良い。
異なるデータベースを 1 つのデータベースとして扱う方法
- Embulk を活用して、データウェアハウスとなる DB にデータを定期的にインポートする。
- Presto を活用して、複数のデータベースに分散クエリを走らせ Presto Server 上で結合処理する
- PostgreSQL の FDW をつかう ← この記事はこれ
- 他にも、やり方はいろいろとありそう。
PostgreSQL の FDW とは
PostgreSQL の FDW (Foreign Data Wrapper) とは、
名前の通り、異なるデータソースにあるデータを PostgreSQL が読み取り・書き取りするための機能のこと。
やってみた
docker-compose で気軽に試してみた。
Dockerfile はこんな感じに。
FROM quay.io/aptible/postgresql:9.5-contrib
ENV DEBIAN_FRONTEND=noninteractive
RUN apt-get update \
&& apt-get -y install git gcc make libhiredis-dev \
&& mkdir -p /inst/ \
&& cd /inst \
&& git clone https://github.com/pg-redis-fdw/redis_fdw.git --branch REL9_5_STABLE --single-branch \
&& cd /inst/redis_fdw \
&& PATH=/usr/local/postgresql/bin/:$PATH make USE_PGXS=1 \
&& PATH=/usr/local/postgresql/bin/:$PATH make USE_PGXS=1 install \
&& apt-get purge -y --auto-remove git gcc make \
&& rm -Rf /inst
quay.io/aptible/postgresql:9.5-contrib という tds_fdw や mysql_fdw が含まれたイメージと、
rungeict/postgres-redis という redis_fdw をインストールする Dockerfile があったので、それらを組み合わせて作った。
残りすべてのソースを見たい場合は kitsuyui/testing-fdw を参照。
- Redis
- MySQL
- PostgreSQL
の 3 つのテーブルを、こんな感じの SQL で JOIN してみると、特に問題なく動作した。
SELECT *
FROM sample_redis AS SR
INNER JOIN sample_mysql AS SM
ON SR.val::int = SM.id
INNER JOIN sample_postgres AS SP
ON SR.key = SP.id
sample_redis, sample_mysql, sample_postgres はそれぞれ Redis, MySQL, PostgreSQL 上に存在しているテーブルである。 (Redis の場合は正確にはテーブルでないが)
使い方
startup.sh で立ち上がるようにしてある。
https://github.com/kitsuyui/testing-fdw/blob/master/fdwpostgres.sql が PostgreSQL で発行するクエリである。
CREATE SERVER
外部サーバの定義を登録する。今回でいうと Redis と MySQL を外部サーバとして登録した。
CREATE USER MAPPING FOR ... SERVER ...
↑ で定義した外部サーバにログインするための情報をあたえるもの。
今回は FOR PUBLIC としているが、ユーザごとにちゃんと分けるには FOR <ユーザ名> にする。
オプションはそれぞれのサーバごとに必要なログイン情報になる。
CREATE FOREIGN TABLE
外部サーバにあるテーブル定義を書く。これは残念ながら省略できない。
どれくらい違和感なく使えるか？
メリット
- 性能面で特に違和感はなかった。
- 今回はすべて Docker ホスト内の通信で完結しているが、これがよりレイテンシが大きい環境下でどれくらい性能が低下するのかは検証できていない。
- 普通に JOIN はできる。
- ビュー、マテリアライズドビュー、ファンクションなど、通常の PostgreSQL の文脈に載せることができる
- マテリアライズド・ビューを作成してしまえば、性能は PostgreSQL の通常のテーブルに扱える(はず、たぶん)。
デメリット
- 外部テーブルの定義を書く必要がある (必須)。 Presto のように catalog に追加すればあとはよしなに、というわけではない。
その他
残念ながら Docker 版の SQL Server は Mac 上で気軽に試すことができないので上記のリポジトリには含んでいない。
が、特に問題なく tds_fdw の動作をすることは検証済み。
参考
- F.31. postgres_fdw ← 公式リファレンス。当然この記事よりも細かく記載してある。
- PostgreSQL FDW を作ってSQLでログ検索してみた ← FDW を知ったきっかけとなったブログ記事。
- https://quay.io/repository/aptible/postgresql ← ベースにした PostgreSQL の Docker イメージ。 SQL Server や PL/v8, PL/Python などの Extension も含んでいる。
- https://hub.docker.com/r/rungeict/postgres-redis/~/dockerfile/ ← redis_fdw の追加にあたって参考にさせていただいた。
- PostgreSQL 楽ちん FDW Foreign Table 作成 ← まだ試していないが、このようなやり方を利用すれば比較的テーブルの定義は楽にできるかもしれない。
- https://wiki.postgresql.org/wiki/Foreign_data_wrappers ← ほかにもいろいろな FDW がある。