kt.log

Create a Date-formatted string from a datetime string in a query for Azure Stream Analytics

Azure Stream Analytics のクエリ内で DateTime フォーマットの文字列から Date フォーマットの文字列を生成する

Azure Stream Analytics で入力から出力にデータを受け渡す際に、入力データを加工して新たな列を作りたいことがあります。よくある例としては、いわゆる DateTime 型の文字列(yyyy-MM-ddTHH:mm:ss) から Date 型の文字列 (yyyy-MM-dd) を生成することが挙げられると思います。
しかしながら、 T-SQL 構文のサブセット である Stream Analytics クエリ言語 としては、 CONVERT 関数 をサポートしていないことに注意が必要です。
そこで、 Stream Analytics のクエリ内で上記を行いたい場合にどのような書き方ができるかを、以下に紹介します。

書き方

説明の前提として、 record.arrayvalue.time から いわゆる DateTime 型のフォーマットの文字列を取得することができるものとします。

LEFT 関数

最もシンプルに記述することができます。

1
LEFT(record.arrayvalue.time, 10)

注意点としては以下の通りです。

  • record.arrayvalue.time について、先頭の10文字が いわゆる Date 型のフォーマット (yyyy-MM-dd) であること
    • これを満たさない場合、当然ですが、予期せぬ文字列が生成されます。

以下は気にしなくて構いません。

  • record.arrayvalue.time が文字列であるかどうか
    • 数値であっても、エラーにはならず、文字列に変換して処理してくれます。
      • そもそも文字列でなかったら DateTime 型になりませんが。

SUBSTRING 関数

LEFT 関数の別解です。

1
SUBSTRING(record.arrayvalue.time, 1, 10)

注意点等は LEFT 関数と同じです。

CONCAT または CONCAT_WS 関数

上記に挙げた方法と比較すると非常に冗長ですが、一つのやり方として示します。

1
2
3
4
5
CONCAT_WS('-',
    RIGHT(REPLICATE('0', 4) + TRY_CAST(YEAR(record.arrayvalue.time) AS nvarchar(max)), 4),
    RIGHT(REPLICATE('0', 2) + TRY_CAST(MONTH(record.arrayvalue.time) AS nvarchar(max)), 2),
    RIGHT(REPLICATE('0', 2) + TRY_CAST(DAY(record.arrayvalue.time) AS nvarchar(max)), 2)
)

他に以下のような書き方も可能です。

1
2
3
4
5
CONCAT_WS('-',
    RIGHT('0000' + TRY_CAST(YEAR(record.arrayvalue.time) AS nvarchar(max)), 4),
    RIGHT('00' + TRY_CAST(MONTH(record.arrayvalue.time) AS nvarchar(max)), 2),
    RIGHT('00' + TRY_CAST(DAY(record.arrayvalue.time) AS nvarchar(max)), 2)
)
1
2
3
4
5
CONCAT_WS('-',
    RIGHT(REPLICATE('0', 4) + TRY_CAST(YEAR(record.arrayvalue.time) AS nvarchar(max)), 4),
    RIGHT(REPLICATE('0', 2) + TRY_CAST(MONTH(record.arrayvalue.time) AS nvarchar(max)), 2),
    RIGHT(REPLICATE('0', 2) + TRY_CAST(DAY(record.arrayvalue.time) AS nvarchar(max)), 2)
)
1
2
3
4
5
CONCAT_WS('-',
    RIGHT(REPLICATE('0', 4) + TRY_CAST(DATEPART(year, record.arrayvalue.time) AS nvarchar(max)), 4),
    RIGHT(REPLICATE('0', 2) + TRY_CAST(DATEPART(month, record.arrayvalue.time) AS nvarchar(max)), 2),
    RIGHT(REPLICATE('0', 2) + TRY_CAST(DATEPART(day, record.arrayvalue.time) AS nvarchar(max)), 2)
)

注意点としては、以下の通りです。

  • record.arrayvalue.time について、いわゆる DateTime 型のフォーマット (yyyy-MM-ddTHH:mm:ss) の文字列であること
    • エラーハンドリングのため TRY ではなく TRY_CAST を使い、 TRY ではエラーになる状況で TRY_CAST で NULL にするようにしていますが、そもそも record.arrayvalue.time が nvarchar(max) にすらキャストできない何かだとしたら、使用している他の関数等でエラーになります。

まとめ

Azure Stream Analytics のクエリ内で、いわゆる DateTime フォーマットの文字列から Date フォーマットの文字列を生成する方法を 3つ紹介しました。
原則、 LEFT 関数を使うのが最も短くシンプルに記述できます。
もし Azure Stream Analytics への入力データが DateTime フォーマットではない日付である可能性があるのであれば、 CONCAT_WS 関数を使ったやり方が良いケースもあるでしょう。

See also