XplentyでRedshiftとBigQueryを往来してみる

2020.07.28

はじめに

データアナリティクス事業本部のkobayashiです。

XplentyはETL、ELTツールとして様々なデータソースを扱え、また処理もXplentyのGUIで直感的に作成できます。 今回はXplentyを使ってRedshiftとBigQuery間でマイグレーションを行ってみたいと思います。

Xplenty | Simplified ETL & ELT to BigQuery, Snowflake, Redshift & Azure

接続設定の確認

XplentyによるRedshift,BigQueryへの接続方法はすでにエントリがありますのでそちらをご確認いただければ問題なく設定できます。

ただ問題なく設定できると書いたばかりですが、Xplentyのジョブを実行する途中で接続設定のミスでハマったしまったので注意点だけ記載しておきます。

移行元となる場合はRedshiftの場合はXplentyが所有するS3に、BigQueryの場合はGoogleCloudStorageにデータがUnloadされるようですが、データソースとストレージのリージョンがあっていないとジョブがエラー終了します。 接続設定ではRegionの値はデータソースのリージョンと一致していなくても接続テストするとSuccessとなるので注意が必要です。

移行用データの確認

移行前にそれぞれのデータソースは以下のようなデータとなっています。

  • Redshift
select trim(s.nspname) as Schema,
       trim(t.name)      as Table,
       t.rows
from (
         select id, name, sum(rows) as rows
         from stv_tbl_perm
         group by id, name
     ) as t
         join pg_class as c on oid = t.id
         join pg_namespace as s on oid = c.relnamespace and s.nspname = 'cm_kobayashi_test'
order by t.name;
schema table rows
cm_kobayashi_test date 365
cm_kobayashi_test event 8798
cm_kobayashi_test sales 172456
cm_kobayashi_test venue 202
  • BigQuery
select dataset_id
     , table_id
     , row_count
from ml_datasets.__TABLES__
order by table_id;
dataset_id table_id row_count
ml_datasets census_adult_income 32561
ml_datasets credit_card_default 2965
ml_datasets iris 150
ml_datasets ulb_fraud_detection 284807

BigQuery -> Redshiftへの移行

Xplentyでの設定は以下の順序で行います。

  1. ソースとなるBigQueryのSourceコンポーネント作成
  2. ターゲットとなるRedshiftのDestinationコンポーネント作成
  3. 1,2を繰り返して移行対象のテーブルを登録
  4. ジョブ実行

すべてGUIで設定できますので難なく設定できると思います。

はじめにサイドバーからPackagesを選択してNew Packageを押下し新しいパッケージを作成します。

BigQueryのSourceコンポーネント作成

XplentyのPackageでBigQueryコンポーネントを追加して移行したいデータテーブルを設定します。

手順1-1).Add componetを押下し、SourcesでBigQueryを選択する。

手順1-2).作成されたアイコンをダブルクリックするとBigQueryコンポーネント設定用のモーダルが表示されるので、設定済みのBigQueryのconnectionを選択する。

手順1-3).Chose input connectionで対象の接続設定を選択する。

手順1-4).Source propertiesTableを選択し、テーブル名を入力しNextを押下する。

手順1-5).読み込むカラムの選択とデータのプレビューが行なえBiqQueryのデータテーブルからデータが取れることが確認できるので、Select allを押下して移行対象のカラムを追加する。

手順1-6).右下のSaveを押下して設定を保存する。

ここまででソースの設定は終わります。設定項目も少なくとても簡単です。

RedshiftのDestinationコンポーネント作成

次にRedshiftコンポーネントを追加して設定を行います。

手順2-1).Add componetを押下し、SourcesでBigQueryを選択する。

手順2-2).画面上にRedshiftのアイコンが作成されるので先に作成したBigQueryのアイコンとDrag&Dropで紐付ける。この紐付けをしていないと移行元であるBigQueryのカラムと移行先のRedshiftのカラムがマップできないのでRedshiftの設定を行う前に紐付けしておく。

手順2-3).RedshiftのアイコンをダブルクリックするとRedshiftコンポーネント設定用のモーダルが表示されるので、設定済みのRedshiftのconnectionを選択する。

手順2-4).移行先のRedshiftのスキーマとテーブルを入力し各種設定を行い、Nextを押下する。(今回の例ではわかりやすいようにbq_のプリフィックスを付けています。)

  • Automatically create table if it doesn't exist : チェックする
  • Automatically add missing columns : チェックする
  • Operation type : Append (Insert only)を選択する

(他の設定は一旦デフォルトで問題ありませんがソースデータの形式によって変更してください。)

手順2-5).移行元の移行先のカラムマッピングを行う。移行先のテーブルは新規作成なのでAuto fillを押下して自動設定を行う。(2でBigQueryとRedshiftの紐付けをしていないとここが設定できないので事前に紐付けすることが大切です。)

手順2-6).右下のSaveを押下して設定を保存する。

ここまでの作業で1テーブル分の移行設定が終わりましたのでこれを繰り返してテーブル分設定します。 今回は4テーブルを移行するので最終的に以下のような形のパッケージになります。

ここまで設定が終わりましたら後はRun jobを押下することでジョブが走り設定に問題がなければマイグレーションが完了します。

移行した後のRedshiftのテーブルは情報は以下になり無事移行ができていることが確認できました。

schema table rows
cm_kobayashi_test bq_census_adult_income 32561
cm_kobayashi_test bq_credit_card_default 2965
cm_kobayashi_test bq_iris 150
cm_kobayashi_test bq_ulb_fraud_detection 284807
cm_kobayashi_test date 365
cm_kobayashi_test event 8798
cm_kobayashi_test sales 172456
cm_kobayashi_test venue 202

注意点

RedshiftのDestinationコンポーネント作成の設定(手順2-4)で「Automatically add missing columns : チェックする」を指定した場合はターゲットになるテーブルにカラムが存在しなければ自動で作成されますが、型は手順1-5で設定した型が選択されサイズは最大で作成されます。

例) ソースの型がSTRING -> 自動作成される型はvarchar(65535)

気になる場合は

ALTER TABLE table_name ALTER COLUMN column_name TYPE new_data_type;

で変更してください。

Redshift -> BigQuery への移行

設定の手順は「BigQuery -> Redshiftへの移行」と逆を行えば良いので、

  1. ソースとなるRedshiftのSourceコンポーネント作成
  2. ターゲットとなるBigQueryのDestinationコンポーネント作成
  3. 1,2を繰り返して移行対象のテーブルを登録
  4. ジョブ実行

で行います。途中の設定は「BigQuery -> Redshiftへの移行」を踏襲すれば特に困ることなく進められますので割愛します。

ジョブ完了後のBigQueryのテーブル情報を見ると正常に移行されていることが確認できます。

dataset_id table_id row_count
ml_datasets census_adult_income 32561
ml_datasets credit_card_default 2965
ml_datasets iris 150
ml_datasets rs_date 365
ml_datasets rs_event 8798
ml_datasets rs_sales 172456
ml_datasets rs_venue 202
ml_datasets ulb_fraud_detection 284807

まとめ

Xplentyを使うことでノーコーディングでRedshiftとBigQuery間でマイグレーションが行えました。またデータを加工したい場合もSource propertiesDestination propertiesでおおよそ設定できるのでよほど込み入ったことを行わない限りXplentyでまかなえるのではないかと感じました。 データマイグレーションにお困りの方にはぜひお勧めします。

最後まで読んで頂いてありがとうございました。