こんにちは、データサイエンスチームのmotchieです。

クラウド上のデータウェアハウス(DWH)といえば、Google BigQueryやAmazon Redshiftなどが有名です。
DWH間でデータ移行の方法については、
RedshiftからBigQueryに移行する場合、BigQuery Data Transfer Service APIを使う方法等がありますが、BigQueryからRedshiftに移行する場合については、あまり情報が見当たらないように思います。

そこで本記事では、BigQueryからRedshiftへデータを移行する方法を一つご紹介したいと思います。

リソースの全体像

流れ

以下の流れでリソースを作成していきたいと思います。

• CloudFormationでRedshiftクラスターを作成する
• GCS経由でBigQueryのデータでS3に出力する
• Glueを使ってデータをRedshiftに読み込ませる
• Redshiftに接続してクエリを投げてみる
• リソースの削除

前提条件として、AWS・GCP上でリソースにアクセスできる十分な権限が必要になります。

CloudFormationでRedshiftクラスターを作成する

まず、AWS上にRedshiftクラスターを作成していきます。
以下のAWSブログのCloudFormationテンプレートを活用したいと思います。

Amazon Web Services ブログ：AWS CloudFormation を使用して Amazon Redshift クラスターの作成を自動化する

上記のブログのテンプレートを活用することで、
AWSのベストプラクティスに沿って設計されたRedshiftクラスターを一発で作成することができます。
設計とテンプレートの詳細については、上記のブログをご覧ください。

各テンプレートのパラメーターについては、基本的にデフォルトの値を使用したいと思います。
ただし、今回の用途では、

• GCSからS3へデータをコピーするための認証情報とバケット
• GlueからRedshiftへアクセスするためのネットワーク設定
  を追加する必要があるため、上記のテンプレートをカスタマイズして、リソースを追加したいと思います。

テンプレートの取得

以下の3つのCloudFormationテンプレートを取得します。

• aws-vpc-blog.template
• linux-bastion-blog.templat
• redshift-blog.template

テンプレートの修正

まず、linux-bastion-blog.template (Bastionスタック用テンプレート)を修正していきます。
なお、修正後のテンプレートの内容は以下で確認できます。
linux-bastion-blog.template

上記の修正によって、以下のリソースが追加されます。

• データ格納用S3バケット
• gsutilコマンド用のIAMユーザー・認証情報
• 認証情報格納用のSecretsManager

また、Redshiftスタックでデータ格納用S3バケット名が必要なため、以下の修正によって、値を出力するようにします。

次に、redshift-blog.template (Redshiftスタック用テンプレート) を修正していきます。
なお、修正後のテンプレートの内容は以下で確認できます。
redshift-blog.template

GlueからVPC内のRedshiftにアクセスする際、ENIを利用します。
AWS ドキュメント：VPC の JDBC データストアに接続する
Redshift・Glueが利用するセキュリティグループに対して、すべてのTCP ポートに対する自己参照のインバウンドルールを追加します。

テンプレートの修正は以上です。
それでは、各スタックをデプロイしていきます。

スタックの作成

VPCスタック、Bastionスタック、Redshiftスタックの順で作成します。

まずパラメータをまとめて作成しておきます。
スタック名などは同じで問題ないですが、EMAIL_NOTIFICATION_LIST と KEY_PAIR_NAME (事前にEC2キーペアの作成が必要です)は自身の値に変更してください。

VPCスタックを作成します。完了まで数分かかります。

マネジメントコンソールでCloudFormationを開き、スタック作成の完了を確認してから、Bastionスタックの作成に移ってください。
Bastionスタックを作成します。以下のコマンドを実行します。

スタックの作成まで数分かかります。
スタックの出力する以下の値は、以降の手順で必要になるため、マネジメントコンソールで確認・取得しておいてください。

最後に、Redshiftスタックを作成します。以下のコマンドを実行します。

同様に、スタックの出力する以下の値は、以降の手順で必要になるため、マネジメントコンソールで確認・取得しておいてください。

