embulkでMySQLからBigQueryにデータロードをしてみるチュートリアル #Embulk

ログ基盤をそろそろ整備しないといけなくなりそうな今日この頃で
スケーリングを管理しなくては行けないDWHのMySQLからBigQueryに移そう。
と思い調べて見たらembulkが便利そうだったので試してみました。

今回やること

Dockerでembulkコンテナを立てる
embulkでMySQLのテーブルデータをExtract(抽出)してBigQueryにLoad(ロード)する

参考にした記事

環境

macOS High Siera

事前準備が必要なもの

docker
- 入れていない人は brew cask install docker と打とう
docker-compose
- 入れていない人は brew install docker-compose と打とう
google-cloud-sdk
- 入れていない人は brew cask install google-cloud-sdk と打とう

Extract(抽出)するデータを確認

今回はMySQLに格納されているこんなデータをBigQueryに入れます(PVとxpathがセットで入ってる日別のデータ)

show create table hoge_pvs\G
*************************** 1. row ***************************
       Table: hoge_pvs
Create Table: CREATE TABLE `hoge_pvs` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `path` varchar(255) NOT NULL,
  `pv` int(11) NOT NULL DEFAULT '0',
  `stat_date` date NOT NULL,
  `created_at` datetime NOT NULL,
  `updated_at` datetime NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `pv_path_stat_date_indexes` (`path`,`stat_da`)
) ENGINE=InnoDB AUTO_INCREMENT=5170235 DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC COMMENT='hogeのpvの日別データ\n（ 日別xpath別 ）'
1 row in set (0.00 sec)

サンプルデータ

良しなにデータ投入してください

hoge_pvs.tsv

id  path    pv  stat_date
1   /12898  7588    2015-02-26
2   /11709  5999    2015-02-26
3   /12896  5367    2015-02-26
4   /10617  5168    2015-02-26
5   /12864  3773    2015-02-26
6   /19749  3184    2015-02-26
7   /13381  3080    2015-02-26
8   /13388  2776    2015-02-26
9   /11714  2528    2015-02-26
10  /10436  1963    2015-02-26

Google Cloud SDKを設定する

既存の設定で問題がなければ読み飛ばして下さい

変数を確認する

組織(organization)がない場合はGCLOUD_ORGANIZATIONは設定しなくて良いです

export GCLOUD_PROJECT_ID={your project id}
export GCLOUD_ACCOUNT={your google account}
export GCLOUD_ORGANIZATION={your google organization}

確認

cat << ETX
  下記設定でGoogle Cloud の設定を行います

  GCLOUD_ACCOUNT      ${GCLOUD_ACCOUNT}
  GCLOUD_PROJECT_ID   ${GCLOUD_PROJECT_ID}
  GCLOUD_ORGANIZATION ${GCLOUD_ORGANIZATION}
ETX

GCloud SDKを設定する

# Google Cloudにログインする
gcloud auth login
# 各種設定
gcloud config set compute/region asia-northeast1
gcloud config set compute/zone asia-northeast1-a
gcloud config set core/account $GCLOUD_ACCOUNT
gcloud config set core/project $GCLOUD_PROJECT_ID

if [ -z ${GCLOUD_ORGANIZATION} ]; then
  gcloud projects create $GCLOUD_PROJECT_ID --organization=$GCLOUD_ORGANIZATION --name=$GCLOUD_PROJECT_ID
else
  gcloud projects create $GCLOUD_PROJECT_ID --name=$GCLOUD_PROJECT_ID
fi

# Gcloud Permissionを設定する
gcloud services enable bigquery-json.googleapis.com

GCloudの設定を確認する

$ gcloud config list

[compute]
region = asia-northeast1
zone = asia-northeast1-a
[core]
account = {your account}
disable_usage_reporting = True
project = {your project}

GCloud IAMからサービスアカウントを払い出してjson形式の秘密鍵を作成する

GCloud IMAページにいきます

秘密鍵を作成します
- jsonフォーマットで作成してください

BigQueryのデータセットを定義する

.bigqueryrc を作成して対象のプロジェクトとデータセットを定義します

.bigqueryrc

project_id = {your project}
dataset_id = test_embulk

データセットを作成する

bq mk test_embulk

ここまでがGCloudで必要な設定

embulkイメージを作成する

コピペして下さい

Dockerfile

FROM openjdk:8-jre-stretch

ENV LC_ALL ja_JP.UTF-8

