Azure Setup: Create Azure Streaming Analytics Job

The first step in the process is to create a new Azure Streaming Analytics job . To do this, we will assume that you have an Azure subscription and have already deployed the Azure IoT suite infrastructure components covered in Chapters 1-4 of this book.

Start by adding a new resource to your existing Azure resource group and searching for Stream Analytics Job, as depicted in Figure 5-9.

Figure 5-9. Adding a stream analytics job to an Azure resource group

After clicking on Stream Analytics job, you will be asked to fill in the parameters for creating the Azure Stream Analytics job, as shown in Figure 5-10.

Figure 5-10. Adding Stream Analytics job parameters

Fill in your parameter choices for the corresponding values:

• Job Name

• Subscription

• Resource Group

• Location

Once you are done, click on the Create button at the bottom of the screen. Your input will then be validated and the new Azure Streaming Analytics job will be created after a brief period of time. It took less than one minute via the portal.

Once you have configured an Azure Streaming Analytics job, you can add and configure additional components of the job, such as the following:

• Inputs: For defining incoming data streams and reference data in our Azure Streaming Analytics Job.

• Functions: For defining references to Azure Machine Learning Web Service calls. In our example, we will check to see if a team member is fatigued to the point of exhaustion.

• Outputs: For defining the output of results from our Azure Streaming Analytics Job into various storage formats and delivery platforms: Hot, Warm, and Cold (from the Lambda architecture).

• SQL Query: Will combine all of the previously defined inputs, functions, and outputs in a series of SQL statements to process the incoming data stream and send computed results to various output destinations and delivery/notification methods.

Figure 5-11 displays a screen capture of a newly created Streaming Analytics job and the corresponding inputs, query, and outputs.

Figure 5-11. Stream Analytics job: add input(s), function(s), query(s), and output(s)

Azure Setup: Streaming Analytics Job and Input Iot Hub Data Stream

Let’s start with configuring the input definition for describing our incoming data stream, which arrives via an IoT Hub configuration in Azure.

IoT Hub is an event processing service that enables event and telemetry ingress to the cloud at massive scale, with low latency and high reliability.

Start by clicking on the inputs image and then + Add, as shown in Figure 5-12.

Figure 5-12. Stream Analytics job, add parameters for input from IoT Hub

Fill in your parameter choices for the corresponding values:

• Input Alias: This will be the primary name used in any SQL Queries to refer to this input stream.

• Source Type: Set to Data Stream. Note the additional option for Reference Data.

• Source : Detected input sources that are Data Streams.

• Subscription : Azure Subscription for this input source.

• IoT Hub: Select the appropriate choice for your environment.

• Endpoint: Select Messaging. Other option includes Operations Monitoring.

• Shared Access Policy Name: For delegated and shared access to resources.

• Consumer Group: All event consumers read the event stream through partitions in a consumer group.

• Event Serialization Format: Specify JSON. Other options include Avro and CSV.

• Encoding: Specify UTF-8.

Once you are done, click on the Create button at the bottom of the screen. Your input will then be validated and the new Azure Streaming Analytics input definition will be created after a brief period of time.

Azure Setup: Streaming Analytics Job Input Reference Data

After we have configured the primary input data path from the IoT Hub, we will next define a secondary type of input definition that will be used for reference data.

Reference data is defined as more static or slower changing data, which can often be refreshed on longer time intervals. A prerequisite for this step is to have a reference data file uploaded into an Azure blob storage container in CSV, JSON, or Avro format.

For our reference implementation, we will upload a .CSV formatted file that contains team member reference data with more static attributes like name, height, weight, gender, e-mail, etc.

This reference data can then be used in JOIN statements in the Azure Streaming Analytics SQL Query language to enrich the data output and provide more meaningful and actionable business results.

Reference data is stored in Azure blob storage and is modeled as a sequence of blobs in ascending date/time order. It only supports adding to the end of the sequence by using a date/time greater than the one specified by the last blob in the sequence.

