#本記事について
普段よりRDSのMysqlにデータを蓄積しており、溜まったデータを分析したいと思いBigQueryにデータを移そうと考えた時の話です。
Embulkというデータ転送エンジンを用いると、簡単に構築可能で、差分更新などの仕組みにも対応しているということでEC2を使って色々な記事を参考に構築してみました。
自分への備忘も含め、これから導入を考えている人向けにも、ハンズオンでこれだけ見れば再現可能な記事を書こうと思います。
#インストール手順
インストール手順を画面ショット付きで解説しようと思います。
まず、今回構築するアーキテクトの構成はこんな感じにシンプルなものを作ります。
VPCのパブリックサブネット内にEC2インスタンスを作成し、その上にEmbulkをインストールします。
プライベートサブネット内にあるRDS MySQLとの通信は外部からは出来ず、
PC内の通信しか通さないようにセキュリティグループを設定します。
今回はRDSは既に構築済という前提で、EC2にEmblukをインストールし、
BigQueryにデータ転送するところまでをハンズオンでやっていきます。
##EC2の構築
AWSのマネジメントコンソールよりEC2インスタンスを起動します。
インスタンスサイズは無料利用枠が使える、t2.microを使用、マシンイメージはAmazon Linux 2を用いました。
今回はRDSと接続するので、プライベートネットワークでRDSを構築している場合は、RDSのインバンドルールにEC2のソースを追加しておきましょう。
##Embulkのインストール
インストールは以下2つのコマンドを入力するだけです。
すごく簡単です。
curl --create-dirs -o bin/embulk -L "http://dl.embulk.org/embulk-latest.jar"
chmod +x bin/embulk
パスも通しておきましょう
#.bashrcにパスを追加
sudo echo 'export PATH="$HOME/.embulk/bin:$PATH"' >> ~/.bashrc
#パスの設定を反映
source ~/.bashrc
EmbulkはJavaアプリケーションなので、Javaのインストールが必要です。
Javaがインストールされていないことを確認し、インストールをしてあげましょう。
## 確認
which java
-bash: $: コマンドが見つかりません
## インストール
sudo yum install java-1.8.0-openjdk
embulkのbundle用環境を作成します。
開発の要件やバージョンの違いに合わせて、bundleで環境を分けて管理するイメージです。
例では「rds-to-mysql-bq」のディレクトリを作成しています。
embulk mkbundle rds-mysql-to-bq
以下のようなディレクトリが作成されれば成功です。
rds-mysql-to-bq
├── embulk
│ ├── filter
│ │ └── example.rb
│ ├── input
│ │ └── example.rb
│ └── output
│ └── example.rb
└── Gemfile
##Embulkのプラグインのインストール
作成されたGemfileの最後に以下の2行を追加します。
vimなどを使ってファイルを編集すると良いでしょう。
# input mysql plugin
gem 'embulk-input-mysql'
# ouput bq plugin
gem 'embulk-output-bigquery'
Gemfileを編集したら、bundleコマンドでプラグインをインストールします
embulk bundle
##Embulkの設定ファイルを書く
次はEmbulkの設定ファイルを作成していきます。設定ファイルはyaml形式で書き、ここに転送元や転送先のスキーマ、転送方法、環境情報を保持します。1つのテーブルにつき1設定ファイルを作る形になります。Embulkでは、Liquidというテンプレートエンジンを使うため、ファイル名を{ファイル名}.yaml.liquidの形式で作成します。
今回はm_brandsというテーブルを対象にしたいので、m_brands.yaml.liquid というファイル名で作成します。
in:
type: mysql
host: {{ env.MYSQL_HOST_NAME }}
user: {{ env.MYSQL_USER }}
password: {{ env.MYSQL_PASSWORD }}
database: test_database
table: m_brands
out:
type: bigquery
auth_method: json_key
json_keyfile: ./credentials/bq_project.json
path_prefix: tmp
file_ext: .csv.gz
source_format: CSV
project: rds-to-biqquery-test
dataset: aws_mysql_tables
auto_create_table: true
table: m_brands
formatter: {type: csv, charset: UTF-8, delimiter: ',', header_line: false}
encoders:
- {type: gzip}
INとOUTのパラメータ詳細については各プラグインページを参考
embulk-input-mysql
embulk-output-bigquery
gcpのクレデンシャルが必要になるので、GCPコンソールから鍵を取得します。
- GCPコンソールの「IAMと管理」メニューの「サービスアカウント」ページに移動
- 「サービスアカウントを作成」よりアカウントを作成する
BigQueryの管理者権限を付与
- サービスアカンウントの作成後、詳細ページより「新しい鍵を作成」しJSON形式で保存
ローカルに落としたJSONファイルのクレデンシャル情報を.credential/bq_projcet.jsonとしてEC2にコピーします。
環境変数の設定
RDSのMySQLの接続情報を環境変数に設定します
export MYSQL_HOST_NAME=sample.xxxxxxxxx4.ap-northeast-1.rds.amazonaws.com #エンドポイント
export MYSQL_USER=root #ユーザー名
export MYSQL_PASSWORD=password #パスワード
printenv | grep MYSQL コマンドで設定内容を確認して問題なければ次へ続きます。
Previewを実行
本実行する前に、これまでの設定が問題なく出来ているかPreviewで確認してみましょう。
ちなみに、MySQLのテーブルに入っているデータはこんな感じです。
スマートフォンの機種と発売日などの情報が入っています。
以下のようなコマンドでPreviewを実行します。-bオプションはbundleフォルダのパスを指定します。
embulk preview -b . m_brands.yaml.liquid
いろいろメッセージが流れた後に、ターミナル上で結果セットの確認ができました！
良い感じですね！
もし、ここでエラーが出た場合は、後述のエラー対応集をご覧ください。
+-----------------+--------------------------------+----------------------+-------------------------+------------------------------------------------------+------------+---------------------------------------------------------------------+-------------------------+-------------------------+
| brand_id:string | brand_name:string | maker:string | relese_date:timestamp | spec:string | url:string | img_src:string | created_at:timestamp | updated_at:timestamp |
+-----------------+--------------------------------+----------------------+-------------------------+------------------------------------------------------+------------+---------------------------------------------------------------------+-------------------------+-------------------------+
| M0000000001 | W-ZERO3 | シャープ | 2005-12-05 00:00:00 UTC | 2005年12月上旬発売|3.7インチ|220g | | https://img1.kakaku.k-img.com/images/productimage/l/31201000830.jpg | 2021-04-04 18:10:13 UTC | 2021-04-04 18:10:13 UTC |
| M0000000002 | W-ZERO3 [es] | シャープ | 2006-07-27 00:00:00 UTC | 2006年7月27日発売|2.8インチ|175g | | https://img1.kakaku.k-img.com/images/productimage/l/31201000833.jpg | 2021-04-04 18:10:13 UTC | 2021-04-04 18:10:13 UTC |
| M0000000003 | X01HT | HTC | 2006-10-14 00:00:00 UTC | 2006年10月14日発売|2.8インチ|176g | | https://img1.kakaku.k-img.com/images/productimage/l/31102000865.jpg | 2021-04-04 18:10:06 UTC | 2021-04-04 18:10:06 UTC |
| M0000000004 | W-ZERO3 [es] Premium version | シャープ | 2006-11-16 00:00:00 UTC | 2006年11月16日発売|2.8インチ|175g | | https://img1.kakaku.k-img.com/images/productimage/l/31201000880.jpg | 2021-04-04 18:10:13 UTC | 2021-04-04 18:10:13 UTC |
| M0000000005 | X01NK | NOKIA | 2007-04-26 00:00:00 UTC | 2007年4月26日発売|2.8インチ|144g |
いざ実行
実行してBigQueryのテーブルに転送するまで通してみます。
runコマンドを実行して転送処理を行います。
embulk run -b . m_brands.yaml.liquid
いろいろメッセージが流れ始めます。。。
1分経たないくらいで全ての処理が終了。
BigQueryの画面を確認してみると...
転送されてたーーーーー！！！
完
#エラー対応集
Running Embulk version (0.9.23) does not match the installed embulk.gem version (0.10.31).
バージョン関連の問題のようです。エラーメッセージにもある通りGemfileを以下のように編集します
#gem 'embulk' ← コメントアウト
gem 'embulk', '< 0.10'
org.jruby.exceptions.RaiseException: Google::Apis::ClientError
BigQueryのロケーションを指定していない場合にエラーが発生するようです。
設定ファイルのOUT句にlocationパラメータを追加してあげれば解決しました。
～略～
out:
type: bigquery
auth_method: json_key
json_keyfile: ./credentials/bq_project.json
path_prefix: tmp
file_ext: .csv.gz
source_format: CSV
project: rds-to-biqquery-test
location: asia-northeast1 ← これを追加！！！
～略～
CSV processing encountered too many errors, giving up.
いくつか原因が考えられますが、私の場合は、改行文字を含む列が存在していたことが理由でした。
設定ファイルのOUT区にallow_quoted_newlinesパラメータを追加しtrueを設定すれば解決しました。
～略～
out:
type: bigquery
auth_method: json_key
json_keyfile: ./credentials/bq_project.json
path_prefix: tmp
file_ext: .csv.gz
source_format: CSV
project: rds-to-biqquery-test
allow_quoted_newlines:1 ←これを追加
location: asia-northeast1
～略～