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

Question

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?

is the column type in the database compatible? Does the db firewall allow connections from ASA? — silent
– silent, Commented Oct 14, 2019 at 11:00
@silent Thanks for the comment, The db does allow connections from ASA and the columnh type in the database is compatible — Collin
– Collin, Commented Oct 14, 2019 at 11:03

CHEEKATLAPRADEEP · Accepted Answer · 2019-10-22 09:39:22Z

3

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.

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.

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

Hope this helps.

answered Oct 22, 2019 at 9:39

CHEEKATLAPRADEEP

12.8k1 gold badge22 silver badges42 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Collin Over a year ago

I found out that it is hard to store data from asa to a relational database. So i decided to write a azure function wich i now use as a output. The functions takes care of the data and de placements in the correct tables.

CHEEKATLAPRADEEP Over a year ago

@Collin Would you like you post that as an answer so you can mark this question as answered?

James · Accepted Answer · 2024-07-23 12:38:42Z

0

Having battled a seemingly similar issue for quite a while, i found that Asure Stream Analytics uses TLS1.0 when connecting to an SQL Database, but not while testing the connection. This resulted in having to change the security settings for my Azure SQL database.

Also see @CHEEKATLAPRADEEP's answer, as this also applies.

answered Jul 23, 2024 at 12:38

James

1011 silver badge4 bronze badges

Collectives™ on Stack Overflow

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

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related