1
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

More than 3 years have passed since last update.

AuroraからRedshiftデータを参照する方法

Posted at

#はじめに
Aurora(PostgreSQL)からRedshiftのデータを参照する方法を整理。

#データ連携方法
①dblinkのみで連携
②postgres_fdwのみで連携
③postgres_fdwとdblinkで連携
※postgres_fdwとdblinkの違いは参考URLの記事を参照。

#環境
【Redshift】
インスタンス:dc2.large × 1ノード
※Redshiftのバージョンは2021/4/10時点での最新バージョンを使用
【Aurora】
PostgreSQL12.4

Redshiftにテーブル「tbl_red1」を作成し、当該テーブルをAuroraから参照。

#①dblinkのみで連携
###事前準備

dba=> #拡張モジュールをインストール
dba=> create extension dblink;
CREATE EXTENSION
dba=> \dx
                                 List of installed extensions
  Name   | Version |   Schema   |                         Description
---------+---------+------------+--------------------------------------------------------------
 dblink  | 1.2     | public     | connect to other PostgreSQL databases from within a database
 plpgsql | 1.0     | pg_catalog | PL/pgSQL procedural language

###データ取得

dba=> SELECT * FROM dblink('host=xxxx port=5439 dbname=dbr user=reduser1 password=xxxx',$REDSHIFT$SELECT col1, col2 FROM public.tbl_red1$REDSHIFT$) AS t1 (col1 varchar, col2 varchar);
 col1 | col2
------+------
 111  | AAA
 222  | BBB
(2 rows)

※dblink_connectを使った実行も可能。(下記参照)
PostgreSQLガイドdblink
PostgreSQLガイドdblink_connect

#②postgres_fdwのみで連携
###事前準備

dba=> #拡張モジュールをインストール
dba=> create extension postgres_fdw;
CREATE EXTENSION
dba=> \dx
                                    List of installed extensions
     Name     | Version |   Schema   |                         Description
--------------+---------+------------+--------------------------------------------------------------
 plpgsql      | 1.0     | pg_catalog | PL/pgSQL procedural language
 postgres_fdw | 1.0     | public     | foreign-data wrapper for remote PostgreSQL servers

dba=> #外部サーバ情報を作成
dba=> CREATE SERVER srv_redshift FOREIGN DATA WRAPPER postgres_fdw OPTIONS (host 'xxxx', port '5439', dbname 'dbr', sslmode 'require');
CREATE SERVER
dba=> \des
            List of foreign servers
     Name     |  Owner   | Foreign-data wrapper
--------------+----------+----------------------
 srv_redshift | auruser1 | postgres_fdw

dba=> #ユーザマッピング情報を作成
dba=> CREATE USER MAPPING FOR auruser1 SERVER srv_redshift OPTIONS (user 'reduser1', password 'xxxx');
CREATE USER MAPPING
dba=> \deu
  List of user mappings
    Server    | User name
--------------+-----------
 srv_redshift | auruser1

###データ取得

dba=> CREATE FOREIGN TABLE tbl_red1 (col1 char(3),col2 varchar(3)) SERVER srv_redshift;
CREATE FOREIGN TABLE
dba=> select * from tbl_red1;
 col1 | col2
------+------
 111  | AAA
 222  | BBB
(2 rows)

#③postgres_fdwとdblinkで連携
###事前準備
上記①と②の事前準備を実施

###データ取得

dba=> SELECT * FROM dblink('srv_redshift',$REDSHIFT$SELECT col1, col2 FROM public.tbl_red1$REDSHIFT$) AS t1 (col1 varchar, col2 varchar);
 col1 | col2
------+------
 111  | AAA
 222  | BBB
(2 rows)

#参考URL
RedshiftとAurora(PostgreSQL)でdblinkを貼る方法
外部データとの連携 ~FDWで様々なデータソースとつなぐ~
PostgreSQL dblink & postgres_fdw
PostgreSQL9.3 新機能を検証してみた Vol.2

1
0
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
1
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?