This article will focus on the most recent updates on ASA for the query language. The rest of the announcements can be found on the main Ignite Fall 2021 post.
Azure Stream Analytics is a fully managed streaming engine with user-friendly user-interface and a simple SQL language. If our language is already very close to T-SQL, the flavor of SQL used in SQL Server and Azure SQL, we are working on closing the gap completely. To that goal, we are extending existing functions, and adding new ones, that will simplify queries for the most common tasks and bring stream and batch closer together.
If all these capabilities were already available in Stream Analytics via User Defined Functions, having them supported by built-in functions reduce query complexity, improve performance, and eventually will make queries more easily portable to T-SQL.
Each function is documented with examples and remarks. They are intended to function as closely as possible as their T-SQL counterparts.
We intend to continue adding new functions to the language, more to come soon.
Up until now, the MIN and MAX aggregate functions could only be applied on columns of data types BIGINT, DATETIME and FLOAT. These two functions have been extended to support BIT and NVARCHAR(MAX) expressions as argument.
In order not to break the previous behavior (implicit casting to FLOAT), only columns that have been explicitly typed in NVARCHAR(MAX) will be accepted as argument:
SELECT
TollId,
System.Timestamp() AS WindowEnd,
MAX(CAST(LicensePlate AS NVARCHAR(MAX)) as MaxLicensePlate
FROM entry
GROUP BY TollId, TumblingWindow(hour,1)
With compatibility level 1.2 we started supporting bit as a native type. We are now adding the associated bitwise operators: & (bitwise AND), | (bitwise OR), ^ (bitwise exclusive OR) and ~ (bitwise NOT).
A bitwise operator performs a bitwise logical operation between the two expressions, taking each corresponding bit for both expressions. For bitwise AND, the bits in the result are set to 1 if and only if both bits (for the current bit being resolved) in the input expressions have a value of 1; otherwise, the bit in the result is set to 0:
170 & 75 = 10
170 : 0000 0000 1010 1010
75 : 0000 0000 0100 1011
-------------------------
10 : 0000 0000 0000 1010
These operators perform bitwise operations on BIT and BIGINT values. Float values will be implicitly (or explicitly with CAST/TRY_CAST) converted with the following rules:
From |
To |
BIT 1 |
FLOAT 1.0 |
BIT 0 |
FLOAT 0.0 |
BIT NULL |
FLOAT NULL |
FLOAT 0.0 |
BIT 0 |
FLOAT any other value |
BIT 1 |
FLOAT NULL |
BIT NULL |
All of these new features have already been released and can be used in your jobs! If you haven't yet created a job, a good place to start with Azure Stream Analytics is to use one of the quick starts from our documentation page.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.