locked
Using Reference Data in query RRS feed

  • Question

  • Hello Guys,

    I am sure others might have similar requirement (It's just me, don't know how to do it)

    I have stream coming from a device, data looks like

    ID001,01

    ID002,01

    ID001,04

    ID001,06

    And Reference data like

    ID001

    ID002

    ID003

    ID004

    I want a query to give me result like

    DeviceID Count

    ID001 3

    ID002 1

    ID003 0

    ID004 0

    But as you all know that only supported joins are Inner and Left Outer. Now I am not sure how can I get this result.

    I will later on filter this with HAVING COUNT=0 to get only those IDs for which I am not receiving data to raise an alert.

    Please advise.

    Regards,

    Wednesday, June 28, 2017 8:44 AM

All replies

  • Though not exactly what I want but this will do what I am trying to achieve.

    https://social.msdn.microsoft.com/Forums/en-US/77469ff0-3acb-4717-a69b-7d3d1c49949a/alert-when-no-input-is-received-for-x-minutes?forum=AzureStreamAnalytics

    Regards,

    Wednesday, June 28, 2017 9:28 AM
  • Hi,

    As you noticed RIGHT JOIN or using reference data as the left table in the LEFT JOIN is not supported.

    As a workaround we would suggest the following query to achieve your goal. Sorry it is little long:

    WITH 
    oneEvent AS /* generate one event per period, any event */
    (
    SELECT COUNT(*), 'count' as deviceid 
    FROM Input
    GROUP BY TumblingWindow(mi, 5)
    ),
    allDevices AS /* generate one event per device per period */
    (
    SELECT refdata.deviceid, oneEvent.deviceid as id
    FROM oneEvent JOIN refdata
    ON GetType(refdata.deviceid) = GetType(oneEvent.deviceid) -- this is just dummy condition as JOIN with no predicate is unsupported
    ),
    actualCounts AS /* compute normal aggregates */
    (
    SELECT System.Timestamp time, 
    deviceid,
    COUNT(*) as nbEvents
    FROM Input
    GROUP BY 
    TumblingWindow(mi, 5), deviceid
    )
    /* left join to allDevices to add nulls for other devices*/
    SELECT 
    System.Timestamp time, 
    allDevices.deviceid,
    actualCounts.nbEvents
    FROM 
    allDevices LEFT JOIN actualCounts
    ON 
    actualCounts.deviceid = allDevices.deviceid 
    AND DATEDIFF(ms, actualCounts, allDevices) = 0
    

    The output will be a count of message for every device in reference data, every 5 minutes:
    time
    deviceid
    nbevents
    "1970-01-01T12:05:00.0000000Z"
    "ID001"
    3
    "1970-01-01T12:05:00.0000000Z"
    "ID002"
    1
    "1970-01-01T12:05:00.0010000Z"
    "ID004"
    null
    "1970-01-01T12:05:00.0010000Z"
    "ID003"
    null

    Thanks,

    JS

    Thursday, June 29, 2017 5:02 AM