ReckonerでMySQLからBigQueryへ週次同期する方法
はじめに
本記事ではMySQLからBigQuery(以降、BQ)へデータを定期転送するための方法について理解を深めて行きたいと思います。本記事で想定しているシチュエーションとしては何らかのwebサービスを運営していて、そのデータをMySQLで管理しているものとします。
ただ、MySQLは大量のデータを頻度高く集計するといったことに不慣れなシステムなのでデータを活用してサービスを改善するためにはそれにあったデータベース(またはDWH)にデータをうつす必要が出てきます。
その代表としてBQを例にデータの転送方法を述べていきたいと思います。
このように書いている前提を少し補足するとBQはDWH(データウェアハウス)とよばれ、応答は遅いものの大容量データの集計に長けています。一方でMySQLはDWHに対してトランザクションデータベースに区分され、大容量データの処理は不得意ですが、素早い応答が可能といった特徴があります(この区分や理解は大雑把なものであり、細かくはKVSなどその他のデータベースとの対比が必要になりますが割愛します。)。
本記事で扱うMySQLやBQはそれぞれを代表するデータベースとなり、多くのサービスで扱われているものとなりますので、それぞれの環境で若干ことなるとしてもご参考になると思われます。
データを転送する
先にも書いた通りMySQLはサービスで利用されているものとなり、MySQLに対して頻度高くサービス外のクエリを実行するのはサービスがスローを起こすなどの原因になるので避けるべきものとなります。そこでMySQLのデータを適切な場所へ移してデータ分析をするとした場合にまず候補に上がってくる一つがBQになると思われます。その理由としてはDWHとして料金が安いといったことやGoogle Cloudにおける分析ツールなど連携しやすく有益なツールが豊富なことが挙げられます。
では、具体的にデータ転送の方法を上げていきます
1. MySQLからデータを出力させ、Google Cloud Storageにデータ転送してからBigQueryにUPする方法
SELECT * FROM Table INTO OUTFILE '/hoge/test.dmp'このようなデータダンプを行い(Tableというテーブルからデータを抽出し、/hoge以下にtest.dmpという名前でデータを出力)、出力結果をシェルなどにより任意のGCS領域に対してデータ転送を行います。
この際、セキュアな設計のためにMySQL側のファイル転送ユーザーに対してGCSへの書き込み権限をふったり、サーバ間におけるIPの参照許諾の設定をするなどが必要となってきます。また様々な要因でMySQLにデータがない、欠損した、または出力データが多くて一定時間以上データ出力にかかり、転送バッチが先に動いたなどのトラブルが起きるので回避のために再実行設定やデータが任意のところに起き終わったことを示す、フラグファイルといった物をおく(フラグファイルの名前決めも必要)と言った具合に多くの設定が必要になってきます。
2. embulkを使ったデータ転送方法
1の例ではシステム間のサーバ(IPなど)、実行ユーザなど細々とした設定を管理する必要があります。そのような煩雑な設定から定期実行から(限りなく)リアルタイムにデータを転送したいといった自由度をあげる仕組みとしてembulkを使った開発例を挙げます。embulk自体のインストールとデータの出力と入力のためのymlファイルと呼ばれる設定ファイルの設定が必要となります。
in:
type: mysql
user: {your mysql user}
password: {your mysql passowrd}
host: {your mysql host}
database: {your mysql database}
query: >-
select * from Table;
out:
type: bigquery
auth_method: {auth key}
json_keyfile: hoge.json
path_prefix: /tmp/ #例
file_ext: .csv.gz
source_format: CSV
project: {your project}
dataset: {your BQ dataset}
auto_create_table: true
table: {your BQ table}
schema_file: {your json file}
formatter: {type: csv, charset: UTF-8, delimiter: ',', header_line: false}
encoders:
- {type: gzip}このような設定ファイルとともに設定内容を決める必要があると理解できます。1より若干保守のことも考えると楽ではありますが手間が多いことが容易に想像できます。
3. Reckoner（ETL）を使ったデータ転送方法
Reckonerを使った接続についてご説明したいと思います。まず下の図が全体像になります。
単純に言えばMySQLとBQそれぞれの接続情報や対象のデータベースを設定してGUI上で連携関係を結ぶといったことをする。
この利点はまず設定ファイルの権限設定や実行ユーザーを気にする必要ないことです。またGUI上で一眼で連携関係がわかるところにあります。そのほかに中継させるディレクトリの管理が不要になり、結果としてデータを移したいだけなのに、他にアプリケーションを導入してそのアプリの管理をしなくてもいいことになります。
これだけ気にしなくてもいい物があると保守性が高いということが言えます。その他にデータを転送時にデータチェックや変形がしたい場合はReckonerに搭載されている変換や分析ツールをMySQLとBQの間に挟んで設定すればいいものになります。
定期実行については
エンジニアが少ない組織や、まずテストフェーズにおいてスピードを優先する場合などに有効な手段と言えます。
まとめ
本記事ではMySQLからBigQueryへのデータ転送の方法の代表例3つをあげさせていただきました。1<2<3となるにつれ設定が少なくなっていく(=保守性が高くなっていく)ことが理解できたかと思います(※gitなどへプルリクエストを送っての管理ができなくなるのでその点ではデメリットと感じる組織もあるとは思います。)。
参考にしていただき、ツールでのスピーディーな開発を検討してみてはいかがでしょうか。
Reckoner（レコナー）について
Reckoner（レコナー）は、スリーシェイクが提供するオンプレミスからクラウドサービスまで、様々なデータを連携させることが可能なクラウドネイティブなデータ連携プラットフォーム（Data Integration Platform as a Service）です。
データ整備に欠かせないETL/ELTやデータパイプラインなどをノンプログラミングで実現し、従来エンジニアが必要とされていた開発・運用コストを大幅に削減できます。
https://www.reckoner.io/
ETLツールについて詳しく知りたい、ETLツールの選び方を知りたいという方はこちらの「ETLツールとは？選び方やメリットを解説」をぜひご覧ください。
