Embulk
ETL

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

ログ基盤をそろそろ整備しないといけなくなりそうな今日この頃で

スケーリングを管理しなくては行けない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ページにいきます

GCLOUD IAM


  • 秘密鍵を作成します


    • jsonフォーマットで作成してください



GCLOUD IAM CREATE SECRET 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-output-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_netwroks"
tty: true
stdin_open: true
volumes:
- "./:/work"

networks:
service_netwroks:



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

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

データが投入されました

BigQuery UI


最後に

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