🌊

RedshiftでUNLOADを実行しS3にテーブルデータを出力する

2025/01/31に公開

はじめに

こんにちは。(心の内では)健康を目指して日々精進しているshimojです。
普段はDWHであるRedshfitをメインにデータ分析基盤周りの業務に携わっております。
業務としてはファイル取込みからデータマートの作成までが多いですが、作成したデータマートをS3に出力することもあります。
出力時はUNLOADを利用しますのでパラーメータ指定別の出力内容について確認したいと思います。

前提

Redshiftクラスターが存在しており、UNLOADする必要な権限が付与されているものとします。

利用テーブルの作成

まずUNLOADする対象のテーブルを作成するため、プロシージャとして作成するコードを記載します。

動作確認テーブル作成のプロシージャコード

--------------------------
-- テスト用のスキーマを作成
--------------------------
create schema test;

--------------------------
-- テーブル再構築用のプロシージャコード
--------------------------
CREATE OR REPLACE PROCEDURE test.generate_test_data(num_records INT)
AS $$
DECLARE
  i INT;
BEGIN
  -- 作成対象のテーブルが存在してたら削除
	DROP TABLE IF EXISTS test.test_data;
  -- テストデータを格納するテーブルを作成
  CREATE TABLE IF NOT EXISTS test.test_data (
    id INT IDENTITY(1,1),
    name VARCHAR(50),
    email VARCHAR(100),
    age INT,
    created_at TIMESTAMP
  );

  -- 指定された件数のデータを生成
  FOR i IN 1..num_records LOOP
    INSERT INTO test.test_data (name, email, age, created_at)
    VALUES (
      'User' || i,
      'user' || i || '@example.com',
      20 + (i % 60),  -- 20歳から79歳までのランダムな年齢
      CURRENT_TIMESTAMP - (random() * INTERVAL '100 days')  -- 過去100日以内のランダムな日付
    );
  END LOOP;

  -- 生成されたデータ数を確認
  RAISE INFO 'Generated % records', num_records;
END;
$$ LANGUAGE plpgsql;
;

作成したプロシージャを以下のように実行します。

--------------------------
-- 「2000」を引数にプロシージャを実行
--------------------------
call test.generate_test_data(2000);

UNLOADの動作確認

作成したテーブルから以下表の4パターンとしてデータをS3に出力します。

No フォーマット ヘッダー パラレル パーティション 圧縮 S3サブパス パーティション
pattern1 csv OFF ON 含めない pattern1 含めない
pattern2 csv ON ON 含む GZIP pattern2 含む
pattern3 json ー(サポート外) ON 含む GZIP pattern3 含む
pattern4 PARQUET ー(サポート外) OFF 含めない ー(サポート外) pattern4 含めない

取得する際に実行するコードは以下となります。

4パターンのUNLOADコマンド
--------------------------
-- pattern1
--------------------------
UNLOAD ('select *, to_char(created_at, ''YYYYMM'') as yyyymm from test.test_data')
TO 's3://{S3bucket名}/unlopad_test/dev/pattern1/'
iam_role 'arn:aws:iam::1234567890:role/{ロール名}'
FORMAT CSV
-- HEADER
-- GZIP
PARALLEL ON -- デフォルトはONだが名義的に指定
-- partition by (yyyymm)
EXTENSION 'csv'
;

--------------------------
-- pattern2
--------------------------
UNLOAD ('select *, to_char(created_at, ''YYYYMM'') as yyyymm from test.test_data')
TO 's3://{S3bucket名}/unlopad_test/dev/pattern2/'
iam_role 'arn:aws:iam::1234567890:role/{ロール名}'
FORMAT CSV
HEADER
GZIP
PARALLEL ON
partition by (yyyymm) INCLUDE
EXTENSION 'csv'
;

--------------------------
-- pattern3
--------------------------
UNLOAD ('select *, to_char(created_at, ''YYYYMM'') as yyyymm from test.test_data')
TO 's3://{S3bucket名}/unlopad_test/dev/pattern3/'
iam_role 'arn:aws:iam::1234567890:role/{ロール名}'
FORMAT JSON
-- HEADER  -- サポート外
GZIP
PARALLEL ON
partition by (yyyymm)
EXTENSION 'json'
;

--------------------------
-- pattern4
--------------------------
UNLOAD ('select *, to_char(created_at, ''YYYYMM'') as yyyymm from test.test_data')
TO 's3://{S3bucket名}/unlopad_test/dev/pattern4/'
iam_role 'arn:aws:iam::1234567890:role/{ロール名}'
FORMAT PARQUET
-- HEADER
-- GZIP
PARALLEL OFF
-- partition by (yyyymm)
EXTENSION 'parquet'
;

S3に出力したオブジェクトパスをCLIコマンドを利用して確認します。

# UNLOAD出力先のパス
$ aws --profile csa-demo s3 ls s3://{S3bucket名}/unlopad_test/dev/
                           PRE pattern1/
                           PRE pattern2/
                           PRE pattern3/
                           PRE pattern4/
2025-01-30 14:06:22          0 


# パターン1
$ aws --profile csa-demo s3 ls s3://{S3bucket名}/unlopad_test/dev/pattern1/
2025-01-30 14:17:21     140450 0000_part_00.csv
2025-01-30 14:17:21          0 0001_part_00.csv


# パターン2
$ aws --profile csa-demo s3 ls s3://{S3bucket名}/unlopad_test/dev/pattern2/
                           PRE yyyymm=202410/
                           PRE yyyymm=202411/
                           PRE yyyymm=202412/
                           PRE yyyymm=202501/

$ aws --profile csa-demo s3 ls s3://{S3bucket名}/unlopad_test/dev/pattern2/yyyymm=202410/
2025-01-30 14:18:24       5000 0000_part_00.csv


# パターン3
$ aws --profile csa-demo s3 ls s3://{S3bucket名}/unlopad_test/dev/pattern3/              
                           PRE yyyymm=202410/
                           PRE yyyymm=202411/
                           PRE yyyymm=202412/
                           PRE yyyymm=202501/

$ aws --profile csa-demo s3 ls s3://{S3bucket名}/unlopad_test/dev/pattern3/yyyymm=202410/
2025-01-30 14:19:20       5259 0000_part_00.json


# パターン4
$ aws --profile csa-demo s3 ls s3://{S3bucket名}/unlopad_test/dev/pattern4/              
2025-01-30 14:19:50      57611 000.parquet

まとめ

Redshiftに構築したテーブルのデータを出力しました。
出力フォーマットやパーティションを含めるなどの設定項目はありますが、作成したマートをファイル出力する際はとても便利だなと思いました。
この記事がどなたかの助けになれば幸いです。

参考リンク

GitHubで編集を提案

Discussion