Query Language Elements (Azure Stream Analytics)
Azure Stream Analytics provides a variety of elements for building queries. They are summarized below.
| Element | Summary |
|---|---|
| APPLY | The APPLY operator allows you to invoke a table-valued function for each row returned by an outer table expression of a query. There are two forms of APPLY: CROSS APPLY returns only rows from the outer table that produce a result set from the table-valued function. OUTER APPLY returns both rows that produce a result set, and rows that do not, with NULL values in the columns produced by the table-valued function. |
| CASE | CASE evaluates a list of conditions and returns one of multiple possible result expressions |
| COALESCE | COALESCE evaluates the arguments in order and returns the value of the first expression that initially does not evaluate to NULL. |
| CREATE TABLE | CREATE TABLE is used to define the schema of the payload of the events coming into Azure Stream Analytics. |
| FROM | FROM specifies the input stream or a step name associated in a WITH clause. The FROM clause is always required for any SELECT statement. |
| GROUP BY | GROUP BY groups a selected set of rows into a set of summary rows grouped by the values of one or more columns or expressions. |
| HAVING | HAVING specifies a search condition for a group or an aggregate. HAVING can be used only with the SELECT expression. |
| INTO | INTO explicitly specifies an output stream, and is always associated with an SELECT expression. If not specified, the default output stream is "output". |
| JOIN and Reference Data JOIN |
JOIN is used to combine records from two or more input sources. JOIN is temporal in nature, meaning that each JOIN must define how far the matching rows can be separated in time. JOIN is also used to correlate persisted historical data or a slow changing dataset (aka. reference data) with the real-time event stream to make smarter decisions about the system. For example, join an event stream to a static dataset which maps IP Addresses to locations. This is the only JOIN supported in Stream Analytics where a temporal bound is not necessary. |
| MATCH_RECOGNIZE | MATCH_RECOGNIZE is used to search for a set of events over a data stream. |
| NULLIF | NULLIF evaluates two arguments and returns null if they are equal. |
| OVER | OVER defines the grouping of rows before an associated aggregate or analytic function is applied. |
| SELECT | SELECT is used to retrieve rows from input streams and enables the selection of one or many columns from one or many input streams in Azure Stream Analytics. |
| UNION | UNION combines two or more queries into a single result set that includes all the rows that belong to all queries in the union. |
| WHERE | WHERE specifies the search condition for the rows returned by the query. |
| WITH | WITH specifies a temporary named result set which can be referenced by a FROM clause in the query. This is defined within the execution scope of a single SELECT statement. |