Using an External Table output in Azure Functions

As seen in my previous blog, I am experimenting with passing data to a SqlAzure table using Azure Functions.

I first used the new and apparently experimental External Table output available in Azure Functions. Experimental just means in preview.

Due to throttling issues, I had to abandon it where I wanted to insert multiple (+200) records.

But this Azure Functions output is a very promising way to insert records in all kinds of data sources. I can recommend using it when you want to insert, alter or lookup data.

Let’s take a closer look.

When you create an Azure Function, it comes standard with the trigger of your choice and a TraceWriter for logging.

You can add extra output called External Table (Experimental):

 

Note: The External Table can also be defined as an Input of an Azure Function.

You get this form to fill in:

We are making use of SqlAzure. And you have to provide a SqlAzure database and table:

Note: I ignore the Data Set Name and the Entity ID. I do not need it to access SqlAzure.

The External Table connection is representing the connection string to SqlAzure. You can fill it in using a dialog:

In the background, this creates a SQL Api connection as a resource in your Resource group

You can change the connection over here afterward:

Let’s go back to the Azure Function and the External table output. Do you see the documentation link? If you open it, you will see just a link:

But this link is great!

Look which data sources are supported:

Connector	Trigger	Input	Output
DB2		x	x
Dynamics 365 for Operations		x	x
Dynamics 365		x	x
Dynamics NAV		x	x
Google Sheets		x	x
Informix		x	x
Dynamics 365 for Financials		x	x
MySQL		x	x
Oracle Database		x	x
Common Data Service		x	x
Salesforce		x	x
SharePoint		x	x
SQL Server		x	x
Teradata		x	x
UserVoice		x	x
Zendesk		x	x

Now this is what I like!

But for now, I am using SqlAzure (Sql Server).

The same page also shows how to use the External Table as input for a list function using “table.ListEntitiesAsync( continuationToken: continuationToken);”.

Unfortunately, this is the only example shown on the page.

Inserting records

Here is an example of how to insert a record.

I first pass a JSON message:

{
  "deviceId":"testdevice",
  "temperature" : 21.5,
  "humidity" : 45.5,
  "fan" : true,
  "led" : false,
  "timeStamp" : "2017-11-08T18:25:43.511Z"
}

And I submit the record in the database using:

#r "Microsoft.Azure.ApiHub.Sdk"
#r "Newtonsoft.Json"

using System;
using Microsoft.Azure.ApiHub;
using Newtonsoft.Json;

public static async Task Run(string myEventHubMessage, TraceWriter log, ITable<Telemetry> outputTable)
{
  log.Info($"C# manually triggered function called with input: {myEventHubMessage}");

  dynamic json = JsonConvert.DeserializeObject(myEventHubMessage);

  var telemetry = new Telemetry
  {
     DeviceId = json.deviceId,
     Temperature = json.temperature,
     Humidity = json.humidity,
     Fan = json.fan,
     Led = json.led,
    TimeStamp = DateTime.Now
  };

  // insert
  await outputTable.CreateEntityAsync(telemetry);
}

public class Telemetry
{
  public string DeviceId {get; set;}
  public decimal Temperature {get; set;}
  public decimal Humidity {get; set;}
  public bool Fan {get; set;}
  public bool Led {get; set;}
  public DateTime TimeStamp {get; set;}
}

What is happening?

First, I have to add “ITable<Telemetry> outputTable” by hand in the Azure Function, this parameter is nog added automatically when the output dialog is saved.

and with the generic ITable<some class> I can specify the format of the table. To use this ITable, I have to reference the “Microsoft.Azure.ApiHub.Sdk” library.

In my example, I then deserialize the JSON entity and fill a new Telemetry class. The new ‘record’ is inserted in the table using  “await outputTable.CreateEntityAsync(telemetry)”.

And that’s it. The record is actually inserted:

The ITable<> class

I was interested in the functionality of this ‘obscure’ ITable. The functionality can be found on GitHub actually. I like that, it’s open source!

And this is what I found yet:

Task<TableMetadata> GetMetadataAsync(
    CancellationToken cancellationToken = default(CancellationToken));
Task<TEntity> GetEntityAsync(
    string entityId,
    CancellationToken cancellationToken = default(CancellationToken));
Task<SegmentedResult<TEntity>> ListEntitiesAsync(
    Query query = null,
    ContinuationToken continuationToken = null,
    CancellationToken cancellationToken = default(CancellationToken));
Task CreateEntityAsync(
    TEntity entity,
    CancellationToken cancellationToken = default(CancellationToken));
Task UpdateEntityAsync(
    string entityId,
    TEntity entity,
    CancellationToken cancellationToken = default(CancellationToken));
Task DeleteEntityAsync(
    string entityId,
    CancellationToken cancellationToken = default(CancellationToken));

A few observations can be made.

First of all, the insert does not return the primary key of the record inserted.

And as I found out later on (that’s why I got the throttling issue), there is no support for batch execution.

Filtering records

The batch execution cannot be fixed. But we can find our inserted record if it can be identified uniquely using other fields:

...
var batchSize = 10;
var fieldName = "DeviceId";
var filter = "DeviceId eq 'testdevice'";
var query = Query.Parse($"$top={batchSize}&$orderby={fieldName}&$filter={filter}");

//retreive table values
var telemetrySegment = await outputTable.ListEntitiesAsync(query);

foreach (var telemetry in telemetrySegment.Items)
{
  log.Info($"Found {telemetry.DeviceId} {telemetry.Temperature} {telemetry.Humidity} {telemetry.Fan} {telemetry.Led} {telemetry.TimeStamp}");
}
...

Yes, filtering a record is actually done in the format of OData filtering. And yes, I like this a lot!

Conclusion

Again, This External Table output is very promising. If I look at the long list of supported data sources, this will be a success in the end.

I hope that Batch execution of actions will be added, this would be a great addition due to the nature of Azure Functions and the usage in an IoT Platform (to prevent throttling).

Meanwhile, just start using it and check out the github page.