locked
Query working but no output to table RRS feed

  • Question

  • Hello guys

    I've got a Developer Program subscription going, and I've been building a webservice with Azure for the last few weeks. Unfortunately, my progress with the Stream Analytics part has stalled since I've come across a problem that (my colleagues) and I just can't figure out.

    With my Stream Analytics engine I'm pulling events from an IoT Hub and trying to group them in timewindows based on their custom timestamps. I've already written a query that does this correctly. But the problem is that it just doesn't write anything into my output table (being a NoSQL table on my Storage Account).
    The query runs without problems in the query editor (when testing with a sample input file) and produces the correct output, but when running 'for real', it doesn't output anything (the output table remains empty). I've even tried renaming the table and outputting to a blob storage, but no dice. Here's the query:

    SELECT
        'general' AS partitionKey, 
        MIN(ID_frame) AS rowKey, 
        DATEADD(second, 1, DATEADD(hour, -3, System.TimeStamp)) AS window_start, 
        System.TimeStamp AS window_end, 
        COUNT(ID_frame) AS device_count
    INTO
        [IoT-Hub-output-table]
    FROM
        [IoT-Hub-input] TIMESTAMP BY Real_timestamp
    GROUP BY TumblingWindow(Duration(hour, 3), Offset(second, -1))

    The interesting part is that, if I omit any windowing in my query, then the table output works just fine. But I need windowing.
    I've been beating my head against the wall about this for a few days now, so I think I've already tried most of the obvious things.


    Thanks for reading


    • Edited by Gi0rgi Friday, October 14, 2016 6:15 AM
    Thursday, October 13, 2016 1:32 PM

Answers

  • Turns out the query did output into my table, but with an amount of delay I didn't expect; I was waiting for 20-30 minutes at max. while the first insertions would began after a little later than half an hour. Thus I was cancelling the Analytics job before any output was produced and falsely assuming it just wouldn't output anything.

    I found this to be the case afer I noted that 'sometimes' (when the job was running for long enough) there appeared to be some output. And in those output records I noticed the big delay between my custom timestamp field and the general timestamp field (which the engine uses to remember when the entity was updated for the last time)

    • Marked as answer by Gi0rgi Monday, October 24, 2016 6:36 AM
    Monday, October 24, 2016 6:35 AM