Currently, Azure Stream Analytics jobs look for the blob refresh only when the machine time advances to the time encoded in the blob name.

For example, our job will look for our pattern named TeamReferenceData{date}{time}.csv . It will then find this file: TeamReferenceData2016-11-1217-30.csv and will process it as soon as possible but no earlier than 5:30 PM on November 13th 2016 UTC time zone. It will never look for a file with an encoded time earlier than the last one that is discovered .

Note See the following link for more information about refreshing reference data for Azure Streaming Analytics jobs using Azure Data Factory:

Refreshing reference data with Azure Data Factory for Azure Stream Analytics Jobs

https://azure.microsoft.com/en-us/blog/refreshing-reference-data-with-azure-data-factory-for-azure-stream-analytics-jobs-3

INPUT Reference Data: Sample SQL Usage

The inclusion of reference data as a potential input for a streaming analytics job means that you can utilize a SQL JOIN (INNER or LEFT OUTER) between streams and reference data sources to enrich your incoming data model.

Note that “reference” data appears as just another input in the ASA SQL query in Listing 5-11.

Listing 5-11. SQL JOIN of Reference Data with Incoming Streaming Data

SELECT myRefData.Name, myStream.Value
FROM myStream
JOIN myRefData
                  ON myStream.myKey = myRefData.myKey

Configure the Streaming Analytics INPUT: Reference Data Definition

To get started, navigate to your Streaming Analytics job in the Azure Portal that was previously defined in this chapter and select the Inputs option on the left-side navigation pane.

Click + Add at the top of the page to add a new input definition. You will need to provide configuration options for the following parameters:

• Input Alias: A unique name to reference this input definition.

• Source Type: Select Reference Data.

• Subscription: Azure subscription for this input source.

• Storage account: Your storage account (brtblobstoragedev in this case).

• Storage account key: Copied from subscription

• Container: ref-data-team

• Path pattern: TeamReferenceData{date}{time}.csv

• Date format: YYY-MM-DD.

• Time format: HH-mm.

• Event Serialization Format: Specify CSV. Other options include JSON and Avro.

• Delimiter: Comma (,).

• Encoding: Specify UTF-8.

Click on the Create button at the bottom of the screen to create the input definition.

The screenshot in Figure 5-13 denotes the input parameters for the input reference definition.

Figure 5-13. Stream Analytics job: add parameters for input for Reference data

After the new input for our reference data is created and tested (this process takes only about one minute), you can then use this new input definition as part of the SQL query to JOIN the “reference data” with the incoming sensor data from the IoT Hub.

Typically, IoT sensor data is transformed to be very “lean” when transmitted over networks, so no additional information other than what is the minimal necessary to keep packet sizes small and transmission costs efficient.

By matching the incoming data streams with more complete profile data from our reference data, we can provide more complete data profiles for the various entities being measured. This capability also allows for finer-grained decision support mechanisms, as additional meaningful attributes may now unlocked and utilized to predict better outcomes.

Azure Setup: Streaming Analytics Job, Functions to Call Azure Ml Web Services

The next step is to define an alias for an Azure Machine Learning Web Service. The Web Service will be used to call out from the processing SQL script and is used to check to see if a team member may displaying symptoms that he/she may be near physical exhaustion.

Azure Machine Learning: Predicting a Team Member’s Health

The Azure Machine Learning predictive model is based on a series of physical stress tests that each team member must undergo each quarter (every three months).

The purpose of the stress tests is to track all the same sensor readings that a team member generate while working, but in a simulated, stress-test, environment that can quickly simulate unfavorable working conditions to induce physical fatigue and mental exhaustion.

At any point that a team member feels that they cannot continue or complete the stress test tasks at hand safely, they simply press a Fatigued button on their vest to stop the test and register their physical attributes at the point of exhaustion.

In this way, key physical sensor readings are correlated with the teammate’s “fatigue” response to the stress tests.

This allows us to create a predictive analytics “training” model, which can be trained using a “binary classification” Machine Learning algorithm in order to predict future outcomes based upon certain attributes such as breathing, heart rate, speed, velocity, temperature, pulse, etc.

