Search code examples
azureazure-stream-analytics

Stream Analytics problem in update fields

I've problem updating a field in stream analytics query. The query is the following :

SELECT * INTO CosmosDBTelemetry FROM IOTHub WHERE type like 't%' or type is null
SELECT * INTO CosmosDBEvents FROM IOTHub WHERE type = 'event'

In CosmosDBTelemetry I've to change deviceId value with the value of another field.

In other words I want to create a new item with a couple of fields in what I'm get in input.

Can anyone help me?

Thanks in advance.

Simone

Solution

  • Firstly, as @silent mentioned in the comment, Stream Analytics integration with Azure Cosmos DB allows you to insert or update records in your container based on a given Document ID column. This is also referred to as an Upsert. More details,please refer to this link.

    enter image description here

    Secondly,i'm afraid it's not appropriate to use ASA to implement your needs. ASA is more inclined to collect data from source than to clean or modify data in the destination. You could get an idea of Azure Function Cosmos DB Trigger. It could monitor cosmos db and modify columns based on other value as soon as any data streams into database.

    If your new column is static, you could use Reference Data Input in ASA.Please see the below scenario:

    If your reference data is not expected to change, then support for static reference data is enabled by specifying a static path in the input configuration. Azure Stream Analytics picks up the blob from the specified path. {date} and {time} substitution tokens aren't required. Because reference data is immutable in Stream Analytics, overwriting a static reference data blob is not recommended.

    This is the usage of Reference Data Input: https://learn.microsoft.com/en-us/stream-analytics-query/reference-data-join-azure-stream-analytics?toc=https%3A%2F%2Flearn.microsoft.com%2Fen-us%2Fazure%2Fstream-analytics%2FTOC.json&bc=https%3A%2F%2Flearn.microsoft.com%2Fen-us%2Fazure%2Fbread%2Ftoc.json