locked
How to create one output record for each field of the input record? RRS feed

  • Question

  • This is a common scenario when creating a Data Historian with data compression. Suppose that the input stream is coming from IOT Hub. It has a time-stamp, a GROUP NAME and several pairs of TAG and VALUE. I would like ASA to generate an output to Azure Table for each TAG containing the the fields: Time-stamp+Tag (key),VALUE if the VALUE changed from the previous by more then a specified amount or it is the top of the hour. Can ASA do that?
    Tuesday, April 5, 2016 7:31 AM

Answers

  • Yes, you can do it with ASA. Use GetRecordProperties and CROSS APPLY to generate event for each field.

    You can use LAG function to access previous event. What does top of the hour mean? Is it the biggest value? You can use TopOne aggregate function for this, but note that result can only be generated in the end of the hour since you need for all events to come in.

    WITH TagValues AS
    (
    SELECT
        input.Timestamp,
        input.groupName,
        recordProperty.PropertyName as tag,
        recordProperty.PropertyValue as value
    FROM input
    CROSS APPLY GetRecordProperties(input.recordField) AS recordProperty
    WHERE recordProperty.PropertyName = 'Tag'
    )

    SELECT * FROM TagValues
    WHERE value > 2 * LAG(value) OVER (PARTITION BY groupName LIMIT DURATION(hour, 1))

    Wednesday, April 6, 2016 4:16 PM