Lookerの「データベースタイムゾーン」と「クエリタイムゾーン」を理解する #looker

小ネタです。

先日、Lookerに関する下記エントリを投稿しました。

その際、Amazon Redshiftのデータベース接続を作成する際の設定で「これで良いっしょ」と特に意識すること無く設定した部分が実は間違ってた(ちょっとした凡ミスだった)事に気付いたのでその際のメモを残しておこうと思います。

Amazon Redshift側の設定

Amazon Redshiftクラスタのタイムゾーンの設定は、仕様に基づき「UTC」固定となっています。

• CONVERT_TIMEZONE 関数 - Amazon Redshift

システムから出力したデータの「投稿日」に相当する部分のタイムスタンプ情報は「実際の投稿日時より9時間未来の値」になっていたため、

post_id|post_date|title|url|author
436981|2019/05/29 21:55:14|MackerelのAWSインテグレーションで必要なRoleをCloudFormation一撃で作ってみる(外部ＩＤ, SES, StepFunctions対応)|https://dev.classmethod.jp/cloud/mackerel-aws-role-cloudformation-2/|kajiwara-taishi
436972|2019/05/29 21:04:11|AWS Cost ExplorerにRI期限切れ通知の機能が追加されました|https://dev.classmethod.jp/etc/cost_explorer_ri_expire_alert/|toyosaki-takashi
425575|2019/05/29 21:00:36|はじめの一歩からわかる「TDD Boot Camp」の開き方 #tddbc|https://dev.classmethod.jp/event/how-to-hold-a-tddbc/|takano-sho
436956|2019/05/29 20:56:23|AWS Client VPN に Android 端末から接続する|https://dev.classmethod.jp/cloud/aws/aws-client-vpn-with-android/|
435467|2019/05/29 19:05:53|ご存知でしたか？EC2インスタンスは再起動なしにディスクサイズ(EBSボリュームサイズ)を増やせます|https://dev.classmethod.jp/cloud/aws/extending-disk-size-without-reboot/|kazue-masaki
434109|2019/05/29 17:45:33|Looker：データベース接続情報からプロジェクト(LookML Project)を作成 #looker|https://dev.classmethod.jp/business/business-analytics/looker-create-lookml-project/|shinyaa31
436319|2019/05/29 17:36:51|Looker社が提唱する『従来の手法・サービスとLookerとの"違い"』 #looker|https://dev.classmethod.jp/business/business-analytics/the-looker-difference/|shinyaa31
436785|2019/05/29 17:00:50|VPC Lambdaのコールドスタートにお悩みの方へ捧ぐコールドスタート予防のハック Lambdaを定期実行するならメモリの割り当ては1600Mがオススメ？！|https://dev.classmethod.jp/cloud/aws/lambda-cold-start-avoid-hack/|iwata-tomoya
436854|2019/05/29 16:35:54|「どこからでも」使える AppStream 2.0 に、「どこでも」使える iPad および Android タブレットがサポートされました|https://dev.classmethod.jp/cloud/aws/appstream-supports-ipad-and-android-tablets/|marumo-atsushi
436837|2019/05/29 15:59:28|[AWS] Windows から Active Directory 認証を使って AWS Client VPN に接続する|https://dev.classmethod.jp/cloud/aws/client-vpn-with-active-directory-auth/|kikuchi-shuji
436684|2019/05/29 02:01:37|AWS IoTを使って増えたRaspberry Piを手軽にシャットダウン|https://dev.classmethod.jp/hardware/raspberrypi/iot_shutdown_raspberrypi/|sakaguchi-tsutomu
436631|2019/05/29 01:51:42|外部へ RDP が出来ない環境から、EC2 に RDP したいときに使える AppStream 2.0|https://dev.classmethod.jp/cloud/aws/appstream-rdp/|marumo-atsushi
436697|2019/05/29 01:35:50|ELBをよりよく理解するためにリリースの歴史と各機能面から紐解いた|https://dev.classmethod.jp/cloud/aws/elb-history-and-specs/|tamura-soshi

この値のままCOPY実施後、後続のSQL処理でデータの日付時刻情報を「9時間過去に戻した値」に調整しました。タイムスタンプ＝UTCの状況で取得する投稿日時＝実際の(日本時間に於ける)投稿日時、の状態に調整しています。

# SELECT post_id,post_date,title,url,author FROM cmdevio.t_blogposts
 ORDER BY post_date DESC LIMIT 10;
 post_id |      post_date      |                                                                  title                                                                   |                                         url                                          |    author_id
