JOIN in Azure Stream Analytics

Question

I have a requirement to validate the values of one column with a master data in stream analytics.

I have written queries to fetch some data from a blob location and One of the column value should be validated against a master data available in another blob location.

Below is the SAQL I tried. signals1 is the master data in blob and signals2 is the data processed and to be validated:

WITH MASTER AS (
SELECT [signals1].VAL as VAL
FROM [signals1]
)

SELECT 
    ID,
    VAL,
    SIG
INTO [output]
FROM signals2

I have to check the VAL from signals2 to be validated against VAL in signals1.

If the VAL in signals2 is there in signals1, then we should write to output. If the VAL in signals2 is not there in signals1, then that doc should be ignored(should not write into output).

I tried with JOIN and WHERE clause, but not working as expected. Any leads, how to achieve this using JOIN or WHERE?

Answer 1

In case your Signal1 data is the reference input, and Signal2 is the streaming input, you can use something like the following query:

with signals as (select * from Signal2 I join Signal1 R ON I.Val = R.Val)

select * into output from signals

I tested this query locally, and I assumed that your reference data(Signal1) is in the format:

[
    {
       "Val":"123",
       "Data":"temp"
    },
    {
       "Val":"321",
       "Data":"humidity"
    }
]

And for example, your Signal2 - the streaming input is:

{
   "Val":"123",
   "SIG":"k8s23kk",
   "ID":"1234589"
}

Have a look at this query and data samples to see if it can guide you towards the solution.

Side note you cannot use this join in case that Signal1 is the streaming data. The way these types of joins are working is that you have to use time-windowing. Without that is not possible.