In this guide we will:
- Setup a Microsoft Azure SQL database for your sensor data
- Setup one database table per type of sensor
- 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:
- create a blank database (instead of the sample used in the guide),
- note down the SQL server admin username and password and
- 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:
- Name it SQLDB_CONNECTION
- 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
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.
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:
-
In Section A above, the different event data fields will be extracted from the request body JSON.
-
In Section B, the SQL query, table, and parameters used are selected based on the type of the event.
-
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:
- (Microsoft) Azure SQL Database with DirectQuery
- (Microsoft) Use DirectQuery in Power BI Desktop