locked
IoT Hub with Particle Photon RRS feed

  • Question

  • Hi,
    I am having a very annoying problem, which i cannot see to find out why it occurs. I use Particle Photon to publish data to Azure IoT Hub. I then use Stream Analytics to get the inputStream and output it to a database. I use the database to expose data to my webapi so i can create a nice webview with angularJS.

    Its working for some messages, but after like 8 messages i get this message: 

    Encountered error trying to write 1 event(s): Cannot convert from property 'temperature' of type 'System.String' to column 'temperature' of type 'System.Double'

    My question is.. why is the first messeges going through and suddenly after a while my temperature gets converted to a string?


    Thursday, December 15, 2016 10:48 PM

All replies

  • Hi,

    It is hard to say for sure without seeing query or your input data, but here is one of very likely situations where this can happen.

    Let say your query is as simple as "SELECT col1 FROM input" and your input data is a sequence of events like:

    { "col1" : 1.0 }
    { "col1" : 2.0 }
    { "col1" : "3" }
    { "col1" : 4.0 }

    then the result will also be of type Float for all events except the third, where it will be of type Nvarchar(Max).

    If this is what you may expect in the input then explicitly converting to desired type should help, like "SELECT col1 = CAST(col1 AS Float) FROM input".

    Please keep in mind though that an event like this:

    { "col1" : "not a number" }

    will still cause an error.

    Thanks,
    Alex.


    Azure Stream Analytics.

    Friday, December 16, 2016 6:03 AM
  • Hi,
    Yes i will try that out!

    the query looks like this: 

    SELECT 
        temperature, humidity, light, motion, timecreated, guid, carbondioxide
       INTO
        StreamAnalyticsOutput
       from
        iotsensorhub;

    The test data from sample looks like this:


    Does that means that all data sent in are sent as Nvarchar(Max) ?

    EDIT
    I am now trying to cast the data, but having trouble with LIGHT and CO2 cause they are INT. Does Stream Analytics support int at all ? 

    • Edited by BennyHoang Friday, December 16, 2016 10:58 AM
    Friday, December 16, 2016 9:41 AM
  • Yes, from your sample data it looks like all values are Nvarchar(Max).

    Stream Analytics does not have Int type, you should use Bigint (or Float, if your data can have values like say 42.1).

    Also if on Stream Analytics side you ouse Bigint, it is fine to have Int column in SQL output, since we would autoconvert, and only fail if some value is really out of the range of Int.

    Thanks,
    Alex.


    Azure Stream Analytics.

    Friday, December 16, 2016 6:32 PM
  • Casting didnt work. Stream Analytics fail to run when i paste your code in. It seems it is working when the temperature is outputed as the data i showed over. But sometimes it fails to read the sensor so it outputs NaN. I have converted it from my devices side so it will output 0.0 if it can't read the sensor. It seems to be working now (so far) 
    Friday, December 16, 2016 9:15 PM
  • I see, so "NaN" is the issue then.

    If you want/need to push this logic to Stream Analytics you can do with following (for 'temperature' as an example):

    SELECT
        ...
        temperature = CASE WHEN temperature = 'NaN' THEN 0.0 ELSE CAST(temperature AS Float),
        ...
    FROM input
    


    Azure Stream Analytics.

    Friday, December 16, 2016 10:54 PM
  • Seems like this works :) I also changed the error policy to "DROP" because from my device is sometimes sending odd informations.. but i think the problem is solved :) i will test it for maybe one more day and see whats occuring. Thank you for your help so far! 
    Friday, December 16, 2016 10:58 PM