SQL Server 2008 R2 StreamInsight

1. SQL Server 2008 R2 StreamInsight Complex Event Processing Event Stream Processing

2. Who Am I? • SQL Server MVP • SQL Server Consultant • Joint author on Wrox Professional SSIS book • www.SQLDTS.comand www.SQLIS.com • Specialise in Moving Data • @allanSQLIS (twitter)

3. Agenda • Use Cases & Challenges • Formulating Declarative Queries • Windows in Time • Event Flow Debugging • Demos

4. What is CEP? Complex Event Processing (CEP) is the continuous and incremental processing of event streams from multiple sources based on declarative query and pattern specifications with near-zero latency.

5. What is CEP? Event Complex Event Processing (CEP) is the continuous and incremental processing of event streams from multiple sources based on declarative query and pattern specifications with near-zero latency. request output stream input stream response

6. What is CEP? Event Complex Event Processing (CEP) is the continuous and incremental processing of event streams from multiple sources based on declarative query and pattern specifications with near-zero latency. request output stream input stream response

7. What is CEP? Event Complex Event Processing (CEP) is the continuous and incremental processing of event streams from multiple sources based on declarative query and pattern specifications with near-zero latency. request output stream input stream response

8. Who might need CEP • Fraud Detection • Real-Time Trade Risking • Algorithmic Trading/Betting • Meter throughputs • Oil, Gas, Water, Electricity • Use to drive alarms, alerts etc

9. StreamInsight Platform .NET C# LINQ StreamInsight Application Development StreamInsight Application at Runtime Event sources Event targets Input Adapters Output Adapters StreamInsight Engine Devices, Sensors Pagers & Monitoring devices Standing Queries KPI Dashboards, SharePoint UI 1 Web servers Query Logic Query Logic 4 2 3 Trading stations Event stores & Databases Query Logic Event stores & Databases Stock ticker, news feeds

10. Query Expressiveness • Selection of events (filter) • Calculations on the payload (project) • Correlation of streams (join) • Stream partitioning (group and apply) • Aggregation (sum, count, …) over event windows • Ranking over event windows (topK)

11. Query Expressiveness • Projection var result = from e ininputStream selectnew { id = e.id, W = (double)e.intW / 10 };

12. Query Expressiveness • Projection • Filter var result = from e ininputStream where e.id > 3 selectnew { id = e.id, W = (double)e.intW / 10 };

13. Query Expressiveness • Projection • Filter • Correlation (Join) var result = fromeLeftin inputStream1 joineRightin inputStream2 on eLeft.id equals eRight.id selectnew { id = eLeft.id, diff = eLeft.W - eRight.w };

14. Query Expressiveness • Projection • Filter • Correlation (Join) • Aggregation over windows var result = from win ininputStream.TumblingWindow( TimeSpan.FromSeconds(10)) selectnew { avg = win.Avg(e => e.W) };

15. Query Expressiveness • Projection • Filter • Correlation (Join) • Aggregation over windows • Group and Aggregate var result = from e ininputStream group e by e.id intoeachGroup from win ineachGroup.TumblingWindow( TimeSpan.FromSeconds(10)) selectnew { eachGroup.Key, avg = win.Avg(e => e.W) };

16. Windowing • Hopping • Tumbling • Snapshot • Count

17. Time Windows Hopping Window Time

18. Time Windows Tumbling Window Time

19. Snapshot Windows

20. Snapshot Windows

21. Debugger • Has its own debugger • You cannot live without it (believe me) • Set breakpoints • Filter • A wonderful tool

22. Tour Trace Reader Live Trace File Reader Query Demo (Joining) Debugger Tool (How it will save you) Demos