Whether the Stream Analytics service supports modifying the row records of the output target database

Question

Hi there,

I now have a scenario where I receive data from EventHub through the Azure Stream Analytics service and UPDATE the data into the Azure Synapse table, now I know INSERT is supported, but I don't know if Update is supported. How to update database rows ?

Thanks & Regards,

zmsoft

Answer 1

Hi zmsoft,

Thanks for reaching out to Microsoft Q&A.

Currently, Azure Stream Analytics (ASA) supports only inserting (appending) rows to SQL outputs, such as Azure SQL DB and Synapse Analytics. However, it does not natively support updating existing rows.

https://shorturl.at/hWA8B

To achieve row updates, you can use a workaround involving Azure Functions as an intermediary layer. Here's a high-level approach:

1. Create an Azure Function: Set up an Azure Function that listens for new events from your Stream Analytics job.
2. Process Events: In the Azure Function, write logic to update the existing rows in your Synapse table based on the incoming event data.
3. Stored Procedure: You can set up your output in Stream Analytics to target a stored procedure within your Azure SQL Database or Synapse SQL. This stored procedure can handle the incoming data and decide whether to perform an INSERT or an UPDATE. In your stored procedure, use MERGE or UPDATE statements to update rows based on the incoming records.
4. Trigger the Function: Ensure that your Stream Analytics job sends events to the Azure Function.

For example, using a stored procedure, you could define it to accept parameters from ASA and then, within the procedure...

CREATE PROCEDURE UpdateOrInsertRecord
@id INT,
@value VARCHAR(100),
@timestamp DATETIME
AS
BEGIN
    MERGE INTO TargetTable AS target
    USING (SELECT @id AS id, @value AS value, @timestamp AS timestamp) AS source
    ON target.id = source.id
    WHEN MATCHED THEN
        UPDATE SET value = source.value, timestamp = source.timestamp
    WHEN NOT MATCHED THEN
        INSERT (id, value, timestamp)
        VALUES (source.id, source.value, source.timestamp);
END;

Please feel free to click the 'Upvote' (Thumbs-up) button and 'Accept as Answer'. This helps the community by allowing others with similar queries to easily find the solution.