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

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

## 今回やること

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


## 参考にした記事

* [公式 embulk ドキュメント](https://www.embulk.org/docs/)
* [Embulkを使って、RDSのデータをBigQueryへロード](https://qiita.com/ritukiii/items/d65912e22992d9278360)

## 環境

* 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ページにいきます

![GCLOUD IAM](https://user-images.githubusercontent.com/1619084/53150908-16f62300-35f5-11e9-8866-5be0b53d4f46.png)

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

![GCLOUD IAM CREATE SECRET JSON](https://user-images.githubusercontent.com/1619084/53151129-b0bdd000-35f5-11e9-81e3-e3c7bd942dd2.png)


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

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

```shell:.bigqueryrc
project_id = {your project}
dataset_id = test_embulk
```

### データセットを作成する

```
bq mk test_embulk
```

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

## embulkイメージを作成する

コピペして下さい

```Dockerfile: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を用意する

コピペして下さい

```yaml: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で定義する

```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から確認する

データが投入されました


![BigQuery UI](https://user-images.githubusercontent.com/1619084/53156536-f92fba80-3602-11e9-8cf4-8e7bcf54ca94.png)



## 最後に

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