オンプレミスのMySQLのデータをBigQueryに移行する方法

直接Reconerから接続できないオンプレミスのMySQLのデータを移行したい場合、一旦MySQLのデータをCSVファイルとして取り出し、そのCSVファイルをS3/GCSにアップロードして移行を行う

定期的にMySQLのデータをBigQueryに移行する方法

  • 「MySQLからデータをダンプする方法」のダンプコマンドを定期的に実行し、日付の入ったCSVファイルが定期的に生成されるようにする。アップロードするファイルがないのを防ぐため、下のアップロードコマンドより定期的に実行する時間をより前に設定する。(例: ダンプコマンドは毎日15時に実行し、アップロードコマンドは毎日23時に実行する)

  • 「S3/GCSにファイルをアップロードする場合」のアップロードコマンドを定期的に実行し、日付の入ったCSVファイルを定期的にアップロードされるようにする。

  • 「パラメータ変数設定」のとおりに設定し、日付の変数が解決されるよう設定する。

  • 各Workflowの要素(SourceやSink等)を、このドキュメントのとおりに適切に設定する。

  • 「Workflowの定期実行設定方法」のとおりに設定し、定期的にWorkflowを実行してS3/GCSからBigQueryにデータを移行するようにする。

MySQLからデータをダンプする方法

次のようなテーブルに対して、

mysql> SELECT * FROM db_name.table_name;
+----------------+-----------------+------------------+------------+---------------------+-------------+--------------+----------------------+-------------+-------------+---------------------+
| column_tinyint | column_smallint | column_mediumint | column_int | column_bigint | column_real | column_float | column_decimal | column_date | column_time | column_timestamp |
+----------------+-----------------+------------------+------------+---------------------+-------------+--------------+----------------------+-------------+-------------+---------------------+
| 127 | 32767 | 8388607 | 2147483647 | 9223372036854775807 | 1.1 | 1.2 | 123456789.0123456789 | 2020-12-31 | 12:34:56 | 2020-12-31 03:12:12 |
+----------------+-----------------+------------------+------------+---------------------+-------------+--------------+----------------------+-------------+-------------+---------------------+

次のようなコマンドで、MySQLからCSVをダンプします。

> mysqldump --user=user --host=127.0.0.1 --port=23306 --tab=/tmp --fields-terminated-by=, db_name table_name > `date "+%Y-%m-%d"`.csv

一行目にカラム名のヘッダーをつけた上で、File Upload Sourceでアップロードします。

プレビューによって、次の通り取得できていることを確認します。

AWS S3にファイルをアップロードする場合

次のドキュメントを参照し、アップロードに必要なAWS CLIをインストール・設定します。

その後、次のようなコマンドでAWS S3へファイルのアップロードを行います。

aws s3 cp `date "+%Y-%m-%d"`.csv s3://target-bucket/

Google Cloud Storage(GCS)にファイルをアップロードする場合

次のドキュメントを参照し、アップロードに必要なgsutilをインストール・設定します。

その後、次のようなコマンドでGCSへファイルのアップロードを行います。

gsutil cp `date "+%Y-%m-%d"`.csv gs://target-bucket/

S3にアップロードする場合のWorkflow図

GCSにアップロードする場合のWorkflow図

パタメーター変数設定

パタメーター変数とは?

ReckonerでS3/GCSにある日付の入ったファイルを定期的に処理する際に、日付を直接指定するようにしてしまうと、毎日Workflowのパスを今日の日付に合わせることになってしまいます。

しかし、Reckonerの「パタメーター変数」を利用することで、「今日の日付」という値を指定することができます。これにより、Workflowを実行したタイミングで「今日の日付」という値が解決され、今日の日付の入ったCSVファイルを、毎日Workflowの設定を変更することなく読み込むことができます。

設定方法

Workflowの編集画面から、右上にある歯車のアイコンをクリックします。

パラメータ変数の設定画面が開くので、次の通り値を入力します。

のマクロのドキュメントのとおり、 CURENT_DATEは今日の日付をWorkflowの実行の際に計算してくれます。これにより、変数のdateを、Workflowの設定内で#{date}のように入力すると、Workflowの実行の際に今日の日付で#{date}が上書きされます。

例えば、今日が2020年11月22日であれば、Reckonerの要素で文字列を設定できる箇所に、#{date}.csvと入力すると、実行した際には2020-11-22.csvと解決されます。

Source詳細

AWS S3(S3にアップロードする場合)

正しい対象の認証情報・バケット・フォーマット・パス接頭辞を設定します。

Google Cloud Storage(GCSにアップロードする場合)

正しい対象の認証情報・バケット・パス接頭辞・フォーマットを設定します。

Transform詳細

フィールド変換

Reckonerのフィールド変換によって、次のように適切な型に変換します。

MySQLのTIME型は、Reckonerで対応する型がないため、Stringに変換します。

整数型は、intかlongに適切に変換します。

浮動小数点数型は、loatかdoubleに適切に変換します。

固定小数点数型は、decimalに変換します。

DATE, TIMESTAMP型は、それぞれdate, timestampに変換します。

Sink詳細

BigQuery

各パラメータを適切なものに設定します。

Sink後のBigQueryのスキーマは次のようになります。

Workflowの定期実行設定方法

Workflowの一覧画面から、Workflowをクリックして移行する詳細画面(編集画面ではありません)の右上の、時計のアイコンをクリックします。

①のスケジュール設定をONにした上で、②の定期的に実行させたい時間を設定します。画像では、毎日日本時間23時に定期的にWorkflowを実行するような設定になっています。最後に③の設定をクリックし、定期実行の設定を保存します。