Obtaining Unbiased Machine Learning Training Data

It should also be noted that there is another distinct advantage to using a simulated “stress test” environment as training data to create the Machine Learning model.

This is due to the fact that the team members have financial incentives to keep working long hours under adverse conditions in order to attain financial rewards and increased compensation.

For this reason, team members may be disinclined to signal fatigue while working on the real production-line jobs in order to maximize their financial compensation. However, by measuring the team’s stress test responses outside of the production work environment—where there are no financial incentives involved—more accurate results can be obtained.

The key to success is to obtain unbiased and accurate training data that more closely resembles the key indicators of fatigue and stress that can be measured, recorded, and utilized to train the Azure Machine Learning Model.

Configure the Streaming Analytics Function Definition

To get started, navigate to your Streaming Analytics job in the Azure Portal that was previously defined in this chapter and select the Functions option on the left-side navigation pane.

Click the + Add at the top of the page to add a new function definition. You will need to provide the following:

• Function Alias : A unique name to reference this function definition.

• Function Type: Defaults to Azure ML. Note: the function option is currently restricted to only refer to Azure Machine Learning Web Service definitions. It is anticipated that Microsoft may open-up this capability to reference “Azure Functions” in the near future. This new functionality would dramatically expand the capabilities of the Azure Streaming Analytics SQL Query language by incorporating function callouts to custom code modules.

• Subscription : The Azure subscription to be used for the function definition.

• URL: Refers to the Azure Machine Learning Web Service deployment name.

• Key : The security key for access to the Azure Machine Learning Web Service deployment.

Click on the Create button at the bottom of the screen to create the function definition. The screenshot in Figure 5-14 denotes the function definition parameters for the reference implementation.

Figure 5-14. Add parameters to the streaming job for function reference to desired Azure Machine Learning Web Service

After the function is created and tested (this process takes only about 1 minute), we can then use this new function definition as part of our SQL Query to call our Azure Machine Learning Web Service to predict whether a team member is at risk of being physically exhausted to the point that it could cause an unsafe work environment.

The screenshot in Figure 5-15 depicts the newly defined function that we created.

Figure 5-15. Stream Analytics job: function parameters for Calling Azure ML Web Service CheckTeamHealth

Also shown is the function signature, which shows all 13 parameters and various field types that must be configured correctly in order to make the function call from the Streaming Analytics SQL query.

We will cover the implementation details of the Azure Machine Learning Web Service called CheckTeamHealth in Chapter 8.

As part of the Chapter 8 background, we cover the creation of an Azure ML training model based on stress test results and utilize a “Binary Classification” algorithm to create a predictive model that can correctly determine if a team member is nearing the point of physical exhaustion. We will also cover the mechanics of deploying, testing, and managing the Azure ML Web Service.

Azure Setup: Streaming Analytics Job Outputs

Now that we have created the inputs and functions for our Azure Streaming Analytics job, it is time to create the various outputs for our streaming job.

This is a critical step in the process, as we will be creating multiple outputs from a single input stream coming from the IoT Hub.

One key set of decisions around defining the streaming job outputs is the notion of the Lambda architecture that we reviewed earlier in the chapter. Azure Streaming Analytics provides the ability to define various output formats and storage options that can directly relate to the notion of Hot, Warm, and Cold data paths:

• Hot Path: for surfacing critical, real-time, actionable information, such as alerts and notifications. Typically, Power BI is used as the visualization tool to create real-time dashboards and visualizations for critical business data.

• Warm Path: Typically slower than Hot Path options, yet provides relatively quick access to critical data via technologies such as DocumentDB and Azure SQL database via canned or ad hoc user queries.

• Cold Path: This is usually the lowest cost and slowest storage option. Typical use case scenarios would be to provide an archive capability or storage that would act as a system of record for all transactions. Typically, Azure blob storage or Azure Data Lake is used for these implementations.

Our reference implementation will seek to implement all three of the Lambda data pathways.