以上でAWSのリソースが作成できました。次はGCPに移ります。

GCS経由でBigQueryのデータでS3に出力する

一般公開データセット

BigQueryでは、一般公開データセットと呼ばれる様々なデータがホストされており、
ユーザーは自身のデータと組み合わせて分析を行うことができます。

今回は、BigQueryの一般公開データセットの中から、Google Analytics Sampleのデータを使いたいと思います。

以下のようなクエリが実行できます。後ほど、Redshiftでも同様のクエリを投げてみたいと思います。

GCSバケットの作成

GCPのコンソールを開き、Cloud Shellを起動します。以下のコマンドはCloud Shellから実行します。
GCSのバケットを作成します。バケット名は適宜変更して実行してください。

BigQueryからGCSにエクスポート

データをBigQueryからGCSに出力します。今回は bq extract CLIコマンドを使用します。
なお、出力データが1GBを超える場合は、ワイルドカード * を使用して、データを分割する必要があります。
Google Cloud ドキュメント：テーブルデータのエクスポート

GCSからS3にデータをコピー

gsutilツールを利用して、GCSからS3にデータをコピーします。
Google Cloud ドキュメント：Cloud Storage の相互運用性
AWSマネジメントコンソールでSecret Managersを開き、Bastionスタックで作成した認証情報を取得します。

IAMユーザーの認証情報を ~/.boto に書き込みます。

GCSからS3にファイルをコピーします。
変数**S3_BUCKET_NAME**には、Bastionスタック作成時 BigQueryDataS3BucketName として出力されていたバケット名を入力してください。

以上で、BigQueryのデータをS3に出力できました。
次にAWSに移って、Glueの設定を行っていきます。

Glueを使ってデータをRedshiftに読み込ませる

AWS Glueを使うことで、様々なデータソースと連携するETLジョブが作成できます。

まず、Glueのクローラを使い、S3データからデータカタログを作成します。

データのクロール

Connectionの設定

GlueからRedshiftへの接続を設定します。

ETLジョブの作成

最後にETLジョブを作成します。

なお、RedshiftはArray・Structのデータ型に対応していないため、ETLで型を変換してからRedshiftに読み込む必要があります。
Arrayはstring型で読み込まれているようです。
Arrayの中身をそれぞれ展開し、適切なデータ型を付与して格納したい場合、自身で変換を追加する必要があります。

今回、hits の列は除外し、customdimmensions のArrayの中身を展開し、long型の customdimensions.index とstring型のcustomdimensions.value としてRedshiftに格納したいと思います。
コード例は以下のようになります。修正箇所をハイライトしています。

ETLジョブが完了し、データがRedshiftに読み込まれました。

Redshiftに接続してクエリを投げてみる

Redshiftに接続し、クエリを投げてみたいと思います。
Bastionサーバにssh接続のコマンド、Redshiftへの接続コマンドは、それぞれBastionスタック・Redshiftスタックの出力で確認できます。

Bastionサーバにssh接続します。

Redshiftに接続します。パスワードはスタック作成時に自身で設定したもの(MASTER_USER_PASSWORD)になります。

Redshiftにテーブルが作成されていることを確認します。

BigQueryと同様のクエリを投げてみます。

以下の通り、同じ実行結果が確認できました。

以上でBigQueryからRedshiftへの移行手順は終了です。

リソースの削除

削除が必要なリソースは以下の通りです。

• GCSバケット (GCS_BUCKET_NAME)
• S3バケット (S3_BUCKET_NAME )の中身(スタック削除時に中身が空である必要があります)
• Redshiftスタック・Bastionスタック・VPCスタック(スタック間に依存関係があるため、この順番で一つずつ削除してください)

不要なコストを避けるため、検証後はAWS/GCPのマネジメントコンソールからリソースの削除をお願いします。

まとめ

この記事では、BigQueryからRedshiftにデータを移行する流れをご紹介しました。
少しでも参考になれば幸いです。