---------+---------------------+------------------------------------------------------------------------------------------------------------------------------------------+--------------------------------------------------------------------------------------+-----------------
  436981 | 2019-05-29 12:55:14 | MackerelのAWSインテグレーションで必要なRoleをCloudFormation一撃で作ってみる(外部ＩＤ, SES, StepFunctions対応)                            | https://dev.classmethod.jp/cloud/mackerel-aws-role-cloudformation-2/                 | kajiwara-taishi
  436972 | 2019-05-29 12:04:11 | AWS Cost ExplorerにRI期限切れ通知の機能が追加されました                                                                                  | https://dev.classmethod.jp/etc/cost_explorer_ri_expire_alert/                        | toyosaki-takashi
  425575 | 2019-05-29 12:00:36 | はじめの一歩からわかる「TDD Boot Camp」の開き方 #tddbc                                                                                   | https://dev.classmethod.jp/event/how-to-hold-a-tddbc/                                | takano-sho
  436956 | 2019-05-29 11:56:23 | AWS Client VPN に Android 端末から接続する                                                                                               | https://dev.classmethod.jp/cloud/aws/aws-client-vpn-with-android/                    | kikuchi-shuji
  435467 | 2019-05-29 10:05:53 | ご存知でしたか？EC2インスタンスは再起動なしにディスクサイズ(EBSボリュームサイズ)を増やせます                                             | https://dev.classmethod.jp/cloud/aws/extending-disk-size-without-reboot/             | kazue-masaki
  434109 | 2019-05-29 08:45:33 | Looker：データベース接続情報からプロジェクト(LookML Project)を作成 #looker                                                               | https://dev.classmethod.jp/business/business-analytics/looker-create-lookml-project/ | shinyaa31
  436319 | 2019-05-29 08:36:51 | Looker社が提唱する『従来の手法・サービスとLookerとの"違い"』 #looker                                                                     | https://dev.classmethod.jp/business/business-analytics/the-looker-difference/        | shinyaa31
  436785 | 2019-05-29 08:00:50 | VPC Lambdaのコールドスタートにお悩みの方へ捧ぐコールドスタート予防のハック Lambdaを定期実行するならメモリの割り当ては1600Mがオススメ？！ | https://dev.classmethod.jp/cloud/aws/lambda-cold-start-avoid-hack/                   | iwata-tomoya
  436854 | 2019-05-29 07:35:54 | 「どこからでも」使える AppStream 2.0 に、「どこでも」使える iPad および Android タブレットがサポートされました                           | https://dev.classmethod.jp/cloud/aws/appstream-supports-ipad-and-android-tablets/    | marumo-atsushi
  436837 | 2019-05-29 06:59:28 | [AWS] Windows から Active Directory 認証を使って AWS Client VPN に接続する                                                               | https://dev.classmethod.jp/cloud/aws/client-vpn-with-active-directory-auth/          | kikuchi-shuji
(10 rows)

当初、このデータに対してDB接続設定をこのような形で指定を行っていました。「クラスタはUTC、データの内容は東京に合わせてるからTokyoで良いんじゃね？」という形です。

直近5月中下旬、個人的にちょっと気合入れてブログ書いたので日毎のブログ本数はこのような数字になっていました。

条件を絞り、日毎の投稿本数カウントをLookerのExploreから算出してみます。数字的には大丈夫そうですね。

ですが、投稿日時の情報を追加で表示してみたところ、このような表示となっていました。

直近の投稿については、以下の様に概ね朝方・午前のうちにまとめて投稿していたものでした。偶々集計結果で一致はしていましたが、集計内容によっては数字が異なった出方をしてしまうため、正確とは言えません。

 author_id |      post_date      |                                                           title                                                            
-----------+---------------------+----------------------------------------------------------------------------------------------------------------------------
 shinyaa31 | 2019-05-29 08:45:33 | Looker：データベース接続情報からプロジェクト(LookML Project)を作成 #looker
 shinyaa31 | 2019-05-29 08:36:51 | Looker社が提唱する『従来の手法・サービスとLookerとの"違い"』 #looker
 shinyaa31 | 2019-05-27 09:51:29 | Lookerの特徴・お勧めポイントをユーザーロール毎に紹介する動画が分かりやすかったのでまとめてみた #looker
 shinyaa31 | 2019-05-27 09:42:08 | Tableau 2019.2 新機能紹介：ダッシュボードに配置出来るナビゲーションボタンの表示スタイルが増えました #tableau
 shinyaa31 | 2019-05-24 08:43:02 | Tableau 2019.2 新機能紹介：ダッシュボード関連の便利機能(x2)が使えるようになりました #tableau
 shinyaa31 | 2019-05-24 08:35:53 | Tableau 2019.2 新機能紹介：PostgreSQL+PostGIS の空間データが使えるようになりました #tableau
 shinyaa31 | 2019-05-23 10:09:31 | Tableau 2019.2 新機能紹介：並べ替えのコントロールの表示／非表示が選択出来るようになりました #tableau
 shinyaa31 | 2019-05-23 09:52:33 | Tableau 2019.2 新機能紹介：新しい空間計算関数: MakeLine()が使えるようになりました #tableau
 shinyaa31 | 2019-05-23 09:48:24 | Tableau 2019.2 新機能紹介：新しい空間計算関数: MakePoint()が使えるようになりました #tableau
 shinyaa31 | 2019-05-23 09:45:04 | Tableau 2019.2 新機能紹介：地図レイヤが「ベクターマップ」に置き換わり、地図表現の選択肢が充実＆性能が向上しました #tableau
(10 rows)

処理に用いていたDB接続の「クエリタイムゾーン」をUTCに変更し(戻し)ます。「クラスタタイムゾーン(UTC)の環境で正確な日時が取れてるのだからこちら(クエリタイムゾーン)もUTCで良かろう」ということですね。

先程のExploreを改めて確認してみます。今度は実際のデータと同じ内容が表示されていることが確認出来ました。

まとめ

というわけで、Lookerのデータベース接続における「データベースタイムゾーン」と「クエリタイムゾーン」の内容確認のご紹介でした。接続対象となるデータベース接続の設定に拠っては、今回ご紹介したように表示内容に差異が発生する可能性があります。ひいては分析や可視化の内容にも影響を及ぼす可能性がありますので、ご利用の際には検証・確認を忘れずに実施頂ければと思います。

• Looker ｜ 特集カテゴリー ｜ DevelopersIO