Exporting IoT Central data to Azure SQL Database

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." https://docs.microsoft.com/en-in/azure/iot-central/core/overview-iot-central

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 https://azure.microsoft.com/en-au/services/iot-central/#updates-announcements 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. https://docs.microsoft.com/en-us/azure/sql-database/sql-database-stream-analytics. 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 https://docs.microsoft.com/en-us/learn/modules/create-your-first-iot-central-app/. After you complete the module you need to follow these additional steps:

1. https://docs.microsoft.com/en-us/azure/iot-central/core/howto-export-data
2. https://docs.microsoft.com/en-us/azure/event-hubs/process-data-azure-stream-analytics
3. https://docs.microsoft.com/en-us/azure/sql-database/sql-database-stream-analytics

Additional settings

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

Azure SQL Database

1. https://docs.microsoft.com/en-us/azure/sql-database/sql-database-networkaccess-overview#allow-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 https://docs.microsoft.com/en-us/stream-analytics-query/stream-analytics-query-language-reference 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 https://azure.microsoft.com/en-us/updates/?status=nowavailable,inpreview,indevelopment&query=IoT%20Central for sure IoT Central will be consolidated as the main Azure platform for IoT.