3

I have a very basic setup, in which I never get any output if I use the TIMESTAMP BY statement.

I have a stream analytics job which is reading from Event Hub and writing to the table storage.

The query is the following:

SELECT
    * 
INTO
    MyOutput
FROM
    MyInput TIMESTAMP BY myDateTime;

If the query uses timestamp statement, I never get any output events. I do see incoming events in the monitoring, there are no errors neither in monitoring nor in the maintenance logs. I am pretty sure that the source data has the right column in the right format.

If I remove the timestamp statement, then everything is working fine. The reason why I need the timestamp statement in the first place is because I need to write a number of queries in the same job, writing various aggregations to different outputs. And if I use timestamp in one query, I am required to use it in all other queries itself.

Am I doing something wrong? Perhaps SELECT * does not play well with TIMESTAMP BY? I just did not find any documentation explaining that...

dennis
  • 562
  • 6
  • 21
  • If you do a SELECT * without the TIMESTAMP and output to blob storage to look at the events, can you give a few examples of myDateTime/EventEnqueuedUtc pairs? Are the myDateTime values in order across all the rows? What are your settings on the CONFIGURE tab for Late Arrival Tolerance Window, Out of Order Tolerance Window, and whether to adjust or drop? – GregGalloway Aug 07 '15 at 06:47
  • @GregGalloway Sure, here are two examples: [{"myDateTime":"2015-08-02T10:59:02.0000000Z", "EventEnqueuedUtcTime":"2015-08-07T10:59:07.6980000Z"}, {"myDateTime":"2015-08-02T10:59:03.0000000Z", "EventEnqueuedUtcTime":"2015-08-07T10:59:07.6980000Z"}]. Late tolerance window: 00.00:00:05, Out of order tolerance: 00:00, Action: Adjust. – dennis Aug 07 '15 at 11:13
  • @GregGalloway And yes, myDateTime values are all in order. This is just a test that I am running, so I can just generate one or two events and see how they do or do not go through... – dennis Aug 07 '15 at 11:15
  • Also tested that changing output to blob container instead of table storage does not fix the problem. Here is the screenshot of what I just did showing input/output events. The first two bumps are scenarios without timestamp by that I shared with you (first with table output, then with blob), the second bump is the same case with timestamp by added. Also showing all errors metrics = 0. http://prntscr.com/81vf6s – dennis Aug 07 '15 at 11:24

1 Answers1

2

{"myDateTime":"2015-08-02T10:59:02.0000000Z", "EventEnqueuedUtcTime":"2015-08-07T10:59:07.6980000Z"}

Late tolerance window: 00.00:00:05

All of your events are considered late arriving because myDateTime is 5 days before EventEnqueuedUtcTime. Can you try sending new events where myDateTime is in UTC and is "now" so it matches within a couple of seconds?

Also, when you started the job, what did you pick as the job start date time? Can you make sure you pick a date before the myDateTime values? You might try this first.

GregGalloway
  • 11,355
  • 3
  • 16
  • 47
  • Edited to mention job start date time. – GregGalloway Aug 07 '15 at 13:24
  • Aaaaah, sorry, I did not consider that providing timestamp will affect how stream analytics decides whether event is late or not. I was under impression it was looking at when the event was physically added to the input. And in my scenario all these events are first collected in some other external storage and then were pushed to EventHub with a hope to be processed. Maybe you could also have an additional metric - something like Discarded late input events. – dennis Aug 07 '15 at 13:39
  • I agree. That's great feedback to provide to Microsoft here: http://feedback.azure.com/forums/270577-azure-stream-analytics – GregGalloway Aug 07 '15 at 13:44
  • And if you want to do a test to determine if it was the job start date time or the late arriving tolerance that was the culprit I would be curious to hear. – GregGalloway Aug 07 '15 at 13:45
  • Yep, setting job start time to custom solved the problem with output events. Will log my suggestions at Azure uservoice. – dennis Aug 07 '15 at 13:55