この記事は公開されてから1年以上経過しています。情報が古い可能性がありますので、ご注意ください。
Amazon S3から
- Amazon RDS for PostgreSQL
- Amazon Aurora with PostgreSQL
に直接データをロードできるようになったのでご紹介します。
他のDBエンジンについては、以下の記事を参照ください。
旧方式:S3 → サーバー → RDS 方式
S3 から RDS にデータをロードする場合、以前は S3 のデータを一度 EC2 などのサーバーに転送し、サーバー上から COPY コマンドを利用して RDS に取り込む必要がありました。
新方式:S3 → RDS 方式
今回の機能追加により、サーバーを介することなく、 S3 から直接データをロードできるようになりました。
対応バージョン
本機能は
- Amazon RDS for PostgreSQL 11.1 以上
- Amazon Aurora with PostgreSQL
でのみ利用可能です。
Amazon RDS for PostgreSQL の 9系・10系といった古いエンジンでは利用できません。
手順概要
RDS for PostgreSQL と Aurora PostgreSQL で操作方法は実質的に同じため、以下では RDS for PostgreSQL を例に手順を紹介します。
- RDS 向け IAM ロールの作成
- RDS PostgreSQL 11 を起動
- S3 連携のエクステンションを有効化
- S3 から RDS PostgreSQL にデータをロード
1. RDS 向け IAM ロールの作成
RDS インスタンスに IAM ロールをアタッチし、 S3 を操作できるようにします。 そのためのロールを作成します。
Permission について
S3 を参照できる IAM ポリシーを用意します。
{
"Version": "2012-10-17",
"Statement": [
{
"Sid": "s3integration",
"Action": [
"s3:GetObject",
"s3:ListBucket",
"s3:PutObject"
],
"Effect": "Allow",
"Resource": [
"arn:aws:s3:::your-s3-bucket-arn",
"arn:aws:s3:::your-s3-bucket-arn/*"
]
}
]
}
マネージドポリシーを利用してまるっと参照権限を与えたい場合、AmazonS3ReadOnlyAccess を利用してください。
動作検証では、このマネージドポリシーを利用しました。
Trust Relationship について
assume するのは RDS のため、Trusted entities は RDS です。
{
"Version": "2012-10-17",
"Statement": [
{
"Sid": "",
"Effect": "Allow",
"Principal": {
"Service": "rds.amazonaws.com"
},
"Action": "sts:AssumeRole"
}
]
}
2. RDS PostgreSQL 11 を起動
インスタンスの起動
RDS PostgreSQL インスタンスを起動します。
本機能はバージョン 11.1 以降にしか対応していないため、DB engine version に「11.1-R1」を選択します。
インスタンスにIAMロールをアタッチ
起動完了後、インスタンス詳細ページの Manage IAM roles エリアで
- Add IAM roles to this instance に作成した IAM ロール
- Feature に s3import
を選択し、Add role します。
接続確認
RDS インスタンスに接続できることを確認します。
$ psql -h mydbinstance.XXX.us-west-2.rds.amazonaws.com -U USERNAME DBNAME
3. S3 連携のエクステンションを有効化
S3 と RDS PostgreSQL 間のデータ連携は aws_s3 エクステンション(とこれが依存する aws_commons )を介して行います。 このエクステンションは、デフォルトでは無効化されているため、明示的に有効化します。
# エクステンションのデフォルトでの有効状態を確認
dbname=> select * FROM pg_available_extensions where name like 'aws%';
name | default_version | installed_version | comment
-------------+-----------------+-------------------+---------------------------------------------
aws_s3 | 1.0 | | AWS S3 extension for importing data from S3
aws_commons | 1.0 | | Common data types across AWS services
(2 rows)
# エクステンションを有効化
dbname=> CREATE EXTENSION aws_s3 CASCADE;
NOTICE: installing required extension "aws_commons"
CREATE EXTENSION
# 有効後の状態を確認
dbname=> \dx
List of installed extensions
Name | Version | Schema | Description
-------------+---------+------------+---------------------------------------------
aws_commons | 1.0 | public | Common data types across AWS services
aws_s3 | 1.0 | public | AWS S3 extension for importing data from S3
plpgsql | 1.0 | pg_catalog | PL/pgSQL procedural language
(3 rows)
利用する関数について
S3 連携では、以下3関数を利用します。
- S3 から RDS にロード(
aws_s3.table_import_from_s3
) - S3 の URI を定義(
aws_commons.create_s3_uri
) - IAM クレデンシャルを定義 (
aws_commons.create_aws_credentials
) (RDS インスタンスが STS ベースで一時クレデンシャルを取得し、 S3 を操作する限りは不要)
利用するデータ型について
aws_commons
エクステンションはAWS連携に必要なデータタイプを定義しています。
具体的には、以下です。
- AWS クレデンシャル型(
_aws_credentials_1
) - S3 URI型(
_s3_uri_1
)
dbname=> \d aws_commons.*;
Composite type "aws_commons._aws_credentials_1"
Column | Type | Modifiers
---------------+------+-----------
access_key | text |
secret_key | text |
session_token | text |
Composite type "aws_commons._s3_uri_1"
Column | Type | Modifiers
-----------+------+-----------
bucket | text |
file_path | text |
region | text |
S3 から RDS PostgreSQL にデータをロード(CSVファイル)
S3 にあるシンプルな CSV ファイルを RDS PostgresQL に取り込んでみます。
S3 に CSV の サンプルデータをアップロード
CSV データ sample.csv を作成し、S3 にアップロードします。
$ cat sample.csv
1,foo
2,bar
3,baz
$ aws s3 cp sample.csv s3://BUCKET-NAME/
ロード先テーブルの作成
S3 ファイルをロードするテーブルを用意します。
dbname=> create table test(a int, b text);
CREATE TABLE
S3 のデータを RDS PostgreSQL にロード
psql 上で aws_s3.table_import_from_s3
を呼び出します。。
dbname=> SELECT aws_s3.table_import_from_s3(
'test',
'',
'(format csv)',
'BUCKET-NAME',
'sample.csv',
'us-west-2'
);
table_import_from_s3
-----------------------------------------------------------------------
3 rows imported into relation "test" from file sample.csv of 21 bytes
(1 row)
dbname=> select * from test;
a | b
---+------
1 | foo
2 | bar
3 | baz
(3 rows)
sample.csv の内容が確かに取り込まれています。
aws_s3.table_import_from_s3
関数の引数は以下の通りです。
dbname=> SELECT aws_s3.table_import_from_s3(
'テーブル名',
'カラムリスト', -- 空文字('')の場合は、テーブルのカラムと一致
'PostgreSQL COPYの引数・フォーマット',
'S3バケット名',
'S3キー',
'S3リージョン'
);
S3 から RDS PostgreSQL にデータをロード(圧縮非CSVファイル)
次に、セパレーターが |
で、gzip 圧縮されたファイルを RDS PostgresQL に取り込んでみます。
S3 にサンプルデータをアップロード
基本的に、先程と同じですが、注意点が一点だけあります。
gzip 圧縮されている場合、S3オブジェクトのメタデータを
- Key: Content-Encoding
- Value: gzip
で登録する必要があります。
$ cat sample.csv
1|foo
2|bar
3|baz
$ gzip sample.csv
# S3メタデータで content-encoding : gzip を設定
$ aws s3 cp sample.csv.gz s3://BUCKET-NAME/ --content-encoding gzip
upload: ./sample.csv.gz to s3://BUCKET-NAME/sample.csv.gz
$ aws s3api head-object --bucket BUCKET-NAME --key sample.csv.gz
{
"AcceptRanges": "bytes",
"LastModified": "Thu, 25 Apr 2019 09:09:36 GMT",
"ContentLength": 47,
"ETag": "\"0bb041b3cb08674dd97849559876eefe\"",
"ContentEncoding": "gzip",
"ContentType": "application/octet-stream",
"Metadata": {}
}
ロード先テーブルの作成
S3 ファイルをロードするテーブルを用意します。
dbname=> create table test_gzip(a int, b text);
Query OK, 0 rows affected (0.05 sec)
S3 のデータを RDS PostgreSQL にロード
psql 上で aws_s3.table_import_from_s3
を呼び出します。。
デリミターが ,
から |
にかわっているため、PostgreSQL COPY
の引数を 'DELIMITER ''|'''
に変更します。
dbname=> SELECT aws_s3.table_import_from_s3(
'test_gzip',
'',
'DELIMITER ''|''',
'BUCKET-NAME',
'sample.csv.gz',
'us-west-2'
);
table_import_from_s3
-------------------------------------------------------------------------------
3 rows imported into relation "test_gzip" from file sample.csv.gz of 47 bytes
(1 row)
dbname=> select * from test_gzip;
a | b
---+-----
1 | foo
2 | bar
3 | baz
(3 rows)
sample.csv.gz の内容が確かに取り込まれています。
S3 URI をショートカットしたい
上記サンプルでは、S3 URI を毎回アドホックに指定しました。
aws_commons.create_s3_uri
関数を使うと、URI に名前をつけて定義し、使い回すことが可能です。
gset
を利用し、SELECT
結果を psql 変数に格納して利用します。
dbname=> SELECT aws_commons.create_s3_uri(
'BUCKET-NAME',
'sample.csv',
'us-west-2'
) AS s3_uri \gset
dbname=> \echo :s3_uri
(BUCKET-NAME,sample.csv,us-west-2)
dbname=> SELECT aws_s3.table_import_from_s3(
'test',
'',
'(format csv)',
:'s3_uri'
);
table_import_from_s3
-----------------------------------------------------------------------
3 rows imported into relation "test" from file sample.csv of 18 bytes
(1 row)
まとめ
S3 のデータを RDS PostgreSQL にロードする方法を紹介しました。
データベースと S3 の連携は
- PostgreSQL ベースの Redshift は最初期から
- Aurora MySQL は数年前から
- RDS Oracle は今年2月下旬から
利用可能でした。RDS PostgreSQL でもようやく利用できるようになりました。
現時点では、Aurora PostgreSQL と RDS for PostgreSQL 11.1 以上でしか動作しないため、すぐに本番投入とはいかないこともあるとは思いますが、PostgreSQL を中心とした分析系システムを構築・運営しているエンジニアにとっては、非常に嬉しい機能アップデートではないかと思います。
それでは。
関連情報
- Importing Amazon S3 Data into an RDS PostgreSQL DB Instance - Amazon Relational Database Service
- Migrating Data to Amazon Aurora with PostgreSQL Compatibility - Amazon Aurora
- Amazon RDS for PostgreSQL Now Supports Data Import from Amazon S3@2019/04/24
- Amazon Aurora with PostgreSQL Compatibility Supports Data Import from Amazon S3@2019/06/20