Stream Analytics doesn't produce output to SQL table when reference data is used

Question

I am working with ASA lately and I am trying to insert ASA stream directly to the SQL table using reference data. I based my development on this MS article: https://msdn.microsoft.com/en-us/azure/stream-analytics/reference/reference-data-join-azure-stream-analytics.

Overview of data flow - telemetry:

I've many devices of different types (Heat Pumps, Batteries, Water Pumps, AirCon...). Each of these devices has different JSON schema for their telemetry data. I can distinguish JSONs by an attribute in message (e.g.: "DeviceType":"HeatPump" or "DeviceType":"AirCon"...)
All of these devices are sending their telemetry to a single Event Hub
Behind Event Hub, there is a single Stream Analytics component where I redirect streams to different outputs based on attribute Device Type. For example I redirect telemetry from HeatPumps with query SELECT * INTO output-sql-table FROM input-event-hub WHERE DeviceType = 'HeatPump'

I would like to use some reference data to "enrich" ASA stream with some IDKeys, before I inserted stream into SQL table.

What I've already done:

Successfully inserted ASA stream directly to SQL table using ASA query SELECT * INTO [sql-table] FROM Input WHERE DeviceType ='HeatPump', where [sql-table] has the same schema than JSON message + standard columns (EventProcessedUtcTime, PartitionID, EventEnqueueUtcTime)
Successfully inserted ASA stream directly to SQL table using ASA query SELECT Column1, Column2, Column3... INTO [sql-table] FROM Input WHERE DeviceType = 'HeatPump' - basically the same query as above, only this time I used named columns in select statement.
Generated JSON file of reference data and put it to the BLOB storage
Created new static (not using {date} and {time} placeholders) reference data input in ASA pointing to the file in BLOB storage.
Then I joined reference data to the data stream in ASA query using the same statement with named columns
Results no output rows in SQL table

When debugging the problem I've used Test functionality in Query ASA

I sample data from Event Hub - stream data.
I upload sample data from file - reference data.
After sampling data from Event Hub have finished, I tested a query -> output produced some rows -> it's not a problem in a query
Yet... if I run ASA, no output rows are inserted into SQL table.

Some other ideas I tried:

Used TRY_CAST function to cast fields from reference data to appropriate data types before I joined them with fields in stream data
Used TRY_CAST function to cast fields in SELECT before I inserted them into SQL table

I really don't know what to do now. Any suggestions?

EDIT: added data stream JSON, reference data JSON, ASA query, ASA input configuration, BLOB storage configuration and ASA test output result

Data Stream JSON - single message

[
 {
    "Activation": 0,
    "AvailablePowerNegative": 6.0,
    "AvailablePowerPositive": 1.91,
    "DeviceID": 99999,
    "DeviceIsAvailable": true,
    "DeviceOn": true,
    "Entity": "HeatPumpTelemetry",
    "HeatPumpMode": 3,
    "Power": 1.91,
    "PowerCompressor": 1.91,
    "PowerElHeater": 0.0,
    "Source": "<omitted>",
    "StatusToPowerOff": 1,
    "StatusToPowerOn": 9,
    "Timestamp": "2018-08-29T13:34:26.0Z",
    "TimestampDevice": "2018-08-29T13:34:09.0Z"
 }
]

Reference data JSON - single message

[
 {
    "SourceID": 1,
    "Source": "<ommited>",
    "DeviceID": 10,
    "DeviceSourceCode": 99999,
    "DeviceName": "NULL",
    "DeviceType": "Heat Pump",
    "DeviceTypeID": 1
 }
]

ASA Query

WITH HeatPumpTelemetry AS
(
    SELECT 
        *
    FROM 
        [input-eh]
    WHERE 
        source='<omitted>'
        AND entity = 'HeatPumpTelemetry'
)
SELECT 
    e.Activation,
    e.AvailablePowerNegative,
    e.AvailablePowerPositive,
    e.DeviceID,
    e.DeviceIsAvailable,
    e.DeviceOn,
    e.Entity,
    e.HeatPumpMode,
    e.Power,
    e.PowerCompressor,
    e.PowerElHeater,
    e.Source,
    e.StatusToPowerOff,
    e.StatusToPowerOn,
    e.Timestamp,
    e.TimestampDevice,
    e.EventProcessedUtcTime,
    e.PartitionId,
    e.EventEnqueuedUtcTime
INTO
    [out-SQL-HeatPumpTelemetry]
FROM
    HeatPumpTelemetry e
    LEFT JOIN [input-json-devices] d ON
        TRY_CAST(d.DeviceSourceCode as BIGINT) = TRY_CAST(e.DeviceID AS BIGINT)

ASA Reference Data Input configuration Reference Data input configuration in Stream Analytics

BLOB storage directory tree Blob storage directory tree

ASA test query output ASA test query output

Did you have multiple "SELECT ... FROM .. WHERE ..." and reference data joins? Can you please try with just one and see if it makes any difference? Does the logs say reference data was loaded? — Vignesh Chandramohan
Hi,Then I joined reference data to the data stream in ASA query using the same statement with named columns please provide your query statement in this step. — Jay Gong
Vignesh Chandramohan: where should I look for logs which say reference data was loaded? — matejp
@JayGong: still no luck. I don't know where to look for logs. I've updated my question with required clarification (ASA query and configuration...). — matejp

Jay Gong Jay Gong · Accepted Answer · 2018-08-30T07:56:49

matejp. I didn't reproduce your issue and you could refer to my steps.

reference data in blob storage:

{
    "a":"aaa",
    "reference":"www.bing.com"
}

stream data in blob storage

[
    {
        "id":"1",
        "name":"DeIdentified 1",
        "DeviceType":"aaa"      
    },
    {
        "id":"2",
        "name":"DeIdentified 2",
        "DeviceType":"No"     
    }
]

query statement:

SELECT
    inputSteam.*,inputRefer.*
into sqloutput
FROM
    inputSteam
Join inputRefer on inputSteam.DeviceType = inputRefer.a

Output:

Hope it helps you.Any concern, let me know.

Stream Analytics doesn't produce output to SQL table when reference data is used

EDIT: added data stream JSON, reference data JSON, ASA query, ASA input configuration, BLOB storage configuration and ASA test output result

2 Answers