RedshiftとAurora(PostgreSQL)でdblinkを貼る方法

Redshiftには、マテリアライズド・ビューを作成する方法は用意されていません。Redshiftはデータウェアハウス向けに設計されたデータベースであるが故、以下の様な理由により、その機能を用意していないのだと考えています。

データウェアハウスは、恒常的にデータ挿入/更新は発生せず、主に夜間処理にて１日１回更新される
マテリアライズド・ビューのような、実データが射影されたVIEWを利用したいのであれば、夜間処理の更新時に Create Table ... As Select ... のように、もう１つテーブルを作ってしまえば補える

一方でRedshiftには、同時実行クエリ数の上限は、１５個推奨という問題もあります。WEB上のダッシュボードサイトにKPIを表示させ、大多数のユーザに参照してもらう、といった利用を想定した場合に、あっさり上限数を超過してしまうという問題があります。

そういった時に、今回のPostgreSQLとdblinkを貼り、PostgerSQL側でマテリアライズド・ビューを作成するという方法は、有効に働きます。

AWSでの紹介サイトはこちら。

https://aws.amazon.com/jp/blogs/big-data/join-amazon-redshift-and-amazon-rds-postgresql-with-dblink/

環境情報

今回試した環境情報です。

Redshift
- 1.0.327
PostgerSQL
- Aurora PostgreSQL 9.6.8

どちらも、同一サブネット上に作成して、両者間を通信できるセキュリティグループ設定をしています。

dblinkの作成

作成方法は上記AWSのサイトでも、紹介されていますが、PostgreSQL側の公式サイトには、より詳しい情報が記載されているので、目を通しておくと良いと思います。

https://www.postgresql.jp/document/9.6/html/postgres-fdw.html

(1) 必要拡張モジュールのインストール

dblinkを貼る際に必要となる、拡張モジュールを読み込みます。AuroraのPostgreSQLに接続して、以下のSQLを実行します。SQL文は全てAuroraマスターユーザで実行しましたが、Postger的に必要な権限が付与されていれば、一般ユーザであっても実行可能と思っています。

test=> create extension postgres_fdw;
CREATE EXTENSION
test=> create extension dblink;
CREATE EXTENSION
test=> \dx
                                      インストール済みの拡張一覧
     名前     | バージョン |  スキーマ  |                             説明

--------------+------------+------------+--------------------------------------------------------------
 dblink       | 1.2        | public     | connect to other PostgreSQL databases from within a database
 plpgsql      | 1.0        | pg_catalog | PL/pgSQL procedural language
 postgres_fdw | 1.0        | public     | foreign-data wrapper for remote PostgreSQL servers
(3 行)

create extension 文は、拡張機能をインストールするためのコマンドで、dblinkとpostgres_fdwがpublicスキーマにインストールされていることが分かります。

https://www.postgresql.jp/document/9.6/html/sql-createextension.html

dblink と postgres_fdw の違いは、公式マニュアルには、以下のように記載されています。

実質上、本モジュール(postgres_fdw ※筆者追記)の提供する機能は以前のdblinkモジュールが提供する機能と重複していますが、postgres_fdwはリモートのテーブルにアクセスするためにより透過的で標準に準拠した構文を利用できるほか、多くの場合においてより良い性能を得る事ができます。

postgres_fdw だけ読み込めば良いんじゃね感ありますが、AWSサイトでは、 dblink も読み込んで、こちらで処理を書いているため、今回の方法もその手順に則ります。

(2) 外部サーバ・オブジェクトの作成

引き続きPostgreSQL側で、以下のSQLを実行して、外部サーバの定義を作成します。Optionで指定している host 、 port 、 dbname は、接続先のRedshiftの情報を入れてあげます。

test=> CREATE SERVER redshift_test_db
   FOREIGN DATA WRAPPER postgres_fdw
   OPTIONS (host 'test-cluster.XXXXXXXXXX.ap-northeast-1.redshift.amazonaws.com', port '5439', dbname 'test', sslmode 'require');
CREATE SERVER
test=> \des
               外部サーバー一覧
       名前       | 所有者 | 外部データラッパ
------------------+--------+------------------
 redshift_test_db | root   | postgres_fdw
(1 行)

