embulkでBigQueryからデータベースにデータを移行した話

はじめに

新卒2年目の岡崎です。最近、embulkでBigQueryからデータベースにデータを移行しました。その時のことを備忘録として記事にします。

データ移行を行った理由

今までは、過去のデータをGoogleが提供していたUAから見て、分析できました。しかし、UAはGA4に移行を完了するにあたり、過去のデータを見れなくなりました。過去のデータが自社のデータベースにないと、毎回BigQueryを使ってデータを見なくてはいけません。この問題を解決するため、embulkを使ってBigQueryからデータベースにデータを移行します。

前提

今回はembulkを使い、BigQueryからデータベースへデータを移行することを目的とします。また、環境構築はdockerでサクッと行いたいので、dockerを使える環境の準備をしてください。

実装

実装の紹介をします。

DockerFile

ここでは、主にembulkのインストールをします。

embulkの公式ドキュメントによると、Java8が推奨されていますが、今回の実装であればJava21の環境でも問題なく動きました。

FROM openjdk:21-jdk

WORKDIR /embulk

# jRubyのインストール
RUN curl --create-dirs -o "./jruby-complete-9.4.5.0.jar" -L "https://repo1.maven.org/maven2/org/jruby/jruby-complete/9.4.5.0/jruby-complete-9.4.5.0.jar"
RUN chmod +x ./jruby-complete-9.4.5.0.jar

# embulkのインストール
RUN curl --create-dirs -o "./embulk-0.11.4.jar" -L "https://dl.embulk.org/embulk-0.11.4.jar"
RUN chmod +x ./jruby-complete-9.4.5.0.jar

ENV PATH="/root/.embulk/bin:${PATH}"

COPY ./embulk/embulk.properties /root/.embulk/embulk.properties
RUN java -jar embulk-0.11.4.jar gem install embulk -v 0.11.4

# プラグインのインストール
RUN java -jar embulk-0.11.4.jar gem install embulk-output-mysql

ENV GEM_HOME="/root/.embulk/lib/gems"
RUN java -Xmx2g -jar embulk-0.11.4.jar gem install embulk-input-bigquery

RUN java -jar embulk-0.11.4.jar gem install liquid -v 5.5.0

COPY embulk .

RUN chmod +x embulk.sh

ENTRYPOINT ["sh", "embulk.sh"]

JRubyのインストール

今回の場合はプラグインのインストールでJRubyのインストールが必要でしたが、不要な場合は飛ばしてください。

RUN curl --create-dirs -o "./jruby-complete-9.4.5.0.jar" -L "https://repo1.maven.org/maven2/org/jruby/jruby-complete/9.4.5.0/jruby-complete-9.4.5.0.jar"
RUN chmod +x ./jruby-complete-9.4.5.0.jar

embulk.properties

JRubyのプロパティを設定します。

jruby=file:///embulk/jruby-complete-9.4.5.0.jar

embulkのインストール

今回は最新のバージョンである0.11.4を指定しました。

RUN curl --create-dirs -o "./embulk-0.11.4.jar" -L "https://dl.embulk.org/embulk-0.11.4.jar"
RUN chmod +x ./jruby-complete-9.4.5.0.jar

ENV PATH="/root/.embulk/bin:${PATH}"

プラグインのインストール

RUN java -jar embulk-{embulkのバージョン}.jar gem install {インストールしたいプラグイン} -v {バージョン}

今回は、以下のプラグインをインストールしています。

用途によって、使用したいプラグインは違うと思います。必要に応じて、プラグインのインストールをしてください。

liquidのインストール

RUN java -jar embulk-0.11.4.jar gem install liquid -v 5.5.0

liquidをインストールすることで、変数を使用することができます。

embulk.sh

embulkのシェルスクリプトの実装例を紹介します。

export TZ="Asia/Tokyo"

current_hour=$(date +%H)
today=$(date +%Y%m%d)

export REPORT_DATE=$today

java -jar embulk-0.11.4.jar run bigquery_to_mysql_report_daily_session_raw.yml.liquid

echo "データを入れることに成功しました"

以下のコマンドで、embulkを実行します。

java -jar embulk-{embulkのバージョン}.jar run ファイル名

bigquery_to_mysql_report_daily_session_raw.yml.liquid

今回は一例として、BigQueryから日別のセッション数を取得し、それをデータベースに挿入する方法を紹介します。

in:
  type: bigquery
  project: 'sample'
  keyfile: ./keyfile.json
  sql: |
    SELECT
      DATE(timestamp_micros(event_timestamp), 'Asia/Tokyo') AS report_date,
      'SERVICE' AS service,
      platform,
      device.category AS device_category,
      CASE
        WHEN device.operating_system IS NULL THEN 'UNDEFINED'
        ELSE device.operating_system
      END AS device_os,
      COUNT(*) AS session_count
    FROM
      `analytics_153613109.events_*`
    WHERE
      _TABLE_SUFFIX = '{{ env.REPORT_DATE }}'
      AND event_name = 'session_start'
    GROUP BY
      report_date, platform, device.category, device.operating_system
    ;

  columns:
    - {name: report_date, type: string}
    - {name: platform, type: string}
    - {name: device_category, type: string}
    - {name: device_os, type: string}
    - {name: user_count, type: long}

out:
  type: mysql
  host: test
  user: test
  password: password
  database: test_db
  table: report_daily_active_user_raw
  mode: merge
  column_options:
    report_date: {value_type: string, timestamp_format: '%Y-%m-%d'}

embulkでは、inとoutに分けて実装をしました。

今回のin句では、BigQueryから日別のセッション数を取得しました。この時の./keyfile.jsonには、BigQueryからデータを取得するためのクレデンシャル情報が書かれています。

また、out句では、MySQLへデータを入れるための実装を行っています。必要に応じて、パスワードなどは環境変数に置き換えてください。

docker-compose.yaml

docker-compose.yamlの実装例です。

name: 'sample_service'
services:
  embulk:
    build:
      context: .
      dockerfile: Dockerfile
    image: latest
    tty: true

最後に、docker-composeを実行し、実際にデータが入ることが確認できたら、実装完了です。

最後に

今回は、embulkでBigQueryからデータベースにデータを移行しました。embulkを使うことで、簡単にデータ移行できます。機会があったら、ぜひ皆さんも使ってみてください。

参考文献

www.embulk.org

zenn.dev