The default id field for a Azure Mobile Services Table, that the table is indexed on, is a string. By default the string that is auto-generated is a 36 character GUID string. This can be 3 or more times rest of the data sent as a HTTP Response to a default HTTP GET for each record from the Telemetry table . This blog discusses this issue and canvasses some options to resolve this overkill.
Ardjson: https://ardjson.codeplex.com
Previous: Ardjson-Part 8: Version 1.4 of the Telemetry Code
This version is implemented in 1.5 JsonParserToGetTelemetrySensorValuesVersion1.5.zip on the Downloads tab at https://ardjson.codeplex.com
In earlier versions of the Telemetry sketch, the system would crash (with system restart) if there were more than a few records. This was because the complete Json array string needed to be stored before processing (parsing of the Json string). This was improved in the later versions of the sketch with the Json parser recoded to be a stream parser such that it operated as a state machine and inched its way through the string as each Response byte was received. All of the Get records sketch from to version 2.0 though cheated a bit on storage because only one name-value pair was required in storage. As each name-value pair was identified, it was display (sent as string to the serial port) and then reused in the next name-value interpretation.
Version 1.4 of the sketch stores all of the name-value pairs interpreted from the Json string embedded in the Response to an HTTP GET from an Azure Mobile Services table. Therefore with a resource challenged Arduino device, storage is again a problem. The code for version 1.5 is artificially limited in that it is limited to 12 name-value pairs in total, which should be increased. Version 2.0 significantly increases the number of records that can be received because it selects only the sensor and value fields to be sent, removing the id field. This id field can’t be ignored because it is the pointer to record identity (uniqueness) and is required for any updates and deletes.
The current version of Azure Mobile Services (AzMS) creates some field-values automatically when a record is added. Apart from the id field, there are the system properties fields as discussed in: ”Ardjson-Part 3c: The Azure Mobile Services API and further cURL HTTP GET Examples” These are:
The id field in the current version of AzMS is a string, into which the service inserts a 36 character GUID string by default. the id field can specify any string though and so the size of this string literal can be significantly reduced. For example if programmatically, if the id is an integer that gets converted to a string for POST and converted back from a string to integer with GET, typical POST code would be:
void send_request(int id, char * sensor, int value) { ... sprintf(buffer, "{\"id\":\"%d\",\"sensor\":\"%s\",\"value\":%d}", id,sensor,value); ... client.println(buffer); ... } void loop() { int Id=137; ... send_request(Id, Sensor,Value); .... }
A typical Response includes:
{"id":"137","sensor":"Temperature1","value":50} HTTP/1.1 201 Created
The id field must be unique. If the same id is POST it get rejected:
{"id":"137","sensor":"Temperature1","value":50} HTTP/1.1 409 Conflict
Hence it is up to the user/application to correctly assign an id to each new record submitted.
It is possible to maintain a counter within an sketch that is incremented as new records are POST. But this means that when the sketch starts, it must query the table to get the number of records. But then records may be deleted and so the maximum id value must be queried, which can be complex in this context. Alternatively one might create a second table with only one record: the last added id!.. Cumbersome! An auto-generated integer id field might be simpler.
The string-GUID id field was an update to AzMS. The first version used an auto-generated integer id but these tables are not available through the Azure Portal. Also, the System Property fields are not available with these tables.
You can create a Version One table using the Microsoft Cross-platform Command Line Tools:
azure account download
azure account import [path to .publishsettings file]
azure mobile table create --integerId [servicename] [tablename]
Note that the servicename isn’t the service URL but the name you gave the service, the first part of the URL.
Security: Delete the downloaded file when done and encrypt the ..azure folder in your user folder.
Further details with respect to the Cross-platform Tools.: http://tinyurl.com/ng864o5
An example:
Prompt>azure account download info: Executing command account download info: Launching browser to http://go.microsoft.com/fwlink/?LinkId=254432 help: Save the downloaded file, then execute the command help: account import <file> info: account download command OK Prompt>azure account import c:\azure\credentials.publishsetting info: Executing command account import info: account import command OK Prompt>azure mobile table create --integerId sportronicsdj telemetry2 info: Executing command mobile table create + Creating table info: mobile table create command OK Prompt>
The auto-generated id field is an SQL BigInt:
telemetry2 Browse Script Columns Permissions id sensor value 1 Temperature1 562 2 Temperature2 134 3 Humidty1 67 4 Humidty2 78 5 Temperature1 926
If downloaded as a JSon Response string the id would be interpreted as an integer requiring only only 2 bytes, compared to the 36 byte GUID string!
The following shows the small changes between V1.4 and V1.5 of this code:
For Arduino Mobile Services tables, create them as Version one tables to save on storage. This gives an integer id which also is useable with chronology sorts and searches. Note also, that if a random record is deleted its id isn’t recycled until all larger ids have been removed.
The drawback is that a date field would need to be added and added to the POST requiring a RTC on the Arduino device*.
* Update: Scripts that run when an Insert (HTTP PATCH) is run can auto-generate a DateTime field. See the next blog in the series,, Ardjson-Part9b: Azure Mobile Services Scripts.
A Reference for this content is: “New tables in Azure Mobile Services: string id, system properties and optimistic concurrency”, Carlos Figueira MSDN blog