Amazon S3からAmazon RDS for PostgreSQLにデータをロードできるようになりました

quiver

2019.04.26

この記事は公開されてから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 を中心とした分析系システムを構築・運営しているエンジニアにとっては、非常に嬉しい機能アップデートではないかと思います。

それでは。

Amazon S3からAmazon RDS for PostgreSQLにデータをロードできるようになりました

旧方式:S3 → サーバー → RDS 方式

新方式:S3 → RDS 方式

対応バージョン

手順概要

1. RDS 向け IAM ロールの作成

Permission について

Trust Relationship について

2. RDS PostgreSQL 11 を起動

インスタンスの起動

インスタンスにIAMロールをアタッチ

接続確認

3. S3 連携のエクステンションを有効化

利用する関数について

利用するデータ型について

S3 から RDS PostgreSQL にデータをロード(CSVファイル)

S3 に CSV のサンプルデータをアップロード

ロード先テーブルの作成

S3 のデータを RDS PostgreSQL にロード

S3 から RDS PostgreSQL にデータをロード(圧縮非CSVファイル)

S3 にサンプルデータをアップロード

ロード先テーブルの作成

S3 のデータを RDS PostgreSQL にロード

S3 URI をショートカットしたい

まとめ

関連情報

イベント

EVENT【5/29（水）大阪】Alteryxの使い方から導入メリットまですべてがわかるセミナー＆ワークショップ

EVENT【5/15（水）リモート】クラスメソッドの会社説明会を開催します

EVENT【5/8リモート】クラスメソッドのフリーランスエンジニア会社説明会〜フィンテック / リテール業界案件特集〜を開催します

EVENT【5/28（火）】AWSを最大活用するための1dayカンファレンス

EVENT【5/17（金）】認証機能の開発工数削減をデモで体験！次世代認証基盤サービス『Auth0 by Okta』導入実践ウェビナー

EVENT【5/14（火）】アノテーションの中途向けオンライン会社説明会を開催します

EVENT【5/23（木）】ユースケースに学ぶAWS運用のノウハウ～可視化から統制まで～

EVENT【5/16（木）】Snowflakeを触ってみよう！初めての方向けハンズオンセミナー

EVENT【5/10（金）名古屋】クラスメソッドグループの会社説明会を開催します！

EVENT【4/25（木）リモート】Google Cloudに携わるエンジニアのキャリア～クラスメソッド会社説明会～

Amazon S3からAmazon RDS for PostgreSQLにデータをロードできるようになりました

旧方式:S3 → サーバー → RDS 方式

新方式:S3 → RDS 方式

対応バージョン

手順概要

1. RDS 向け IAM ロールの作成

Permission について

Trust Relationship について

2. RDS PostgreSQL 11 を起動

インスタンスの起動

インスタンスにIAMロールをアタッチ

接続確認

3. S3 連携のエクステンションを有効化

利用する関数について

利用するデータ型について

S3 から RDS PostgreSQL にデータをロード(CSVファイル)

S3 に CSV の サンプルデータをアップロード

ロード先テーブルの作成

S3 のデータを RDS PostgreSQL にロード

S3 から RDS PostgreSQL にデータをロード(圧縮非CSVファイル)

S3 にサンプルデータをアップロード

ロード先テーブルの作成

S3 のデータを RDS PostgreSQL にロード

S3 URI をショートカットしたい

まとめ

関連情報

イベント

EVENT【5/29（水）大阪】Alteryxの使い方から導入メリットまですべてがわかるセミナー＆ワークショップ

EVENT【5/15（水）リモート】クラスメソッドの会社説明会を開催します

EVENT【5/8リモート】クラスメソッドのフリーランスエンジニア会社説明会 〜フィンテック / リテール 業界案件特集〜 を開催します

EVENT【5/28（火）】AWSを最大活用するための1dayカンファレンス

EVENT【5/17（金）】認証機能の開発工数削減をデモで体験！次世代認証基盤サービス『Auth0 by Okta』導入実践ウェビナー

EVENT【5/14（火）】アノテーションの中途向けオンライン会社説明会を開催します

EVENT【5/23（木）】ユースケースに学ぶAWS運用のノウハウ～可視化から統制まで～

EVENT【5/16（木）】Snowflakeを触ってみよう！初めての方向けハンズオンセミナー

EVENT【5/10（金） 名古屋】クラスメソッドグループの会社説明会を開催します！

EVENT【4/25（木）リモート】Google Cloudに携わるエンジニアのキャリア～クラスメソッド会社説明会～

関連記事

Amazon Connect Customer Profilesのデータソース自動更新と更新時間変更について

VPCフローログの送信先についてまとめてた

【CDK】AWS Step FunctionsでS3のCOPY Objectを実行

Invalid principal in policyが出たら確認したい！IAMロール・IAMユーザーが削除されるとPrincipal要素のARNがプリンシパルIDに置き換わります

S3 に CSV のサンプルデータをアップロード

EVENT【5/8リモート】クラスメソッドのフリーランスエンジニア会社説明会〜フィンテック / リテール業界案件特集〜を開催します

EVENT【5/10（金）名古屋】クラスメソッドグループの会社説明会を開催します！