Azure Stream Analytics Job Produces No Output Despite Successful Query Tests and No Runtime Error

Question

Hello,

I am encountering an issue with my Azure Stream Analytics job. The job runs without any errors in the monitoring metrics, and when I test the queries individually using the query editor, they produce the expected results. However, the job does not output any events to the PowerBI

Job Details:

1. Input Source: Data is being ingested from Event Hub
2. Query: The query calculates metrics based on a tumbling window and performs temporal joins. Below is the query I’m using:

WITH

FirstStep AS (

SELECT

    macAddress,

    MIN(value) AS FirstStepValue,

    System.Timestamp AS TumblingWindowTimestamp

FROM 

    fitrockr

WHERE 

    dataType = 'STEPS'

GROUP BY

    macAddress, TumblingWindow(Duration(minute, 30))

),

AdjustedSteps AS (

SELECT

    i.identifier,

    i.tenant,

    i.dataType,

    i.macAddress,

    i.value AS CurrentSteps,

    f.FirstStepValue,

    CAST(i.value - f.FirstStepValue AS FLOAT) AS AdjustedSteps,

    i.timestamp AS InputTimestamp,

    f.TumblingWindowTimestamp AS WindowStartTimestamp,

    i.EventProcessedUtcTime

FROM 

    fitrockr i

JOIN 

    FirstStep f

ON 

    DATEDIFF(second, i, f) BETWEEN 0 AND 1800

    AND i.macAddress = f.macAddress

WHERE 

    i.dataType = 'STEPS'

),

StepMetrics AS (

SELECT

    a.identifier,

    a.tenant,

    a.macAddress,

    a.CurrentSteps,

    a.AdjustedSteps,

    a.InputTimestamp AS Timestamp,

    a.WindowStartTimestamp,

    a.EventProcessedUtcTime,

    DATEADD(second, a.InputTimestamp / 1000, '1970-01-01T00:00:00Z') AS EventTime,

    CAST(a.AdjustedSteps * 0.762 AS FLOAT) AS Distance,

    CAST(a.AdjustedSteps * 0.762 * 60 / 30000 AS FLOAT) AS Speed

FROM 

    AdjustedSteps a

)

SELECT

identifier,

EventProcessedUtcTime,

EventTime,

AdjustedSteps,

Timestamp,

Distance,

Speed

INTO [FitR-1]

FROM StepMetrics;

Answer 1

Hello @Thanaphon Amattayakul ,

welcome to this moderated Azure community forum.

Azure Stream analytics jobs are a great solution for transforming, enriching, and distributing messages.

The problem could be either the incoming messages, the query or the output configuration.

The incoming messages can be recorded.

The editor in the Azure portal is not the best way to test the query.

Please check out the Visual Studio Code extension.

See this blog post on how use this offline test environment for stream analytics queries with a local file as input.

Especially the fact you can test a run of messages in within seconds, the diagnostics provided give a good insights in the inner workings of the query.

Once you have tested your query, it is recommended to deploy the query but with another output, eg. an EventHub.

Once you see messages arrive at that output, the PowerBI output must be checked.

---

If the response helped, do "Accept Answer". If it doesn't work, please let us know the progress. All community members with similar issues will benefit by doing so. Your contribution is highly appreciated.