locked
Azure Stream Analytics Query - failing RRS feed

  • Question

  • Hi, I'm wondering if anyone can help here.

    We have an Azure Stream Analytics Query which is flattening an incoming json array so that it can be processed easily downstream and output to a flat file or to an azure sql database.

    Unfortunately the only output that works is a blob where the format is specified as "array" and not "line separated".

    The error message we get when the job fails when trying to output as "line separated" or to a sql db is:

    - Unable to cast object of type 'Microsoft.EventProcessing.RuntimeTypes.RecordN' to type 'Microsoft.EventProcessing.RuntimeTypes.IArray'.\r\n",

    the query is:

    Select
        event.ENDPOINT_ID
        , event.VERSION
        , event.Submission_DateTime
        , event.RETURNTYPE
        , event.EventProcessedUtcTime
        , event.PartitionId
        , event.EventEnqueuedUtcTime
        ,arrayElement.ArrayValue.Metric_ID
        ,arrayElement.ArrayValue.unit_code
        ,arrayElement.ArrayValue.Site_Code
        ,arrayElement.ArrayValue.Department_Code
        ,arrayElement.ArrayValue.Period_DateTime_Start
        ,arrayElement.ArrayValue.Period_DateTime_End
        ,arrayElement.ArrayValue.Metric_Value
    into bloboutput
    FROM [eventhubstreaminput] as event
    CROSS APPLY GetarrayElements(event.Metrics) as arrayElement

    the output json as an array is:

    [{"endpoint_id":"002","version":"1.1","submission_datetime":"2016-12-22 09:21:45","returntype":"ProjectA","eventprocessedutctime":"2016-12-22T11:27:08.2457510Z","partitionid":0,"eventenqueuedutctime":"2016-12-22T09:21:47.1530000Z","metric_id":"110","unit_code":"ABC","site_code":"ABCDE","department_code":"ABC","period_datetime_start":"2016-12-19 00:00:00","period_datetime_end":"2016-12-19 23:59:59","metric_value":0.0},{"endpoint_id":"002","version":"1.1","submission_datetime":"2016-12-22 09:21:45","returntype":"ProjectA","eventprocessedutctime":"2016-12-22T11:27:08.2457510Z","partitionid":0,"eventenqueuedutctime":"2016-12-22T09:21:47.1530000Z","metric_id":"112","unit_code":"ABC","site_code":"ABCDE","department_code":"ABC","period_datetime_start":"2016-12-19 00:00:00","period_datetime_end":"2016-12-19 23:59:59","metric_value":130.0}]

    I have noticed that the json output of the query (immediately above) has square brackets around it...indicating it's an array, however I don't know in stream analytics how to adjust the query to make the output a true line separated compatible output.


    Geoff



    Geoff

    Thursday, December 22, 2016 1:30 PM

All replies

  • Hello,

    We are checking on the query and would get back to you soon on this. I apologize for the inconvenience and appreciate your time and patience in this matter.

    Regards,
    Sumanth BM

    Thursday, December 22, 2016 6:26 PM
  • Thanks Sumanth, I appreciate it.

    Geoff

    Thursday, December 22, 2016 6:34 PM
  • Any news? 

    Geoff

    Friday, December 30, 2016 4:37 PM