# http://docs.docker.jp/engine/articles/dockerfile_best-practice.html
# 日本語化も行っている
RUN apt-get update && apt-get install -y \
    build-essential \
    libpq-dev nodejs \
    locales \
    fonts-ipafont-gothic \
    fonts-ipafont-mincho \
 && apt-get clean \
 && rm -rf /var/lib/apt/lists/* \
 && echo "ja_JP.UTF-8 UTF-8" > /etc/locale.gen \
 && locale-gen ja_JP.UTF-8 \
 && /usr/sbin/update-locale LANG=ja_JP.UTF-8

ENV MYSQL_MAJOR 5.6
# mysql 関連
WORKDIR /tmp
RUN set -ex; \
    key='A4A9406876FCBD3C456770C88C718D3B5072E1F5' \
 && export GNUPGHOME="$(mktemp -d)" \
 && gpg --batch --keyserver hkp://keyserver.ubuntu.com:80 --recv-keys "$key" \
 && gpg --export "$key" > /etc/apt/trusted.gpg.d/mysql.gpg \
 && rm -r "$GNUPGHOME" \
 && apt-key list > /dev/null \
 && echo "deb http://repo.mysql.com/apt/debian/ jessie mysql-${MYSQL_MAJOR}" > /etc/apt/sources.list.d/mysql.list \
 && apt-get clean \
 && apt-get update \
 && apt-get install -y mysql-client \
 && rm -rf /var/lib/apt/lists/*

ENV DATABASE_USER=root \
    DATABASE_PASSWORD=root \
    DATABASE_HOST=mysql

# Embulk 本体をインストールする
RUN wget -q https://dl.embulk.org/embulk-latest.jar -O /bin/embulk \
  && chmod +x /bin/embulk

# 使いたいプラグインを入れる
RUN embulk gem install embulk-input-mysql \
 && embulk gem install embulk-output-bigquery

WORKDIR /work

docker-compose.ymlを用意する

コピペして下さい

docker-compose.yml

version: '3'

services:
  embulk:
    image: "embulk:latest"
    build: "."
    container_name: "embulk"
    networks:
      - "service_networks"
    tty: true
    stdin_open: true
    volumes:
      - "./:/work"

networks:
  service_networks:

イメージのビルド && コンテナを立ち上げる

$ docker-compose up -d
.
.
.
Starting embulk                 ... done



$ docker-compose ps

       Name                     Command             State           Ports
----------------------------------------------------------------------------------
etl                   bash                          Up

config.ymlを定義する

embulkが MySQL => BigQueryをするための設定ファイルです

in がMySQL側で
out がBigQuery側です
config.yml中の
- out.projectはBigQueryのプロジェクトIDを定義して下さい bq mk xxxx　で作成したもの

in:
  type: mysql
  user: {your mysql user}
  password: {your mysql passowrd}
  host: {your mysql host}
  database: {your mysql database}
  query: >-
    select id, path, pv, stat_date from hack_pvs order by id  limit 10;
out:
  type: bigquery
  auth_method: json_key
  # 先程GCloud で作成したサービスアカウントの秘密鍵のファイルパスを指定してください
  json_keyfile: hoge.json
  path_prefix: /tmp/ # 一時ファイル作成場所
  file_ext: .csv.gz
  source_format: CSV
  project: {your project}
  dataset: test_embulk
  auto_create_table: true
  table: hoge_pvs
  schema_file: schema.json
  formatter: {type: csv, charset: UTF-8, delimiter: ',', header_line: false}
  encoders:
  - {type: gzip}

BigQuery側のSchemeをjsonで定義する

schema.json

[
  {"name": "id",   "type": "INTEGER", "mode": "required"},
  {"name": "path", "type": "STRING", "mode": "required"},
  {"name": "pv", "type": "STRING", "mode": "required"},
  {"name": "stat_date", "type": "TIMESTAMP", "mode": "required"}
]

確認

embulkの定義があってるか確認します

$ docker-compose exec embulk sh -c 'embulk preview config.yml'


2019-02-21 08:57:34.738 +0000: Embulk v0.9.15
2019-02-21 08:57:35.280 +0000 [WARN] (main): DEPRECATION: JRuby org.jruby.embed.ScriptingContainer is directly injected.
.
.

+---------+-------------+---------+-------------------------+
| id:long | path:string | pv:long |     stat_date:timestamp |
+---------+-------------+---------+-------------------------+
|       1 |      /12898 |   7,588 | 2015-02-26 00:00:00 UTC |
|       2 |      /11709 |   5,999 | 2015-02-26 00:00:00 UTC |
|       3 |      /12896 |   5,367 | 2015-02-26 00:00:00 UTC |
|       4 |      /10617 |   5,168 | 2015-02-26 00:00:00 UTC |
|       5 |      /12864 |   3,773 | 2015-02-26 00:00:00 UTC |
|       6 |      /19749 |   3,184 | 2015-02-26 00:00:00 UTC |
|       7 |      /13381 |   3,080 | 2015-02-26 00:00:00 UTC |
|       8 |      /13388 |   2,776 | 2015-02-26 00:00:00 UTC |
|       9 |      /11714 |   2,528 | 2015-02-26 00:00:00 UTC |
|      10 |      /10436 |   1,963 | 2015-02-26 00:00:00 UTC |
+---------+-------------+---------+-------------------------+

実行

$ docker-compose exec embulk sh -c 'embulk run config.yml'

BigQuery UIから確認する

データが投入されました

最後に

embulk設定だけで簡単にデータの移動ができてマジ感動します。。。。。