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

11990 ワード

BigQuery RDS Embulk ETL AWS AWS テキストリンク

本記事について

普段より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_USER = 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
～略～

Author And Source

この問題について(EC2でEmbulkを使ってRDSのMysqlからBigQueryにデータ移行してみた), 我々は、より多くの情報をここで見つけました https://qiita.com/knakazono10111/items/0bff34a64cf85cc4bcfd

著者帰属：元の著者の情報は、元のURLに含まれています。著作権は原作者に属する。

Content is automatically searched and collected through network algorithms . If there is a violation . Please contact us . We will adjust (correct author information ,or delete content ) as soon as possible .