Reading Sensor data and uploading it on Microsoft Azure

Reading Sensor data via Intel Edison and storing data in SQL database over Microsoft Azure 

• In this post, I have explained the entire process in bullet points. In this project, using Ambient light sensor, the sensor value is displayed on terminal console, which is actually sent at Microsoft Azure. 
• This value can be verified using Device Explorer tool, Later Using Stream Analytics Job, the data is streamed and saved in SQL server, which is created online, in Microsoft Azure in form of SQL table. At this stage, this data can be used for analysis purpose and data visualization using suitable tools such as Power BI.

-- Open portal.azure.com
-- Click New—Internet of Things—IoT Hub
-- Give unique name to your IoT hub –Select pricing to Free –resource group (create new and give any name) and create it.
-- Now go to Dashboard and click on you newly create IoT Hub.
-- Go to Settings—Shared Access Policies—select policy “IoT Hub Owner”—Go to Shared access key—Select “Connection String—primary key” and copy the key. And paste it in notepad. This is Key 1.
-- In Iot hub, search for IoT Devices, click on “Add”, give device id, authentication type—symmetric key, select auto generate key, Enable with connect device to IoT Hub and Save. 
-- After Save, reopen the device and copy Connection String--Primary key in notepad. This is Key 2.

-- Go to this page to download “Device Explorer” https://github.com/Azure/azure-iot-sdk-csharp/releases and then install it.
-- Paste the copied connection string (Key 1) in box under configuration tab –IoT Hub Connection String and Update.
-- Click on Management tab and click create tab—give name to Device ID.
-- Once it is created, a device id with given name is create with multiple tabs.
-- Click under Connection string Tab, right click on content—copy connection string for selected device and paste it again in notepad. This is the same key 2 you got earlier also.

Paste this key in program against connectionString variable.
-- This key is very essential for sending data to Microsoft Azure IoT Hub.

Actual program which would run in Edison, written in JavaScript (This program will fetch light intensity and post that data via MQTT protocol usingg Microsoft Azure IoT Hub)

var mraa = require("mraa") ;

var sense= new mraa.Aio(0);

var Protocol = require('azure-iot-device-mqtt').Mqtt;

var Client = require('azure-iot-device').Client;

var Message = require('azure-iot-device').Message;

//

//Azure methods

//

var connectionString=”HostName=azuredison.azure-devices.net;DeviceId=azuredison_iot;SharedAccessKey=xxxxxxxxxxxxxxxxxxxxxx”;

var client = Client.fromConnectionString(connectionString, Protocol);

var connectCallback = function (err) {

  if (err) {

    console.error('Could not connect: ' + err.message);

  } else {

    console.log('Client connected');

    client.on('message', function (msg) {

      console.log('Id: ' + msg.messageId + ' Body: ' + msg.data);

      client.complete(msg, printResultFor('completed'));

    });

  }

};

client.open(connectCallback);

function lightRead()

{

    var lightVal= sense.read();

    //console.log(lightVal);

    var obj= {"lightVal":lightVal};

    var json= JSON.stringify(obj);

    var message = new Message(json);

    client.sendEvent(message, printResultFor('send'));

    console.log(json);

    setTimeout(lightRead,10000);

}

lightRead();

function printResultFor(op) {

  return function printResult(err, res) {

    if (err) console.log(op + ' error: ' + err.toString());

    if (res) console.log(op + ' status: ' + res.constructor.name);

  };

}

·       Access Intel Edison using Putty/TeraTerm and install following packages in Intel Edison (Yocto Linux)
#                              npm install azure-iot-device-mqtt
                                npm install azure-iot-device
  -- Paste connection String copied in notebook in connection string variable. 
  -- Run this code and it will through light intensity values on console
  -- Go to device Explorer tool—Data Tab—Select Device ID as created—Click Monitor Tab.
  -- You should be able to see the light intensity data in this tool. This means that data is transferred to Microsoft Azure But we are able to check its value in this tool.
  -- Click on New—SQL Database—Create
  -- Give Name to SQL database—Subscription—Free Trial—Resource Group—Use existing—Blank Database—Create new sql server—give server name, usename and password for sql server.
  -- After sql database is created, Goto Data explorer (preview) option, login to sql server, and run Query. This query would create sql table where variable LightVal data would be stored:
                    create table AzureIoTEdison(LightVal varchar(255));   
   
  -- Run another query. This query would fetch all data stored in sql table AzureIoTEdison. Since at this stage no value is available (program is not running in edison), it would return 0:
                     Select * from AzureIoTEdison
  
  -- Click New—Search Stream Analytics Job—Create. Click on overview window and look in Job Topology.
  -- There three blocks are available: Inputs, Query, Output.
  -- Click on Input Block—Add—Input alias—“Edison” or any name of input device. Source—IoT Hub.
  -- IoT hub—selected automatically, Event serialization format—JSON, Encoding—UTF-8
  -- Click Create
  -- Click on Output Block—Add—Give output alias name—Edisonoutputdata. Sink—SQL database, Database—selected automatically, give username & password for SQL database, Table—AzureIoTEdison (name of the table created)--Click Create
  
  -- Click on Query Block. Paste this code :

          SELECT

    lightVal
INTO

    [edisonoutputdata]
FROM

    [edison]

Save

-- Start Stream Job
-- Run the code in Edison using Putty #node main.js
-- Open Device Explorer tool

-- Open new tab and open portal.azure.com here again

-- Open dashboard, sql database, Data Explorer(preview)—login to sql server and type query and run:
                    Select * from AzureEdison

-- Now in the result window of sql sever, you can see the data coming up from sensor.  
-- Same data is also visible in Data Explorer tool. This data also get stored in SQL Database. Make sure your program is already running in Edison.
-- In Sql database, in DatExplore(preview) option, login to sql server. You will see multiple options as: Tables, Views, Stored Procedures.
-- Click on Tables—dbo.AzureIoTEdison—LightVal(varchar,null). This is the table in which sensor data is stored in SQL server. TO view this data, Select dbo.AzureIoTEdison—Click Edit Data(Preview). You can map these values with the values generated by sensors in Putty Console or Data Explorer. This verifies that Data actually get stored in SQL server in Microsoft Azure cloud.
-- Once you are done, you can Stop Stream Analytics Job.

Hope you have got a fair insight of how to save sensor data in SQL database over Microsoft Azure. Steps mentioned in this post are well verified and tested,still, if you face any issue, feel free to post your query. I would be happy to help you.

I will come up with few more interesting posts on Microsoft Azure in coming future.

Stay tuned and post your comments!