2

How to update or delete data in azure sql DB using azure stream analytics

saran k
  • 307
  • 2
  • 14
  • Were you able to resolve this somehow? I am also looking to use Azure SQL as the output sink of Stream analytics and struggle with this due to primary key violations. – JaKu Oct 22 '20 at 16:02

2 Answers2

0

Currently, Azure Stream Analytics (ASA) only supports inserting (appending) rows to SQL outputs (Azure SQL Databases, and Azure Synapse Analytics).
You should consider to use workarounds to enable UPDATE, UPSERT, or MERGE on SQL databases, with Azure Functions as the intermediary layer.

You can find more information about such workarounds in this MS article.

Nicola P
  • 81
  • 3
-2

Firstly, we need to know what is Azure Stream Analytics.

An Azure Stream Analytics job consists of an input, query, and an output. Stream Analytics ingests data from Azure Event Hubs, Azure IoT Hub, or Azure Blob Storage. The query, which is based on SQL query language, can be used to easily filter, sort, aggregate, and join streaming data over a period of time. You can also extend this SQL language with JavaScript and C# user defined functions (UDFs). You can easily adjust the event ordering options and duration of time windows when preforming aggregation operations through simple language constructs and/or configurations.

Azure Stream Analytics now natively supports Azure SQL Database as a source of reference data input. Developers can author a query to extract the dataset from Azure SQL Database, and configure a refresh interval for scenarios that require slowly changing reference datasets.

That means that you can not insert or update data in azure sql DB using Azure Stream Analytics.

Azure Stream Analytics is not a database manage tool.

Hope this helps.

Leon Yue
  • 15,693
  • 1
  • 11
  • 23
  • 2
    Azure SQL can be used as SA output. – JaKu Feb 07 '21 at 21:43
  • 1
    "That means that you can not insert or update data in azure sql DB using Azure Stream Analytics.". This is untrue. You can insert data into your SQL database from SA. Whether you would want it is something else. https://learn.microsoft.com/en-us/azure/stream-analytics/sql-database-output – Rens Groenveld Sep 24 '21 at 10:12