Exporting IoT Central data to Azure SQL Database

[vhyrodrigues]

Internet of Things (IoT) has emerged as one of the most disruptive forces driving the digital transformation. To help organizations start rapidly connecting devices and start to consume information generated and of course manage these devices Microsoft Azure have IoT Central offer.

What is IoT Central?

"IoT Central is an IoT application platform that reduces the burden and cost of developing, managing, and maintaining enterprise-grade IoT solutions. Choosing to build with IoT Central gives you the opportunity to focus time, money, and energy on transforming your business with IoT data, rather than just maintaining and updating a complex and continually evolving IoT infrastructure." Source

One of many features of the IoT Central is the one that you're able to export reported data from the devices on IoT Central and this is a useful feature since IoT Central just keep your information for 30 days and another thing to keep in mind is once you enable the feature to export data you will only get data from the moment you turned on. The feature lets you export your data continuously to Azure Event Hubs, Azure Service Bus, or Azure .... Data export uses the JSON format and can include telemetry, device information, and device template information. Use the exported data for:

• Warm-path insights and analytics. This option includes triggering custom rules in Azure Stream Analytics, triggering custom workflows in Azure Logic Apps, or passing it through Azure Functions to be transformed.
• Cold-path analytics such as training models in Azure Machine Learning or long-term trend analysis in Azure SQL Database and Microsoft Power BI.

I used it as the base for my IoT Central application and then later for the data export to Azure SQL Database was the Microsoft Learning module "Create your first Azure IoT Central". After you complete the module you need to follow these additional steps:

1. Export your Azure IoT Central data to Azure Event Hub
2. Process data from Event Hubs using Stream Analytics
3. Stream data using Stream Analytics to Azure SQL Database

Additional settings

These are the lessons learned that I had in this scenario.

Azure SQL Database

1. Configure your Database to accept connection from Azure services
2. Make sure your Table has the right Data Type, especially the Date Time Field that is some cases required the Type of datetime2

Azure Stream Analytics

1. If your Stream Analytics Job fail to check the Activity Log, basically what happen with me was that even after I fix my Date Time column still throwing the same error in the Stream Analytics interface but the error was different on the Activity Log. (Cache?)
2. GetMetadataPropertyValue is an Input Metadata function (Responsible to get information like DeviceID and others) it's not working for this specific scenario. I change my code to send DeviceID with the Telemetry.
3. You can use the power to Stream Analytics Query Language to do some nice things like for example extract the Latitude and Longitude from the field Location in JSON format simply using [Location].lat or [Location].lon

Other

• Be careful to not forget your devices and services running, this type of test environment can easily consume your free tier quota and subscription credits.

With more and more updates coming for sure IoT Central will be consolidated as the main Azure platform for IoT.