https://www.postgresql.jp/document/9.6/html/sql-createserver.html

(3) ユーザマッピングの定義

PostgreSQL上のユーザと、Redshift上のユーザの対応付けをします。今回はAurora Postgreのマスターユーザと、Redshiftのマスターユーザを紐づけています。（どっちもrootって名前にしちゃったから分かりづらいけど、optionで指定している方がRedshift側のユーザ定義です...）

test=> CREATE USER MAPPING FOR root
   SERVER redshift_test_db
   OPTIONS (user 'root', password 'Passw0rd');
CREATE USER MAPPING
test=> \deu
    List of user mappings
      Server      | User name
------------------+-----------
 redshift_test_db | root
(1 row)

対象ユーザはマスターユーザでなくとも、参照元テーブルのSELECT権限をもっているユーザなら問題ありません。

https://www.postgresql.jp/document/9.6/html/sql-createusermapping.html

Redshift側にテーブル作成

Redshift側で参照元となるテーブルを作成しておき、データを挿入します。

test=# CREATE TABLE public.voiceloid
   (
     voice_id VARCHAR(3),
     name VARCHAR(20),
     lover_id VARCHAR(3),
     PRIMARY KEY(voice_id)
   )
   BACKUP YES
   DISTSTYLE EVEN
   SORTKEY(voice_id);
CREATE TABLE
test=# INSERT INTO public.voiceloid values('001','結月ゆかり','002');
test=# INSERT INTO public.voiceloid values('002','弦巻マキ','001');
test=# COMMIT;

マテリアライズド・ビューの作成

PostgreSQL側で以下のSQLを実行して、マテビューを作成します。

test=> CREATE MATERIALIZED VIEW v_voiceloid_lover AS
  SELECT *
  FROM dblink('redshift_test_db',$REDSHIFT$
    SELECT B.name, A.name
    FROM public.voiceloid A
    JOIN public.voiceloid B
    ON A.voice_id = B.lover_id
  $REDSHIFT$) AS t1 (name varchar, lover varchar);
test=> \dm
                        リレーション一覧
 スキーマ |       名前        |           型           | 所有者
----------+-------------------+------------------------+--------
 public   | v_voiceloid_lover | マテリアライズドビュー | root
(1 行)

SQL文内の $REDSHIFT$ という文字列が意味不明なのですが、公式サイトによると、以下の通りただの心遣いみたいです。

The SQL query is passed in as a string between double dollar quotes ($REDSHIFT$). Using dollar quotes makes reading and writing the query easier.

The double dollar quotes are labeled REDSHIFT to help highlight the SQL that will be sent to Amazon Redshift.

SQL文末のAS句にて、マテビューで作成される結果セットに対してカラム名とデータ型を定義してますね。

では、作成したマテビューに対して、SELECTしてみます。

test=> SELECT * FROM v_voiceloid_lover;
    name    |   lover
------------+------------
 結月ゆかり | 弦巻マキ
 弦巻マキ   | 結月ゆかり
(2 行)

ちゃんとレコードが見られますね。

データの更新

Redshift側に新規データを挿入してみて、マテリアライズド・ビューに反映されるかどうか確認します。Redshift側で以下レコードを挿入します。

test=# INSERT INTO public.voiceloid VALUES('003','紲星あかり','001');
test=# COMMIT;

PostgreSQL側でマテリアライズド・ビュー更新の処理を実行した後、SELECT文を実行します。以下はその更新処理に関する公式ページ。

https://www.postgresql.jp/document/9.6/html/sql-refreshmaterializedview.html

test=> REFRESH MATERIALIZED VIEW v_voiceloid_lover;
REFRESH MATERIALIZED VIEW
test=> SELECT * FROM v_voiceloid_lover;
    name    |   lover
------------+------------
 紲星あかり | 結月ゆかり
 弦巻マキ   | 結月ゆかり
 結月ゆかり | 弦巻マキ
(3 行)

挿入したレコードも含めて、結果が返ってきてますね。

Postgre側にマテビューを作成することができるので、そもそもRedshiftへ接続してのテーブルコピー処理 CREATE TABLE ... AS SELECT ... も実行できます。ただREFRESHコマンドによりデータが更新できるため、マテビューを利用した方が、メンテナンスが容易になると思います。