Passing JSON arrays to SqlAzure from Azure Functions using OPENJSON

During my last project, we had to pass arrays of data to SqlAzure.

Normally, passing data is not that interesting. But we were passing data in bulk to SqlAzure so there is always a chance of throttling.

This is when you pass correct records towards SqlAzure but you reach certain limitations eg. if you hit the DTU limits.

We were getting a few hundred records which we first inserted one by one (using the ExternalTable (experimental) output). But we only we able to insert approx. forty records.

So we switched back to a stored procedure.

The second challenge was how to pass an array of records. Because we got the data as JSON, we wanted to pass an array of records like JSON.

In the example below, I show how to call a stored procedure within an Azure Function. The code could be simplified if not I wanted to do some transformations.

Setting up the stage for the database

Before we check out the Azure Function, first we set up the database.

I just created a SqlAzure database (inside a SqlAzure server) and filled it with one table:

CREATE TABLE [dbo].[telemetry]
(
  [Id] [int] IDENTITY(1,1) NOT NULL,
  [DeviceId] [nvarchar](50) NOT NULL,
  [Temperature] [float] NOT NULL,
  [Humidity] [float] NOT NULL,
  [Fan] [bit] NOT NULL,
  [Led] [bit] NOT NULL,
  [TimeStamp] [datetime] NOT NULL,
    CONSTRAINT [PK_telemetry] PRIMARY KEY CLUSTERED
    (
      [Id] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

This creates a simple table which has to be filled using a stored procedure. This stored procedure reads JSON and inserts in bulk.

The JSON we pass to the table will look like this:

[
  {
    "DeviceName":"testdevice",
    "TimeStamp":"2017-11-08T18:25:43.511Z",
    "Temperature":21.5,
    "Humidity":45.5,
    "Fan":true,
    "Led":false
  },
  {
    "DeviceName":"testdevice",
    "TimeStamp":"2017-11-08T18:25:48.511Z",
    "Temperature":22.5,
    "Humidity":44.5,
    "Fan":false,
    "Led":true
  }
]

So I constructed this stored procedure:

CREATE PROCEDURE usp_insert_telemetry_json
  @jsonString nvarchar(max)
AS
BEGIN
  -- SET NOCOUNT ON added to prevent extra result sets from
  -- interfering with SELECT statements.
  -- SET NOCOUNT ON;

  INSERT INTO telemetry
    ([DeviceId], [TimeStamp], [Temperature], [Humidity], [Fan], [Led])
  SELECT
     [DeviceId], [TimeStamp], [Temperature], [Humidity], [Fan], [Led]
  FROM OPENJSON(@jsonString)
  WITH 
  (
    [DeviceId] nvarchar(50) '$.DeviceName',
    [TimeStamp] datetime '$.TimeStamp',
    [Temperature] float '$.Temperature',
    [Humidity] float '$.Humidity',
    [Fan] bit '$.Fan',
    [Led] bit '$.Led'
  )
END

What we see it a string as the parameter passed to the stored procedure. This string is converted to an array using OPENJSON(). This results in a list which is used for the INSERT INTO SELECT construction.

And that’s all.

Do you see I have put the SET NOCOUNT ON into comments? I want to see how many records are created with the stored procedure call.

Calling the stored procedure

So let’s switch over to the stored procedure. I get my messages from an IoTHub. The JSON message we receive looks like this:

{
  "deviceName": "testdevice",
  "telemetry": 
  [
    {
      "temperature" : 21.5,
      "humidity" : 45.5,
      "fan" : true,
      "led" : false,
      "timeStamp" : "2017-11-08T18:25:43.511Z"
    },
    {
      "temperature" : 22.5,
      "humidity" : 44.5,
      "fan" : false,
      "led" : true,
      "timeStamp" : "2017-11-08T18:25:48.511Z"
    }
  ]
}

The message contains an array of telemetry items and is passed by one device called “testdevice”.

We pass this structure to the SqlAzure table using an EventTrigger:

#r "System.Data"
#r "Newtonsoft.Json"
#r "System.Linq"

using System;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using Newtonsoft.Json;
using System.Linq;

public static void Run(string myEventHubMessage, TraceWriter log)
{
  log.Info($"Telemetry list trigger function processing raw message: {myEventHubMessage}");

  var dynamicMessage = JsonConvert.DeserializeObject<dynamic>(myEventHubMessage);

  var telemetryList = new List<Telemetry>();

  foreach(var item in dynamicMessage.telemetry)
  {
    telemetryList.Add(new Telemetry
    {
      DeviceName = dynamicMessage.deviceName,
      Temperature = item.temperature,
      Humidity = item.humidity,
      Fan = item.fan,
      Led = item.led,
      TimeStamp = item.timeStamp
    });
  }

  var telemetryListString = JsonConvert.SerializeObject(telemetryList);

  log.Info($"Output started for {telemetryList.Count} items; {telemetryListString}");

  // you can better store a connection string in the appsettings of your Azure Function
  var connectionstring = "[add the connection string of your SqlAzure DB here. Do not forget to fill it with name and password]";
  using (SqlConnection con = new SqlConnection(connectionstring))
  {
    using (SqlCommand cmd = new SqlCommand("usp_insert_telemetry_json", con)) 
    {
      cmd.CommandType = CommandType.StoredProcedure;
      cmd.Parameters.Add("@jsonString", SqlDbType.VarChar).Value = telemetryListString;
      con.Open();
      
      var countRowsAffected = cmd.ExecuteNonQuery();

      log.Info($"processed SQL command succesfully; uploaded {countRowsAffected} rows");
    }
  }
}

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

What is happening here?

the IoTHub message is converted to a Dynamic. This is just for convenience, I do not have to define C# classes first for mapping JSON to an instance.

Because I know the structure of the JSON message, I can transform it into another (array) structure. This is the format which is accepted by the stored procedure easily.

And finally, I serialize the new structure to a JSON string and pass it to the stored procedure.

After execution of the stored procedure, I receive the number of records inserted.

It’s good to see this stored procedure is not throttled. I can easily execute a lot of records at once:

2017-11-09T19:12:05.525 Function started (Id=1de7f233-2088-43e4-841a-e352f2cbfce9)
2017-11-09T19:12:05.651 Telemetry list trigger function processing raw message: {
    "deviceName": "testdevice",
    "telemetry": 
    [
       ....
    ]
}
2017-11-09T19:12:07.245 Output started for 534 items; [{"DeviceName":"testdevice"....
2017-11-09T19:12:09.854 processed SQL command succesfully; uploaded 534 rows
2017-11-09T19:12:09.872 Function completed (Success, Id=1de7f233-2088-43e4-841a-e352f2cbfce9, Duration=4341ms)

#r references

We make use of both the System.Data and Newtonsoft.Json libraries. We have to reference them using the #r notations. This notation looks a bit wierd but it’s just like adding references in a console application in Visual Studio.

Conclusion

As demonstrated, using a stored procedure makes it possible to pass large amounts of data into the SqlAzure database without worrying about throttling due to a lot of single inserts.

I used some mapping to show how to convert the format of the JSON to an array. But it should be easy to just pass the raw JSON string directly into the stored procedure. This should simplify the Azure function even more.

Advertentie

Een gedachte over “Passing JSON arrays to SqlAzure from Azure Functions using OPENJSON

Reacties zijn gesloten.