embulkでMySQLにコピーする時にハマったこと

概要

embulk+digdagでデータベースのコピーを行い、コピーしたデータベースをmetabaseで閲覧できるようにしたデータ分析基盤を構築しました。

その際embulkを使ってMySQLにデータをコピーする時にいろいろハマったので、その解決方法を紹介します。

embulkとは？

今回利用したembulkとは、以下のように紹介されています。

Embulk(エンバルク)とは、オープンソースの並列データ転送フレームワークです。
「fluentd」のバッチ版のようなバルク処理に特化したプラグインベースのデータローダーです。
大規模データセットのバルクインポートを行えます。

引用元：https://www.ossnews.jp/oss_info/Embulk

詳細は他のサイトに譲りますが、このようにembulkとはデータ転送を行うためのフレームワークで、これを利用することでymlファイルを作成することでデータベースへのコピーが簡単に行うことができます。

環境

コピー元とコピー先

今回はコピー元・コピー先ともMySQLです。cronではなく、digdagを利用してembulkを動かしています。

なお元々AWS上に本番環境があり本番用DBではAmazon RDS for MySQLを使っていたため、コピー先も同様にしました。データ分析基盤を作る時は本来ならRedShiftのようなデータウェアハウス(DWH)へのコピーが望ましいかもしれません。しかしデータ量もまだ少なく短時間で構築する必要があったため、コピー先もMySQLにしました。

• コピー元: MySQL(RDS for MySQL)
• コピー先: MySQL(RDS for MySQL)

利用プラグイン

コピー元・コピー先ともにMySQLなので、以下のプラグインを使いました。

• embulk-input-mysql
  • バージョン: 0.9.0
• embulk-output-mysql
  • バージョン: 0.8.0

さらにデータの民主化を目指し、個人情報をマスクして安全にデータを扱えるように以下のプラグインも利用しました。ただこのプラグインは本ページでの内容には関係しないため、説明は割愛します。

• embulk-filter-mask
  • バージョン: 0.2.1

embulkのymlファイル

embulkでコピーするにあたって、最初に以下のようなymlファイルを作成しました。これを元にして、以下からの説明を行います。

test.ymlファイル

in:
  type: mysql
  user: (DB接続用ユーザ)
  password: (DB接続用ユーザのパスワード)
  database: (DB名)
  host: (DBホスト)
  table: (コピー元のテーブル名)
  select: "*"

out:
  type: mysql
  user: (DB接続用ユーザ)
  password: (DB接続用ユーザのパスワード)
  database: (DB名)
  host: (DBホスト)
  table: (コピー先のテーブル名)
  select: "*"

日本語のデータが文字化けした

embulk runでコピーを行ったところ、日本語が入ったカラムのデータが文字化けしてしまいました。

原因

文字コードに関する設定だったので、テーブル設定を見てみると以下のようにDEFAULT CHARSET=utf8となっていたのでutf8で作られることがわかりました。

mysql> show create table aaaaaaa\G
*************************** 1. row ***************************
       Table: aaaaaaa
Create Table: CREATE TABLE `aaaaaaa` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  `created_at` datetime NOT NULL,
  `updated_at` datetime NOT NULL,
  `start_date` date DEFAULT NULL,
  `end_date` date DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=16 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

mysql>

解決方法①

コピー先DBでもutf8を利用するため、ymlファイルは変更せずに、MySQLの設定の一つであるcharacter_set_serverをutf8と設定します。（character_set_serverのデフォルト値は空白です）

なおRDS for MySQLを利用しているので、設定ファイル(my.cnf)ではなくRDSインスタンスに設定したパラメータグループを変更しました。

解決方法②(2020/11/24追記)

embulkのoptionsに{ characterEncoding: UTF-8 }をつけても文字化けを回避できます。

datetime型カラムの時間が9時間ズレた

datetime型のカラムを含むテーブルをコピーしたところ、以下のように９時間ズレてしまいました。(created_atとupdated_atがdatetime型カラム)

• コピー元テーブル

id	name	created_at	updated_at
1	A社	2017-09-28 02:56:36	2017-12-19 03:44:54

• コピー先テーブル

id	name	created_at	updated_at
1	A社	2017-09-28 11:56:36	2017-12-19 12:44:54

原因

embulkを動かすサーバ(EC2)側のTimezoneはJSTなのに、DBのTimezoneがUTCのためでした。

解決方法

