Input file to Azure Stream analytics in Json format error while previewing

N2120 81

Thanks for your time, I have a job in azure stream analytics with an input json format file which is accessed through Azure data lake. the format of json is of format [{"tag": {"id":"1234","name":"viv","date":2020-01-01"}}] and when I run the query locally for hours , I do not see the input events getting ingested. User's image

the json file is of gz format and has the below

"Serialization": {
    "Type": "Json",
    "Encoding": "UTF8"
},

I try to preview data and I get the error

SyntaxError: Expected property name or '}' in JSON at position 1 (line 1 column 2)
Make sure the input has recently received data and the correct format of those events has been selected.

can you pls advice if the how the file can be ingested

Sander van de Velde 28,386 Reputation points MVP

2024-02-21T22:47:20.3533333+00:00

Hello @N2120 ,

Are you testing this in the Azure portal?

Can you try the same with the VS Code Stream Analytics extension?

It takes a moment to understand how this project works and how inputs and outputs must be set but you can test within second is the flow is correct, even with cloud input and local output.

Regarding the file extensions, .gz means its compressed. Have you tested with the GZip or Deflate compression type setting?
N2120 81 Reputation points

2024-02-21T23:04:46.04+00:00
@Sander van de Velde the above error messages are from visual code stream analytics only. Here are the other config of json input from VS code extention:

"Serialization": { "Type": "Json", "Encoding": "UTF8" }, "PartitionKey": null, "CompressionType": "GZip", "ScriptType": "Input"

Yes gz is compressed. I am unable to preview data from portal as well. these are the settings from portal

and I am able to sucessfully test the connection .
while previewing input data, from query , I am getting this error  While sampling data, no data was received from '1' partitions. Thanks for your help.

1 answer

Wilko van de Velde 2,226 Reputation points

2024-02-22T07:31:00.6433333+00:00

Hello @N2120 ,

The (input) JSON you provided has not the correct syntax, this should be:
[{"tag": {"id":"1234","name":"viv","date":"2020-01-01"}}]

There was a quote missing before the date. This could be a typo, so I'm not sure if this solves your problem.

Therefore I tried to recreated your setup with a storage account and a stream analytics job. I saved just a regular JSON (without compression) to the storage account:

Next, I created a input in stream analytics:

When then looking at the preview of the input the content is shown:

So, try to save an example file in JSON into your storage account and test the setup with that file. If this works, this might indicate there is something wrong with the compression.

Hope this will help.

Kind Regards,

Wilko

Please do not forget to "Accept the answer” wherever the information provided helps you, this can be beneficial to other community members. If you have extra questions about this answer, please click "Comment".
Please sign in to rate this answer.
Sander van de Velde 28,386 Reputation points MVP

2024-02-22T08:02:01.6566667+00:00

If this works, move over to the same flow but with .GZ (compressed) format.

N2120 81 Reputation points

2024-02-22T11:53:36.7366667+00:00

@Sander van de Velde thank you. Yes I missed a quote in json . Apologize. When I upload a sample file in portal after decompression in plain json and upload it, it works and shows data . when i upload sample file in uncompressed gz format it doesn’t extract it , shows the jibberish data . Is compression not working . . How can I overcome this then ?

Sander van de Velde 28,386 Reputation points MVP

2024-02-22T13:35:03.69+00:00

Hello N2120, How are the files compressed? Did you actually use either GZIP or Deflate or another method?

With 'uncompressed gz format' you mean just renaming the JSON file? But you had the decompression option on?

N2120 81 Reputation points

2024-02-22T14:05:08.7033333+00:00

@Sander van de Velde the input files are live stream data sent to us in compressed format stored in data lake.

goal for the input stream is to able to uncompress those gz files and extract the json file and use it in query. currently, when I upload sample file of actual gz file , I dont see it uncompressing it and sending the json back even though the settings say Event Compression Type: gz Event Serialization format: json in portal .

Wilko van de Velde 2,226 Reputation points

2024-02-23T07:03:51.53+00:00

I did another test:

I compressed the plain json file from yesterday with 7zip to a gzip compressed file.

Then I uploaded this to the storage account and changed the input of Stream Analytics so it would take gzip compression.

With this change, the preview is still visible.

Can you try this suggestion as well? If this works, this might indicate there is something wrong with the compression.

N2120 81 Reputation points

2024-02-23T12:16:52.6266667+00:00

@Wilko van de Velde Thanks for trying that. my Json file starts with a square bracket and then curly braces. I believe that is what is causing issues. Can you try your sample file with the same format and then compress it and view it in portal.

Wilko van de Velde 2,226 Reputation points

2024-02-23T13:12:44.37+00:00

I already have the square brackets:

I uploaded my file to GitHub, can you please try it with this file: https://github.com/wilkovandevelde/MicrosoftQ-A

N2120 81 Reputation points

2024-02-25T00:32:34.3066667+00:00

@Wilko van de Velde I deleted all files and just uploaded one file and it worked in portal but I couldnt make it work locally with visual code extension. Is there a way to export the input .json from portal as a downloadable file so that I can make it work locally

Wilko van de Velde 2,226 Reputation points

2024-02-26T06:58:55.2533333+00:00

You can download the file using the portal, just go to the container in the storage account and then select the file. A new window will pop up and there is a download button:
Sign in to comment