Getting sensor data into a Microsoft Azure SQL database

In this guide we will:

  1. Setup a Microsoft Azure SQL database for your sensor data
  2. Setup one database table per type of sensor
  3. Configure an Azure Function to insert sensor data into the tables in this database

Before you begin

You need to have completed the Microsoft Azure integration guide.

Step 1: Create and configure the database

Use the following guide but:

  1. create a blank database (instead of the sample used in the guide),
  2. note down the SQL server admin username and password and
  3. skip the “Query the SQL database” part.

(Microsoft) Create an Azure SQL database in the Azure portal

Step 2: Create tables

We need to create the tables where the events will be stored.

Navigate to the SQL server that we just created by clicking on SQL databases in the menu, and then clicking on the SQL server name.

open-sql-database.png

Open the Query editor and login with the SQL server admin username and password.

open-query-editor.png

Copy the following queries to the Query 1 window and run them, one by one, by pressing the Run button.

CREATE TABLE touch_events (
  event_id char(23) NOT NULL UNIQUE,
  device_id char(20) NOT NULL,
  timestamp char(30) NOT NULL
);

Sets up the table for touch events.

CREATE TABLE temperature_events (
  event_id char(23) NOT NULL UNIQUE,
  device_id char(20) NOT NULL,
  timestamp char(30) NOT NULL,
  temperature float NOT NULL
);

Sets up the table for temperature events.

CREATE TABLE prox_events (
  event_id char(23) NOT NULL UNIQUE,
  device_id char(20) NOT NULL,
  timestamp char(30) NOT NULL,
  state varchar(15) NOT NULL
);

Sets up the table for proximity events.

CREATE TABLE water_events (
  event_id char(23) NOT NULL UNIQUE,
  device_id char(20) NOT NULL,
  timestamp char(30) NOT NULL,
  state varchar(15) NOT NULL
);

Sets up the table for water events.

CREATE TABLE humidity_events (
  event_id char(23) NOT NULL UNIQUE,
  device_id char(20) NOT NULL,
  timestamp char(30) NOT NULL,
  temperature float NOT NULL,
  humidity float NOT NULL
);

Sets up the table for humidity events.

CREATE TABLE countingprox_events (
  event_id char(23) NOT NULL UNIQUE,
  device_id char(20) NOT NULL,
  timestamp char(30) NOT NULL,
  total integer NOT NULL
);

Sets up the table for countingprox events.

CREATE TABLE countingtouch_events (
  event_id char(23) NOT NULL UNIQUE,
  device_id char(20) NOT NULL,
  timestamp char(30) NOT NULL,
  total integer NOT NULL
);

Sets up the table for countingtouch events.

For each query you run, you should see a “Query succeeded…” message.

query-success.png

We might get the same event more than once as a side-effect of the Data Connectors have an at-least-once guarantee. To prevent duplicate rows, these queries make the event_id unique.

Step 3: Get the Connection String

To connect the Azure Function with the SQL database, we are going to need connection details that are stored in something called a “connection string”.

To get this string, go back to SQL databases, click your database, and then Show database connection strings.

open-connection-strings.png

In the ADO.NET tab, copy the entire string (use the copy button). It will look something like this one (but all on one line):

copy-connection-string.png

Paste this one into any text editor, replace {your_username} and {your_password} in the string (also remove the curly-braces) with the SQL server admin username and password.

Save this connection string for later.

Set up Azure Function to write to the database

Step 1: Add a connection string to Azure Function

Open the application settings of your Azure Function, by going to App Services, click on the function name, go to the Platform features tab, and then Application settings.

open-application-settings.png

Scroll down to Application settings, press Add new setting and:

  1. Name it SQLDB_CONNECTION
  2. Copy and paste the connection string from earlier into the Value field.

add-connection-string.png

Remember to scroll up and press Save.

Step 2: Change Azure Runtime from v3 to v2

Although the example might work in the latest Azure Runtime version (v3) we recommend using version 2 as there have been some reports on dependencies not loading correctly when using in-portal editing.

From the Platform features tab select the Function app settings.

function-app-settings.png

Scroll down to Runtime version, and select ~2.

function-app-settings-select.png

Step 3: Edit Azure Function code

Go to the Azure Function and replace the existing code with the following code listing:

#r "System.Configuration"
#r "System.Data"

using System.Net;
using System.Configuration;
using System.Data.SqlClient;
using System.Threading.Tasks;

