'Stream Analytics Query (Select * into output)(Exclude specific columns)

I have a query like;

SELECT
*
INTO [documentdb]
FROM
    [iothub] 
TIMESTAMP BY eventenqueuedutctime

I need to use * because data is dynamic and dont have specific schema. Problem is Iothub system information data is written to documentdb in this query. Is there any way to exclude Iothub system information data?

Thanks.

azureazure-stream-analytics


Solution 1:[1]

This is not possible currently but this will be possible in Job Compatibility Level 1.2 in near future. For now, one workaround is that you could create a post create trigger in Cosmos DB to remove this property from the document.

Solution 2:[2]

To answer your question, Azure stream analytics service doesn't have an in-built support for excluding columns from dynamic data (iothub information). But, we can achieve this by using UDF. Here is more info on UDF.

UDF can help us in deleting the column from input data and returning us the updated json.

There are two steps basically to achieve this:

  1. Create a JavaScript UDF.

    • Go to functions from left hand side navigation (below inputs).
    • Click on Add --> JavaScript UDF.
    • Give a function alias = removeiothubinfo
    • keep output type - any.

    • copy paste following code into function definition.

      function main(input) {
  delete input['IoTHub'];
  return input;
}

      Click on Save

  2. Update query

    • Go to query mode and copy paste the following query :

      WITH NewInput AS
(
SELECT
    udf.removeiothubinfo(iothub) AS UpdatedJson
FROM
    [iothub]
)
SELECT
    UpdatedJson.*
INTO
    [documentdb]
FROM
    NewInput

      Click on Save

I suggest you to test your query before running the job by uploading a sample file containing similar structure for json.

Edited

Also, even in job compatibility level 1.2 there has been no additional functionality to achieve this. Check this out for more info.

Solution 3:[3]

As @chetangm said in his answer, no such filtering mechanism is supported in ASA so far. Yes, you could use create trigger in Cosmos db, however it need to be triggered in sdk code or REST API. It won't be triggered automatically.

I provide you with another workaround that using Azure Function Cosmos DB Triggered. It could be executed when data is added to or changed in Azure Cosmos DB. You just need to remove the fields you don't want in the function code.

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 chetangm
Solution 2 halfer
Solution 3 halfer

Related Questions