How to normalize incoming events in a Stream Analytics job
If you want to normalize the incoming events in a Stream Analytics job and transform records with the following format:
| Time | Id | P1 | P2 | P3 |
|---|---|---|---|---|
| 2015-12-16T10:00:01.0000000Z | 01 | 15 | 30 | 10 |
| 2015-12-16T10:00:01.0000000Z | 02 | 25 | 40 | 20 |
| 2015-12-16T10:00:01.0000000Z | 03 | 45 | 18 | 5 |
| 2015-12-16T10:00:01.0000000Z | 04 | 20 | 23 | 56 |
to the following format:
| Time | Id | Type | Value |
|---|---|---|---|
| 2015-12-16T10:00:01.0000000Z | 01 | P1 | 15 |
| 2015-12-16T10:00:01.0000000Z | 01 | P2 | 30 |
| 2015-12-16T10:00:01.0000000Z | 01 | P3 | 10 |
| 2015-12-16T10:00:01.0000000Z | 02 | P1 | 25 |
| 2015-12-16T10:00:01.0000000Z | 02 | P2 | 40 |
| 2015-12-16T10:00:01.0000000Z | 02 | P3 | 20 |
| 2015-12-16T10:00:01.0000000Z | 03 | P1 | 45 |
| 2015-12-16T10:00:01.0000000Z | 03 | P2 | 18 |
| 2015-12-16T10:00:01.0000000Z | 03 | P3 | 5 |
| 2015-12-16T10:00:01.0000000Z | 04 | P1 | 20 |
| 2015-12-16T10:00:01.0000000Z | 04 | P2 | 23 |
| 2015-12-16T10:00:01.0000000Z | 04 | P3 | 56 |
you can use the GetRecordProperties function and CROSS APPLY operator as shown by the following query:
SELECT e.Time, e.Id, record.PropertyName as Type, record.PropertyValue AS Value
FROM Events e
CROSS APPLY GetRecordProperties (e) AS record
WHERE record.PropertyName = 'p3' OR
record.PropertyName = 'p2' OR
record.PropertyName = 'p3'
You can use the following input file to test the Stream Analytics query on the Azure Management portal.
[
{
"Time": "2015-12-16T10:00:01.0000000Z",
"Id": "01",
"P1": 15,
"P2": 30,
"P3": 10,
},
{
"Time": "2015-12-16T10:00:01.0000000Z",
"Id": "02",
"P1": 25,
"P2": 40,
"P3": 20,
},
{
"Time": "2015-12-16T10:00:01.0000000Z",
"Id": "03",
"P1": 45,
"P2": 18,
"P3": 5,
},
{
"Time": "2015-12-16T10:00:01.0000000Z",
"Id": "04",
"P1": 20,
"P2": 23,
"P3": 56,
}
]
Running the query against the test file returns the following dataset:
| Time | Id | Type | Value |
|---|---|---|---|
| 2015-12-16T10:00:01.0000000Z | 01 | P1 | 15 |
| 2015-12-16T10:00:01.0000000Z | 01 | P2 | 30 |
| 2015-12-16T10:00:01.0000000Z | 01 | P3 | 10 |
| 2015-12-16T10:00:01.0000000Z | 02 | P1 | 25 |
| 2015-12-16T10:00:01.0000000Z | 02 | P2 | 40 |
| 2015-12-16T10:00:01.0000000Z | 02 | P3 | 20 |
| 2015-12-16T10:00:01.0000000Z | 03 | P1 | 45 |
| 2015-12-16T10:00:01.0000000Z | 03 | P2 | 18 |
| 2015-12-16T10:00:01.0000000Z | 03 | P3 | 5 |
| 2015-12-16T10:00:01.0000000Z | 04 | P1 | 20 |
| 2015-12-16T10:00:01.0000000Z | 04 | P2 | 23 |
| 2015-12-16T10:00:01.0000000Z | 04 | P3 | 56 |