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:

    • __createdAt: system created to track the creation of the record.
    • __updatedAt: sssystem created to track the last update to the record.
    • __version: version used to support concurrency.
    • __deleted: system created to support soft delete.

     

    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.

Version One of Azure Mobile Services Tables

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:

  • Download and install the Windows version from:  Microsoft Cross-platform Command Line Tools Windows installer.
  • First set up your Azure credentials:
  • From an Admin  command prompt  enter:
  • azure account download
  • Login with  the same account credential you us to login to the Azure Portal.
  • A browser will open
  • Save the file  when prompted without spaces in the path (ie. Change the filename to have no spaces etc.. or use double quotes around the path in the next step).
  • From the same command prompt:
azure account import [path to .publishsettings file]
  • Then run the following command:
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:

image

 

Conclusion

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


Next: Ardjson-Part9b: Azure Mobile Services Scripts