This is a follow up article of our "Connecting Azure IoT Hub with Ubidots" series. If you haven't checked our first article, we highly recommend going through it first to learn how to configure your Azure account before connecting it to Ubidots.
Requirements
1. Set up an SQL Server
The first resource that you need to add is SQL database. The SQL database is a collection of data organized into tables for simple organization
Use the search bar at the top and type SQL Server. Click on it.
Click the "+ Create" Button
Fill the following settings according to:
Subscription: select your azure subscription
Resource group: the same resource group that your IoT hub uses
Server name: the name for your SQL Server
Location: the location of the server hosting this resource. Note that you must select the same location for all of the following resources
Authentication Method: select the settings most appropriate for your particular implementation. For this article, it was chosen Use SQL Authentication method.
Set a admin login username and a password.
Click on Next: Networking button
Enable other Azure resources to interact with this SQL Database Server by setting to Yes the Firewall rule
Click on Review + create
Wait for the newly created resource to be deployed. Then click on Go to resource button
2. Set up an SQL Database
Click on Create database button
Fill the following settings according to:
Database name: the name of the database
SQL Elastic Pool: select this according to your needs. For this article, this feature was not required
Compute + Storage: Click on Configure database in order to change the pricing model and the requirements for your project according to your desired pricing quota
Click on Review + create to create the database
Once the resource is deployed click on the Go to resource button
Click on Query editor(preview)
Enter the credentials created with the SQL Server in the previous step
Click on OK button to login to your database
If a warning pops-up telling you that you can't login from your current IP address, just click on that warning and your IP address will be added to the firewall exception rules
Create a new table on the current database by copying the following query into the query editor, then press "Run"
CREATE TABLE MyUbidotsTable (
ID int NOT NULL IDENTITY(1,1),
valueUbi float,
PRIMARY KEY (ID)
);
To visualize the table please copy the query below into the query editor, then press "Run". Once the query run successfully, press "result" to visualize the table:
SELECT * FROM MyUbidotsTable
3. Create a Logic app
In order to handle the data going from Ubidots to Azure IoT Hub, a Logic App will be implemented. It will receive a HTTP request from Ubidots, then parse the data to send it to the SQL Database in IoT Hub.
Create a new logic app following the steps provided in previous the tutorial
Wait for the resource to be deployed. Then click the Go to resouce button
Create the first action on the Logic App by selecting When an HTTP requests is received
Click on Add new parameter
Click on POST
Click +New step
Search "parse json" within the search bar as you can see below, then select the action Data Operation - Parse JSON
Assign the parameters below to the action:
Content: assign the Body
Schema: assign the following schema:
{
"properties": {
"value": {
"type": "number"
}
},
"type": "object"
}
Press +New step
Search "SQL server" on the search bar and click on it
As action select SQL Server - Insert row (V2)
Fill the following settings according to:
Connection name: the name of the database
Authentication type: Select the same authentication method as you did for the SQL Database
SQL Server name: the name that given to the SQL Server on its set up, followed by ".database.windows.net"
SQL database name: the name of the database created on step #2
Username: the username that you choose at the SQL Server setup. This only applies if you chose SQL Server Authentication as authentication method
Password: the password that you choose at the SQL Server setup. This only applies if you chose SQL Server Authentication as authentication method
Gateway: Select No gateways found
Click the Create Button
Wait for the connection to the database to take effect
Set the following settings as:
Server name: select Use connection settings. This will set the value of the previously selected Server name
Datanase name: select Use connection settings. This will set the value of the previously selected Database name
Table name: select the table created on former steps. If you have been following this tutorial's nomenclature, it should be "MyUbidotsTable"
Click the dropdown menu displaying the message Add new parameter and select valueUbi
Set the mouse pointer on the valueUbi field
From the menu appearing on the right, search for value and drag it to the valueUbi field
Click Save button
Click Run Trigger, wait for it to finish and copy the HTTP POST URL
4. Ubidots WebHook Setup
To finish the integration, an Ubidots event will be setup to send data to Azure IoT Hub. Reference this article for additional details to create a WebHook event in Ubidots.
Go to the Devices section on your Ubidots account and Create a new device. For this article it was labeled as "azure-device", however, you can use any label that you find appropriate
Create a new Raw Variable for this device. In this article it was named "button", however, you can use any name
Go to the event section and press "Add event" to create a new event. Select the device and the variable created on the previous step
Set the following settings in the IF triggers tab
Go to the THEN actions tab, select Add new action and then Trigger Webhook
On the HTTP Method and URL section select POST and paste the HTTP POST URL obtained on step #4
On the Body section, delete all and paste the following:
{"value": {{last_value}}}
Go go the BACK TO NORMAL tab
Check the Trigger this action when the event condition goes back to normal state option
Copy the following on the Body section
{"value": {{last_value}}}
Click on the green check mark to save the event configuration
Go to the dashboard section to create a switch control widget to test how everything works. Every time that the button changes its state, it will trigger the WebHook posting the last value in Azure
To create the widget, press Create widget > Control > Switch > "name of the device in Azure" > "name of the variable" > Finish. If you have had followed the same nomenclature as in this article, then the steps are Create widget > Control > Switch > azure-device > button > Finish. Once the widget is created you'll see something like the picture below:
Verify if your logic app is running. If not, press Run to restart the function
Now from your Ubidots dashboard press the switch 3 times, and visualize into the Azure SQL database tools how the values arrive. Please note that if you do not run the query, you data will not appear
By following this technical guide, it was possible to create a table which stores inbound data from Ubidots in the Azure SQL database
In this case it is being evaluated the amount of times the button is being pressed in the Ubidots dashboard.
The ID field makes reference to the entry on the table and the valueUbi makes reference to the status of the button/switch
Also, in the overview of the logic app you can visualize run and trigger histories; if you do not see new/updated information press "Refresh" to update the page.
Now that you know how to manage a SQL database in Azure, you can make the integration for the first logic app created to store values sent to Ubidots in the Azure database.
5. Feedback, Suggestion and Related Articles
Feel free to post questions or suggestions in our community portal, or contact us via support@ubidots.com.
Other users also found helpful...