Understand outputs from Azure Stream Analytics

• 05/8/2020
• 31 minutes to read
• • 
  • 
  • 
  • 
  • 
  • +26

This article describes the types of outputs available for an Azure Stream Analytics job. Outputs let you store and save the results of the Stream Analytics job. By using the output data, you can do further business analytics and data warehousing of your data.

When you design your Stream Analytics query, refer to the name of the output by using the INTO clause. You can use a single output per job, or multiple outputs per streaming job (if you need them) by providing multiple INTO clauses in the query.

To create, edit, and test Stream Analytics job outputs, you can use the Azure portal, Azure PowerShell, .NET API, REST API, and Visual Studio.

Some outputs types support partitioning. Output batch sizes vary to optimize throughput.

Azure Data Lake Storage Gen 1

Stream Analytics supports Azure Data Lake Storage Gen 1. Azure Data Lake Storage is an enterprise-wide, hyperscale repository for big data analytic workloads. You can use Data Lake Storage to store data of any size, type, and ingestion speed for operational and exploratory analytics. Stream Analytics needs to be authorized to access Data Lake Storage.

Azure Data Lake Storage output from Stream Analytics is currently not available in the Azure China 21Vianet and Azure Germany (T-Systems International) regions.

The following table lists property names and their descriptions to configure your Data Lake Storage Gen 1 output.

SQL Database

You can use Azure SQL Database as an output for data that's relational in nature or for applications that depend on content being hosted in a relational database. Stream Analytics jobs write to an existing table in SQL Database. The table schema must exactly match the fields and their types in your job's output. You can also specify Azure SQL Data Warehouse as an output via the SQL Database output option. To learn about ways to improve write throughput, see the Stream Analytics with Azure SQL Database as output article.

You can also use Azure SQL Managed Instance as an output. You have to configure public endpoint in SQL Managed Instance and then manually configure the following settings in Azure Stream Analytics. Azure virtual machine running SQL Server with a database attached is also supported by manually configuring the settings below.

The following table lists the property names and their description for creating a SQL Database output.

There are two adapters that enable output from Azure Stream Analytics to Azure Synapse Analytics (formerly SQL Data Warehouse): SQL Database and Azure Synapse. We recommend that you choose the Azure Synapse Analytics adapter instead of the SQL Database adapter if any of the following conditions hold true:

• Throughput: If your expected throughput now or in the future is greater than 10MB/sec, use the Azure Synapse output option for better performance.

• Input Partitions: If you have eight or more input partitions, use the Azure Synapse output option for better scale-out.

Azure Synapse Analytics (Preview)

Azure Synapse Analytics (formerly SQL Data Warehouse) is a limitless analytics service that brings together enterprise data warehousing and Big Data analytics.

Azure Stream Analytics jobs can output to a SQL pool table in Azure Synapse Analytics and can process throughput rates up to 200MB/sec. This supports the most demanding real-time analytics and hot-path data processing needs for workloads such as reporting and dashboarding.

The SQL pool table must exist before you can add it as output to your Stream Analytics job. The table's schema must match the fields and their types in your job's output.

To use Azure Synapse as output, you need to ensure that you have the storage account configured. Navigate to Storage account settings to configure the storage account. Only the storage account types that support tables are permitted: General-purpose V2 and General-purpose V1. Select Standard Tier only. Premium tier is not supported.

The following table lists the property names and their descriptions for creating am Azure Synapse Analytics output.

Blob storage and Azure Data Lake Gen2

Data Lake Storage Gen2 makes Azure Storage the foundation for building enterprise data lakes on Azure. Designed from the start to service multiple petabytes of information while sustaining hundreds of gigabits of throughput, Data Lake Storage Gen2 allows you to easily manage massive amounts of data.A fundamental part of Data Lake Storage Gen2 is the addition of a hierarchical namespace to Blob storage.

Azure Blob storage offers a cost-effective and scalable solution for storing large amounts of unstructured data in the cloud. For an introduction on Blob storage and its usage, see Upload, download, and list blobs with the Azure portal.

The following table lists the property names and their descriptions for creating a blob or ADLS Gen2 output.

When you're using Blob storage as output, a new file is created in the blob in the following cases:

