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

Comments (0)

Skip to main content