none
Help me with query, please... RRS feed

  • Question

  • I am trying to set up a seemingly elementary task.
    Here it is:
    My event hub receives every second two identical messages containing DeviceId and Message: 
    DeviceId_1, "Message_1"
    DeviceId_1, "Message_1"

    Next second:
    DeviceId_1, "Message_2"
    DeviceId_1, "Message_2"
    and so on...
    I am trying to set up a query which groups and counts messages:. 
    Here is my query: 

    SELECT System.TimeStamp as Time, DeviceId, Message, Count(*) as MsgCount     
    INTO Output
    FROM
        Input   
    Group BY TUMBLINGWINDOW(s,10), DeviceId, Message


    I expect to see: 
    DeviceId_1 "Message_1"  2
    DeviceId_1 "Message_2"  2
    DeviceId_1 "Message_3"   2
    .......................
    DeviceId_1, "Message_n" 2

    What I am seeing in fact, with each record created every 10 seconds:
    DeviceId_1 Message_2  2
    DeviceId_1 Message_9  2
    DeviceId_1 Message_12  2
    DeviceId_1 Message_18  2
    DeviceId_1 Message_21  2

    I am really stuck... What am doing wrong, where are all my counts for messages in between of those Message_2-Message_9-12-18-21?

    Thank you, Sergei

    Friday, October 7, 2016 4:28 AM

All replies

  • If you count every 10 seconds with TUMBLINGWINDOW(s,10) wouldn't you expect 20?
    Just checking, but have you tried adding the TIMESTAMP BY?

    FROM      Input TIMESTAMP BY System.TimeStamp


    Please mark the post as answered if it answers your question | My SSIS Blog: http://microsoft-ssis.blogspot.com | Twitter


    Friday, October 7, 2016 5:36 AM
  • Thank you, SSISJoost!

    The query itself worked as I found out; what failed was inserting records to Azure Tables (it's my output) because of duplicate partitionkey+rowkey pairs.   What I am really surprised with, the errors left no trace in any logs. 

    Is this supposed to be like this in ASA? Or I don;t know where to find them?

    Friday, October 7, 2016 3:14 PM