QuickSightでデータセットの増分更新をする際は日時列のタイムゾーンを意識する

いわさです。

先日、QuickSightでデータセットの増分更新が出来るようになりました。
増分更新はSQLベースのデータセットの場合に、日時フィールドを対象に一定期間遡った範囲に絞った取り込みを行うことでフル更新のデメリットを回避することが出来るアップデートです。

しかし、試しに直近3時間以内に更新されたレコードを増分更新しようとしたところ、12時間以内のものが更新されてしまいました。
よく考えたら、起点が現在時刻だとしてタイムゾーンはどうなるのでしょうか。

本日はそのあたりの調査結果と、私がMySQLで問題になった事例をご紹介します。

余計に取り込まれる

前回の記事で作成したデータベースとテーブルをそのまま利用しています。
1時間ごとにupdatetimeが異なる以下のようなレコード群を用意しました。

現在時刻(JST)を10/27 16:45とします。
これらはすでにフル更新でSPICEへ取り込み済みです。

なお、RDS for MySQL上のtime_zoneはAsia/Tokyoを指定している状態です。

mysql> select * from piyo;
+------+------+---------------------+
| id   | name | updatetime          |
+------+------+---------------------+
|    1 | 111  | 2021-10-27 16:45:41 |
|    2 | 111  | 2021-10-27 15:45:41 |
|    3 | 111  | 2021-10-27 14:45:41 |
|    4 | 111  | 2021-10-27 13:45:41 |
|    5 | 111  | 2021-10-27 12:45:41 |
|    6 | 111  | 2021-10-27 11:45:41 |
|    7 | 111  | 2021-10-27 10:45:41 |
|    8 | 111  | 2021-10-27 09:45:42 |
|    9 | 111  | 2021-10-27 08:45:42 |
|   10 | 111  | 2021-10-27 07:45:42 |
|   11 | 111  | 2021-10-27 06:45:42 |
|   12 | 111  | 2021-10-27 05:45:42 |
|   13 | 111  | 2021-10-27 04:45:42 |
|   14 | 111  | 2021-10-27 03:45:42 |
|   15 | 111  | 2021-10-27 02:45:42 |
|   16 | 111  | 2021-10-27 01:45:43 |
+------+------+---------------------+
16 rows in set (0.02 sec)

全レコードのnameを更新し、3時間前までのものを増分更新してみましょう。

mysql> update piyo set name = 222;
Query OK, 16 rows affected (0.08 sec)
Rows matched: 16  Changed: 16  Warnings: 0

3件取り込まれてほしいはずが、12件取り込まれてしまいました。

テーブルで確認してみましょう。
本記事とは関係のない理由で時分秒の部分が正確に表示出来ていませんのでid列と先ほどのMySQL上のテーブルを突合してどれが取り込まれたのかを確認したいと思います。

12時間前までのものが取り込まれています。

クエリログを確認する

MySQLの一般ログを有効化し、SPICE増分ロード時のクエリを確認してみましょう。
以下の記事を参考にDBパラメータを設定しました。

パラメータグループの設定後、RDSを再起動するとマネジメントコンソール上からログが確認出来るようになります。(log_ouyput=FILEの場合)

QuickSightからアクセスされたことがわかるログを確認することが出来ます。

2021-10-27T07:50:30.161254Z       20 Query    /* QuickSight 7ab55e51-7e09-4423-97f1-b036eb7cfb1e */
SELECT `id`, `name`, `updatetime`
FROM `piyo`
WHERE `updatetime` > CONVERT_TZ(DATE_ADD(FROM_UNIXTIME(0), INTERVAL cast(cast(1635310106690 as char(10)) as decimal) SECOND), @@session.time_zone,'UTC')

このクエリからわかるように、やはり増分更新はクエリの条件に日時列を指定して取り込むという動作のようです。
クエリにあわせてフルスキャンされないようデータストア側も最適化されていれば、取り込み時のコンピューティングリソースへの負荷も下がりそうですね。

