概要

Amazon S3 にある CSV データを TROCCO 経由で Amazon Redshift に転送する検証をしました。

TROCCO から Redshift への直接接続は、Redshift が VPC 内に配置されるため、インターネット経由では到達できません。
本手順では、Amazon EC2 を踏み台として SSH トンネルを構成し、VPC 内の Redshift へデータを転送します。

構成図

TROCCO は 2 つのネットワーク経路を使ってデータを転送します。

S3 へのアクセスは IAM ロールの AssumeRole で行い、Redshift へのアクセスは VPC 外から直接到達できないため EC2 踏み台への SSH トンネルを経由します。

1. AWS リソースの準備

本手順では、Redshift および S3 はすでに構築済みであることを前提とします。
接続においてポイントとなる設定のみを抜粋して解説します。

なお、今回は検証のため設定は最小限に留めておりますが、本番環境での利用時は以下の点を合わせて検討してください。

• 踏み台 EC2: 今回はポート 22 のみ許可する構成としていますが、運用においては OS パッチの定期適用や SSH ログの監査も実施することを推奨します
• IAM アクセスキー: AWS では定期的なローテーションが推奨されています、ローテーションを行う場合は、TROCCO の接続情報に登録したアクセスキーも合わせて更新が必要です
• IAM ポリシーのリソーススコープ: 今回は検証のため一部のアクションに * を指定していますが、本番環境では特定のバケットやリソース ARN に絞り込むことを推奨します

EC2 踏み台の設定

TROCCO は SSH トンネル経由で VPC 内の Redshift に接続します。
そのため、パブリックサブネットに EC2 を配置し、Elastic IP を割り当てます。

EC2 の要件

項目	設定値
OS	Amazon Linux 2023
サブネット	パブリックサブネット
Elastic IP	必須(固定 IP が必要)
キーペア	RSA 形式のキーペア

セキュリティグループの設定(EC2)

EC2 のセキュリティグループには、TROCCO からの SSH 接続(ポート 22)を許可するインバウンドルールを追加します。
TROCCO の送信元 IP アドレスは以下から確認できます。

TROCCO ドキュメント

セキュリティグループの設定(Redshift)

Redshift のセキュリティグループには、踏み台 EC2 のセキュリティグループからポート 5439 を許可するインバウンドルールを追加します。

インバウンド: TCP 5439 ← 踏み台 EC2 のセキュリティグループ

IAM の設定

S3 接続用 IAM ロール(A アカウント)

TROCCO が S3 にアクセスするための IAM ロールを A アカウントに作成します。
信頼ポリシーには TROCCO の AWS アカウント ID と ExternalId を設定します。

値は TROCCO の接続情報画面から確認します。

{
  "Effect": "Allow",
  "Principal": { "AWS": "arn:aws:iam::<TROCCO_AWS_ACCOUNT_ID>:root" },
  "Action": "sts:AssumeRole",
  "Condition": {
    "StringEquals": { "sts:ExternalId": "<ExternalId>" }
  }
}

アクセスポリシーには転送元 S3 バケットへの読み取り権限を付与します。

アクション	リソース
s3:ListAllMyBuckets / s3:GetBucketLocation	*
s3:ListBucket	転送元バケット
s3:GetObject	転送元バケット/*

接続情報 – Amazon S3 | TROCCO Docs

Redshift 接続用 IAM ユーザー(B アカウント)

TROCCO が Redshift に接続するための IAM ユーザーを B アカウントに作成します。
TROCCO は Redshift へのデータロード時に内部で中間 S3 バケットを使用するため、その読み書き権限も必要です。

アクション	リソース
s3:ListAllMyBuckets / s3:GetBucketLocation	*
s3:ListBucket	中間 S3 バケット
s3:GetObject / s3:PutObject / s3:DeleteObject	中間 S3 バケット/*
sts:GetFederationToken	*

接続情報 – Amazon Redshift | TROCCO Docs

2. TROCCO 接続情報の作成

以下の接続設定をそれぞれ作成します。

• A アカウントへの S3 接続
• B アカウントへの Redshift 接続

S3 接続情報

IAM ロール方式で接続情報を作成します。

• AWS アカウント ID : 自環境のアカウント ID
• IAM ロール名 : TROCCO が AssumeRole するロール名

Redshift 接続情報

IAM ユーザーのアクセスキーと、Redshift のエンドポイントを設定します。

Redshift は VPC 内に閉じているため、SSH 踏み台経由で接続します。
接続情報の SSH 設定を以下の通りに行います。

• 接続タイプ : SSH 経由で接続 を選択
• SSH ホスト : EC2 の Elastic IP
• SSH ユーザー : ec2-user
• SSH 秘密鍵 : キーペアから払い出した RSA 秘密鍵をペースト
• SSL : 有効(必須)

設定後、「接続を確認」ボタンでホストとデータベースへの疎通を確認します。

3. データ転送設定の作成

転送元に「Amazon S3」、転送先に「Amazon Redshift」を選択して転送設定を作成します。

転送元 S3 の設定

転送元 S3 のバケット名とパスプレフィックスを指定します。

入力ファイル形式の設定

今回は MS932 エンコーディングの CSV ファイルを Zip 圧縮して Amazon S3 にアップロードしています。
そのため、TROCCO の設定は以下の通りとなります。

• 解凍形式 : zip
• 入力ファイル形式 : CSV/TSV(ヘッダー行あり)

文字エンコーディングと改行コードは自動的に判定されます。
MS932 の場合も正しく認識されました。

スキーマの確認

設定後、TROCCO が転送元データのスキーマを自動解析してプレビューを表示します。

今回は、事前に以下の CREATE TABLE 文を実行して、手動で転送先テーブルを作成しておきます。

CREATE TABLE IF NOT EXISTS trocco.inventory (
  inventory_id       INTEGER NOT NULL,
  product_code       VARCHAR(20) NOT NULL,
  product_name       VARCHAR(200) NOT NULL,
  category           VARCHAR(50) NOT NULL,
  warehouse_code     VARCHAR(10) NOT NULL,
  warehouse_name     VARCHAR(100) NOT NULL,
  quantity           INTEGER NOT NULL DEFAULT 0,
  unit               VARCHAR(20) NOT NULL,
  unit_price         DECIMAL(12, 2) NOT NULL DEFAULT 0,
  inventory_amount   DECIMAL(14, 2) NOT NULL DEFAULT 0,
  last_received_date DATE,
  last_shipped_date  DATE,
  remarks            VARCHAR(500),
  PRIMARY KEY (inventory_id)
) DISTSTYLE AUTO SORTKEY AUTO ENCODE AUTO;

4. 動作確認

転送ジョブを実行し、ステータスが「成功」になることを確認します。

転送成功後、Redshift で SELECT してデータを確認します。

まとめ

EC2 踏み台を経由することで、VPC 内に閉じた Redshift に TROCCO から安全に接続できることを確認しました。

EC2 踏み台の運用が難しい場合は、TROCCO から S3 にファイルを書き出し、Redshift Spectrum で外部テーブルとして参照する方法もあります。
この方法では EC2 が不要になり、よりサーバーレスな構成を実現できます。

参考資料

• TROCCO ドキュメント : IP アドレス
• TROCCO ドキュメント : Amazon S3 接続情報
• TROCCO ドキュメント : Amazon Redshift 接続情報