Understand outputs from Azure Stream Analytics

• 12/21/2018
• 27 minutes to read
• Contributors
  • 
  • 
  • 
  • 
  • 
  • all

This article describes the different types of outputs available for an Azure Stream Analytics job. Outputs let you store and save the results of the Stream Analytics job. 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 using the INTO clause. You can use a single output per job, or multiple outputs per streaming job if you need 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, and 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 hyper-scale repository for big data analytic workloads. Data Lake Store enables you to store data of any size, type and ingestion speed for operational and exploratory analytics. Stream Analytics has to be authorized to access the 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 Data Lake Storage is selected as an output in the Azure portal, you are prompted to authorize a connection to an existing Data Lake Store.

   

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

3. Once you have the Data Lake Store account authenticated, you can configure the properties for your Data Lake Store output. The table below is the list of property names and their description 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 indicating the need for reauthorization in the Operation Logs. Currently, there is a limitation where the authentication token needs to be manually refreshed every 90 days for all jobs with Data Lake Store output. However, you can overcome this limitation by authenticating using managed identities (preview).

To renew authorization, Stop your job > go to your Data Lake Store output > click the Renew authorization link, and for a brief time a page will pop up indicating Redirecting to authorization.... The page automatically closes and if successful, indicates Authorization has been successfully renewed. You then need to click Save at the bottom of the page, and can proceed by restarting your job from the Last Stopped Time to avoid data loss.

SQL Database

Azure SQL Database can be used as an output for data that is 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 an Azure SQL Database. The table schema must exactly match the fields and their types being output from your job. An Azure SQL Data Warehouse can also be specified as an output via the SQL Database output option as well. To learn about ways to improve write throughput, refer to the Stream Analytics with Azure SQL DB as output article. The table below lists the property names and their description for creating a SQL Database output.

Blob storage

Blob storage offers a cost-effective and scalable solution for storing large amounts of unstructured data in the cloud. For an introduction on Azure Blob storage and its usage, see the documentation at How to use Blobs.

The table below lists the property names and their description for creating a blob output.

When 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). The maximum allowed number of blocks may be reached 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 is 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, new file is created for each output partition.
• If a file or a container of the storage account is deleted by the user.
• If the output is time partitioned by using the path prefix pattern, a new blob is used when the query moves to the next hour.
• If the output is partitioned by a custom field, 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 8000, a new blob may be created per partition key.

Event Hub

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.

There are a few parameters that are needed to configure Event Hub data streams as an output.

Power BI

Power BI can be used as an output for a Stream Analytics job to provide for a rich visualization experience of analysis results. This capability can be used 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 are 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, then click Authorize Now. The following page is shown:

   

3. In this step, provide the work or school account for authorizing the Power BI output. If you are not already signed up for Power BI, choose Sign up now. The work or school account you use for Power BI could be different from the Azure subscription account, which you are currently logged in with.

Configure the Power BI output properties

Once you have the Power BI account authenticated, you can configure the properties for your Power BI output. The table below is the list of property names and their description to configure your Power BI output.

For a walk-through of configuring a Power BI output and dashboard, see the Azure Stream Analytics & Power BI article.

Schema Creation

Azure Stream Analytics creates a Power BI dataset and table on behalf of the user if one does not already exist. In all other cases, the table is updated with new values. Currently, there is a limitation that only one table can exist within a dataset. Power BI uses the FIFO retention policy. When enabled, data will collect in a table until it hits 200,000 rows.

Data type conversion 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 BIs Entity Data Model (EDM) types if a POWER BI dataset and table do not exist.

Schema Update

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 may not fit into the original schema.

The SELECT * query should be avoided to prevent dynamic schema update across rows. In addition to potential performance implications, it could also result in uncertainty of the time taken for the results. The exact fields that need to be shown on Power BI dashboard should be selected. 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 the Stream Analytics. If Multi-Factor Authentication (MFA) is configured on your Azure Active Directory (AAD) 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 one can leverage for structured data with fewer constraints on the schema. Azure Table storage can be used to store data for persistence and efficient retrieval.

The table below lists the property names and their description for creating a table output.

Service Bus Queues

Service Bus Queues offer a First In, First Out (FIFO) message delivery to one or more competing consumers. Typically, messages are expected to be received and processed by the receivers in the temporal order in which they were added to the queue, and each message is received and processed by only one message consumer.

The table below lists the property names and their description 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

While 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 table below lists the property names and their description for creating a table output.

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

Azure Cosmos DB

Azure Cosmos DB is a globally distributed, multi-model database service that offers limitless elastic scale around the globe, rich query, and automatic indexing over schema-agnostic data models, guaranteed low latency, and industry-leading comprehensive SLAs. To learn about Cosmos DB collection options for Stream Analytics, refer to the Stream Analytics with 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 enables you to run code on-demand without having to explicitly provision or manage infrastructure. It lets you implement code that is triggered by events occurring in Azure or third-party services. This ability of Azure Functions to respond to triggers makes it a natural output for an Azure Stream Analytics. This output adapter allows 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 new Azure Function Output adapter is available with the following configurable properties:

When Azure Stream Analytics receives 413 (http Request Entity Too Large) exception from Azure function, it reduces the size of the batches 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 max 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 is no event landing in a time window, no output is generated. As a result, computeResult function is not called. This behavior is consistent with the built-in windowed aggregate functions.

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 may cause bottlenecks in your pipeline. To learn more about late arrival policy, visit 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 does not write one message at a time, and uses batches for efficiency. When both the incoming and the outgoing events rate is high, it 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 to output batching:

Next steps