Power BI can connect to many data sources as you know, and Spark on Azure HDInsight is one of them. In area of working with Big Data applications you would probably hear names such as Hadoop, HDInsight, Spark, Storm, Data Lake and many other names. Spark and Hadoop are both frameworks to work with big data, they have some differences though. In this post I’ll show you how you can use Power BI (either Power BI Desktop or Power BI website) to connect to a sample of Spark that we built on an Azure HDInsight service. by completing this section you will be able to create simple spark on Azure HDInsight, and run few Python scripts from Jupyter on it to load a sample table into Spark, and finally use Power BI to connect to Spark server, load, and visualize the data.

This post is a section of Power BI from Rookie to Rock Star online book.

Prerequisite

• For running sample mentioned in this section you need to have an Azure subscription. (You can apply for a free trial as well)
• You need to install Microsoft Spark ODBC Driver on your machine (https://go.microsoft.com/fwlink/?LinkId=616229). This is for connecting from Power BI Desktop.
• For some features of Spark Connection through Power BI website you’d need Power BI Pro account (You can apply for a trial).

A Bit of Introduction

What is Spark? What is HDInsight?

I’ve spoken about Power BI well enough in previous chapters. However you might be new to big data names such as Spark and HDInsight, and would like to know what these are? I say it in the most simple method; HDInsight is the framework for running big data, you can have your big data storage as a distributed storage for massive availability. Spark on the other hand is another big data framework that works great for analytics and big data operation, however it doesn’t work with distributed files. So the real world big data solution has most of the time Spark on top of an HDFS (Hadoop Distributed File System) or Azure HDInsight to gain both scalability and powerful analytics. Spark is an Apache application, and often called as Apache Spark. However Spark can be installed on both Windows and Linux servers. In this sample for simplicity we will use Spark on Linux server.

What is Jupyter?

Jupyter is a live notepad engine for writing and running codes such as Python (it supports mote than 40 languages!) on servers (such as Spark server).

Setup and Configure Spark on Azure HDInsight

Let’s start building the first Spark on Azure HDInsight. You can skip this part if you have an Spark on Azure HDInsight already.

1. Login to Azure Portal through https://portal.azure.com.
2. Go to New, and then under Data + Analytics choose HDInsight
3. 
4. In the New HDInsight Cluster definition section set a name for cluster (my sample is mysparkcluster), then set Cluster Type as Spark (it is still in preview stage), then Cluster Operating System as Linux. When you choose Linux it will install on Ubuntu 14.04 LTS.
5. 
6. Choose one of your subscriptions for Azure (If you have more than one), then assign a resource group. You can choose one of existing resource groups or create a new one.
7. 
8. Set the Credentials for Spark cluster. set a password for admin user. Then write a SSH username and password. SSH username and password used to connect to spark cluster through command line. In this example we won’t use command line. There is also a limitation on some reserved SSH user names that you cannot use.
9. 
10. Set Data Source. You can choose an existing storage account, or create a new one. and set the container and location.
11. 
12. Choose Node Pricing Tiers. In this example because we are not processing large amount of data I’ll do it with couple of nodes. You can see how much this cluster costs you per hour based on your selection of nodes and pricing tier for worker nodes and head node.
13. 
14. Now Create the HDInsight Cluster.
15. It will take about 15 minutes for the Cluster to be created and visible in your Microsoft Azure dashboard.
16. 
17. Now you have your Spark on Azure HDInsight ready for the next step.

Setup the Sample Table for Spark on Azure HDInsight

In this section we will load a sample data into a table (inside our Spark on Azure HDInsight cluster) using some codes that we run from Jupyter.

1. Open Jupyter for your Spark on Azure HDInsight with going to https://<your spark cluster name>.azurehdinsight.net/jupyter
2. You will be asked for username and password. Enter the admin username and password we’ve created in previous section.
3. 
4. Click on New and choose a Python 2 type notebook.
5. 
6. You can name the document something meaningful and then copy and paste code below there into a cell

7. from pyspark import SparkContext
   from pyspark.sql import *
   from pyspark.sql import HiveContext
   from pyspark.sql import Row
   
   # Create Spark and Hive contexts
   sc = SparkContext('yarn-client')
   hiveCtx = HiveContext(sc)

    

8. Then press Shift+Enter (to run the code and go to the next cell)
9. You will see the Jupyter icon showing that the Kernel is busy after running commands.

10. 

    Kernel Busy

11. Wait for the command to run, and Kernel back to idle state.

12. 

    Kernel Idle

13. Now add script below in the second cell;

14. # Create an RDD from sample data
    hvacText = sc.textFile("wasb:///HdiSamples/HdiSamples/SensorSampleData/hvac/HVAC.csv")
    
    # Create a schema for our data
    Entry = Row('Date', 'Time', 'TargetTemp', 'ActualTemp', 'BuildingID')
    
    # Parse the data and create a schema
    hvacParts = hvacText.map(lambda s: s.split(',')).filter(lambda s: s[0] != 'Date')
    hvac = hvacParts.map(lambda p: Entry(str(p[0]), str(p[1]), int(p[2]), int(p[3]), int(p[6])))
    
    # Infer the schema and create a table       
    hvacTable = hiveCtx.createDataFrame(hvac)
    hvacTable.registerTempTable('hvactemptable')
    dfw = DataFrameWriter(hvacTable)
    dfw.saveAsTable('hvac')

    Commands above will populate a hive table called hvac from a CSV sample file located on the server.

15. After pressing Shift+Enter, and waiting for the kernel to go back to idle mode. you can query all hive tables with below command;

16. hiveCtx.sql("SHOW TABLES").show()

     

17. after Shift+Enter you will see the result below;

18. +---------------+-----------+
    |      tableName|isTemporary|
    +---------------+-----------+
    |  hvactemptable|       true|
    |hivesampletable|      false|
    |           hvac|      false|
    +---------------+-----------+

19. You can even query hvac table to see the data;

20. hiveCtx.sql("SELECT * FROM hvac LIMIT 10").show()

     

21. and the result will be;

22. +-------+--------+----------+----------+----------+
    |   Date|    Time|TargetTemp|ActualTemp|BuildingID|
    +-------+--------+----------+----------+----------+
    |6/10/13| 2:43:51|        68|        63|         3|
    |6/11/13| 3:43:51|        70|        55|        17|
    |6/12/13| 4:43:51|        69|        59|        17|
    |6/13/13| 5:43:51|        69|        68|        19|
    |6/14/13| 6:43:51|        67|        79|         2|
    |6/15/13| 7:43:51|        67|        71|         3|
    |6/16/13| 8:43:51|        69|        63|        16|
    |6/17/13| 9:43:51|        66|        56|         9|
    |6/18/13|10:43:51|        65|        61|        16|
    |6/19/13|11:43:51|        67|        74|         7|
    +-------+--------+----------+----------+----------+

Now we have table with the data ready for Power BI to work with, Let’s go to the next step.

Connection from Power BI Desktop to Spark on Azure HDInsight

As I said in the very first part this tutorial, you need to install Microsoft Spark ODBC driver to be able to connect from Power BI Desktop to Spark on Azure HDInsight.

1. Open Power BI Desktop and go to Get Data Section.
2. Choose Azure, and then Azure HDInsight Spark.
3. 
4. Enter the spark cluster URL as https://<your spark cluster name/azurehdinsight.net
5. 
6. If you don’t have Microsoft Spark ODBC Driver installed you will see an error as below;
7. 
8. If you have driver installed you will be asked for the username and password (the admin user that we’ve created for Spark on Azure HDInsight before)
9. After successful connection you will see list of tables. Choose hvac simply and then Load.
10. 
11. That was really simple from Power BI point of view, wasn’t it?!
12. Now you can visualize it as you want! for example I’ve created an Area Chart with Time as axis, and average of ActualTemp and average of TargetTemp as values. I’ve changed color of data values as below.
13. 
14. I’ve also added a slicer (horizontal) for the BuildingID.
15. 
16. And here is the result.

You can simply publish your report to Power BI website. Let’s now see how you can connect to Spark from Power BI website itself.

Connection from Power BI website to Spark on Azure HDInsight

1.  Login to Power BI website and Get Data from Databases, and Choose Spark on Azure HDInsight as the source.
2. 
3. Type in the server name as https://<your spark cluster name>.azurehdinsight.net and then connect
4. Sign in to the server with user name and password
5. 
6. Now you can build the report you want from the hvac table.

References;

 https://powerbi.microsoft.com/en-us/documentation/powerbi-spark-on-hdinsight-with-direct-connect/

https://azure.microsoft.com/en-us/documentation/articles/hdinsight-apache-spark-use-bi-tools/

https://azure.microsoft.com/en-us/documentation/articles/hdinsight-apache-spark-jupyter-spark-sql/

Reza Rad

Trainer, Consultant, Mentor

Reza Rad is a Microsoft Regional Director, an Author, Trainer, Speaker and Consultant. He has a BSc in Computer engineering; he has more than 20 years’ experience in data analysis, BI, databases, programming, and development mostly on Microsoft technologies. He is a Microsoft Data Platform MVP for 12 continuous years (from 2011 till now) for his dedication in Microsoft BI. Reza is an active blogger and co-founder of RADACAD. Reza is also co-founder and co-organizer of Difinity conference in New Zealand, Power BI Summit, and Data Insight Summit.
Reza is author of more than 14 books on Microsoft Business Intelligence, most of these books are published under Power BI category. Among these are books such as Power BI DAX Simplified, Pro Power BI Architecture, Power BI from Rookie to Rock Star, Power Query books series, Row-Level Security in Power BI and etc.
He is an International Speaker in Microsoft Ignite, Microsoft Business Applications Summit, Data Insight Summit, PASS Summit, SQL Saturday and SQL user groups. And He is a Microsoft Certified Trainer.
Reza’s passion is to help you find the best data solution, he is Data enthusiast.
His articles on different aspects of technologies, especially on MS BI, can be found on his blog: https://radacad.com/blog.