Fivetranを使ってAmazon AuroraのデータをGoogle BigQueryに同期してみた

太陽剣オーロラプラズマ返し
2020.12.08

大阪オフィスの玉井です。

Fivetranは、SalesforceやZendeskといったSaaSのデータを、各種DBやDWHに自動連携できます。しかも、そのSaaSの分析に最適なスキーマやテーブルへの変換も、Fivetranがやってくれます。

実は、Fivetranは、連携できるデータソースとして、SaaSにとどまらず、各種DBを指定することもできます。つまり、DBからDWHへの定期的なデータの同期をFivetranで実行できるということです

今回はFivetranを使って、Amazon AuroraにあるデータをGoogle BigQueryに移行してみました。

公式情報

実際にやってみる前に…

環境

Amazon Aurora

簡単作成→開発/テストでサクッと作成したものを使用します。

  • Amazon Aurora with MySQL compatibility
  • db.r5.large
  • アクセスの際、SSHトンネルは不要

バージョンについては、下記の通りとなっています(公式)。

MySQL version 5.1.5 or above for non-RDS databases (5.5.40 is the earliest version tested). MySQL version 5.6.13 or above for RDS databases.

事前に済ませておくこと

Auroraを起動しておく

検証用のAuroraを作成して立ち上げます。

Auroraへのデータの登録

下記を参考に、サンプルデータを入れておきます(今回はこれをBigQueryに連携します)。

実際に入れたデータがこちら。

FivetranのDestinationにBigQueryを登録

登録方法は下記を参照。

やってみた

AuroraにFivetranがアクセスできるようにする

概要

FivetranでAuroraを設定する場合、Writerインスタンスへアクセスできるようにする必要があります。理由は、Fivetranはデータの同期を増分更新するのですが、増分更新のために、FivetranはAuroraのbinlogを利用するからです。binlogを利用できるのはWriterインスタンスだけです(ですので、FivetranはReaderインスタンスに接続することはできません)。

パブリックアクセスの確認

ということで、まずWriterインスタンスを確認します。

インスタンスの情報を確認し、パブリックアクセシビリティがOFFになっている場合は、ONにしてください(今回はSSHトンネルは介さないため。Fivetranはインターネットを介してAuroraにアクセスする)。セキュリティのコントロールは、セキュリティグループで行います(後でやります)。

ついでにエンドポイントとポートも確認しておきます。

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

続いて、セキュリティグループの設定をします。やることはシンプルで、FivetranのIPが通るルールをインバウンドに追加するだけです。

FivetranのIPはリージョンによって異なります。下記サイトでIPを確認しましょう。

ネットワークACLの確認

FivetranがAuroraにアクセスできるように、ACLの確認も行います。

WriterインスタンスのVPCを選びます。

VPCの詳細からネットワークACLを選びます。

VPCがデフォルトVPCを使っている場合、下記のように全通信が許可されるようなルールがすでにあります(インバウンドもアウトバウンドも)。そうじゃない場合は、FivetranのIPを追加して、Fivetranがアクセスできるように設定する必要があります。

AuroraにFivetran用のユーザーを作成する

Fivetran専用のユーザーを用意します(ユーザーを使い回すのではなく、必ずFivetran用ユーザーを作成しましょう)。

公式ドキュメントにサンプルクエリがあるので、今回はそれをそのまま使います(Writerインスタンスで実行しましょう)。レプリケーション権限を与えておくのがポイントです。

CREATE USER fivetran@'%' IDENTIFIED BY 'パスワード';
GRANT SELECT, REPLICATION CLIENT, REPLICATION SLAVE ON *.* TO fivetran@'%';

その他のAuroraの設定を実施する

冒頭、「Fivetranは増分更新のためにAuroraのbinlogを使用する」ということを記述しました。つまり、Auroraがbinlogを出力するように設定する必要があります。そのためには、DBクラスターのパラメータグループを編集する必要があります。

WriterインスタンスのDBクラスターパラメータグループを確認し、デフォルトの場合は、別途作成したパラメータグループに置き換える必要があります。

新しくパラメータグループを作成し、binlog_formatというパラメータの値をROWに変更します。

そして、WriterインスタンスのDBクラスターパラメータグループを上記のものに変更し、再起動します(再起動後、パラメータが正しく変わっているか確認)。

Fivetranのデータ同期のためには、binlogを24時間以上、分保持する必要があります。デフォルト設定のままだと、さっさと無くなっちゃうため、設定し直す必要があります。公式にサンプルクエリがあるため、これをそのまま使います。

-- 設定の確認
CALL mysql.rds_show_configuration;
-- 設定の変更
CALL mysql.rds_set_configuration('binlog retention hours', 24);

設定値がNULLの場合はデフォルトなので、保持期間を24時間以上に変更しましょう。

ちなみに、保持期間の推奨は7日間(168時間)ですが、ログが増える分、ディスク容量も食うところに注意です

FivetranでAuroraをデータソースとして登録する

Aurora側の設定を一通り終えたところで、FivetranにAuroraを登録します。設定値は画面に書かれている通りです。今回は下記のようになりました。

ぶっちゃけよくわかってない項目としてReplica IDというものがあります。Fivetran側でMySQLのレプリカセット(?)を一意に判別するためのIDだそうなのですが、最初から値が入っており、そのまま変更しなくても問題なく同期することができました。

各種設定が問題ない場合、接続テストが正常終了し、データ連携にうつっていきます。

Destinationに連携したいデータを設定する

データ同期元がDBの場合、登録したDB(今回はAurora)にあるテーブルのうち、どれを同期するか選ぶ必要があります。冒頭で用意しておいたサンプルデータが入っているテーブルを選びます。

ちなみに、information_schemaなどのメタデータ系のDBは表示されませんでした(表示する方法があるかどうかは未検証)。

いざ連携(Sync)

後はSyncをスタートするだけですね。成功しました。

Destination側に連携されたデータを確認する

Auroraのデータが、BigQueryに正しく入っているかどうか確認します。正しく連携することができました。型も適切なものに変換してくれています。

BigQuery側

Aurora側

注意点など

  • Amazon Aurora Serverlessは非対応です。

おわりに

データ分析をやりたいという時、DWHの導入が強く推奨されますが、そういう時、「DWHに入れたい元データがDBにあって、移行や定期的な同期を実施しないといけない」というケースは結構あると思います。

こういう「DB to DWH」なデータ連携にFivetranを使うと、めちゃくちゃ楽に連携できるので、データ分析における元データの移行や同期に頭を悩ませている担当者の方、ぜひトライアルとかやってみてください。