BigQuery Transfer ServiceでAmazon RedshiftからBigQueryにデータ移行する

Amazon Redshift（以下、Redshift）にあるデータを BigQuery に移行して活用する案件があったので、調べたところ、BigQuery Data Transfer Service で Redshift からのデータ移行がベータ版ですがサポートされていました。

cloud.google.com

本記事では Redshift にあるサンプルデータを BigQuery に移行してみたのでその方法を紹介します。

移行方法の概要
AWS ネットワークの構築
- セキュリティグループ
サンプルデータの用意
Redshift クラスタの構築
データをアンロードする S3 バケットの作成
IAM ロールの権限追加
AWS アクセスキーの作成
GCP プロジェクトの作成
BigQuery Data Transfer Service API の有効化
BigQuery データセットの作成
BigQuery Data Transfer Service の設定
Redshift マイグレーションで足りない機能
- データ差分の同期
- スケジュール実行
まとめ
参考

移行方法の概要

redshift-overview-diagram

(https://cloud.google.com/bigquery-transfer/docs/redshift-migration から引用)

BigQuery Datatransfer Service では次のことを行ってデータを移行します。

GCP側から Redshift に接続し、S3 バケットにデータをアンロード（エクスポート）する
S3 バケットにあるデータを BigQuery にインポートする

本記事では、AWS に Redshift クラスタを構築してサンプルデータを投入します。このデータを BigQuery Data Transfer Service によって BigQuery に転送します。なお、AWS、GCP ともに東京リージョンとしています。

AWS ネットワークの構築

VPC、サブネットの作成は省略します。サブネットはパブリックアクセスによる VPC 外部からアクセスするためにルートテーブルにインターネットゲートウェイ（igw-xxx）をアタッチする必要があります。

セキュリティグループ

セキュリティグループ作成時に、インバウンドルールで以下のルールを追加します。

タイプ	プロトコル	ポート範囲	ソース	説明
Redshift	TCP	5439	PC からアクセスされる IP アドレス	connect test
Redshift	TCP	5439	GCP の IP アドレス（後述）	GCP

VPN などの IP アドレス は PC から Redshift クラスタへの接続およびデータのインポートを行うため、PC からアクセスされるグローバル IP アドレスを許可してください。

GCP の IP アドレスは以下のドキュメントから東京リージョンの IP アドレスをコピーしてください。

Amazon Redshift からのデータ移行 | BigQuery Data Transfer Service

サンプルデータの用意

次のコマンドかマネジメントコンソールで S3 バケットを作成します。S3 バケット名は全世界でユニークである必要があるため、重複しない名前にしてください。

$ aws s3 mb s3://redshift-transfer-test1234 --region ap-northeast-1

ここからサンプルデータ（tickitdb.zip）をダウンロード、ZIP ファイルを解凍したらフォルダごと S3 バケットにアップロードします。

$ aws s3 cp tickitdb s3://redshift-transfer-test1234/tickitdb/ \
--recursive --region ap-northeast-1

# アップロードされたかの確認
$ aws s3 ls s3://redshift-transfer-test1234/tickitdb/
2020-05-30 23:40:20     445838 allevents_pipe.txt
2020-05-30 23:40:20    5893626 allusers_pipe.txt
2020-05-30 23:40:20        465 category_pipe.txt
2020-05-30 23:40:20      14534 date2008_pipe.txt
2020-05-30 23:40:20   11585036 listings_pipe.txt
2020-05-30 23:40:20   11260097 sales_tab.txt
2020-05-30 23:40:20       7988 venue_pipe.txt

なお、S3 にあるデータはずっと残り続けるため、明示的に削除を行うか、ライフサイクルで日数経過したオブジェクト（データ）を自動削除するようにしましょう。

Redshift クラスタの構築

IAM ロールの作成

IAM ロールのマネジメントコンソールから作成画面に行き、[ロールの作成]をクリックします
[ユースケースの選択]で [Redshift] を選択し、[Redshift - Customizable] を選択します
[Attach アクセス権限ポリシー]では [AmazonS3ReadOnlyAccess] を付与します
IAM ロールを適当な名前（今回は myRedshiftRole）をつけます

作成後、IAM ロール画面に行き、ロール ARN に表示されている文字列（arn:aws:iam::<AWSアカウントID>:role/myRedshiftRole）をコピーして控えておきます。

サブネットグループの作成

Redshift のマネジメントコンソールに行き、[設定]から[サブネットグループ]を選択し、[クラスターサブネットグループの作成]をクリックし、次のように各項目を入力していきます。

項目名	値
名前	my-cluster-subnet-group
説明	半角英数字で適当に入力
VPC	上で設定した VPC

アベイラビリティーゾーン、サブネットは上で設定したものを選択し、[クラスターサブネットグループの作成]をクリックします。

Redshift クラスタの作成

Redshift のマネジメントコンソールに行き、[クラスターを作成]をクリックし、次のように各項目を入力していきます。

項目名	値
クラスター識別子	my-redshift
ノードの種類	dc2.large
ノード	1
データベース名	tickitdb
データベースポート	5439
マスターユーザー名	好きなユーザ名を設定
マスターユーザーのパスワード	好きなパスワードを設定
使用可能な IAM ロール	先ほど作成した IAM ロール（`myRedshiftRole`）
Virtual Private Cloud (VPC)	先ほど設定した VPC
セキュリティグループ	上で作成したセキュリティグループ
クラスターサブネットグループ	先ほど作成したサブネットグループ（`my-cluster-subnet-group`）
アベイラビリティーゾーン	サブネットグループで設定したリストから選択
拡張された VPC のルーティング	有効化
パブリックアクセス可能	はい
Elastic IP アドレス	なし

残りの設定はすべて変更しないで[クラスターを作成]をクリックします。作成完了までに数分程度かかります。

作成が完了（Available）になったらエンドポイント（ホスト名）を調べます。エンドポイントはRedshift のマネジメントコンソールに行き、[クラスター]から作成した Redshift クラスタ（my-redshift）選択します。[プロパティ]タグを選択し[エンドポイント]に表記されているものをコピーします。ポート番号とデータベース名（:5439/tickitdb）を取り除いたものを控えておいてください。

接続確認

psql コマンドや SQL クライアントツールで Redshift クラスタに接続できるかどうかを確認します。エンドポイントは先ほど控えたものを使います。

$ psql -U <ユーザ名> -d <データベース名> -h <エンドポイント> -p <ポート番号>

# 例
$ psql -U <ユーザ名> -d tickitdb -h my-redshift.xxxxxxxxxxxx.ap-northeast-1.redshift.amazonaws.com -p 5439
Password for user <ユーザ名>: [パスワードを入力]

サンプルデータのロード

S3 に配置したサンプルデータを Redshift クラスタにロード（インポート）します。最初に Redshift クエリに接続した状態で、以下のクエリでテーブルを作成します。

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);