• If the file exceeds the maximum number of allowed blocks (currently 50,000). You might reach the maximum allowed number of blocks without reaching the maximum allowed blob size. For example, if the output rate is high, you can see more bytes per block, and the file size is larger. If the output rate is low, each block has less data, and the file size is smaller.
• If there's a schema change in the output, and the output format requires fixed schema (CSV and Avro).
• If a job is restarted, either externally by a user stopping it and starting it, or internally for system maintenance or error recovery.
• If the query is fully partitioned, and a new file is created for each output partition.
• If the user deletes a file or a container of the storage account.
• If the output is time partitioned by using the path prefix pattern, and a new blob is used when the query moves to the next hour.
• If the output is partitioned by a custom field, and a new blob is created per partition key if it does not exist.
• If the output is partitioned by a custom field where the partition key cardinality exceeds 8,000, and a new blob is created per partition key.

Event Hubs

The Azure Event Hubs service is a highly scalable publish-subscribe event ingestor. It can collect millions of events per second. One use of an event hub as output is when the output of a Stream Analytics job becomes the input of another streaming job. For information about the maximum message size and batch size optimization, see the output batch size section.

You need a few parameters to configure data streams from event hubs as an output.

Power BI

You can use Power BI as an output for a Stream Analytics job to provide for a rich visualization experience of analysis results. You can use this capability for operational dashboards, report generation, and metric-driven reporting.

Power BI output from Stream Analytics is currently not available in the Azure China 21Vianet and Azure Germany (T-Systems International) regions.

The following table lists property names and their descriptions to configure your Power BI output.

For a walkthrough of configuring a Power BI output and dashboard, see the Azure Stream Analytics and Power BI tutorial.

Create a schema

Azure Stream Analytics creates a Power BI dataset and table schema for the user if they don't already exist. In all other cases, the table is updated with new values. Currently, only one table can exist within a dataset.

Power BI uses the first-in, first-out (FIFO) retention policy. Data will collect in a table until it hits 200,000 rows.

Convert a data type from Stream Analytics to Power BI

Azure Stream Analytics updates the data model dynamically at runtime if the output schema changes. Column name changes, column type changes, and the addition or removal of columns are all tracked.

This table covers the data type conversions from Stream Analytics data types to Power BI Entity Data Model (EDM) types, if a Power BI dataset and table don't exist.

Update the schema

Stream Analytics infers the data model schema based on the first set of events in the output. Later, if necessary, the data model schema is updated to accommodate incoming events that might not fit into the original schema.

Avoid the SELECT * query to prevent dynamic schema update across rows. In addition to potential performance implications, it might result in uncertainty of the time taken for the results. Select the exact fields that need to be shown on the Power BI dashboard. Additionally, the data values should be compliant with the chosen data type.

Table storage

Azure Table storage offers highly available, massively scalable storage, so that an application can automatically scale to meet user demand. Table storage is Microsoft's NoSQL key/attribute store, which you can use for structured data with fewer constraints on the schema. Azure Table storage can be used to store data for persistence and efficient retrieval.

The following table lists the property names and their descriptions for creating a table output.

Service Bus queues

Service Bus queues offer a FIFO message delivery to one or more competing consumers. Typically, messages are received and processed by the receivers in the temporal order in which they were added to the queue. Each message is received and processed by only one message consumer.

In compatibility level 1.2, Azure Stream Analytics uses Advanced Message Queueing Protocol (AMQP) messaging protocol to write to Service Bus Queues and Topics. AMQP enables you to build cross-platform, hybrid applications using an open standard protocol.

The following table lists the property names and their descriptions for creating a queue output.

The number of partitions is based on the Service Bus SKU and size. Partition key is a unique integer value for each partition.

Service Bus Topics

Service Bus queues provide a one-to-one communication method from sender to receiver. Service Bus topics provide a one-to-many form of communication.

The following table lists the property names and their descriptions for creating a Service Bus topic output.

The number of partitions is based on the Service Bus SKU and size. The partition key is a unique integer value for each partition.

Azure Cosmos DB