さて、条件を指定する際にタイムゾーンの変換がされています。

mysql> select CONVERT_TZ(DATE_ADD(FROM_UNIXTIME(0), INTERVAL cast(cast(1635310106690 as char(10)) as decimal) SECOND), @@session.time_zone,'UTC') hoge;
+---------------------+
| hoge                |
+---------------------+
| 2021-10-27 04:48:26 |
+---------------------+
1 row in set, 1 warning (0.05 sec)

UTCでクエリされていますね。
3時間前を取り込むと指定した場合は、12時間前（3時間+9時間）でUTCで計算されています。

MySQLだとDATETIME型だとこの事象が生じてしまう

おそらく上記の挙動は正しくて、増分更新設定時にはタイムゾーンを考慮した日付列を指定する必要があるということがわかりました。

今回使用したMySQLのDATETIME型はフォーマットそのままの状態で日付データを格納します。
つまりJSTでUPDATE/INSERTしたフィールド値はタイムゾーン情報なしでJST形式のまま保存されます。

よってJSTの日付データに対して、UTCの日付情報でクエリしていたため、9時間の取り込み範囲の誤差が生じてしまいました。

MySQLのTIMESTAMP型を使ってみる

一方、MySQLにはTIMESTAMP型という日付形式も用意されています。
こちらは格納時にUTCに変換されて格納されます。

こちらを試してみましょう。

以下はNOW()関数を使って現在時刻から１時間刻みでupdatetimeを動的に設定したサンプルです。

insert into piyo2 values (1, '111', NOW());
insert into piyo2 values (2, '111', NOW() - INTERVAL 1 HOUR);
insert into piyo2 values (3, '111', NOW() - INTERVAL 2 HOUR);
insert into piyo2 values (4, '111', NOW() - INTERVAL 3 HOUR);
...

mysql> update piyo2 set name = 222;
Query OK, 16 rows affected (0.08 sec)
Rows matched: 16  Changed: 16  Warnings: 0

mysql> select * from piyo2;
+------+------+---------------------+
| id   | name | updatetime          |
+------+------+---------------------+
|    1 | 222  | 2021-10-27 08:05:49 |
|    2 | 222  | 2021-10-27 07:05:49 |
|    3 | 222  | 2021-10-27 06:05:49 |
|    4 | 222  | 2021-10-27 05:05:49 |
|    5 | 222  | 2021-10-27 04:05:49 |
|    6 | 222  | 2021-10-27 03:05:49 |
|    7 | 222  | 2021-10-27 02:05:49 |
|    8 | 222  | 2021-10-27 01:05:49 |
|    9 | 222  | 2021-10-27 00:05:49 |
|   10 | 222  | 2021-10-26 23:05:49 |
|   11 | 222  | 2021-10-26 22:05:49 |
|   12 | 222  | 2021-10-26 21:05:49 |
|   13 | 222  | 2021-10-26 20:05:49 |
|   14 | 222  | 2021-10-26 19:05:49 |
|   15 | 222  | 2021-10-26 18:05:49 |
|   16 | 222  | 2021-10-26 17:05:50 |
+------+------+---------------------+
16 rows in set (0.07 sec)

UTCに変換されて保存されていますね。
QuickSightから増分更新をしてみます。

期待どおり3時間前までの3件が取り込まれました。

まとめ

DATETIMEとTIMESTAMPのMySQLでの取り扱いについては、通常使う上では皆さん意識されているのかもしれません。
QuickSightで日付列として指定する際に、どちらもタイムゾーンの概念を意識せずに指定できてしまうので、完全に無意識でDATETIMEを使っていました。

今回はMySQLで確認しましたが、他のエンジンでも増分更新クエリはUTCを前提に調整されているかもしれません。
QuickSightの増分更新クエリで日付列を指定する際は、タイムゾーンを意識しましょう。