Azure: Saving IoT Data into SQL Server

I've received several questions from the community about specific details of how I built the IoT to Tableau solution with Azure, particularly the part where I save data to SQL Server. This post is meant as a follow up to my prior post and hopefully will shed some light on the technical details. As always, if you can't find the answer you are looking for, use the form below to send me an email. Happy coding!

iot.png

This tutorial will assume you have an IoT device set up, publishing data to Azure IoT Hub, and that you can see the data in Azure in the raw JSON payload (or other) format. If you don't have this set up, please go complete the device setup and Azure set up documented here.

Setting up the Stream Analytics Job

Once data is publishing in Azure IoT Hub, you will need to set up a Stream Analytics job to get that data somewhere else, like SQL server.

Follow this tutorial to set up your Stream Analytics job, but stop before you get to the section called "Add an output to the Stream Analytics job".

At this point, we are going to deviate from the tutorial. We don't want to use PowerBI, instead, we want to save the data into SQL Server then we can use any normal data visualization tool to access it. However, we first must create a SQL instance, then database, then storage table before we can continue setting up Stream Analytics.

Steps to Create a new SQL Database:

  1. Create a new SQL database instance by entering a name, pricing tier, and region. Make sure "Select Source" is listed as blank. Click Create.
  2. Wait for the SQL Instance to be deployed. Once it is, you should see it under "SQL Databases" on your Azure Dashboard.
  3. Open the instance and then open the "Query Editor." Login using the credentials you set up previously.
  4. Using standard SQL, create a new table for your IoT data to live in. Coming from Particle.io, the webhook had the payload from my device, Device ID, Published At (date time), and the name of the web hook. (Azure IoT hub appends additional metadata but I wasn't going to capture it in my SQL table.)  In my case, my Particle Photon data looked like this because I coded it that way in the IDE: {Sensor_Value_1|Sensor_Value_2|...|Sensor_Value_N|}

  5. Verify the table is created without issues by refreshing the left pane of the Query Editor. You should see your table name show up under the "Tables" folder.
  6. Now you have set up a SQL instance, created a login, and created a table to hold the data. Let's return to the Stream Analytics job.
CREATE TABLE [dbo].[particle_data] (
    [PUBLISHED_AT]    NVARCHAR (30) NOT NULL,
    [DEVICE_ID]    NVARCHAR (40) NOT NULL,
    [EVENT]        NVARCHAR (4000),
    [RAW_DATA]         NVARCHAR (4000),
    [DATA]           NVARCHAR (4000)
)
GO

Steps to Connect SQL DB to Stream Analytics:

  1. So, under the "Outputs" screen of the Stream Analytics job, create a new output for "SQL Database." Make sure the Database field matches the database name from above. Enter your database username/password credentials. Lastly, enter the Table name from what you created above. Click "Save" when complete. Azure will then test the connection. If all is well, move on. If it fails, troubleshoot your connection first by double-checking the names of the database and table. :)
  2. Go to the "Query" tab of the Stream Analytics Job.
  3. Enter a new query like the following there. Be sure to replace the names of the input/output to match the names of your own Stream Analytics Job Input/Output aliases.
  4. Click "Save." If you are feeling extra cautious, you can test the job by using some test data. I generated some data from the IoT Hub results and saved it as a JSON file. Here is what it looked like if you'd like to re-use the data.
  5. After you save the output location, click the "Overview" tab of the Stream Analytics Job and click "Start." The job should start without any issue.
  6. Wait for the IoT device to publish data. Once it does, the payload should now show up in the SQL database table you created before. Use the "Query Editor" to confirm the data exists. Use the query: select top 10 * from dbo.particle_data
[{"data":"151654|35|76|3141|-51|87.925781|3.996300|","device_id":"3100220039383037","event":"send","published_at":"2018-01-21T03:52:19.1510000Z","EventProcessedUtcTime":"2018-01-21T03:52:35.4095036Z","PartitionId":0,"EventEnqueuedUtcTime":"2018-01-21T03:52:20.5840000Z","IoTHub":{"MessageId":null,"CorrelationId":null,"ConnectionDeviceId":"310022001247343339383037","ConnectionDeviceGenerationId":"636520993218760636","EnqueuedTime":"2018-01-21T03:52:19.3520000Z","StreamId":null}}]
SELECT
DEVICE_ID,
EVENT,
PUBLISHED_AT,
DATA,
IOTHUB as RAW_DATA
INTO
    [alias of stream analytics job output]
FROM
    [alias of stream analytics job input]    

Connect Tableau Desktop

For those of you with a copy of Tableau Desktop, use the connector for SQL Server. Enter your instance URL, database name,  username, password. Remember the database URL will look something like this: instance-name.database.windows.net

Once you've connected Tableau Desktop, create visualizations and dashboards as you would normally. Or feel free to use mine as a starting point.

Hopefully this helps some of you fellow IoT Enthusiasts! As always, email me with any specific questions you need answered. Enjoy!

sql server auth.png