EmbulkでMySQLのdate型をBigQueryに送る。2019-11-12

14909 ワード
MySQL Embulk ubuntu18.04 BigQuery MySQL テキストリンク

概要

EmbulkでMySQLのデータをBigQueryに送ります。
そのときMySQLの date型 をBigQueryの date型 として気楽に送るとエラーになります。
その対応などを書きます。

Embulk

Embulkの利用、の前の準備 の通り

MySQL 環境

% cat /etc/lsb-release
DISTRIB_ID=Ubuntu
DISTRIB_RELEASE=18.04
DISTRIB_CODENAME=bionic
DISTRIB_DESCRIPTION="Ubuntu 18.04.3 LTS"

% sudo apt-get install mysql-server-5.7 mysql-client-5.7
% mysqld --version
mysqld  Ver 5.7.27-0ubuntu0.18.04.1 for Linux on x86_64 ((Ubuntu))

検証に使うテーブル

CREATE TABLE `example_table` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `date` date DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

てきとうに数件データを入れた。

insert into example_table
(`date`)
values
('2018-03-27'),
('2018-04-30'),
('2018-05-01'),
('2018-12-31'),
('2019-01-01'),
('2019-03-27'),
('2019-04-30'),
('2019-05-01'),
('2019-12-31'),
('2020-01-01');

mysql> select * from example_table;
+----+------------+
| id | date       |
+----+------------+
|  1 | 2018-03-27 |
|  2 | 2018-04-30 |
|  3 | 2018-05-01 |
|  4 | 2018-12-31 |
|  5 | 2019-01-01 |
|  6 | 2019-03-27 |
|  7 | 2019-04-30 |
|  8 | 2019-05-01 |
|  9 | 2019-12-31 |
| 10 | 2020-01-01 |
+----+------------+

Embulk: MySQL -> BigQueryの設定周り

必要なプラグインのインストール

% embulk gem install embulk-input-mysql
% embulk gem install embulk-output-bigquery

とりあえずの設定ファイル

他の記事でも解説されているので詳細な内容については割愛します。

ポイント

  • MySQLの設定ではカラムの指定のみ
  • BigQueryのschema設定で、MySQLの date型 をBigQueryの方でも date型 で指定
config.yml
in:
  type: mysql
  host: localhost
  user: USER_NAME
  password: PASSWORD
  database: DATABASE_NAME
  table: example_table
  select: id, date
out:
  type: bigquery
  auth_method: json_key
  json_keyfile: JSON_KEY
  path_prefix: /tmp/
  file_ext: .csv.gz
  source_format: CSV
  project: PROJECT
  auto_create_dataset: true
  dataset: example_dataset
  auto_create_table: true
  table: bq_example_table
  schema_file: ./schema.json
  formatter: {type: csv, charset: UTF-8, delimiter: ',', header_line: false}
  encoders:
  - {type: gzip}
scheme.json
[
  {"name": "id", "type": "INTEGER", "mode": "required"},
  {"name": "date", "type": "DATE", "mode": "required"}
]

本題のDate型の動きを検証

上述の「とりあえずの設定ファイル」で実行してみる

エラーで失敗します。

エラーの原因をログから拾うと以下の内容

"Error while reading data, error message: Could not parse '2018-03-27 00:00:00.000000 +00:00' as date for field date (position 1) starting at location 0"

BigQueryの date型 のカラムに 2018-03-27 00:00:00.000000 +00:00 を入れようとして時間も付いているのでエラーになった様子。

ちょっと設定を変えねばなりません。

BigQueryのdateカラムをSTRINGにしちゃう

scheme.json
 [
   {"name": "id",   "type": "INTEGER", "mode": "required"},
-  {"name": "date", "type": "DATE", "mode": "required"}
+  {"name": "date", "type": "STRING", "mode": "required"}
 ]

とすると、いちおうエラーなくインサートされます。

ただしBigQueryには 2018-03-27 00:00:00.000000 +00:00 という文字列として入ります。
以下の通り、一手間加えれば期待通りに動くもののちょっとなんとかしたい気持ちになります。

  • cast
    • cast(substr(date, 0, 10) as DATE)
  • 範囲指定
    • where cast(substr(date, 0, 10) as DATE) between '2018-01-01' and '2018-12-31'

MySQLの結果を加工

MySQLの結果をそのまま渡すと時間もくっつくみたいなので、ちょっと加工します。

（scheme.json は date型 の状態。）

config.yml
   table: example_table
   select: id, date
+  column_options:
+    date: {type: string, timestamp_format: "%Y-%m-%d", timezone: "Asia/Tokyo"}
 out:
   type: bigquery

dateを YYYY-MM-DD 形式にしてあげます。

参考: https://blog.adachin.me/archives/9196

これでBigQueryには date型 で、値は 2018-03-27 のような値で良い感じにインサートされました。

追記: output-bigquery側で加工

考え方としては「MySQLの結果を加工」と同様です。
output-bigquery側でも設定できるようになったようです。（詳細はコメント欄参照）

「MySQLの結果を加工」と同様なので設定例だけ記載します。outの方に設定を追加します。

config.yml
   formatter: {type: csv, charset: UTF-8, delimiter: ',', header_line: false}
   encoders:
   - {type: gzip}
+  column_options:
+  - {name: date, type: DATE, timezone: "Asia/Tokyo"}

STRING型 -> DATE型 で出そうな影響

とりあえずの逃げで「BigQueryのdateカラムをSTRINGにしちゃう」で運用していたので、BigQuery側を date型 にすることで出そうな影響について考えます。

  • 範囲指定
    • むしろこれまでより楽になるので特に影響なさそう
  • タイムゾーン
  • <それ以外>
    • <何かあれば追記>

まとめ

Embulkで良い感じにMySQLの date型 をBigQueryの date型 として送れました。