Search code examples
azureazure-sql-databaseazure-iot-hubazure-stream-analytics

Stream Analytics doesn't output the data to SQL but does to a blob storage

In my project I receive data from Azure IoThub and want to send it to a SQL database using Azure stream analytics. I'm trying to achieve this using the following query:

SELECT
    IoTDataArrayElement.ArrayValue.sProjectID AS id
INTO
    [test-machine]
FROM
    [iothub-input] AS e
CROSS APPLY GetArrayElements(e.iotdata) AS IoTDataArrayElement
HAVING IoTDataArrayElement.ArrayValue IS NOT NULL

When I run the query in the environment provided by stream analytics and press test query I get the expected output which is a projectID. But when I start the stream analytics job the data doesn't go in to my database table. The table has 1 column 'id'.

When I try to send all the data to a blob storage the stream analytics job works.

Can someone please explain to me why the query I use for sending the data to a database doesn't actually send the data to a database?

Solution

  • Couple of things you need to verify to make successfully configuration of Azure SQL DB as output:

    • Make sure firewall settings is ON for All Azure Services.

    enter image description here

    • Make sure you have configured the output to the sql database with the correct properties defined.

    The following table lists the property names and their description for creating a SQL Database output.

    enter image description here

    • Make sure the table schema must exactly match the fields and their types in your job's output.

    Hope this helps.