CSV reference data in Azure Stream Analytics

Question

I have a stream analytics application where events are JSON encoded and look like this

{customerID: 45, state:"S2" , timestamp:"2017-06-06 14:19:21.77"}
{customerID: 74, state:"S2" , timestamp:"2017-06-06 14:19:26.61"}
{customerID: 79, state:"S2" , timestamp:"2017-06-06 14:19:28.50"}
{customerID: 10, state:"D" , timestamp:"2017-06-06 14:19:31.79"}
{customerID: 70, state:"S2" , timestamp:"2017-06-06 14:19:31.93"}
{customerID: 37, state:"S2" , timestamp:"2017-06-06 14:19:32.17"}
{customerID: 41, state:"D" , timestamp:"2017-06-06 14:19:33.48"}

I have reference data for customers in a CSV file that looks like this:

"CUST_ID", "Age", "Rich"
1, "50", "Y"
2, "22", "N"

I load the data files in the aforementioned formats and test the following query

select A.[CUSTOMERID], A.[state], B.[AGE], B.[GENDER_CODE] from clickstream A timestamp by A.[TIMESTAMP] left join refdata B on A.[CUSTOMERID]=B.[CUST_ID]

I get the following error message with no details:

Now, the same query works perfectly if the data reference data is represented as JSON. Is there a working example I can have a look at with CSV reference data?

score 1 · Answer 1 · answered Jun 08 '17 at 05:54

1

I have reference data for customers in a CSV file

When we create input, we need to specify Event serialization format which let Stream Analytics know which serialization format (JSON, CSV, or Avro) we're using for incoming data streams. Please check the Event serialization format of your input refdata, and make sure if it is set to CSV.

answered Jun 08 '17 at 05:54

Fei Han

26,415
1
30
41

Thanks for your reply. I checked that and it is indeed set to CSV. Were you able to successfully test a job with CSV reference data? I cannot even get a minimum working example to test successfully. – nihil0 Jun 08 '17 at 10:17

score 1 · Answer 2 · answered Dec 12 '17 at 12:22

I had the same problem with stream analytics.

Just FYI, converting the .csv file to .json file (using a online tool for example), and changing the reference data serialization format to JSON solved the problem. Now I can test the query by submitting the json-file trough the Azure portal query tool. Seems like CSV implementation of stream analytics is buggy, as I'm sure that my csv was correctly formatted.

score 0 · Answer 3 · answered Nov 14 '17 at 12:27

"Test" button with reference data didn't work for me at all. I had to use Azure Stream analytics tools for Visual Studio to test it in local environment.

Regarding an error in path pattern. I used YYYY-MM-DD and by default stream analytics used YYYY/MM/DD. When the job was running, I saw an exclamation mark next to input in Stream analytics dashboard.

To debug error you should click on reference data input in Stream Analytics overview and you should see warnings on its blade.

Mine warning was like: Initializing input without a valid reference data blob for UTC time 11/14/2017 12:22:32 PM, example path: 'https://blabla.blob.core.windows.net/cohorts/2017/11/14/12-22/result.csv'

CSV reference data in Azure Stream Analytics

3 Answers3