Streaming Analytics Job: Cold Path Output Blob Storage

We will be using Azure blob storage for our “cold path” output option. A prerequisite for this step is to create an Azure Storage account and a container in the storage account to hold our data.

Note Refer to the following link for more information:

About Azure storage accounts

https://azure.microsoft.com/en-us/documentation/articles/storage-create-storage-account

A handy tool for navigating Azure Storage is the “Azure Storage Explorer,” which can be downloaded from http://azurestorageexplorer.codeplex.com . Since it is on CodePlex, it’s also great to have the source code, to see how the Azure Storage API’s are called from .NET.

Once you have created an Azure Storage Account and a container in the storage account, you will need the storage account access keys for the next step.

To get started with our first output definition, navigate (via the Azure Portal) to the Azure Streaming Analytics job we defined earlier in the chapter:

Select Outputs:

• Click on the + Add to add a new output definition.

• Under the Sink parameter, select Blob Storage from the drop-down list. This action will properly set the remaining fields for you to populate:

• Output Alias: A name for this output definition that will be used in our SQL query.

• Sink: Refers to the selected output destination; in this case, select blob storage.

• Subscription: The Azure subscription to be used for this output definition.

• Storage Account: The name of the Azure Storage account that was created as a prerequisite for this output definition.

• Storage Account Key : The corresponding Account key for the storage account.

• Container: The name of the storage account repository or folder that will contain our output data.

• Path Pattern : Denotes the file path used to locate your blobs within the specified container. Within the path pattern definition, you can choose to use the keywords {date} and {time} to help create a logical representation of the data output. For our implementation, we will use [date]/[time] to keep the data separated by date and time. This will provide a file structure within the blob container similar to the path “year/month/day/time”.

• Date Format : The default format is YYYY/MM/DD. Accept the default.

• Time Format : The default format is HH. Accept the default.

• Event Serialization Format: The choices are JSON, CSV, and Avro. We will use JSON for our output definition, especially since the incoming data stream is in the same format (JSON).

The screenshot in Figure 5-16 depicts the parameter options for defining an output definition for blob storage.

Figure 5-16. Stream Analytics job: cold path output definition parameters using blob storage

Once you have entered all the required parameters, click on the Create button at the bottom of the screen and the new output definition will be quickly tested and added to the Azure Streaming Analytics job.

Streaming Analytics Job: Warm Path Output: Azure SQL Database

For our “warm path” we will be using Azure SQL database for our output option. A mandatory prerequisite for this step is to create an Azure database server, an Azure database, and an Azure table to store the data.

Note Refer to the following link for more information on creating an Azure SQL database:

SQL Database tutorial: Create a SQL database in minutes by using the Azure portal:

https://azure.microsoft.com/en-us/documentation/articles/sql-database-get-started/

Listing 5-12 is a Transact-SQL script that will create an Azure SQL database table named IotHubSensorReadings.

Listing 5-12. SQL Table Definition for [IotHubSensorReadings]

CREATE TABLE [dbo].[IotHubSensorReadings](
     [UserId] [char](256) NOT NULL,
     [Age] [float] NOT NULL,
     [Height] [float] NOT NULL,
     [Weight] [float] NOT NULL,
     [HeartRateBPM] [float] NOT NULL,
     [BreathingRate] [float] NOT NULL,
     [Temperature] [float] NOT NULL,
     [Steps] [float] NOT NULL,
     [Velocity] [float] NOT NULL,
     [Altitude] [float] NOT NULL,
     [Ventilization] [float] NOT NULL,
     [Activity] [float] NOT NULL,
     [Cadence] [float] NOT NULL,
     [Speed] [float] NOT NULL,
     [HIB] [float] NOT NULL,
     [HeartRateRedZone] [float] NOT NULL,
     [HeartrateVariability] [float] NOT NULL,
     [Status] [int] NOT NULL,
     [Id] [char](256) NOT NULL,
     [DeviceId] [char](256) NOT NULL,
     [MessageType] [int] NOT NULL,                                                                                       
     [Longitude] [float] NOT NULL,
     [Latitude] [float] NOT NULL,
     [Timestamp] [datetime2](7) NOT NULL,
     [EventProcessedUtcTime] [datetime2](7) NOT NULL,
     [PartitionId] [int] NOT NULL,
     [EventEnqueuedUtcTime] [datetime2](7) NOT NULL
)

