Parse an object as string in output in Stream Azure Analytics

Question

This question is regarding Stream Analytics. I want to export a blob into a SQL.I know the process, my question is with the query I have to use.

{"performanceCounter":[{"available_bytes":{"value":994164736.0},"categoryName":"Memory","instanceName":""}],"internal":{"data":{"id":"459bf840-d259-11e5-a640-1df0b6342362","documentVersion":"1.61"}},"context":{"device":{"type":"PC","network":"Ethernet","screenResolution":{},"locale":"en-US","id":"RD0003FF73B748","roleName":"Sdm.MyGovId.Static.Web","roleInstance":"Sdm.MyGovId.Static.Web_IN_1","oemName":"Microsoft Corporation","deviceName":"Virtual Machine","deviceModel":"Virtual Machine"},"application":{"version":"R2.0_20160205.5"},"location":{"continent":"North America","country":"United States","clientip":"104.41.209.0","province":"Washington","city":"Redmond"},"data":{"isSynthetic":false,"samplingRate":100.0,"eventTime":"2016-02-13T13:53:44.2667669Z"},"user":{"isAuthenticated":false,"anonAcquisitionDate":"0001-01-01T00:00:00Z","authAcquisitionDate":"0001-01-01T00:00:00Z","accountAcquisitionDate":"0001-01-01T00:00:00Z"},"operation":{},"cloud":{},"serverDevice":{},"custom":{"dimensions":[],"metrics":[]},"session":{}}}
{"performanceCounter":[{"percentage_processor_total":{"value":0.0123466420918703},"categoryName":"Processor","instanceName":"_Total"}],"internal":{"data":{"id":"459bf841-d259-11e5-a640-1df0b6342362","documentVersion":"1.61"}},"context":{"device":{"type":"PC","network":"Ethernet","screenResolution":{},"locale":"en-US","id":"RD0003FF73B748","roleName":"Sdm.MyGovId.Static.Web","roleInstance":"Sdm.MyGovId.Static.Web_IN_1","oemName":"Microsoft Corporation","deviceName":"Virtual Machine","deviceModel":"Virtual Machine"},"application":{"version":"R2.0_20160205.5"},"location":{"continent":"North America","country":"United States","clientip":"104.41.209.0","province":"Washington","city":"Redmond"},"data":{"isSynthetic":false,"samplingRate":100.0,"eventTime":"2016-02-13T13:53:44.2668221Z"},"user":{"isAuthenticated":false,"anonAcquisitionDate":"0001-01-01T00:00:00Z","authAcquisitionDate":"0001-01-01T00:00:00Z","accountAcquisitionDate":"0001-01-01T00:00:00Z"},"operation":{},"cloud":{},"serverDevice":{},"custom":{"dimensions":[],"metrics":[]},"session":{}}}
{"performanceCounter":[{"percentage_processor_time":{"value":0.0},"categoryName":"Process","instanceName":"w3wp"}],"internal":{"data":{"id":"459bf842-d259-11e5-a640-1df0b6342362","documentVersion":"1.61"}},"context":{"device":{"type":"PC","network":"Ethernet","screenResolution":{},"locale":"en-US","id":"RD0003FF73B748","roleName":"Sdm.MyGovId.Static.Web","roleInstance":"Sdm.MyGovId.Static.Web_IN_1","oemName":"Microsoft Corporation","deviceName":"Virtual Machine","deviceModel":"Virtual Machine"},"application":{"version":"R2.0_20160205.5"},"location":{"continent":"North America","country":"United States","clientip":"104.41.209.0","province":"Washington","city":"Redmond"},"data":{"isSynthetic":false,"samplingRate":100.0,"eventTime":"2016-02-13T13:53:44.2668342Z"},"user":{"isAuthenticated":false,"anonAcquisitionDate":"0001-01-01T00:00:00Z","authAcquisitionDate":"0001-01-01T00:00:00Z","accountAcquisitionDate":"0001-01-01T00:00:00Z"},"operation":{},"cloud":{},"serverDevice":{},"custom":{"dimensions":[],"metrics":[]},"session":{}}}

Well you can see 3 json objects which of them have different fields for the objects in the array performanceCounter. Basically the first object of every object. in the first is available_bytes, 2nd is percentage_processor_total, and 3rd is percentage_processor_time.

Because I'm exporting this to a sql table called performaceCounter, I should have a different column for every different object, so I would like to save this into an string and then I will parse it in my app.

As starting point I have this query that reads an input(the blob) and write into an output(SQL)

  Select GetArrayElement(A.performanceCounter,0) as a
INTO
  PerformanceCounterOutput
FROM PerformanceCounterInput A

This GetArrayElement takes the index 0 of the array in performanceCounter but then writes a different column for each different field that find in every object. So I should have all different counters and create a column for each one, but my idea is more like a column call performanceCounterData and save string like

'"available_bytes":"value":994164736.0},"categoryName":"Memory","instanceName":""'

or this

"{"percentage_processor_total":{"value":0.0123466420918703},"categoryName":"Processor","instanceName":"_Total"}"

or

"{"percentage_processor_time":"value":0.0},"categoryName":"Process","instanceName":"w3wp"}"

How can I cast an array like a String? I tried CAST(GetArrayElement(A.performanceCounter,0) as nvarchar(max)) but I can't.

Please some good help will be rewarded

Answer 1

You can do something like below, this gives the counters as (propertyName, propertyValue) pairs.

with T1 as
(
select 
  GetArrayElement(iotInput.performanceCounter, 0) Counter,
  System.Timestamp [EventTime]
from 
    iotInput timestamp by context.data.eventTime
)

 select
  [EventTime],
  Counter.categoryName,
  Counter.available_bytes [Value]    
 from 
  T1
 where
  Counter.categoryName = 'Memory'

  union all

 select
   [EventTime],
  Counter.categoryName,
  Counter.percentage_processor_time [Value]    
 from 
  T1
 where
  Counter.categoryName = 'Process'

Query that gives one column per counter type can also be done, you will have to either do a join or a group by with 'case' statements for every counter.

Answer 2

With the following solution I get 2 columns with the name of the property and another with the value of the property, that it was my initial purpose

With pc as
    (
        Select 
        GetArrayElement(A.[performanceCounter],0) as counter
        ,A.context.data.eventTime as eventTime
          ,A.context.location.clientip as clientIp
          ,A.context.location.continent as continent
          ,A.context.location.country as country
          ,A.context.location.province as province
          ,A.context.location.city as city
        FROM PerformanceCounterInput A
    )
        select 
    props.propertyName,
    props.propertyValue,
    pc.counter.categoryName,
    pc.counter.instanceName,
    pc.eventTime,
    pc.clientIp,
    pc.continent,
    pc.country,
    pc.province,
    pc.city
    from pc
    cross apply  GetRecordProperties(pc.counter) as props
    where props.propertyname<>'categoryname' and props.propertyname<>'instancename'

Anyway if somebody finds how to write an object in plain text in analytics, still rewarded and appreciated will be