I am using azure-streamanalytics-cicd to test my asaql queries of the Stream Analytics Job.

Inside this query I need to have current timestamp:

SELECT 
  ....,
  System.Timestamp() AS event_datetime
INTO [mssql-output]

Now in the testing part I am providing all the mocked inputs and verifying it against hardcoded outputs. But the test fails all the time, because of the timestamp value. Query itself is working as expected, but to have a proper CI/CD pipeline we need to run those tests.

How can I mock it, so it can be checked?

CodePudding user response：

You can (and should) define a custom timestamp by using TIMESTAMP BY.

When developing locally, if you don't provide a TIMESTAMP, we use the query start time as the timestamp for all records. If you do any type of time processing (analytics functions like lag or lead, stream-stream join, temporal aggregations), they will most likely behave strangely to you, because they will operate on that single time slice of the query start time.

But also it means that System.Timestamp() will be different for every run, which will break unit tests.

We are working on adding an exclude list in the test case definition template, so you can tell the test runner to not take that column into account when comparing results. But this is a long term thing that won't help here.

What will work is using TIMESTAMP BY. If you don't have a event timestamp or don't want to use one from the payload, you can always use TIMESTAMP BY EventEnqueuedUtcTime, which is the default implicit behavior anyway. This way you can add EventEnqueuedUtcTime to your input mock files and test input files, and control the behavior of the unit test.