Azure Stream Analyticsとは

Azure Stream Analyticsは、SQL 風のシンプルな言語を使用して、IoT または非 IoT の複数のデータストリームに対する超並列のリアルタイム分析を簡単に開発および実行できます。（Stream Analytics - リアルタイムデータ分析 | Microsoft Azure から引用）

f:id:akubicharm:20180927182425p:plain

データ分析のためのクエリ

IoTのデバイスなどから収集したデータを分析するクエリは SQL のように記述できるので、SQLを書いたことがある人ならはブスに理解できます。また、入力データのフォーマットにはJSON、 Avro、CSVを想定しているので、それらを簡単に処理するための組み込み関数などがいくつか用意されています。

今回は、入れ子要素の扱い方とJSON の配列データを扱う方法を紹介します。

入れ子要素の扱い

Stream Analytics のクエリでは、JSON の入れ子データは「.」連結で簡単に取得することができます。例えば、下記のようなデータが IoT Hub からStream Analyticsに送られてくる場合

{"id": "001",  "person":  {"name": "taro", "age": 20}}

person の入れ子要素のname、ageを取得するためのクエリは以下のようになります。

SELECT
  id
, person.name
, person.age
INTO
  [blob]
FROM
  [iothub]

配列要素の扱い

JSON の要素が配列の場合は、組み込み関数を利用して配列要素を取り出して、フラットなデータとして扱うためにGetArrayElements 関数とAPPLY句を利用します。

GetArrayElements関数

文法

GetArrayElements (配列要素のキー)

GetArrayElements関数で配列要素のキーを指定することで、配列要素を一行のデータとして扱うことができるようになります。 GetArrayElements関数で配列要素を取得すると、配列のインデックス（ArrayIndex）と配列の要素（ArrayValue)として値を取得することができます。

APPLY句

文法

FROM （入力ソースまたは入力ソースのエリアス）
{CROSS |OUTER} APPLY （配列要素）AS （配列要素のエリアス）

配列要素には GetArrayElements（配列要素のキー）を指定します。配列要素の値を参照する場合には、SELECT句で(配列要素のエリアス).(要素名)と指定します。

APPLYには、CROSS APPLYとOUTER APPLYがあります。

CROSS APPLY : SQLの INNER JOINと同じでデータがある場合のみ取得可能です。
OUTER APPLY : SQLのOUTER JOINと同じでデータがない場合はNULL値が返ります。

以下のような入れ子の配列データを扱う方法を見ていきましょう。

[
{
 "id": "001",
 "data": [
    {"sub": 1001, "name": "alpha001"},
    {"sub": 2001, "name": "bravo001"},
    {"sub": 3001, "name": "charly001"}
  ]
},
{
 "id": "002",
 "data":[
    {"sub": 2, "name": "bravo"}
  ]
},
{
 "id": "003",
 "dummy": [
    {"sub": 3, "name": "charly"}
  ]
}
]

INNER JOIN風な使い方

配列の要素をINNER JOINして利用する場合には、CROSS APPLY句を利用します。下記のサンプルでは、

8行目で、GetArrayElements関数で入力となるJSONからdata要素を配列として取得し、arrayElementというエリアスで参照できるようにしています。そして、id と取得したdata要素をJOINしています。

3行目では、配列要素として取得したdata要素の配列インデックスを取得しています。id=001のデータはdata要素配列は3なので、0, 1, 2 というインデックスが取得されます。

4-5行目では、配列要素として取得したdata要素の値を取得しています。

f:id:akubicharm:20180927202906p:plain

1:    SELECT  
2:        event.id,
3:        arrayElement.ArrayIndex, 
4:        arrayElement.ArrayValue.sub,
5:        arrayElement.ArrayValue.name
6:    into [blob] 
7:    FROM [iothubsim] as event 
8:    CROSS APPLY GetArrayElements(event.data) AS arrayElement

クエリの結果は、以下のようになります。

ID	ARRAYINDEX	SUB	NAME
"001"	0	1001	"alpha001"
"001"	1	2001	"bravo001"
"001"	2	3001	"charly001"
"002"	0	2	"bravo"

OUTER JOIN 風な使い方

配列の要素をINNER JOINして利用する場合には、OUTER APPLY句を利用します。下記のサンプルでは、

8行目で、GetArrayElements関数で入力となるJSONからdata要素を配列として取得し、arrayElementというエリアスで参照できるようにしています。そして、id と取得したdata要素をOUTER JOINしています。

f:id:akubicharm:20180927203302p:plain

1:    SELECT  
2:        event.id,
3:        arrayElement.ArrayIndex, 
4:        arrayElement.ArrayValue.sub,
5:        arrayElement.ArrayValue.name
6:    into [blob] 
7:    FROM [iothubsim] as event 
8:    OUTER APPLY GetArrayElements(event.data) AS arrayElement