Azure Stream Analytics - Identify common data across the multiple data sets

user_2629756 0
Viewed 26

I am capturing data from multiple sources with Azure Events hub and send to Azure Stream Analytics. These sources have some common data points. Ex :

  • Source 1 : PNR No
  • Source 2 : PNR No

enter image description here

How can identify the common data across these multiple data sets?

azure-stream-analytics azure-eventhub azure
1 Answers

I understand that the multiple sources are ingested in a single event hub? I suppose there is a field SourceId that allows to identify where a data point is coming from?

If it's the case, you can use Common Table Expressions (WITH) to scope these sources, and join between them.

WITH 
  SourceA AS (
    SELECT * FROM Input WHERE SourceId = 'A'
  ),
  SourceB AS (
    SELECT * FROM Input WHERE SourceId = 'B'
  )

SELECT
  A.*,
  B.*
FROM A
LEFT JOIN B 
  ON A.[Pnr NO] = B.[Pnr NO]
  AND DATEDIFF(second,A,B) BETWEEN 0 AND 60

Note that this is not a universal pattern. It solves a specific business problem where we need to find a matching event for A in B (FROM A LEFT JOIN B), that happens in the following 60 seconds (DATEDIFF(second,A,B) BETWEEN 0 AND 60).

There are lots of different patterns to apply here depending on the specific scenario you are building for. For some, analytics functions (like last) should be used instead of joins.

If you elaborate on your need I may be able to find the right pattern.

user_6339250
Related