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

# 概要

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

# Embulk

[Embulkの利用、の前の準備](https://qiita.com/abetomo/items/634b5b4d76862490301c) の通り

# 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))
```

## 検証に使うテーブル

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

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

```sql
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');
```

```sql
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にしちゃう

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

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

<img width="286" alt="スクリーンショット 2019-11-12 11.42.29.png" src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3800/a040e6ce-46b5-f557-5f66-7022c6060567.png">

ただし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型` の状態。）

```diff: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` のような値で良い感じにインサートされました。

<img width="136" alt="スクリーンショット 2019-11-12 12.06.15.png" src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3800/593601a6-f622-1e33-a9f0-06447274ac36.png">

## 追記: output-bigquery側で加工

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

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

```diff: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型` にすることで出そうな影響について考えます。

* 範囲指定
    * むしろこれまでより楽になるので特に影響なさそう
* タイムゾーン
    * `DATE 型は、タイムゾーンに関係なく` とドキュメントにあるので考えなくてよさそう。
    * https://cloud.google.com/bigquery/docs/reference/standard-sql/data-types?hl=ja
    * MySQLから取り出す時にタイムゾーンを正しく扱えばOK
* <それ以外>
    * <何かあれば追記>

# まとめ

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