2

I have two Event hub inputs (Event-A & Event-B) to azure stream analytics. Input Event-A: Primary (whenever I am getting event from 'event-A' then I have to do joins with data from 'event-B' for last 20 minutes duration) Input Event-B: Secondary (Its a kind of reference data but from other Azure EventHub)

Select a.id,b.id,b.action into outputevent from eventA a
left join eventB b on a.id = b.id -- don't know how to consider last 20 minutes event-B data

Need to match/join with Event-B only for last 20 minutes duration and don't know which window function applicable for this use case (And observed most of the window function is waiting for the future to actually trigger but my requirement is to play with past 'event-B' when I receive event-A)

Two event hub to stream analytics

user1531248
  • 521
  • 1
  • 5
  • 17
  • Here you go with solutions, ///////////////////// Code starts /////////// select b.eventenqueuedutctime as btime,a.Id,a.SysTime,a.UTCTime ,b.Id as BId,b.SysTime as BSysTime into outputStorage -- to blob storage (container) from eventA a TIMESTAMP BY eventenqueuedutctime left outer join eventB b TIMESTAMP BY eventenqueuedutctime on a.id = b.id and datediff(minute,b,a) between 0 and 180 -- join with last 3 hours of eventB data //////////////////////// Code Ends //////////// "datepart" matters a lot for "left join" delay when there is no match with the right table (eventB) – user1531248 Nov 07 '20 at 03:52

1 Answers1

0

I have formatted your own answer so it's more readable for others:

select 
  b.eventenqueuedutctime as btime,
  a.Id,
  a.SysTime,
  a.UTCTime ,
  b.Id as BId,
  b.SysTime as BSysTime 
  into outputStorage -- to blob storage (container) 
from 
  eventA a TIMESTAMP BY eventenqueuedutctime 
left outer join 
  eventB b TIMESTAMP BY eventenqueuedutctime 
on 
  a.id = b.id and 
  datediff(minute,b,a) between 0 and 180 -- join with last 3 hours of eventB data

You posted this answer in 2020. Is this query still in use today?

zemien
  • 562
  • 6
  • 17