Question

Jonathan Iwazaki Microsoft (MSFT CSG) on Tue, 14 Jun 2016 19:15:53


From @pidgey_op via Twitter:

"You asked if i was looking to trim the leading 0 or if i was looking for consistency between the StreamAnalytics and TSQL, and I guess the answer is both. The TSQL is hooked to a webapp (among other things) from which I can create a new machine (the device in question is an IoT hour meter for that machine). When I created the machine via my app (so using dbContext and the EntityFramework model which was generated from this database at the start of the project) it changes the machines serial number (mach_ID) from 01384 to 1384. This is fine. Unfortunately the hour meter is reporting the entire machine id (01384). This is happening via HTTP Post to an event hub. The stream analytic job pulls the message out of the event hub, processes it and stores it in the database. Knowing that 01384 probably wouldn't convert to 1384 on it's own, I tried casting it to a bigint, since integers don't hold on to the leading 0. I expected this to convert 01384 to 1384 the same as my TSQL db does (it's an int field) but it isn't reporting a failure and that '01384' is not a valid number (possibly suggesting that it is the Cast to a BigInt that is failing and not the attempt at inserting the data to the database)."

After making this work, the customer replied:

"I think i might have uncovered one small thing. When i send it a json with Mach_ID:01384 it fails, but Mach_ID:"01384" Succeeds. It seems like i should still be able to do that. It acts as if it doesn't recognize 01384 as a valid number (probably because of the leading 0) I guess that changes my earlier answer to "I'm trying to get rid of the leading 0", but cast not accepting a leading 0 might be something worth the engineers looking in to.

This is the exact error i get: "Input string '01384' is not a valid number. Path '[0].MACH_ID', line 1, position 17."

I'm not sure what exactly is wrong here, but there's an inconsistency between the TSQL query language and the stream analytics query language."

Thanks,

@AzureSupport


Sponsored



Replies

pidgey_op on Tue, 14 Jun 2016 19:57:43


Original questioner here with some updates. Using the Advanced REST Client extension for Chrome I fired a number of tests at my event hub to see what exactly failed and what doesn't.

The stream analytics query is as follows:

SELECT
    Mach_ID,
    Cast(MD_Occurence as DATETIME) as MD_Occurence, 
    Cast(MD_Battery as Float) as MD_Battery, (cast(MD_HM1 as float)/3600) as MD_HM1, 
    (cast(MD_HM2 as float)/3600) as MD_HM2, MD_EmissionsTripped,
    EventEnqueuedUtcTime
INTO
    MachineDataTable
FROM
    MachineDataStream

...and these are the data sets i sent to the event hub. These were confirmed by querying the database tables via SSMS.

[{"MACH_ID":6,
"md_occurence":"2016-06-14 14:15:41",
  "md_battery":12.5,
  "md_hm1":0000410760,
  "md_hm2":0000410760,
  "md_emissionsTripped":0}
]
SUCCESS
------------------------------------------------------------------------------------------------------
[{"MACH_ID":"6",
"md_occurence":"2016-06-14 14:16:41",
  "md_battery":12.5,
  "md_hm1":0000410760,
  "md_hm2":0000410760,
  "md_emissionsTripped":0}
]
SUCCESS
------------------------------------------------------------------------------------------------------
[{"MACH_ID":10001,
"md_occurence":"2016-06-14 14:16:41",
  "md_battery":12.5,
  "md_hm1":0000410760,
  "md_hm2":0000410760,
  "md_emissionsTripped":0}
]
SUCCESS
------------------------------------------------------------------------------------------------------
[{"MACH_ID":"10001",
"md_occurence":"2016-06-14 14:17:41",
  "md_battery":12.5,
  "md_hm1":0000410760,
  "md_hm2":0000410760,
  "md_emissionsTripped":0}
]
SUCCESS
------------------------------------------------------------------------------------------------------
[{"MACH_ID":"1384",
"md_occurence":"2016-06-14 14:17:41",
  "md_battery":12.5,
  "md_hm1":0000410760,
  "md_hm2":0000410760,
  "md_emissionsTripped":0}
]
SUCCESS - DELAYED?
------------------------------------------------------------------------------------------------------
[{"MACH_ID":1384,
"md_occurence":"2016-06-14 14:18:41",
  "md_battery":12.5,
  "md_hm1":0000410760,
  "md_hm2":0000410760,
  "md_emissionsTripped":0}
]
SUCCESS
------------------------------------------------------------------------------------------------------
[{"MACH_ID":01384,
"md_occurence":"2016-06-14 14:19:41",
  "md_battery":12.5,
  "md_hm1":0000410760,
  "md_hm2":0000410760,
  "md_emissionsTripped":0}
]
FAILED - SEE NOTES BELOW
Error:
Message:
Input string '01384' is not a valid number. Path '[0].MACH_ID', line 1, position 17.

Message Time:
2016-06-14 19:39:38Z

Type:
IncorrectSerializationFormat
------------------------------------------------------------------------------------------------------
[{"MACH_ID":"01384",
"md_occurence":"2016-06-14 14:19:41",
  "md_battery":12.5,
  "md_hm1":0000410760,
  "md_hm2":0000410760,
  "md_emissionsTripped":0}
]
SUCCESS

As you can see, it doesn't seem to care what i hand it for an ID, as long as its a valid number. It treats numbers inside and outside of quotes the same. When given the id without the leading 0, it finds it in both cases. When given the id WITH the leading zero, but inside of quotes, it accepts it. It is only when it has a leading zero and isn't wrapped in quotes that it fails. To amend this, I attempted to cast the incoming id as an integer with the thought process that, no matter what format it thinks it is in, casting it to an integer would remove the leading zero and make it valid (which is what i think is dynamically happening currently when it is handed the string "01384" rather than an integer '01384'). Unfortunately doing this nets the same error as seen above: "Input string '01384' is not a valid number. Path '[0].MACH_ID', line 1, position 17."

Ryan CrawCour [MSFT] on Tue, 14 Jun 2016 21:03:34


So this is about handling "01384" ... a string representing a number.
Are you looking for the output to be an int without the leading 0, hence you trying to case the string to an int?

pidgey_op on Wed, 15 Jun 2016 12:26:09


Hi Ryan,

Yes, that was my intended goal. I've likely caught this early enough in my project that I can have a firmware revision made on my end to dodge the error (by wrapping the serial number in quotes, the stream analytics compiler properly handles it...i have theories on why that's happening; it seems to be something with the way the JSON is being deserialized). 

What I find odd is this: If I hand the SA (stream analytics) the message with the serial in quotes, it handles it perfectly, regardless of the leading 0. When it isn't wrapped in quotes, it fails, claiming that the input string '01348' is invalid.

To me, this isn't the Cast command failing to turn 01348 into a number (because when handed a string of the same value it does it fine). This seems more like the deserializer is trying to create an integer value and load that value into it (01348) and that is saying "hey, this isn't a real number, because numbers don't have leading 0's!".

I mean, we all know that they do have leading 0's, they just aren't displayed, but something about this deserialization process isn't able to handle that. I think that's what needs to be addressed, not the Cast command.

Ryan CrawCour [MSFT] on Mon, 27 Jun 2016 18:58:17


Thanks for the additional info. I will chase up what appears to be the root cause of this.