Azure Stream Analytics How to handle multiple output table?

Question

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

I'm looking into the exact same topic today. All I can find so far implies that you need to create an output per table...which seems crazy, though if you do base the target on the input then you could have data coming through for invalid targets... — Timbo, Jul 12 '17 at 17:32
@Timbo: Under the hood it's using BCP to load those tables.You can validate by monitoring activity via Query Performance Insight in the portal. Once you see that happening it makes more sense why it forces you to treat each table as an individual output. It's also a consideration if you plan to have more than one job target the same table due to possible table lock contention. — Pete M, Aug 17 '17 at 23:48

score 6 · Answer 1 · answered Jul 14 '17 at 13:17

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

Azure Stream Analytics How to handle multiple output table?

1 Answers1

Linked