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

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

今回やること

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

参考にした記事

• 公式 embulk ドキュメント
• Embulkを使って、RDSのデータをBigQueryへロード

環境

• 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設定だけで簡単にデータの移動ができてマジ感動します。。。。。