CODEWITHSUNDEEP
sqlsql-serverazureazure-stream-analytics
Inventor22
Inventor22

Reputation: 89

How to send data to only one Azure SQL DB Table from Azure Streaming Analytics?

Background

I have set up an IoT project using an Azure Event Hub and Azure Stream Analytics (ASA) based on tutorials from here and here. JSON formatted messages are sent from a wifi enabled device to the event hub using webhooks, which are then fed through an ASA query and stored in one of three Azure SQL databases based on the input stream they came from.

The device (Particle Photon) transmits 3 different messages with different payloads, for which there are 3 SQL tables defined for long term storage/analysis. The next step includes real-time alerts, and visualization through Power BI.

Here is a visual representation of the idea: IoT: Particle Photon + Azure

The ASA Query

SELECT
    ParticleId,
    TimePublished,
    PH,
    -- and other fields
INTO TpEnvStateOutputToSQL
FROM TpEnvStateInput


SELECT
    ParticleId,
    TimePublished,
    EventCode,
    -- and other fields
INTO TpEventsOutputToSQL
FROM TpEventsInput


SELECT
    ParticleId,
    TimePublished,
    FreshWater,
    -- and other fields
INTO TpConsLevelOutputToSQL
FROM TpConsLevelInput

Problem: For every message received, the data is pushed to all three tables in the database, and not only the output specified in the query. The table in which the data belongs gets populated with a new row as expected, while the two other tables get populated with NULLs for columns which no data existed for.

From the ASA Documentation it was my understanding that the INTO keyword would direct the output to the specified sink. But that does not seem to be the case, as the output from all three inputs get pushed to all sinks (all 3 SQL tables).

The test script I wrote for the Particle Photon will send one of each type of message with hardcoded fields, in the order: EnvState, Event, ConsLevels, each 15 seconds apart, repeating.

Here is an example of the output being sent to all tables, showing one column from each table: enter image description here

Which was generated using this query (in Visual Studio):

SELECT 
    t1.TimePublished as t1_t2_t3_TimePublished, 
    t1.ParticleId as t1_t2_t3_ParticleID, 
    t1.PH as t1_PH, 
    t2.EventCode as t2_EventCode, 
    t3.FreshWater as t3_FreshWater
FROM dbo.EnvironmentState as t1, dbo.Event as t2, dbo.ConsumableLevel as t3
WHERE t1.TimePublished = t2.TimePublished AND t2.TimePublished = t3.TimePublished

For an input event of type TpEnvStateInput where the key 'PH' would exist (and not keys 'EventCode' or 'FreshWater', which belong to TpEventInput and TpConsLevelInput, respectively), an entry into only the EnvironmentState table is desired.

Question: Is there a bug somewhere in the ASA query, or a misunderstanding on my part on how ASA should be used/setup?

I was hoping I would not have to define three separate Stream Analytics containers, as they tend to be rather pricey. After running through this tutorial, and leaving 4 ASA containers running for one day, I used up nearly $5 in Azure credits. At a projected $150/mo cost, there's just no way I could justify sticking with Azure.

Upvotes: 1

Views: 1019

Answers (2)

Vignesh Chandramohan
Vignesh Chandramohan

Reputation: 1306

In your example, all three "select into" statements are reading from the same input source, and don't have any filter clauses, so all rows would be selected.

If you only want to rows select specific rows for each of the output, you have to specify a filter condition. For example, assuming you only want records with a non null value in column "PH" for the output "TpEnvStateOutputToSQL", then ASA query would look like below

SELECT
    ParticleId,
    TimePublished,
    PH
    -- and other fields INTO TpEnvStateOutputToSQL FROM TpEnvStateInput WHERE PH IS NOT NULL

Upvotes: 1

Bart Czernicki
Bart Czernicki

Reputation: 3683

ASA is supposed to be purposed for Complex Event Processing. You are using ASA in your queries to essentially pass data from the event hub to tables. It will be much cheaper if you actually host a simple "worker web app" to process the incoming events.

This blog post covers the best practices: http://blogs.msdn.com/b/servicebus/archive/2015/01/16/event-processor-host-best-practices-part-1.aspx

ASA is great if you are doing some transformations, filters, light analytics on your input data in real-time. Furthermore, it also works great if you have some Azure Machine Learning models that are exposed as functions (currently in preview).

Upvotes: 1

Related Questions