0

I am trying to write an SAQL on the data which is coming from event Hub in json format.

The input to the azure Stream Analytics job is as shown below.

{"ver":"2019-12-28 18:41:45.4184730","Data":"Data01","d":{"IDNUM":"XXXXX01","Time1":"2017-12-20T00:00:00.0000000Z","abc":"610000","efg":"0000","XYZ":"00000","ver":"2017-12-20T18:41:45.4184730Z"}}
{"ver":"2019-12-28 18:41:45.4184730","Data":"Data01","d":{"IDNUM":"XXXXX02","Time1":"2017-12-20T00:00:00.0000000Z","abc":"750000","efg":"0000","XYZ":"90000","ver":"2017-12-20T18:41:45.4184730Z"}}
{"ver":"2017-01-01 06:28:52.5041237","Data":"Data02","d":{"IDNUM":"XXXXX03","acc":-10.7000,"PQR":35.420639038085938,"XYZ":139.95817565917969,"ver":"2017-01-01T06:28:52.5041237Z"}}
{"ver":"2017-01-01 06:28:52.5041237","Data":"Data02","d":{"IDNUM":"XXXXX04","acc":-8.5999,"PQR":35.924240112304688,"XYZ":139.6097412109375,"ver":"2017-01-01T06:28:52.5041237Z"}}

In the first two rows, the attribute Time1 is available where as in last two rows Time1 attribute itself is not present.

I have to store the data into cosmos DB based on the Time1 attribute in the input data.

Path in json data >>> input.d.Time1.

I have to store data which are having Time1 into a cosmosDB container and data which are not having Time1 into another container.

I tried with the below SAQL.

SELECT [input].ver,
    [input].Data,
    d.*
INTO [cosmosDB01]
FROM [input] PARTITION BY PartitionId
WHERE [input].Data is not null
AND [input].d.Time1 is not null

SELECT [input].ver,
    [input].Data,
    d.*
INTO [cosmosDB01]
FROM [input] PARTITION BY PartitionId
WHERE [input].Data is not null
AND [input].d.Time1 is null

Is there any other ways like IS EXISTS keyword in stream analytics query ?

Vasucd
  • 357
  • 2
  • 10
Antony
  • 970
  • 3
  • 20
  • 46
  • hi,Antony,any updates here? – Jay Gong Jan 10 '20 at 07:02
  • I am getting output for the below query. ```SELECT [input].ver, [input].Data, d.* INTO [cosmosDB01] FROM [input] PARTITION BY PartitionId WHERE [input].Data is not null AND [input].d.Time1 is null ``` even though the field d.Time doesn't exists. – Antony Jan 11 '20 at 04:19
  • @Jay Gong- I am getting output for the above query, but watermark is getting increased drastically and SU utilization becomes 90% and count of backlogged input is also increasing.. Provided enough SU and RU, but still facing the issues. Is that because of the SAQL written above? – Antony Jan 11 '20 at 09:53

1 Answers1

0

Per my knowledge,there is no is_exists or is_defined sql built-in keyword in ASA so far. You have to follow the way you mentioned in the question to deal with multiple outputs scenario.

(Similar case:Azure Stream Analytics How to handle multiple output table?)

Surely,you could submit feedback to ASA team to push the progress of ASA.

Jay Gong
  • 23,163
  • 2
  • 27
  • 32