EC2でEmbulkを使ってRDSのMysqlからBigQueryにデータ移行してみた

#本記事について

普段よりRDSのMysqlにデータを蓄積しており、溜まったデータを分析したいと思いBigQueryにデータを移そうと考えた時の話です。

Embulkというデータ転送エンジンを用いると、簡単に構築可能で、差分更新などの仕組みにも対応しているということでEC2を使って色々な記事を参考に構築してみました。

自分への備忘も含め、これから導入を考えている人向けにも、ハンズオンでこれだけ見れば再現可能な記事を書こうと思います。

#インストール手順

インストール手順を画面ショット付きで解説しようと思います。

まず、今回構築するアーキテクトの構成はこんな感じにシンプルなものを作ります。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/658983/513c8cc1-7472-ee78-657e-c2ade2edcc59.png)


VPCのパブリックサブネット内にEC2インスタンスを作成し、その上にEmbulkをインストールします。

プライベートサブネット内にあるRDS MySQLとの通信は外部からは出来ず、
PC内の通信しか通さないようにセキュリティグループを設定します。

今回はRDSは既に構築済という前提で、EC2にEmblukをインストールし、
BigQueryにデータ転送するところまでをハンズオンでやっていきます。

##EC2の構築

AWSのマネジメントコンソールよりEC2インスタンスを起動します。
インスタンスサイズは無料利用枠が使える、t2.microを使用、マシンイメージはAmazon Linux 2を用いました。

今回はRDSと接続するので、プライベートネットワークでRDSを構築している場合は、RDSのインバンドルールにEC2のソースを追加しておきましょう。

##Embulkのインストール
インストールは以下2つのコマンドを入力するだけです。
すごく簡単です。

```bash
curl --create-dirs -o bin/embulk -L "http://dl.embulk.org/embulk-latest.jar"
chmod +x bin/embulk
```

パスも通しておきましょう

```bash
#.bashrcにパスを追加
sudo echo 'export PATH="$HOME/.embulk/bin:$PATH"' >> ~/.bashrc

#パスの設定を反映
source ~/.bashrc
```

EmbulkはJavaアプリケーションなので、Javaのインストールが必要です。
Javaがインストールされていないことを確認し、インストールをしてあげましょう。

```bash
## 確認
which java
-bash: $: コマンドが見つかりません

## インストール
sudo yum install java-1.8.0-openjdk
```

embulkのbundle用環境を作成します。
開発の要件やバージョンの違いに合わせて、bundleで環境を分けて管理するイメージです。
例では「rds-to-mysql-bq」のディレクトリを作成しています。

```bash
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などを使ってファイルを編集すると良いでしょう。

```bash
# input mysql plugin
gem 'embulk-input-mysql'

# ouput bq plugin
gem 'embulk-output-bigquery'
```

Gemfileを編集したら、bundleコマンドでプラグインをインストールします

```bash
embulk bundle
```

##Embulkの設定ファイルを書く

次はEmbulkの設定ファイルを作成していきます。設定ファイルはyaml形式で書き、ここに転送元や転送先のスキーマ、転送方法、環境情報を保持します。1つのテーブルにつき1設定ファイルを作る形になります。Embulkでは、Liquidというテンプレートエンジンを使うため、ファイル名を`{ファイル名}.yaml.liquid`の形式で作成します。
今回はm_brandsというテーブルを対象にしたいので、m_brands.yaml.liquid というファイル名で作成します。

```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](https://github.com/embulk/embulk-input-jdbc/tree/master/) 
[embulk-output-bigquery](https://github.com/embulk/embulk-output-bigquery)

gcpのクレデンシャルが必要になるので、GCPコンソールから鍵を取得します。

1. GCPコンソールの「IAMと管理」メニューの「サービスアカウント」ページに移動
2. 「サービスアカウントを作成」よりアカウントを作成する
    ![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/658983/04fc61f3-6a3a-a796-1276-b959f214f24b.png)

BigQueryの管理者権限を付与
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/658983/307ce175-9bc2-f51c-05f7-8b171db4b6d4.png)

3. サービスアカンウントの作成後、詳細ページより「新しい鍵を作成」しJSON形式で保存
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/658983/59b41083-bc44-7982-551b-889841577aa7.png)

ローカルに落としたJSONファイルのクレデンシャル情報を`.credential/bq_projcet.json`としてEC2にコピーします。

## 環境変数の設定
RDSのMySQLの接続情報を環境変数に設定します

```bash
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のテーブルに入っているデータはこんな感じです。

スマートフォンの機種と発売日などの情報が入っています。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/658983/1a88a7fc-bb70-f97f-ef7d-74a2d2c7a9a2.png)

以下のようなコマンドでPreviewを実行します。-bオプションはbundleフォルダのパスを指定します。


```bash
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コマンドを実行して転送処理を行います。

```bash
embulk run -b . m_brands.yaml.liquid

```

いろいろメッセージが流れ始めます。。。
1分経たないくらいで全ての処理が終了。
BigQueryの画面を確認してみると...

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/658983/20e22ccf-f35e-9db3-7dfb-29db84c9d241.png)

転送されてたーーーーー！！！
完

#エラー対応集

### 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`パラメータを追加してあげれば解決しました。

```bash
～略～
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`を設定すれば解決しました。

```bash
～略～
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
～略～
```