コピー元とコピー先でデータが変化しないように、以下のようにymlファイルのin側、out側にoptions: { useLegacyDatetimeCode: false, serverTimezone: UTC }を設定しました。

embulkで使うymlファイル

in:
  type: mysql
  user: (DB接続用ユーザ)
  password: (DB接続用ユーザのパスワード)
  database: (DB名)
  host: (DBホスト)
  options: { useLegacyDatetimeCode: false, serverTimezone: UTC }
  table: (datetime型カラムを含むテーブル)
  select: "*"

out:
  type: mysql
  user: (DB接続用ユーザ)
  password: (DB接続用ユーザのパスワード)
  database: (DB名)
  host: (DBホスト)
  options: { useLegacyDatetimeCode: false, serverTimezone: UTC }
  table: (datetime型カラムを含むテーブル)
  select: "*"

date型カラムの日付データが前日になった

これが一番解決に苦労した問題です。

embulkによるコピーをしたら、以下のようにdate型カラムのデータが1日ズレてしまいました。(start_dateとend_dateがdate型カラム)
もちろん前に紹介したoptions: { useLegacyDatetimeCode: false, serverTimezone: UTC }は設定済です。

• 元のデータ

id	name	start_date	end_date
1	BBBBB	2018-02-13	2018-12-31

• embulk runした後のコピー先のデータ

id	name	start_date	end_date
1	BBBBB	2018-02-12	2018-12-30

→日付が前日になっている！

原因

あらためてembulk previewの結果を見ました。

preview実行時のログ(ymlファイルをtest.ymlとしています)

$ embulk preview test.yml
2018-03-11 13:56:27.836 +0900: Embulk v0.9.4

********************************** INFORMATION **********************************
  Join us! Embulk-announce mailing list is up for IMPORTANT announcement such as
    compatibility-breaking changes and key feature updates.
  https://groups.google.com/forum/#!forum/embulk-announce
*********************************************************************************

2018-03-11 13:56:28.659 +0900 [INFO] (main): Started Embulk v0.9.4
2018-03-11 13:56:31.108 +0900 [INFO] (0001:preview): Gem's home and path are set by default: "/opt/digdag/.embulk/lib/gems"
2018-03-11 13:56:32.022 +0900 [INFO] (0001:preview): Loaded plugin embulk-input-mysql (0.9.0)
(略)
2018-03-11 13:56:32.641 +0900 [INFO] (0001:preview): > 0.00 seconds
+---------+-------------+-------------------------+-------------------------+
| id:long | name:string |  start_date:timestamp   |   end_date:timestamp    |
+---------+-------------+-------------------------+-------------------------+
|    1    |    BBBBB    | 2018-02-12 15:00:00 UTC | 2018-12-30 15:00:00 UTC |   
(以下略)
$

date型カラムなので日付情報しかないはずなのに、embulk preview時に自動的に15:00:00 UTCという時間情報がついてました。

どうやらembulkプラグインのembulk-input-mysqlでは、date型カラムをtimestamp型として認識することで9時間戻ってしまうことで、前日の日付になったようです。

しかもdatetime型のカラムは上記で説明した設定で回避できましたが、date型カラムは回避できないようです。

解決方法

いろいろ調べてみると、column_optionsでカラムの型を変えることができることが分かりました。これを試行錯誤した結果、一旦string型で持ってきてコピーする時にdate型で入れるようにすることで、無事コピーできることがわかりました。

具体的には、こういったymlファイルを作成して解決しました。

in:
  type: mysql
  (中略)
  table: (date型カラムを含むテーブル)
  select: "*"
  column_options:
    start_date: {type: string, value_type: string}
    end_date: {type: string, value_type: string}

out:
  type: mysql
  (中略)
  table: (date型カラムを含むテーブル)
  select: "*"
  column_options:
    start_date: {type: date, value_type: string}
    end_date: {type: string, value_type: string}

こうした設定によって、無事に日付もコピーできるようになりました。

まとめ

こうした問題がありましたが、このページ書いたように解決策も見つかったため、無事にデータ分析基盤が利用できるようになりました。

この挑戦のお陰で手を動かしながら技術を身につけることができたので、新しい知見を得ることができました。

digdagの話やmetabaseの話もまた別でまとめようと思います。

あとがき

MySQLにはdate型カラムがあるのに正常に値が取れないため、もしかしたらプラグインがDB接続する時に利用しているドライバ(MySQL Connector/J)がdate型に対応していないのかもしれません。

バグの可能性もありますが、修正スキルが無かったのでこうした対応を取りました。