Azure Stream Analytics Jobsで時刻情報（UTC）を日本時間（JST）に変換する方法

How to convert UTC time into JST time region via Stream Analytics Jobs.

先日AzureのStream JobsでAzure IoT Hubから取得したデータのQueue時刻情報をUTC時間で取得して，それをPower BIで視覚化していたのですが，この時刻情報の変換する方法について判明したのでそのメモを残します．

なお，執筆時以前（2019/02/04）にはこの情報を見つけることができませんでした(StackOver Flowで見つけました)。

ちなみにこの方法はSQLクエリを書く際には一般的な方法だと思います．SQLクエリで時刻を変換したい人があまりいないため情報が見つかりにくかったのかも？

Azure Stream Analytics Jobsについて

Azure Stream Analyticsとは

Azure Stream Analytics は、デバイスからの大量のデータ ストリームを分析することができるイベント処理エンジンです。 受信データは、デバイス、センサー、Web サイト、ソーシャル メディア フィード、アプリケーションなどから得ることができます。 また、データ ストリームから情報を抽出し、パターンや関係を把握することもできます。 これらのパターンを使用して、アラートの作成、レポート ツールへの情報のフィード、後で使用するための情報の保存など、別のアクションをダウンストリームでトリガーすることができます。

https://docs.microsoft.com/ja-jp/azure/stream-analytics/stream-analytics-introduction

AWSのKinesis StreamのAzure版のStreamデータを扱うサービスだと私は捉えています．

今回，Azure IoT HubとAStream Analyticsを繋ぎ，その際に取得できるJSON形式のデータ中の時刻情報の変換について触れます．

フロー

このようにIoTデバイスから流れてきたJSONにAzure IoT Hubがさらにいくつかの情報を追加で付与したものがStream Analytics Jobsで流れてくるので，これを変換しましょう．

UTCをJSTに変換

Stream Analyticsには「クエリ」を書くことができます．ここで，「流れてくるデータ」から必要なデータを抽出，必要に応じて変換して他のサービスに流すことができるというすぐれものです．

「クエリ」はStream Analyticsの左のメニューのジョブトポロジ（Job Topology）の中にあります．

• Azure IoT Hubから流れてくるデータ
  • https://docs.microsoft.com/en-us/azure/stream-analytics/stream-analytics-define-inputs

When your data comes from an Event Hub stream input, you have access to the following metadata fields in your Stream Analytics query:

上記マイクロソフトのリファレンスから，以下のデータが付与されて届くので(これ以外にもあります），これらを変換する例を示します．

Property	Description
EventProcessedUtcTime	The date and time that the event was processed by Stream Analytics.
EventEnqueuedUtcTime	The date and time that the event was received by Event Hubs.
PartitionId	The zero-based partition ID for the input adapter.
EventEnqueuedUtcTime	The date and time that the event was received by Event Hubs.

そのままの場合

以下のクエリで時刻情報はUTC timeのまま流せます

SELECT
    EventProcessedUtcTime,
    EventEnqueuedUtcTime,
    PartitionId
INTO
    [YourOutputAlias]
FROM
    [YourInputAlias]

これをPower BIで受けとると，日本の現地時間の９時間前の時間が表示されます．PartitionIDはString型です．

なお，EventProcessedUtcTimeが取れない場合はIoTHub.EnqueuedTimeでも試してみると良いかもしれません．私の場合はなぜかこちらしか取れませんでした．

日本時間（JST）へ変換

本題です．といっても普通にSQLでUTC時間を変換する常套手段です．

UTC(Coordinated Universal Time)とは協定世界時であり，世界で基準となる時刻です．日本とイギリスの時差は+9hなので，+0900すれば良いです．クエリを以下に書き換えることで日本時間になります．

SELECT
    DATEADD(hour, 9, EventProcessedUtcTime),
    DATEADD(hour, 9, EventEnqueuedUtcTime),
    PartitionId
INTO
    [YourOutputAlias]
FROM
    [YourInputAlias]

これからわかるように，他の地域の時刻へも変換できます．これで他のサービスへ流すと変換された時刻情報が送られます．

今回，Power BIで時刻情報をそのまま表示するとこのように見えました．

これで変換できたことが確認できました．