Azure Cosmos DB is a globally distributed database service that offers limitless elastic scale around the globe, rich query, and automatic indexing over schema-agnostic data models. To learn about Azure Cosmos DB container options for Stream Analytics, see the Stream Analytics with Azure Cosmos DB as output article.

Azure Cosmos DB output from Stream Analytics is currently not available in the Azure China 21Vianet and Azure Germany (T-Systems International) regions.

The following table describes the properties for creating an Azure Cosmos DB output.

Azure Functions

Azure Functions is a serverless compute service that you can use to run code on-demand without having to explicitly provision or manage infrastructure. It lets you implement code that's triggered by events occurring in Azure or partner services. This ability of Azure Functions to respond to triggers makes it a natural output for Azure Stream Analytics. This output adapter enables users to connect Stream Analytics to Azure Functions, and run a script or piece of code in response to a variety of events.

Azure Functions output from Stream Analytics is currently not available in the Azure China 21Vianet and Azure Germany (T-Systems International) regions.

Azure Stream Analytics invokes Azure Functions via HTTP triggers. The Azure Functions output adapter is available with the following configurable properties:

Azure Stream Analytics expects HTTP status 200 from the Functions app for batches that were processed successfully.

When Azure Stream Analytics receives a 413 ("http Request Entity Too Large") exception from an Azure function, it reduces the size of the batches that it sends to Azure Functions. In your Azure function code, use this exception to make sure that Azure Stream Analytics doesn't send oversized batches. Also, make sure that the maximum batch count and size values used in the function are consistent with the values entered in the Stream Analytics portal.

Also, in a situation where there's no event landing in a time window, no output is generated. As a result, the computeResult function isn't called. This behavior is consistent with the built-in windowed aggregate functions.

Custom metadata properties for output

You can attach query columns as user properties to your outgoing messages. These columns don't go into the payload. The properties are present in the form of a dictionary on the output message. Key is the column name and value is the column value in the properties dictionary. All Stream Analytics data types are supported except Record and Array.

Supported outputs:

• Service Bus queue
• Service Bus topic
• Event hub

In the following example, we add the two fields DeviceId and DeviceStatus to the metadata.

• Query: select *, DeviceId, DeviceStatus from iotHubInput
• Output configuration: DeviceId,DeviceStatus

The following screenshot shows output message properties inspected in EventHub through Service Bus Explorer.

System properties for Service Bus Queue and Topic outputs

You can attach query columns as system properties to your outgoing service bus Queue or Topic messages. These columns don't go into the payload instead the corresponding BrokeredMessage system property is populated with the query column values. These system properties are supported - MessageId, ContentType, Label, PartitionKey, ReplyTo, SessionId, CorrelationId, To, ForcePersistence, TimeToLive, ScheduledEnqueueTimeUtc. String values of these columns are parsed as corresponding system property value type and any parsing failures are treated as data errors. This field is provided as a JSON object format. Details about this format are as follows -

• Surrounded by curly braces {}.
• Written in key/value pairs.
• Keys and values must be strings.
• Key is the system property name and value is the query column name.
• Keys and values are separated by a colon.
• Each key/value pair is separated by a comma.

This shows how to use this property –

• Query: select *, column1, column2 INTO queueOutput FROM iotHubInput
• System Property Columns: { "MessageId": "column1", "PartitionKey": "column2"}

This sets the MessageId on service bus queue messages with column1's values and PartitionKey is set with column2's values.

Partitioning

The following table summarizes the partition support and the number of output writers for each output type:

The number of output writers can also be controlled using INTO <partition count> (see INTO) clause in your query, which can be helpful in achieving a desired job topology. If your output adapter is not partitioned, lack of data in one input partition will cause a delay up to the late arrival amount of time. In such cases, the output is merged to a single writer, which might cause bottlenecks in your pipeline. To learn more about late arrival policy, see Azure Stream Analytics event order considerations.

Output batch size

Azure Stream Analytics uses variable-size batches to process events and write to outputs. Typically the Stream Analytics engine doesn't write one message at a time, and uses batches for efficiency. When the rate of both the incoming and outgoing events is high, Stream Analytics uses larger batches. When the egress rate is low, it uses smaller batches to keep latency low.

The following table explains some of the considerations for output batching:

Next steps