CODEWITHSUNDEEP
azure-stream-analytics
Aparna
Aparna

Reputation: 845

In StreamAnalytic Query how to join data from same input

I have some json data(InputFuelCon) being processed by a stream analytics query. I want to join the input with itself..i.e when I look at one value in the input, I need to look at another value in that same input..how can I do that.. The format of the json is something like

[{"timeseries":[{"fqn":"STATUS.EngineFuelConsumption","vqts":[{"v":10,"q":192,"t":"2018-05-10T12:34:34.000Z"}]},
{"fqn":"STATUS.ShaftsRunning","vqts":[{"v":"1","q":192,"t":"2018-05-10T12:35:34.000Z"}]}]}]

Running the following gives rows but 0 as the value

WITH DataInput1 AS 
(
SELECT
    DATA.Fqn AS fqn,
    DATA.Value AS value,
    DATA.time AS time
    FROM 
(
    SELECT
        Tag.ArrayValue.Fqn AS fqn,
        VQT.ArrayValue.V AS value,
        VQT.ArrayValue.T AS time
    FROM MetsoQuakeFuelCon AS TimeSeries
    CROSS APPLY GetArrayElements(TimeSeries.[timeSeries]) AS Tag
    CROSS APPLY GetArrayElements(Tag.ArrayValue.vqts) AS VQT
) AS DATA
WHERE DATA.fqn like  '%EngineFuelConsumption'

),
DataInput2 AS 
(
SELECT
    DATA.Fqn AS fqn,
    DATA.Value AS value,
    DATA.time AS time
    FROM 
(
    SELECT
        Tag.ArrayValue.Fqn AS fqn,
        VQT.ArrayValue.V AS value,
        VQT.ArrayValue.T AS time
    FROM MetsoQuakeFuelCon AS TimeSeries
    CROSS APPLY GetArrayElements(TimeSeries.[timeSeries]) AS Tag
    CROSS APPLY GetArrayElements(Tag.ArrayValue.vqts) AS VQT
) AS DATA
WHERE DATA.fqn like  '%ShaftsRunning' and DATA.Value like '1'

),
DataInput as (
select I1.Fqn AS fqn,
      cast(I1.Value as bigint)/30 AS value,
     DATETIMEFROMPARTS(DATEPART(year,I1.Time ),DATEPART(month,I1.Time ),DATEPART(day,I1.Time )
    ,DATEPART(hour,I1.Time ),00,00,00 ) AS time
       from DataInput1 I1 JOIN DataInput2 I2 
ON 
I1.Time=I2.Time and
DATEDIFF(MINUTE,I1,I2) BETWEEN 0 AND 1
)

select * from DataInput

DataInput1 and DataInput2 if run by themselves, give one record each and with sql experience, the datainput join on the timestamp should give the result, but it doesn't. I don't understand how DATEDIFF(MINUTE,I1,I2) BETWEEN 0 AND 1 works but if I remove it, then there is an error. Any help will be greatly appreciated.

Upvotes: 0

Views: 121

Answers (1)

Jean-Sébastien
Jean-Sébastien

Reputation: 737

please find some answers below. Let me know if you have any further question.

Why your query doesn't return data with this sample:

I looked at the data and query and the following statement implies you have strict equality on the value "Time": I1.Time=I2.Time. However in your sample, the time is different for the 2 entries, so that's why there is no result.

The DATEDIFF statement doesn't relax any of the equality requirements in the JOIN statement.

By removing the line "I1.Time=I2.Time and" you will see a result for your sample. In that case it will join records arriving within a minute. Note that if you have more than 1 record arriving within the same minute, you will see more than 1 joined result with this logic. Also you may want to use application timestamp to compare the timestamp in the data, and not the arrival time of the message.

More info about DATEDIFF:

JOIN in Azure Stream Analytics are temporal in nature, meaning that each JOIN must provide some limits on how far the matching rows can be separated in time. In your case since there is no explicit TIMESTAMP, the arrival time will be used.

Then, the JOIN condition will be applied, and in your example there is no data matching the condition.

Upvotes: 1

Related Questions