create table venue(
    venueid smallint not null distkey sortkey,
    venuename varchar(100),
    venuecity varchar(30),
    venuestate char(2),
    venueseats integer);

create table category(
    catid smallint not null distkey sortkey,
    catgroup varchar(10),
    catname varchar(10),
    catdesc varchar(50));

create table date(
    dateid smallint not null distkey sortkey,
    caldate date not null,
    day character(3) not null,
    week smallint not null,
    month character(5) not null,
    qtr character(5) not null,
    year smallint not null,
    holiday boolean default('N'));

create table event(
    eventid integer not null distkey,
    venueid smallint not null,
    catid smallint not null,
    dateid smallint not null sortkey,
    eventname varchar(200),
    starttime timestamp);

create table listing(
    listid integer not null distkey,
    sellerid integer not null,
    eventid integer not null,
    dateid smallint not null  sortkey,
    numtickets smallint not null,
    priceperticket decimal(8,2),
    totalprice decimal(8,2),
    listtime timestamp);

create table sales(
    salesid integer not null,
    listid integer not null distkey,
    sellerid integer not null,
    buyerid integer not null,
    eventid integer not null,
    dateid smallint not null sortkey,
    qtysold smallint not null,
    pricepaid decimal(8,2),
    commission decimal(8,2),
    saletime timestamp);

