概要
オンプレからクラウドに移行(マイグレーション)した場合や、クラウド上でセルフホスティングしていたDBをマネージドDBに移行した場合に問題となるのが「データは欠損なく移行されたか」です。
データの数が少なければプログラミングで差分チェックなどを書けますが、億単位の数であったりTB単位のサイズといったオーダーでは自前でプログラミングして差分を出すのは現実的では有りません。
そこで分散処理に強いBigQueryを利用することで、大規模データのマイグレーションにおいても完全性のチェックが実現できます。
環境
- mongoexport 100.5.0
- gsutil 5.5
- bq v2.0.72
アーキテクチャ図
アーキテクチャとしては以下です。今回データベースはMongoDBを想定しています。
手順
EL
src(マイグレーション元)となるDBとtarget(マイグレーション先)となるDBそれぞれでmongoexportでExtract(抽出)し、GCSを経由してBigQueryへLoad(書き込み)します。
mongoexport
$ mongoexport --uri="mongodb://example.com:27017/test" \ --collection=users --out=users.json
転送量を減らすために圧縮しておきます。
$ ls -lh users.json -rw-r--r-- 1 jun06t jun06t 468M Aug 2 04:15 devices.json $ gzip users.json $ ls -lh users.json.gz -rw-r--r-- 1 jun06t jun06t 145M Aug 2 04:15 devices.json.gz
※BigQueryがimportする際のgzipの最大サイズは4GBまでという制限があるので、それを超える場合はjsonファイルのままアップロードします。
スキーマ用意
BigQueryへ入れる際のスキーマを用意しておきます。
mongodb_schema.json
としておきます。
[ { "name":"_id", "type": "STRING" }, { "name":"name", "type": "STRING" }, { "name":"createdAt", "type": "INT64" }, { "name":"updatedAt", "type": "INT64" } ]
ref: スキーマの指定 | BigQuery | Google Cloud
GCS
GCSへアップロードします。
$ gsutil mv ./users.json.gz gs://MY_BUCKET/mongoexport/users.json.gz
BigQuery
BigQueryへ書き込みます。
先程作成したスキーマと、GCSアップロードしたファイルを指定します。
$ bq load \ --source_format=NEWLINE_DELIMITED_JSON \ --max_bad_records=999999 \ --ignore_unknown_values=true \ --encoding=UTF-8 \ --replace \ "MY_DATASET.users_before" \ "gs://MY_BUCKET/mongoexport/devices.json.gz" \ "mongodb_schema.json"
完全性チェック
BigQueryを用いた完全性のチェックです。
以下のようにいくつかのパターンで差分を出します。
- データの欠損をチェックしたい
- データの更新も含めてチェックしたい
- どちらか一方にしかないデータを網羅したい
データの欠損をチェックしたい
データの欠損をチェックしたい場合はprimary key(MongoDBなら_id
)のみで大丈夫です。
SELECT _id FROM `MY_PROJECT.MY_DATASET.users_before` EXCEPT DISTINCT SELECT _id FROM `MY_PROJECT.MY_DATASET.users_after`
上記はbeforeにあってafterにないをチェックしています。
件数が0件なので欠損がないことが確認できます。
元のフィールドを埋めて差分CSVをそのままmongoimportしたい場合はLEFT JOINしてください。
WITH left_data AS ( SELECT _id FROM `MY_PROJECT.MY_DATASET.users_before` EXCEPT DISTINCT SELECT _id FROM `MY_PROJECT.MY_DATASET.users_after` ) SELECT left_data._id AS _id, B.name AS name, B.createdAt AS createdAt, B.updatedAt AS updatedAt, FROM left_data LEFT OUTER JOIN `MY_PROJECT.MY_DATASET.users_before` AS B ON left_data._id = B._id;
データの更新も含めてチェックしたい
更新をチェックしたいフィールドを指定すれば良いです。以下では全フィールドをチェックしています。
SELECT * FROM `MY_PROJECT.MY_DATASET.users_before` EXCEPT DISTINCT SELECT * FROM `MY_PROJECT.MY_DATASET.users_after`
どちらか一方にしかないデータを網羅したい
- tableAにあってtableBにない
- tableBにあってtableAにない
の両方を出したい場合はUNION ALL
を使って結合します。
( SELECT * FROM `MY_PROJECT.MY_DATASET.users_before` EXCEPT DISTINCT SELECT * FROM `MY_PROJECT.MY_DATASET.users_after` ) UNION ALL ( SELECT * FROM `MY_PROJECT.MY_DATASET.users_after` EXCEPT DISTINCT SELECT * FROM `MY_PROJECT.MY_DATASET.users_before` )
その他
Extractしている間に追加されたデータはどうするか
メンテナンス期間を設けずチェックする場合は、Extractを開始した時間を前もって記録しておきます。そして
- ExtractしたデータはBigQueryでチェック
- Extract以降に追加されたデータはデータ量が少ないのでクエリでチェック
といった形で分ければ良いでしょう。
スキーマがネストしていたらどうすればいいか
例えば
{ _id: 'id001', _id: { first: "John", last: "Smith" }, createdAt: Long("1506749962"), updatedAt: Long("1506749962") }
のようなドキュメントの場合どういったスキーマにすべきかですが、type: RECORD
を使えば実現できます。
[ { "name":"_id", "type": "STRING" }, { "name":"name", "type": "RECORD", "fields": [ { "name":"first", "type": "STRING" }, { "name":"last", "type": "STRING" } ]}, { "name":"createdAt", "type": "INT64" }, { "name":"updatedAt", "type": "INT64" } ]
ただしこのままだとBigQueryのクエリでEXCEPT DISTINCTが使えず、以下のエラーが発生します。
Column 2 in EXCEPT DISTINCT has type that does not support set operation comparisons
そこでTO_JSON_STRING
することで差分がチェックできます。
SELECT TO_JSON_STRING(b) FROM `MY_PROJECT.MY_DATASET.users_before` b EXCEPT DISTINCT SELECT TO_JSON_STRING(a) FROM `MY_PROJECT.MY_DATASET.users_after` a
差分をimportしたい
BigQueryでは結果をCSVファイルとして出力できるので、それをmongoimportでそのままimportできます。
$ mongoimport --host=mongodb://example.com:27017 --db=test \ --collection=users --type=csv --headerline \ users.csv
まとめ
大規模データのマイグレーションにおいて課題となりがちな完全性のチェック方法としてBigQueryを活用する手法を紹介しました。
参考
- MongoDB to BigQuery - Stack Overflow
- BigQuery Table Comparison. Introduction | by Mark Scannell | Google Cloud - Community | Medium
- sql - efficient way to compare two tables in bigquery - Stack Overflow
- BigQueryで2つのテーブルの差分を求める方法 - Qiita
- sql - Generic comparison method for two tables in BigQuery if tables contain STRUCT type - Stack Overflow