Don’t Modify and Analyze Data in Your Azure Stream Analytics Job!
TLDR: An Azure Stream Analytics Job is a fantastic engine that can ingest data from multiple sources and output it to multiple destinations. And that’s exactly how I recommend using it. Using the Stream Analytics Query Language to analyze and manipulate data will sooner or later cause you problems.
Job Topology
Just to remind the Job topology, this is what it looks like:
Apparently, the Inputs and Outputs fulfill their purpose, but why do Functions and Query exist? Well, the Function can be triggered by any incoming input and perform actions accordingly, and the Query (written in the Stream Analytics Query Language) is the one that produces the output.
Why Avoid Using the Query Language for Data Manipulation?
The Stream Analytics Query Language provides great capabilities for data analysis and manipulation. You can filter, group, and transform data (e.g., text-to-date conversions), but I urge you to avoid it. Here’s why:
-
Hard to Debug: While Microsoft recommends editing the Stream Analytics Job in VS Code for better control, debugging issues between Input and Output often provides little to no information about failures.
-
Limited T-SQL Features: The query language is powerful but lacks the full spectrum of T-SQL features. If possible, perform data manipulations in a programming language like C# before streaming.
-
Performance Delays: Data manipulations take time, and the more you do, the more delays you’ll experience.
Recommendation
Use the Query part only to filter and route events to the correct output. This way, you leverage the Stream Analytics Job for what it excels at: ingesting and outputting data efficiently.