Note that the column names in the IotHubSensorReadings table exactly match the column names of our input data stream. This is not a necessary step, but as you will soon see, this helps make it very easy when it comes time to write the SQL query statements. To fix any column name mismatches, we can simply use the T-SQL AS clause to rename an incoming column to a destination column in our SELECT statements.

Once the prerequisite Azure SQL Database artifacts (Server, Database, and Table) have been created, you will need to gather the credentials for the database (username and password) for the next step in the configuration process—creating the streaming job output definition.

To create the Azure Streaming Analytics job output definition for the “warm” data path, start by navigating (via the Azure Portal) to the previously defined streaming job definition and selecting Outputs.

Click on + Add to add a new output definition:

• Under the Sink parameter, select SQL database from the drop-down list. This action will properly set the remaining fields for you to populate:

• Output Alias: A name for this output definition that will be used in our SQL query.

• Sink: Refers to the selected output destination; in this case, select SQL database.

• Subscription: The Azure subscription to be used for this output definition.

• Database: The name of the Azure SQL database we created as a prerequisite earlier in the chapter.

• Server Name: The name of the Azure SQL Server we created as a prerequisite earlier in the chapter. Note that the naming convention should follow the following format:

• <Your Server Name>.database.windows.net

• Username: The username credential for the Azure SQL database server.

• Password: The password credential for the Azure SQL database server.

• Table: The name of the Azure SQL database table we created as a prerequisite earlier in the chapter. Our reference implementation example used the table named IotHubSensorReadings.

The screenshot shown in Figure 5-17 depicts the parameter options for defining an output definition for a SQL database.

Figure 5-17. Stream Analytics job for warm path output definition parameters using SQL database

Once you have entered all the required parameters, click on the Create button at the bottom of the screen and the new output definition will be quickly tested and added to the Azure Streaming Analytics job.

Streaming Analytics Job: Hot Path OUTPUT Power BI

For our “hot path” we will be using Power BI for our output option. As stated previously, Power BI is a rich data visualization tool that allows you to create rich, real-time, dashboards and visualizations for critical business data.

Power BI displays dashboards that are interactive and can be created and updated from many different data sources in real time. In a later chapter of this book, we will be using Power BI as a real-time operational dashboard just like a dashboard in an automobile. It displays critical information about vehicles, such as its speed, its fuel level, or oil temperature. In our reference application, we will be monitoring team member’s health and activity data in real time.

A mandatory prerequisite for this step is to have set up and configured an Azure Active Directory user that we can use to authenticate against Power BI during the output definition process.

Azure Active Directory is a foundational piece of Power BI Authentication process and stores the users, groups, and domains in addition to other settings and configuration options.

Power BI apps are integrated with Azure Active Directory (AAD) to provide secure sign in and authorization for your Power BI applications. In order to integrate a Power BI application with Azure Active Directory, you would need to register the application details with Azure AAD via the Azure Management Portal.

To sign up for the Power BI service, your Azure Active Directory must have at least one organizational user. See the following link for details about creating an Azure Active Directory tenant:

Create an Azure Active Directory tenant for a Power BI app:

https://powerbi.microsoft.com/en-us/documentation/powerbi-developer-create-an-azure-active-directory-tenant

Once a tenant and a user within that tenant have been created, you can use that new organizational user to sign up for the Power BI service at the following link:

Sign up for the Power BI service:

https://powerbi.microsoft.com/en-us/documentation/powerbi-admin-free-with-custom-azure-directory

See the following link for more information about Azure Active Directory:

Azure Active Directory: http://azure.microsoft.com/en-us/services/active-directory

Once the prerequisites for the Power BI Output option have been configured (Azure Active Directory user/password and registration on the Power BI site), the next step is start the creation of the Power BI output option.

