■ ■ やりたいこと ■ ■
PostgreSQLからRedshiftのテーブルを参照したい。
方法としては、DBLINKとpostgres_fdwというのがあるらしい。
■DBLINK
あるPostgreSQLから、別のPostgreSQLにアクセスできる。
PostgreSQL標準の機能。
リモートDBが持つテーブルにローカルライクにアクセスできるが、
・リモート表をSELECTする場合、データを全件ローカルに持ってくるためデータ転送が多くなる。
・リモートDBのテーブルや行をロックしない。整合性を担保するためには二相コミットの実装が必要。
・独特なSQL書式
など、制約はある。
■postgres_fdw
DBLINKの後継機能。
クエリはオーソドックスな書式で通用するが、結合、ソート、
LIMIT、集約などがリモートではなくローカルで行われる。
＊WHERE句によるシンプルな絞り込みはリモートで行ってくれる。
■それぞれの特徴を把握して、使い分けが必要。
■ ■ 設定 ■ ■
■Redshiftでの作業
・PostgreSQLから参照する用のテーブル作成
https://docs.aws.amazon.com/ja_jp/redshift/latest/gsg/rs-gsg-create-sample-db.html
サンプルデータはS3からLOADする。
既存のIAMロールに"AmazonS3ReadOnlyAccess"を与えたが、いろんな検証で使ってきたため、ロール名と権限内容が一致せずわかりにくい。IAMロールに限らず、セキュリティグループ名やインスタンス名も、内容と一致していない。
使い分けを考えないと。
あと、適切かつ分かりやすい名称をぱっと思いつく才能がほしい。
意外と悩むところ。
・セキュリティグループの設定
PostgreSQLからポート5349(Redshift)に接続できるよう、インバウンドの設定を追加する。
PostgreSQLのIPは、踏み台にて以下のコマンドを実行して確認。
$ nslookup
> <PostgreSQLのエンドポイント>
■PostgreSQLでの作業
・まずはRDS PostgreSQLを起動
バージョン9.5以上、Redshiftと同じAZ。
・モジュール導入
CREATE EXTENSION postgres_fdw;
CREATE EXTENSION dblink;
¥dx
・Redshiftのサーバ情報を設定
/* 外部サーバ設定 */
CREATE SERVER foreign_server
FOREIGN DATA WRAPPER postgres_fdw
OPTIONS (
host 'xx.xx.xx.xx',
port '5439',
dbname '<dbName>',
sslmode 'require');
＊hostはRedshiftのプライベートIP
/* PostgreSQLとRedshiftのユーザ情報をマッピングさせる */
CREATE USER MAPPING FOR <PostgreSQL DB Name>
SERVER foreign_server
OPTIONS (
user '<Redshift User Name>',
password '<Redshift Password>');
/* DBLINKでRedshiftのデータをSELECT */
SELECT *
FROM dblink('foreign_server',$REDSHIFT$
SELECT sellerid, sum(pricepaid) sales
FROM sales
WHERE saletime >= '2008-01-01'
AND saletime < '2008-02-01'
GROUP BY sellerid
ORDER BY sellerid DESC
$REDSHIFT$) AS t1 (sellerid int, sales decimal)
LIMIT 10;
/* postgres_fdwでRedshiftのデータをSELECT */
未実施
■ ■ わからなかったところ ■ ■
・DBLINKで参照されるテーブルは、publicスキーマに作る必要があるのか？？
テスト用のスキーマに作成したテーブルは参照できなかった。
・postgres_fdwでの参照もやってみたい。