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 the Query editor and login with the SQL server admin username and password.

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.

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.

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):

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.

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.

Remember to scroll up and press Save.

Step 2: Change Azure Runtime from v3 to v2

From the Platform features tab select the Function app settings.

Scroll down to Runtime version, and select ~2.

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

• (Microsoft) How to open Azure SQL database to access by external tools

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

• (Microsoft) Azure SQL Database with DirectQuery
• (Microsoft) Use DirectQuery in Power BI Desktop