Pushing data thru Azure Stream Analytics to Power BI - part 2
Note: see this article for some new insights.
In part 1 I provided an overview of the given solution. And now it is time to configure Azure Service Bus, Event Hubs and Stream Analytics to be able to sent and receive messages. I still be using the ‘old’ Azure Management Portal as I cannot use the new portal: I always get lost in that one.
The steps that needs to be done:
- Configure Azure Service Bus
- Create a new namespace
- Create a sender event hub
- Define access policy
- Create a custom consumer group
- Create a receiver event hub
- Configure Azure Stream Analytics
- Connect the sender event hub as input
- Connect the receiver event hub as output
- Define the event processing query
To use the Azure Service Bus and their Event Hubs in combination with Stream Analytics we first need to create a new namespace by clicking on Create a new namespace in the Service Bus page in the Azure Portal. One important thing is that it needs to be a Standard messaging tier. This is because we need to define a different Consumer Group as Stream Analytics cannot connect to the $Default group.
![Event Hub namespace/content/images/2016/6/EventHub%20Namespace.png)
After we have created the namespace the Event Hubs can be created. This can easily done by using the standard way of creating objects in the Azure Management Portal.
By default an event hub has 16 partitions and a retention of 1 day. There is no need to change this in this case, but in a real life scenario these values can be altered to satisfy the business need.
To access the even hub externally access policies need to be created. The direction of the policies are based on the program/apps that is using the event hub. By clicking the arrow left of the event hub name we can configure the event hub. In the configure tab we need to add a policy and set the permission to Send.
To use the sender event hub (PowerBIEventHub) with Stream Analytics we need to create a new Consumer Group. This can be done via the Consumer Groups tab next to the Configure tab.
For the receiver event hub (PowerBIReceiver) we can create it via the same way as the sender event hub. Only this one doesn’t need a custom consumer group and the permission of the access policy needs to be set to Listen.
Now that the event hubs are set, Stream Analytics can be created and configured. This is pretty simple. Stream Analytics needs a storage account, but this can be done while creating a Stream Analytics.
Stream Analytics is basically a pipeline with an input and a output and in between a query defines which records of the input flows to the output. In our case we are linking the event hubs as in- and output of this stream via the Input and Output tab
In the Input tab we can easily add an input and a wizard pops up. In the first step choose Data Stream
In step 2 choose Event Hub as we are connecting our event hub to Stream Analytics
In step 3 we need to provide all the input event hub information. In this step the consumer group is needed we created before and it will not accept the $Default group as special characters are not accepted. The Input Alias is needed while defining the query; it is the ‘table’ used to select information from by the stream query.
And in the last step (4) choose JSON and UTF8 as we will send this information to the sender event hub.
Adding an output is almost the same as adding an input: after clicking the *add output *option a wizard pops up. In the first step we select Event Hub as this is the only option we can easily use to process the data directly. (Note: in the UserVoice suggestions there is already a request to add an API output with status ‘Under Review’…)
And in the final step, number 2, we provide the receiver event hub information. There is no need to configure the advanced settings in this case.
Now that we have a input and an output, we need to define a query to retrieve the input information and add it to the output. This is done by using the query tab. In my case I use a very simple query that is just putting all the input unfiltered to the output. The query has a SQL-like syntax and uses two ‘tables’, namely the input and output name provided by the different wizards.
After we created the query we can test it by uploading a JSON file and look at the output. If everything is set correctly we can start the stream by clicking the play button and the Azure service are setup and working correctly and ready to use by the sending and processing app.
- Introduction: part 1
- Configuring Azure services: part 2
- Sending, reading and processing the events: part 3
Jan Pieter Posthuma
My name is Jan Pieter Posthuma and I am a Microsoft Data Analytics consultant working for Rubicon, a local consultancy firm in The Netherlands.