CODEWITHSUNDEEP
jsonazure-sql-databaseazure-stream-analytics
Jignesh Ansodariya
Jignesh Ansodariya

Reputation: 12685

Azure Stream Analytics How to handle multiple output table?

I have created one ASA job and also created one input alias and one output alias
like this

and I edited query section like this 

WITH 
[StreamData]
AS (
SELECT
    employee_id, 
    first_name,
    last_name,
    age,
    salary
FROM 
  [DeviceDataStream] 
WHERE
    [ObjectType] IS NULL -- Filter out device info and command responses
) 

SELECT
    employee_id, 
    first_name,
    last_name,
    age,
    salary
INTO
    [Telemetry]
FROM
    [StreamData]

I have created table in SQL database
like this

and my input from IOT device is like this

{"employee_id":4,"first_name":"Joseph","last_name":"Marshal","age":34,"salary":890000}

up to this all things are working fine.

now I want to store my input JSON to different tables based on type and my new input JSON will be like this

{"type":"emp","employee_id":4,"first_name":"Joseph","last_name":"Marshal","age":34,"salary":890000}

different fields will be there with different types and as per that I want to store data in different table, so what changes I need and where please guide me for that. thanks

Upvotes: 6

Views: 3867

Answers (1)

Jignesh Ansodariya
Jignesh Ansodariya

Reputation: 12685

hey finally I got easy solution for this I have created one output for each output table

like this

and my query is like this 

WITH 
    [StreamData]
AS (
   SELECT
    *
    FROM 
      [DeviceDataStream] 
    WHERE
       [ObjectType] IS NULL -- Filter out device info and command responses
) 

SELECT
   EventProcessedUtcTime,
    PartitionId,
    EventEnqueuedUtcTime,
    IoTHub,
    employee_id, 
    first_name,
    last_name,
    age,
    salary
INTO
    [Telemetry]
FROM
    [StreamData]
WHERE type = 'emp'   --Table 1

SELECT
    EventProcessedUtcTime,
    PartitionId,
    EventEnqueuedUtcTime,
    PersonID, 
    FirstName,
    LastName,
    City,
    height
INTO
    [TelemetryP]
FROM
    [StreamData]
WHERE type = 'prsn'  --Table 2

Upvotes: 6

Related Questions