Ignite 2021 – Azure Stream Analytics SQL improvements

This post has been republished via RSS; it originally appeared at: New blog articles in Microsoft Tech Community.

New built-in functions and improvements for Azure Stream Analytics SQL

 

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. 

 

New built-in functions:  

Each function is documented with examples and remarks. They are intended to function as closely as possible as their T-SQL counterparts. 

ASA New SQL Functions - Ignite 2021.png

The documentation is being updated now, those articles below should be up very soon! Sorry about that ;)

 

  • CONCAT_WS : returns a string resulting from the concatenation of string values in an end-to-end manner with a separator 
  • LEFT / RIGHT : returns the left or right part of a string with the specified number of characters 
  • NULLIF : returns a null value if the two specified expressions are equal, the first if not 
  • REPLICATE : repeats a string value a specified number of times 
  • REVERSE : returns the reverse order of a string value 
  • SPACE : returns a string of repeated spaces 
  • STUFF : inserts a string into another string 
  • TRANSLATE : replaces values in a string from a source to a target set 
  • TRIM / LTRIM / RTRIM : removes whitespace characters at the start, the end or both 
  • UNICODE / NCHAR : Returns the integer value, as defined by the Unicode standard, of a character, and the other way around 

We intend to continue adding new functions to the language, more to come soon. 

 

MIN/MAX now supports NVARCHAR(MAX) 

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)

 

 

New bitwise operators for bits

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 

 

Get started now!

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.

Leave a Reply

Your email address will not be published. Required fields are marked *

*

This site uses Akismet to reduce spam. Learn how your comment data is processed.