コーヒーが好きな木谷映見です。
今回は、Amazon Redshift Serverless をプライベートサブネットに作成して S3 バケットからデータを COPY できるように設定します。
- 背景
- 構成図
- S3 バケットの作成
- EC2 インスタンスに psql をインストールして Redshift Serverless に接続する
- S3 ゲートウェイ型 VPC エンドポイントの作成
- 拡張された VPC のルーティングをオンにする
- S3 バケットからデータを COPY する
- 余談：S3 インターフェイス型 VPC エンドポイントを使えるのか？
- 参考
背景
Redshift Serverless はデフォルトの設定を利用すると、Amazon VPC 内のパブリックサブネット上に 3 分程度で構築することができます。詳細は サーバーワークスエンジニアブログ - 「30 分で試せる！Amazon Redshift Serverless データ分析ハンズオン」のご紹介 をご参照ください。
ただ、実際の業務情報を配置して分析するとなると、Redshift Serverless はプライベートサブネットに置く構成になることが多いのではないかと思います。
今回は、プライベートサブネットに配置した Redshift Serverless に、Amazon S3 からデータを COPY する構成を試します。
構成図
東京リージョンの VPC 内プライベートサブネットに Redshift Serverless を作成します。S3 バケットからデータをコピーするために、Redshift Serverless から COPY を実行します。
Redshift Serverless に接続して操作を行うためのクライアントとして、パブリックサブネットに EC2 インスタンスを 1 台作成し、セッションマネージャーで手元の端末から接続できるようにしておきます。
今回 S3 バケットから COPY してくるデータとして、Redshift 向けに提供されている サンプルデータベース を使用します。詳細はリンク先をご参照ください。
S3 バケットの作成
マネジメントコンソールからサクッとデフォルト設定で作成します。
| 設定項目 | パラメータ | 備考 |
|---|---|---|
| バケット名 | 任意の名前 | ユニークな一意の名前 |
| AWS リージョン | アジアパシフィック（東京） | |
| オブジェクト所有者 | ACL 無効 | |
| ブロックパブリックアクセス設定 | パブリックアクセスをすべて ブロックにチェック | |
| バケットのバージョニング | 無効 | |
| デフォルトの暗号化 | 無効にする |
サンプルデータ格納
作成した S3 バケットに サンプルデータベース のデータを格納します。
Redshift 入門ガイドの ステップ 4: Amazon S3 から Amazon Redshift にデータをロードする のページの下部に、tickitdb.zip というリンクがあるので、クリックしてサンプルデータの zip をダウンロードします。
こちらのリンクからもダウンロードできます。
クリックするとすぐに zip ファイルがダウンロードされます。
tickitdb.zip を展開し、tickitdb フォルダごと S3 バケットにアップロードしてください。
EC2 インスタンスに psql をインストールして Redshift Serverless に接続する
EC2 インスタンスから Redshift Serverless への接続するまでの設定は、以下ブログをご参照ください。
blog.serverworks.co.jp
上記ブログでは Redshift Serverless に付与する IAM ロールに強めの権限（AmazonRedshiftAllCommandsFullAccess）をアタッチしているのですが、今回は特定の S3 バケットにだけ通信できるよう、以下のような権限を設定したカスタム管理ポリシーをアタッチします。
{
"Version": "2012-10-17",
"Statement": [
{
"Action": [
"s3:GetObject",
"s3:GetBucketAcl",
"s3:GetBucketCors",
"s3:GetEncryptionConfiguration",
"s3:GetBucketLocation",
"s3:ListBucket",
"s3:ListAllMyBuckets",
"s3:ListMultipartUploadParts",
"s3:ListBucketMultipartUploads",
"s3:PutObject",
"s3:PutBucketAcl",
"s3:PutBucketCors",
"s3:DeleteObject",
"s3:AbortMultipartUpload",
"s3:CreateBucket"
],
"Effect": "Allow",
"Resource": [
"arn:aws:s3:::(S3 バケット名)",
"arn:aws:s3:::(S3 バケット名)/*"
]
}
]
}
（S3 バケット名）には S3 バケットの作成 で作成した S3 バケット名を入れます。
S3 ゲートウェイ型 VPC エンドポイントの作成
プライベートサブネットの Redshift Serverless が S3 バケットに通信するための S3 ゲートウェイ型 VPC エンドポイントを作成します。
VPC マネジメントコンソールから作成します。
| 設定項目 | パラメータ | 備考 |
|---|---|---|
| 名前タグ | 任意の名前 | |
| サービスカテゴリ | AWS のサービス | |
| サービス | com.amazonaws.ap-northeast-1.s3 | タイプ：Gateway を選択 |
| VPC | Redshift Serverless を作成している VPC を選択 | |
| ルートテーブル | Redshift Serverless が所属しているプライベートサブネットに紐づけているルートテーブルを選択 |
ポリシーは今回、カスタムします。
Redshift Serverless に付与した IAM ロールが、データを COPY する対象の S3 バケットとのみ通信できるよう、以下のように設定します。
{
"Version": "2012-10-17",
"Statement": [
{
"Effect": "Allow",
"Principal": "*",
"Action": "s3:*",
"Resource": [
"arn:aws:s3:::(S3 バケット名)",
"arn:aws:s3:::(S3 バケット名)/*"
],
"Condition": {
"ArnEquals": {
"aws:PrincipalArn": "arn:aws:iam::xxxxxxxxxxxx:role/yyMMdd_RedshiftServerlessRole"
}
}
}
]
}
"aws:PrincipalArn" には、Redshift Serverless に付与した IAM ロールを入れます。
S3 ゲートウェイ型 VPC エンドポイントのエンドポイントポリシーは "Principal" ではなく "Condition" の使用が推奨されています。
詳細は以下のブログをご参照ください。
拡張された VPC のルーティングをオンにする
VPC エンドポイントを作成しただけでは、Redshift Serverless は VPC 経由の通信をしてくれません。
ワークグループ画面から拡張された VPC のルーティングをオンにします。
詳細は、以下のブログをご参照ください。
blog.serverworks.co.jp
S3 バケットからデータを COPY する
psql をインストールした EC2 インスタンスにログインし、Redshift Serverless にログインします。
ログインコマンドは サーバーワークスエンジニアブログ - EC2 インスタンスに psql をインストールして Redshift Serverless に接続する - EC2 インスタンスから Redshift Serverless に接続する をご参照ください。
sh-4.2$ psql -h 20221018-workgp.123456789012.ap-northeast-1.redshift-serverless.amazonaws.com -U rsadmin -d dev -p 5439
Password for user rsadmin:
psql (9.2.24, server 8.0.2)
WARNING: psql version 9.2, server version 8.0.
Some psql features might not work.
SSL connection (cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256)
Type "help" for help.
dev=#
ログインすると、デフォルトで dev というデータベース、public というスキーマになっています。今回はこのまま進めていきます。
dev=# select current_schema(); current_schema ---------------- public (1 row) dev=#
テーブルの作成
ステップ 4: Amazon S3 から Amazon Redshift にデータをロードする ページの「クエリエディタにより Amazon S3 からサンプルデータをロードする」を確認し、データをコピーするためのテーブルを作成します。
まずは以下のコマンドで users というテーブルを作っていきましょう。
create table users(
userid integer not null distkey sortkey,
username char(8),
firstname varchar(30),
lastname varchar(30),
city varchar(30),
state char(2),
email varchar(100),
phone char(14),
likesports boolean,
liketheatre boolean,
likeconcerts boolean,
likejazz boolean,
likeclassical boolean,
likeopera boolean,
likerock boolean,
likevegas boolean,
likebroadway boolean,
likemusicals boolean);
実行結果
dev=# create table users( dev(# userid integer not null distkey sortkey, dev(# username char(8), dev(# firstname varchar(30), dev(# lastname varchar(30), dev(# city varchar(30), dev(# state char(2), dev(# email varchar(100), dev(# phone char(14), dev(# likesports boolean, dev(# liketheatre boolean, dev(# likeconcerts boolean, dev(# likejazz boolean, dev(# likeclassical boolean, dev(# likeopera boolean, dev(# likerock boolean, dev(# likevegas boolean, dev(# likebroadway boolean, dev(# likemusicals boolean); CREATE TABLE dev=#
以下のコマンドで、空っぽのテーブルができていることを確認します。
select * from users;
実行結果
dev=# select * from users; userid | username | firstname | lastname | city | state | email | phone | likesports | liketheatre | likeconcerts | likejazz | likeclassical | likeopera | likerock | likevegas | likebroad way | likemusicals --------+----------+-----------+----------+------+-------+-------+-------+------------+-------------+--------------+----------+---------------+-----------+----------+-----------+---------- ----+-------------- (0 rows) dev=#
COPY コマンドの実行
S3 バケットから Redshift Serverless にデータをコピーするための COPY コマンドの構文は以下です。
COPY table-name [ column-list ] FROM data_source authorization [ [ FORMAT ] [ AS ] data_format ] [ parameter [ argument ] [, ... ] ]
実行コマンド
copy users from 's3://s3-redshift-serverless-test/tickitdb/allusers_pipe.txt' iam_role 'arn:aws:iam::xxxxxxxxxxxx:role/yyMMdd_RedshiftServerlessRole' delimiter '|' region 'ap-northeast-1';
IAM ロールには、Redshift Serverless に付与している IAM ロールの ARN を入れています。
実行結果
dev=# copy users from 's3://s3-redshift-serverless-test/tickitdb/allusers_pipe.txt' dev-# iam_role 'arn:aws:iam::xxxxxxxxxxxx:role/yyMMdd_RedshiftServerlessRole' dev-# delimiter '|' region 'ap-northeast-1'; INFO: Load into table 'users' completed, 49990 record(s) loaded successfully. COPY dev=#
以下のコマンドで、users テーブルにデータがコピーできていることを確認します。
select * from users;
実行結果
dev=# select * from users;
userid | username | firstname | lastname | city | state | email | phone | likesports | liketheatre
| likeconcerts | likejazz | likeclassical | likeopera | likerock | likevegas | likebroadway | likemusicals
--------+----------+-------------+-------------+------------------------+-------+----------------------------------------------------------------+----------------+------------+------------
-+--------------+----------+---------------+-----------+----------+-----------+--------------+--------------
211 | VXX01NGK | Plato | Shepherd | Peabody | VT | euismod@egestas.ca | (189) 293-0192 | |
| | | | f | | | |
306 | FOE31HPK | Drew | Barber | La Puente | AB | velit@Integer.ca | (138) 726-7299 | |
| f | | | | | f | | t
308 | PMC51BUN | Erica | Howard | Watertown | BC | dui.quis@lacusNullatincidunt.com | (639) 945-1641 | | t
| f | | | | f | | t | f
608 | FMC21RUR | Brady | Daniels | Waterloo | YT | Cras.eu@massanonante.com | (953) 410-6388 | t |
| | | | f | | | | f
920 | NBR25BWJ | Forrest | Dunn | San Antonio | SK | augue.scelerisque.mollis@volutpatnunc.edu | (602) 260-6962 | | f
| | | f | f | | f | | f
1363 | SEZ33ZMZ | Odette | Branch | Valdosta | BC | est.vitae.sodales@Loremipsum.com | (332) 958-4565 | t |
| t | | | | | | |
1379 | JNM60FAT | William | Mendez | Ansonia | NB | lorem@vestibulum.edu | (899) 877-3768 | |
| t | t | t | f | f | t | |
1382 | SGM02RKA | Chaim | Meadows | Aliquippa | MT | Cum@vitaesodales.org | (146) 392-5816 | |
| | t | t | t | t | | t | f
:
:
:
:
:
dev=#
大量に出力されるので、Ctr+C で止めました。
他、サンプルデータベースには以下のデータも用意されているので、テーブルを作成して S3 バケットからデータをコピーし、お好みで分析を試してみてください。
- CATEGORY テーブル
- DATE テーブル
- EVENT テーブル
- VENUE テーブル
- LISTING テーブル
- SALES テーブル
余談：S3 インターフェイス型 VPC エンドポイントを使えるのか？
オンプレミス拠点から Direct Connect や VPN で AWS クラウドに接続している方は、S3 バケットへのデータをコピーを S3 インターフェイス型 VPC エンドポイント経由で行っているかもしれません。
この場合、この S3 インターフェイス型 VPC エンドポイントを使って Redshift Serverless にデータコピーすることができないか？と考えられる方がいらっしゃるかもしれません。ちなみに私は考えました。
S3 インターフェイス型 VPC エンドポイントについては以下のブログが参考になりますので、あわせてご参照ください。
blog.serverworks.co.jp
調査しました結果、COPY コマンドにインターフェイス型 VPC エンドポイントの DNS 名を指定するようなオプションが見当たらないので、今のところサポートされていないのではないかと思われます。
インターフェイス型 VPC エンドポイントはゲートウェイ型 VPC エンドポイントと比べ有料であること、帯域幅制限があること、AZ ごとに冗長性を考慮する必要があることを考えると、ゲートウェイ型 VPC エンドポイントを作成した方がメリットがあると考えられます。
参考
COPY コマンドを使用し、Amazon S3 からロードする
Amazon S3 から Amazon Redshift にデータをロードする
emi kitani(執筆記事の一覧)
AS部LX課。2022/2入社、コーヒーとサウナが好きです。執筆活動に興味があります。AWS認定12冠。