# テーブル一覧を表示して確認
\dt
 public | category | table | <ユーザ名>
 public | date     | table | <ユーザ名>
 public | event    | table | <ユーザ名>
 public | listing  | table | <ユーザ名>
 public | sales    | table | <ユーザ名>
 public | users    | table | <ユーザ名>
 public | venue    | table | <ユーザ名>

次のクエリで S3 からデータをロードします。S3 バケット名（redshift-transfer-test1234）、AWS アカウント ID は適宜ご自身の環境に合わせてください。

copy users from 's3://redshift-transfer-test1234/tickitdb/allusers_pipe.txt' 
credentials 'aws_iam_role=arn:aws:iam::<AWSアカウントID>:role/myRedshiftRole' 
delimiter '|' region 'ap-northeast-1';

copy venue from 's3://redshift-transfer-test1234/tickitdb/venue_pipe.txt' 
credentials 'aws_iam_role=arn:aws:iam::<AWSアカウント>:role/myRedshiftRole' 
delimiter '|' region 'ap-northeast-1';

copy category from 's3://redshift-transfer-test1234/tickitdb/category_pipe.txt' 
credentials 'aws_iam_role=arn:aws:iam::<AWSアカウントID>:role/myRedshiftRole' 
delimiter '|' region 'ap-northeast-1';

copy date from 's3://redshift-transfer-test1234/tickitdb/date2008_pipe.txt' 
credentials 'aws_iam_role=arn:aws:iam::<AWSアカウントID>:role/myRedshiftRole' 
delimiter '|' region 'ap-northeast-1';

copy event from 's3://redshift-transfer-test1234/tickitdb/allevents_pipe.txt' 
credentials 'aws_iam_role=arn:aws:iam::<AWSアカウントID>:role/myRedshiftRole' 
delimiter '|' timeformat 'YYYY-MM-DD HH:MI:SS' region 'ap-northeast-1';

copy listing from 's3://redshift-transfer-test1234/tickitdb/listings_pipe.txt' 
credentials 'aws_iam_role=arn:aws:iam::<AWSアカウントID>:role/myRedshiftRole' 
delimiter '|' region 'ap-northeast-1';

copy sales from 's3://redshift-transfer-test1234/tickitdb/sales_tab.txt'
credentials 'aws_iam_role=arn:aws:iam::<AWSアカウントID>:role/myRedshiftRole'
delimiter '\t' timeformat 'MM/DD/YYYY HH:MI:SS' region 'ap-northeast-1';

# データがロードされたかの確認
SELECT "table", tbl_rows FROM svv_table_info order by tbl_rows;
  table   | tbl_rows
----------+----------
 category |       11
 venue    |      202
 date     |      365
 event    |     8798
 users    |    49990
 sales    |   172456
 listing  |   192497
(7 rows)

データをアンロードする S3 バケットの作成

Redshift クラスタからデータをアンロード（エクスポート）する S3 バケットを作成します。BigQuery Data Transfer Service によってこの S3 バケットから BigQuery にデータが転送されます。

$ aws s3 mb s3://redshift-unload-test1234 --region ap-northeast-1

IAM ロールの権限追加

Redshift クラスタから S3 にアンロードするために必要な権限を追加します。

IAM ロールのマネジメントコンソールから myRedshiftRole を選択します
[インラインポリシーの追加]をクリックします
[JSON]タブを選択し、以下を貼り付けて[ポリシーの確認]をクリックします。redshift-unload-test1234 は作成した S3 バケットに置き換えてください
ポリシー名は適当（unload_to_s3）にしてください

{
  "Version": "2012-10-17",
  "Statement": [
    {
      "Sid": "Stmt1590859748143",
      "Action": [
        "s3:PutObject"
      ],
      "Effect": "Allow",
      "Resource": "arn:aws:s3:::redshift-unload-test1234/*"
    }
  ]
}

AWS アクセスキーの作成

