Understand outputs from Azure Stream Analytics

• 3/25/2019
• 28 minutes to read
• Contributors
  • 
  • 
  • 
  • 
  • 
  • all

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 Store

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

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

Authorize an Azure Data Lake Store account

1. When you select Data Lake Store as an output in the Azure portal, you're prompted to authorize a connection to an existing Data Lake Store instance.

   

2. If you already have access to Data Lake Store, select Authorize Now. A page pops up and indicates Redirecting to authorization. After authorization succeeds, you're presented with the page that allows you to configure the Data Lake Store output.

3. After you have the Data Lake Store account authenticated, you can configure the properties for your Data Lake Store output.

   

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

Renew Data Lake Store authorization

You need to reauthenticate your Data Lake Store account if its password has changed since your job was created or last authenticated. If you don't reauthenticate, your job does not produce output results and shows an error that indicates the need for reauthorization in the Operation Logs.

Currently, the authentication token needs to be manually refreshed every 90 days for all jobs with Data Lake Store output. You can overcome this limitation by authenticating through managed identities (preview).

To renew authorization:

1. Select Stop to stop your job.

2. Go to your Data Lake Store output and select the Renew authorization link.

   For a brief time, a pop-up page indicates Redirecting to authorization. If authorization is successful, the page indicates Authorization has been successfully renewed and then closes automatically.

3. Select Save at the bottom of the page. You can then restart your job from the Last Stopped Time to avoid data loss.

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.

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

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 How to use blobs.

The following table lists the property names and their descriptions for creating a blob 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.

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.

Authorize a Power BI account

1. When Power BI is selected as an output in the Azure portal, you're prompted to authorize an existing Power BI user or to create a new Power BI account.

   

2. Create a new account if you don’t yet have one, and then select Authorize Now. The following page appears:

   

3. Provide the work or school account for authorizing the Power BI output. If you're not already signed up for Power BI, select Sign up now. The work or school account that you use for Power BI might be different from the Azure subscription account that you're now signed in with.

Configure the Power BI output properties

After you have the Power BI account authenticated, you can configure the properties for your Power BI output. 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 article.

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.

Renew Power BI Authorization

If your Power BI account password changes after your Stream Analytics job was created or last authenticated, you need to reauthenticate Stream Analytics. If Azure Multi-Factor Authentication is configured on your Azure Active Directory (Azure AD) tenant, you also need to renew Power BI authorization every two weeks. A symptom of this issue is no job output and an "Authenticate user error" in the Operation Logs:

To resolve this issue, stop your running job and go to your Power BI output. Select the Renew authorization link, and restart your job from the Last Stopped Time to avoid data loss.

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.

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 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 collection 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:

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.

Partitioning

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

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