QuickSight でカスタムクエリで SPICE データセットを作成したときに、増分更新機能が使えるのかを試してみた

いわさです。

以前 QuickSight の SPICE 増分更新機能の仕組みとして、データソースに対して日時列を指定した増分更新用のクエリが発行されるという内容をご紹介しました。

Amazon RDS のクエリログから以下のようなクエリが発行されることを確認しています。

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')

増分更新機能では、SPICE へデータを格納しないダイレクトクエリ機能の場合は利用出来ないという制限事項がありました。

最近 SPICE データセットを作成する際にカスタムクエリを使ったのですが、上記のクエリログの挙動から、カスタムクエリの場合でも増分更新が使えないのではと気になりました。
しかしドキュメントを見てみると、特にカスタムクエリの場合については言及されていません。

そこで、本日は実際にカスタムクエリタイプの SPICE データセットを増分更新出来るのかを検証してみました。

カスタムクエリデータセット

今回は、以下のテーブルを Aurora MySQL へ用意し、ターゲットデータソースとしています。

create table piyo (hogenum int, hogestr varchar(10), updatetime1 datetime DEFAULT NULL, updatetime2 TIMESTAMP);

データセットを作成する際に「カスタムSQLを使用」を選択することで任意の SQL クエリをデータソースに発行して、データセットとして使用することが出来ます。
ここでクエリモードを SPICE に指定すると、カスタムクエリで取得した内容を SPICE へ格納して利用することが可能です。

増分更新...できるぞ

さて、では上記のデータセットに対して増分更新が出来るか確認していきましょう。
すぐ結論にいきますが、増分更新出来ます。

SPICE の更新ダイアログで増分更新を選択することが出来ます。

では、先程のカスタムクエリのように、元のデータテーブルとカスタムクエリで列名が異なる場合はどのような動作になるのでしょうか。
増分更新設定で選択出来る日付列は SPICE に格納されるデータ構造の列名でした。
よって、例のようにカスタムクエリで日付列に名前を割り当てている場合はそちらを利用する形となります。

データソースに対してどういう挙動となるのか

さらに、この増分更新を行った際に、データソースに対してどういうアクセスが発生しているのかも確認してみましょう。

冒頭の記事でも実施していますが、Aurora MySQL のパラメータを変更することで、クエリログを出力することが出来ます。
ここでは、Amazon QuickSight で増分更新を行ったときのクエリログを採取します。

クエリログを見てみると以下のクエリが発行されていました。

SELECT `hogenum`, `hogestr`, `hogetime`
FROM (select 
    hogenum, 
    hogestr, 
    updatetime2 as hogetime 
from 
    piyo 
where 
    hogestr like 'aaa%') AS `from-piyo-where-like-aaa`
WHERE `hogetime` > CONVERT_TZ(DATE_ADD(FROM_UNIXTIME(0), INTERVAL cast(cast(1658264759687 as char(10)) as decimal) SECOND), @@session.time_zone,'UTC')

ここから、カスタムクエリで増分更新を行った場合にはサブクエリが利用されていることがわかりました。

さいごに

本日は、QuickSight でカスタムクエリでSPICEデータセットを作成したときに、増分更新機能が使えるのかを試してみました。

カスタムクエリの場合でも増分更新が出来、サブクエリが使われていることがわかりました。
これらの動きを知ることで、データソースに対して適切なインデックスを作成するなどで、増分更新時のパフォーマンスを改善することも出来そうですね。

mysql> alter table piyo add index hogeindex(hogestr);
Query OK, 0 rows affected (0.09 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> explain SELECT `hogenum`, `hogestr`, `hogetime`
    -> FROM (select 
    ->     hogenum, 
    ->     hogestr, 
    ->     updatetime2 as hogetime 
    -> from 
    ->     piyo 
    -> where 
    ->     hogestr like 'aaa%') AS `from-piyo-where-like-aaa`
    -> WHERE `hogetime` > CONVERT_TZ(DATE_ADD(FROM_UNIXTIME(0), INTERVAL cast(cast(1658264759687 as char(10)) as decimal) SECOND), @@session.time_zone,'UTC');
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+------------------------------------+
| id | select_type | table | partitions | type  | possible_keys | key       | key_len | ref  | rows | filtered | Extra                              |
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+------------------------------------+
|  1 | SIMPLE      | piyo  | NULL       | range | hogeindex     | hogeindex | 13      | NULL |    2 |    50.00 | Using index condition; Using where |
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+------------------------------------+
1 row in set, 1 warning (0.03 sec)