BigQuery Data Transfer Service に設定するための AWS アクセスキーを用意します。IAM のマネジメントコンソールに行き、[ユーザー]を選択します。[ユーザを追加]をクリックし、次のように各項目を入力していきます。

項目名	値
ユーザ名	bigquery-data-transfer-service
アクセスの種類	プログラムによるアクセス
アクセス許可の設定	既存のポリシーを直接アタッチ
ポリシー	[AmazonS3FullAccess]、[AmazonRedshiftReadOnlyAccess] をアタッチ
タグ	未設定

IAM ユーザ作成後、AWS アクセスキー情報が入ったクレデンシャルファイル（CSV 形式）をダウンロードするか、画面に表示されているアクセスキー ID とシークレットアクセスキーを控えておいてください。

GCP プロジェクトの作成

ここから GCP 側の設定になります。GCP のマネジメントコンソールに行き、プロジェクト一覧から[新しいプロジェクト]を選択します。プロジェクト名、プロジェクト ID はユニーク（重複なし）かつ、わかりやすいものにします。

BigQuery Data Transfer Service API の有効化

[API とサービス]から[ライブラリ]を選択します。検索窓から [BigQuery Data Transfer Service API] を入力します。検索にヒットした [BigQuery Data Transfer API] を選択し[有効]をクリックします。

BigQuery データセットの作成

[ドキュメント]を参考に、次のように各項目を入力して BigQuery のデータセットを作成します。

項目名	値
データセット ID	tickitdb
データのロケーション	東京（asia-northeast1）
デフォルトのテーブルの有効期限	無制限
暗号化	Google が管理する鍵

BigQuery Data Transfer Service の設定

BigQuery のコンソールに行き、[転送]を選択し[転送を作成]をクリックし、次のように各項目を入力していきます。

項目名	値
ソースタイプ	Migration: Redshift
転送構成名	migration from redshift
データセットID	tickitdb
JDBC connection url for Amazon Redshift	JDBC URL（後述1）
Username of your database	Redshift クラスタの接続ユーザ名
Password of your database	Redshift クラスタの接続パスワード
Accesskey ID	AWS アクセスキー ID を貼り付け
Secret access key	シークレットアクセスキーを貼り付け
Amazon S3 URI	アンロード用に作成した S3 バケットの URI（後述2）
Redshift schema	public
Table name patterns	（空のまま）
VPC and reserved IP range	（空のまま）
通知オプション	（デフォルトのまま）

後述1･･･Redshift のマネジメントコンソールに行き、[クラスター]から作成した Redshift クラスタ（my-redshift）選択します。[プロパティ]タグを選択し[JDBC URL]に表記されているものをコピーします。

後述2･･･s3://<S3バケット名>/（s3://redshift-unload-test1234）

[保存]をクリックすると BigQuery へのデータ転送が開始されます。下図のようにログに Summary: succeeded 7 jobs, failed 0 jobs. と表示されていればデータ転送は完了となります。

bigquery-data-transfer-service

BigQuery のマネジメントコンソールからもデータを確認できます。

bigquery-tickitdb

Redshift マイグレーションで足りない機能

Redshift から BigQuery へのデータ転送が簡単にできることを紹介しました。しかし、まだベータ版なのか現時点（2020年5月）で足りない機能が多いことに気づきました。

データ差分の同期

Redshift から BigQuery へのデータを全件転送しましたが、ソース元の Redshift クラスタのデータが追加・変更・削除された場合に、BigQuery に同期してほしいですよね。

残念ながら現時点では差分同期ができませんでした。再度データ転送をするには、BigQuery Data Transfer Service のコンソールから[今すぐ転送を実行]をクリックするしかありませんが、これを実行すると同じデータがまた追加されます。つまりデータの重複が発生します。

スケジュール実行

データ差分同期ができないので、スケジュール実行もできません。

まとめ

本記事では BigQuery Data Transfer Service を使って Amazon Redshift から BigQuery にデータを転送する方法を紹介しました。とても簡単にデータ移行ができる反面、データ差分同期やスケジュール実行ができないため今後に期待したいところです。

本日も乙

ただの自己満足な備忘録。