3

Can I have multiple queries in the same output in Azure Stream Analytics Job?

For example

SELECT property1, property2 INTO Output1 WHERE Property3 ='Answer'
SELECT property4, property5 INTO Output1 WHERE Property3 ='Question'
Georgia Kalyva
  • 737
  • 2
  • 9
  • 23
  • You'd have to define two different outputs; then you can run your two queries. See [this near-duplicate question](https://stackoverflow.com/questions/36287058/can-one-have-multiple-queries-in-streaming-analytics-job). – David Makogon Jul 20 '18 at 14:36
  • If you want them in one output consider using [CASE](https://msdn.microsoft.com/en-us/azure/stream-analytics/reference/case-azure-stream-analytics) to insert prop1 and prop2 when prop3 is an answer and prop4 and prop5 otherwise – Peter Bons Jul 20 '18 at 14:37

3 Answers3

5

Union the results first and alias the combo using WITH, then Select from that table into the output.

https://msdn.microsoft.com/en-us/azure/stream-analytics/reference/union-azure-stream-analytics

Didn't test this, but should give the idea:

WITH Combined AS (
SELECT property1, property2 
FROM [input-hub] 
WHERE Property3 ='Answer'
UNION
SELECT property4, property5 
FROM [input-hub] 
WHERE Property3 ='Question'
)

SELECT * 
INTO [output-cosmos]
FROM Combined
Jason H
  • 111
  • 1
  • This will throw an error saying that WIth cannot be used for two SELECT statements – Interested_Programmer Feb 07 '19 at 20:48
  • This is, because the "With" statement is not needed. You can combine them solely with the UNION statment, i. e. get rid of "With combined AS (" and the closing parenthesis and it should work. – MichaelA Oct 07 '20 at 11:13
2

Another method is to add the same output under two different ASA Alias for defined Output Sinks. For example: 1 output, MyBlob can be reference in ASA as 2 Alias, OutPut1 and OutPut2.

Benefits to this method are to allow control of the path that the data is output to. While it may go to the same output sink, it can be configured to output to a different path or container.

Tiffany Fischer
  • 118
  • 7
1

No, I just tried and it fires this error while starting the job

Stream Analytics job has validation errors: Query compilation error: Duplicate output names are not allowed 'output-cosmos'.

I used the following query

SELECT deviceId, pgm
INTO [output-cosmos]
FROM [input-hub]
WHERE pgm.running = true

SELECT deviceId, pgm
INTO [output-cosmos]
FROM [input-hub]
WHERE pgm.running = true
Riccardo Bellini
  • 248
  • 2
  • 7
  • Above query is different than the suggestion. This doesn't union, it has two parallel queries to the same output. Please use union if you care about ordered outputs. Otherwise create two different outputs going to the same cosmos db and use one in each query – Vignesh Chandramohan Jul 21 '18 at 14:46