使用Docker，在PostgreSQL中尝试通过使用FDW与MySQL和Redis进行JOIN操作

2 年 ago
韵, 科
2 minutes

经过/经历

最近，我正在对数据仓库的建设和选择进行各种调研。
在经过时间的发展，一个成长起来的组织通常会存在各种不同的数据库。
这些数据库之间存在着特性、擅长与不擅长以及位置上的差异（OLAP，OLTP，KVS，文档数据库，云端，本地…），每种差异都具有合理的意义。

然而对于数据分析师来说，将这些各个数据库中存在的数据视为一个数据库进行分析会更加方便。

将不同的数据库视为一个数据库的方法

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 的 Dockerfile，用于安装 redis_fdw。我将它们组合在一起创建了一个镜像。

如果您希望查看剩余所有的源代码，请参考 kitsuyui/testing-fdw。

    • Redis

 

    • MySQL

 

    PostgreSQL

使用这样形式的 SQL，将这 3 个表 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

样本_redis、样本_mysql、样本_postgres分别是存在于Redis、MySQL和PostgreSQL上的表格。（对于Redis来说，严格来说不是表格）

用法

设置了通过startup.sh启动。
https://github.com/kitsuyui/testing-fdw/blob/master/fdwpostgres.sql 是在PostgreSQL中发出的查询。

创建服务器

注册外部服务器定义。本次例子中注册了Redis和MySQL作为外部服务器。

为…服务器创建用户映射。

这是提供登录到已在上述处定义的外部服务器所需的信息。

这次是以公开形式进行的，但为了每个用户做好区分，需要将之改为 FOR <用户名> 。
选项将分别成为每个服务器所需的登录信息。

创建外部表

必须编写外部服务器上的表定义，这是无法遗漏的遗憾。

这个东西能用得多自然？

优点

    • 性能面で特に違和感はなかった。

今回はすべて 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 がある。

bannerAds