概要
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型で指定
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}
[
{"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にしちゃう
[
{"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型 の状態。）
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の方に設定を追加します。
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型 として送れました。