Reuse the result from subquery in Azure Stream Analytics Query

Question

I am using Azure Stream Analytics and I am facing some problem on query part. Here is my codes.

WITH subquery as (
    SELECT 
    messageId,
    deviceId,
    temperature, 
    humidity,
    EventProcessedUtcTime,
    DemoML(temperature, humidity) as result1
    from DemoInput
    )

SELECT
    messageId as messageId,
    deviceId as deviceId,
    temperature as temperature,
    humidity as humidity,
    EventProcessedUtcTime as EventProcessedUtcTime,
    result1.[Scored Labels] as result,
    result1.[Scored Probabilities] as resultProbability
INTO
    [DemoOutput]
FROM
    [subquery]


SELECT
    result1
INTO
    [c2d]
FROM
    [subquery]

DemoML is a function where it will return the result. I want to put result1 into two different outputs. But I only managed to put result1 into one output. How can I achieve that? I am totally new to SQL.

score 1 · Answer 1 · answered Nov 29 '17 at 15:43

1

You could use INSERT ... OUTPUT ... INTO ... SELECT to insert twice:

WITH subquery as (
    SELECT 
    messageId,
    deviceId,
    temperature, 
    humidity,
    EventProcessedUtcTime,
    DemoML(temperature, humidity) as result1
    from DemoInput
    )
INSERT INTO target_1(col1,...)
OUTPUT inserted.col1, ...
INTO target_2(col1, ..)
SELECT
    messageId as messageId,
    deviceId as deviceId,
    temperature as temperature,
    humidity as humidity,
    EventProcessedUtcTime as EventProcessedUtcTime,
    result1.[Scored Labels] as result,
    result1.[Scored Probabilities] as resultProbability
FROM  [subquery];

Simplified:

CREATE TABLE t1(i INT);
CREATE TABLE t2(i INT);
CREATE TABLE src(i INT);
INSERT INTO src(i) VALUES(10),(20);

INSERT INTO t1(i)                 -- target one
OUTPUT inserted.i
INTO t2(i)                        -- target two
SELECT i
FROM src;

DBFiddle Demo

answered Nov 29 '17 at 15:43

Lukasz Szozda

162,964
23
234
275

In this case, am I putting same data into two different targets? – Sam Nov 29 '17 at 15:48
@SamTew Yes, just check demo link, but you could manipulate column lists if needed. – Lukasz Szozda Nov 29 '17 at 15:48
What if I want to put two different set of data into two table? – Sam Nov 29 '17 at 15:56
@SamTew Then you could use `INSERT over DML` syntax. Your original question is solved. – Lukasz Szozda Nov 29 '17 at 16:01
Hi, From the title, I see you want to do this in Azure Stream Analytics. In this case CREATE TABLE and INSERT statements are not supported. Your initial query looks good and will have 2 different output. What issue do you see when you run it? – Jean-Sébastien Nov 30 '17 at 00:31
@JSAzure Hi. It looks good and no error when running but it wont trigger the Azure Function. FYI, c2d is the output to Azure Function. When I changed subquery to DemoInput, then it will trigger the function. So I suspect the problem lies on the query. – Sam Nov 30 '17 at 07:52
1

@SamTew The only difference I see here is the Azure ML call. Did you test it independently to be sure it returns some value? – Jean-Sébastien Dec 01 '17 at 18:01
@JSAzure I realize the my problem is the Azure function. The code in Azure function does not fit the input. – Sam Dec 02 '17 at 02:06
@JSAzure I asked the question in another post. Can you help me to take a look? https://stackoverflow.com/questions/47604272/c-sharp-jsonconvert-deserializeanonymoustype-failed – Sam Dec 02 '17 at 03:29

Reuse the result from subquery in Azure Stream Analytics Query

1 Answers1