The creation of a Power BI output option is basically a two-step process:

1. Authenticate against Power BI. While doing this step, Azure Streaming Analytics is generating and saving those credentials for when the job is running and pushing real-time data to your Power BI dashboard applications.

2. Provide the necessary details for the output definition.

To create the Azure Streaming Analytics job output definition for the “hot” data path, start by navigating (via the Azure Portal) to the previously defined streaming job definition. Select Outputs.

Click on + Add to add a new output definition:

• Under the Sink Parameter: Select Power BI from the drop-down list. This action will properly set the remaining fields for you to populate.

• Output Alias: A name for this output definition that will be used in our SQL Query.

• Sink : Refers to the selected output destination; in this case, select Power BI.

• Authorize Connection : By clicking on the Authorize button, you will be presented with an authorization screen for you to authenticate against the Power BI service.

As mentioned previously, these credentials are cached and used for real-time access to push new data into your Power BI dashboard.

Figure 5-18 displays a screenshot of the required parameters for the first step.

Figure 5-18. Stream Analytics job: hot path output definition parameters, Power BI authentication

After clicking on the Authorize button, you will see a screen similar to the one in Figure 5-19 for you to enter your Power BI credentials.

Figure 5-19. Stream Analytics job: hot path output definition parameters Power BI AAD Authentication screen

After you have authenticated against Power BI with your AAD credentials, you will then gain access to the remaining configuration parameters required to complete the output definition.

• Output Alias: A name for this output definition that will be used in our SQL query.

• Sink : Refers to the selected output destination; in this case, select Power BI.

• Group Workspace : Refers to a workspace in your Azure Power BI tenant under which the output dataset will be created.

• Dataset Name : A descriptive dataset name for the Power BI output that will be used to reference when creating Power BI dashboards and visualizations.

• Table Name : A descriptive table name in a dataset for the Power BI output that will be used to reference when creating Power BI dashboards and visualizations.

Figure 5-20 depicts the additional configuration parameters that are visible once the Power BI connection has been authenticated.

Figure 5-20. Stream Analytics job: hot path output definition parameters Power BI additional parameters after authentication

Once you have entered all the required parameters, click on the Create button at the bottom of the screen and the new output definition will be quickly tested and added to the Azure Streaming Analytics job.

We will explore in greater detail the use of Power BI in Chapter 9.

Azure Streaming Analytics Job: Inputs and Outputs

At this point, you have created the following components of the Azure Streaming Analytics job:

• Inputs:

  • IoT Hub

  • Reference data

• Functions:

  • Azure ML Web Service – ChkTeamHealth()

• Outputs:

  • Blob storage

  • Azure SQL database

  • Power BI

At this point, your Azure Streaming Analytics job should look like the screenshot in Figure 5-21.

Figure 5-21. Stream Analytics job: two input definitions and three output definitions

Azure Setup: Streaming Analytics Job SQL Query

Now that we have created the inputs, functions, and outputs for our Azure Streaming Analytics job, it is time to pull it all together using the ASA SQL Query language.

In the reference implementation, we will make use of most of the options and features available with Azure Streaming Analytics in order to help illustrate how easy it becomes to put together a high-performance ingestion engine that can easily be enhanced to add capabilities to turn massive volumes of data into real-time, actionable data.

The SQL queries that you will implement will allow you to combine the two input definitions along with the Azure ML function definition and then populate three separate output data path destinations for our streaming data. The three paths will correlate to our Lambda architecture guidance for Hot, Warm, and Cold data paths:

• Hot Path: We defined an output definition using Power BI, which will allow us to update real-time dashboards and visualizations with the sensor data plus the results of the Azure Machine Learning Web Service calls via the function definition.

• Warm Path: We will target Azure SQL database which will allow us to add, update, and query data from many clients.

• Cold Path: We will use Azure blob storage to archive the data in its original format plus the additional results of the JOIN on the INPUT with the reference data definition for team member reference health data.

To create the Azure Streaming Analytics job SQL Query definition, start by navigating (via the Azure Portal) to the previously defined streaming job definition. Select Query.

• You will be presented with a SQL Query Editor window.

• Enter the SQL Query text as shown in Figure 5-14 to implement the “hot path” output to Power BI. Note the call to ChkTeamHealth(). This calls the Azure ML Web Service to predict the team member’s exhaustion level.

SQL Query Hot Path: Call Azure ML and Then Output to Power BI

Listing 5-13. ASA SQL Query That Implements a Function Call to an Azure Machine Learning Web Service

-- ************************
-- * HOTPATH
-- * Invoke Machine Learning As a Function "ChkTeamHealth()"
-- * Via ASA SQL Subquery
-- * then output to Power BI (Hot) & BLOB Storage (COLD) Storage
-- ************************
WITH [subquery] AS
(
    SELECT UserId, ChkTeamHealth(
        UserId,
        BreathingRate,
        Temperature,
        Ventilization,
        Activity,
        HeartRateBPM,
        Cadence,
        Velocity,
        Speed,
        HIB,
        HeartrateRedZone,
        HeartrateVariability,
        "N")
    as result from [input-iothub]
    TIMESTAMP BY [Timestamp]
)
SELECT UserId,                                                                               
        result.[BreathingRate],
        result.[Temperature],
        result.[Ventilization],
        result.[Activity],
        result.[HeartRateBPM],
        result.[Cadence],
        result.[Velocity],
        result.[Speed],
        result.[HIB],
        result.[HeartrateRedZone],
        result.[HeartrateVariability],
        result.[Scored labels],
        result.[Scored Probabilities],
        result.[Timestamp]
INTO [output-powerbi]
FROM subquery

Note If there are no red squiggly lines, you can click on the Save button in the top-left navigation pane in the Azure Portal to save the SQL query.

It is highly recommended that you resolve any grammatical errors in your SQL script before progressing. The most common issues are misspellings between your input/function/output definitions and the ones in the reference implementation scripts provided as sample code with this book.

SQL Query Warm Path: Output to Azure SQL Database

Enter the SQL query text as shown in Listing 5-14 to implement the “warm path” output to Azure SQL database .

Note The field names in the SQL table definition were matched to the incoming JSON column names to expedite development efforts.

Listing 5-14. ASA SQL Query that Outputs to Azure SQL Database

-- ****************************
-- * WARM Path
-- * OUTPUT to Azure SQL DB
-- ***************************
SELECT
    UserId,
    Age,
    Height,
    Weight,
    HeartRateBPM,
    BreathingRate,
    Temperature,
    Steps,
    Velocity,
    Altitude,
    Ventilization,
    Activity,
    Cadence,
    Speed,
    HIB,
    HeartRateRedZone,
    HeartrateVariability,
    Status,
    Id,
    DeviceId,
    MessageType,
    Longitude,
    Latitude,                                                                                       
    [Timestamp],
    EventProcessedUtcTime,
    PartitionId,
    EventEnqueuedUtcTime
INTO [output-sqldb]

FROM [input-iothub]
TIMESTAMP BY [Timestamp]                                                                                       

SQL Query Cold Path: Output to Azure SQL Database

Enter the SQL Query text shown in Listing 5-15 to implement the “cold path” output to Azure blob storage .

Note The incoming sensor data from IoT Hub is JOINed with the team member’s reference data.

Listing 5-15. ASA SQL Query that JOINS with Reference Data and Outputs to Azure Blob Storage

-- ************************
-- * COLD Path
-- * OUTPUT ALL incoming fields into to Azure BLOB Storage
-- * JOIN on Reference Data
-- ************************
SELECT
   IH.UserId,
    IH.Age,
    IH.Height,
    IH.Weight,
    IH.HeartRateBPM,
    IH.BreathingRate,
    IH.Temperature,
    IH.Steps,                                                                                       
    IH.Velocity,
    IH.Altitude,
    IH.Ventilization,
    IH.Activity,
    IH.Cadence,
    IH.Speed,
    IH.HIB,
    IH.HeartRateRedZone,
    IH.HeartrateVariability,
    IH.Status,
    IH.Id,
    IH.DeviceId,
    IH.MessageType,
    IH.Longitude,
    IH.Latitude,
    IH.[Timestamp],
    IH.EventProcessedUtcTime,
    IH.PartitionId,
    IH.EventEnqueuedUtcTime,
    RF.healthInformation__age,
    RF.healthInformation__height,
    RF.healthInformation__weight,
    RF.healthInformation__gender,                                                                                       
    RF.healthInformation__race

INTO
    [output-blob]
FROM
    [input-iothub] IH
TIMESTAMP BY [Timestamp]

JOIN
[input-refdata-team] RF
    ON IH.UserId = RF.id

Click on the Save button in the top-left navigation pane in the Azure Portal to save the SQL query.

Azure Setup: Streaming Analytics Job, Start Job

Now that the last remaining step to completing the Azure Streaming Analytics job has been completed, it is time to start our streaming Analytics job and view the expected output results.

Navigate to the streaming analytics job you created previously and click on the |> Start button on the top-center of the Azure Portal, as shown in Figure 5-22.

Figure 5-22. Start the Stream Analytics job

 This step will normally take a few minutes (three to five) for the Azure Streaming Analytics job to fully start.

Using the Device Simulator to Test the Azure Streaming Analytics Job Stream

Once the Azure streaming job is running, you can run the C# Device Simulator code from the GitHub repository to test the Azure Streaming Analytics job.

Once downloaded, you will find a Visual Studio Solution in the rtdevicesSimulator folder. This is a simple C# .NET Console application that creates a comprehensive dataset of sensor information and sends it into the Azure IoT Hub connection.

Once you locate the solution in the repository, you can then load, build, and run the solution (Simulator.sln). You will see a console window similar to the one in Figure 5-23.

Figure 5-23. C# .NET Console Device Simulator output

You should also start to see the data flowing into the various output destinations as defined in the Azure Streaming Analytics job.

Verify the Azure Streaming Analytics job Output: Azure Blob Storage

You can easily check and verify the Azure blob output destination using tools like the Cloud Explorer in Visual Studio 2015 or by using the “Azure Storage Explorer,” which can be downloaded from http://azurestorageexplorer.codeplex.com .

Figure 5-24 provides an example screenshot of the Azure blob output results when viewed with the Visual Studio Cloud Explorer tool.

Figure 5-24. Visual Studio 2015 Cloud Explorer Tool to verify Azure blob output results from THE Streaming Analytics job

Verify the Azure Streaming Analytics job Output: Azure SQL Database

To verify the Azure SQL database output results from the Azure Streaming Analytics job, YOU can use tools like SQL Server Management Studio (SSMS). You can download it from the following link:

Download SQL Server Management Studio (SSMS)

https://msdn.microsoft.com/en-us/library/mt238290.aspx

Figure 5-25 provides an example screenshot of the Azure SQL database output results when a SELECT statement is executed against the output SQL table and viewed within the SQL Server Management Studio Query window.

Figure 5-25. Using SQL Server Management Studio to verify Azure SQL database output results from THE Streaming Analytics job

Verify the Azure Streaming Analytics job Output: Power BI

To verify the Streaming Analytics job output to Power BI, you can sign in to THE Power BI web site at the following link using your Azure Active Directory organizational ID: https://powerbi.microsoft.com/en-us .

Once you have signed in to the Power BI Web Portal, you can navigate down the left-side navigation pane to the topic area named Datasets and select the option for Streaming Datasets.

After clicking on the link, you should see a list of available streaming datasets. Look for one named iothubdata, which represents the output from THE Azure Streaming Analytics job, as shown in Figure 5-26.

Figure 5-26. Working with the “hot path” output streaming dataset for Power BI to verify the output results from the Streaming Analytics job

For more in-depth coverage of Power BI as the output path for Azure Streaming Analytics, see Chapter 9.