public static async Task<HttpResponseMessage> Run(HttpRequestMessage req, TraceWriter log)
{
    // Section A: Get event data
    dynamic body = await req.Content.ReadAsAsync<object>();
    string eventId = body.@event.eventId;
    string targetName = body.@event.targetName;
    string deviceId = targetName.Substring(targetName.Length - 20);
    string timestamp = body.@event.timestamp;
    string eventType = body.@event.eventType;


    // Section B: Create the SQL query based on event type
    string text;
    string[] parameterNames;
    string[] parameterValues;

    switch (eventType) 
    {
        case "temperature":
{ text = "INSERT INTO temperature_events (event_id, device_id, timestamp, temperature) " + "VALUES (@event_id, @device_id, @time, @temp);"; float temperature = body.@event.data.temperature.value; parameterNames = new string[] {"@event_id", "@device_id", "@time", "@temp"}; parameterValues = new string[] {eventId, deviceId, timestamp, temperature.ToString()}; break; }
case "touch":
{ text = "INSERT INTO touch_events (event_id, device_id, timestamp) " + "VALUES (@event_id, @device_id, @time);"; parameterNames = new string[] {"@event_id", "@device_id", "@time"}; parameterValues = new string[] {eventId, deviceId, timestamp}; break; }
case "objectPresent":
{ text = "INSERT INTO prox_events (event_id, device_id, timestamp, state) " + "VALUES (@event_id, @device_id, @time, @state);"; string state = body.@event.data.objectPresent.state; parameterNames = new string[] {"@event_id", "@device_id", "@time", "@state"}; parameterValues = new string[] {eventId, deviceId, timestamp, state}; break; }
case "waterPresent":
{ text = "INSERT INTO water_events (event_id, device_id, timestamp, state) " + "VALUES (@event_id, @device_id, @time, @state);"; string state = body.@event.data.waterPresent.state; parameterNames = new string[] {"@event_id", "@device_id", "@time", "@state"}; parameterValues = new string[] {eventId, deviceId, timestamp, state}; break; }
case "humidity":
{ text = "INSERT INTO humidity_events (event_id, device_id, timestamp, temperature, humidity) " + "VALUES (@event_id, @device_id, @time, @temp, @humidity);"; float temperature = body.@event.data.humidity.temperature; float humidity = body.@event.data.humidity.relativeHumidity; parameterNames = new string[] {"@event_id", "@device_id", "@time", "@temp", "@humidity"}; parameterValues = new string[] {eventId, deviceId, timestamp, temperature.ToString(), humidity.ToString()}; break; }
case "objectPresentCount":
{ text = "INSERT INTO countingprox_events (event_id, device_id, timestamp, total) " + "VALUES (@event_id, @device_id, @time, @total);"; string total = body.@event.data.objectPresentCount.total; parameterNames = new string[] {"@event_id", "@device_id", "@time", "@total"}; parameterValues = new string[] {eventId, deviceId, timestamp, total.ToString()}; break; }
case "touchCount":
{ text = "INSERT INTO countingtouch_events (event_id, device_id, timestamp, total) " + "VALUES (@event_id, @device_id, @time, @total);"; string total = body.@event.data.touchCount.total; parameterNames = new string[] {"@event_id", "@device_id", "@time", "@total"}; parameterValues = new string[] {eventId, deviceId, timestamp, total.ToString()}; break; }
default: log.Error("Unsupported event type received, check Data Connector. EventType = " + eventType); return req.CreateResponse(HttpStatusCode.OK); // Return OK to prevent data connector from resending } // Log for debugging and information log.Info("Update: " + deviceId + ", " + eventType + ", " + timestamp); // Section C: Connect to SQL database and var str = Environment.GetEnvironmentVariable("SQLDB_CONNECTION"); using (SqlConnection conn = new SqlConnection(str)) { conn.Open(); using (SqlCommand cmd = new SqlCommand(text, conn)) { // Add the parameters for (int i = 0; i < parameterValues.Length; i++) { cmd.Parameters.AddWithValue(parameterNames[i], parameterValues[i]); } try { // Execute query var rows = await cmd.ExecuteNonQueryAsync(); } catch (SqlException ex) when (ex.Number == 2627) { // Ignore duplicate events... } catch (SqlException ex) {
// Log the SQL exception for easier debugging
for (int i = 0; i < ex.Errors.Count; i++)
{
log.Info("SQL Exception: " + ex.Errors[i].Message);
}
// Propagate other errors so that the Data Connector can retry later. return req.CreateResponse(HttpStatusCode.InternalServerError); } } } return req.CreateResponse(HttpStatusCode.OK); }

What this code does is that:

  1. In Section A above, the different event data fields will be extracted from the request body JSON.

  2. In Section B, the SQL query, table, and parameters used are selected based on the type of the event.

  3. In Section C, the Azure Function connects to the SQL database (using the connection string), fills in the templated parameters and executes the SQL query - which inserts the event into the SQL database.

Next

If you are using PowerBI, consider the following two guides to read live data from the Azure SQL database: