On a recent project I was tasked with importing data into Power BI from a Hive table. For those of you who are new to Azure or Big Data, Hive is a data warehousing infrastructure for Hadoop which sits in the HDInsight stack on Azure. The primary purpose of Hive is to provide data summarisation, query and analysis for big data sets. In this blog I’m going to take you through the steps and note any Gotchas so that you can connect to Hive using Power BI.
Connecting to Hive
As Hive is part of the Azure HDInsight stack it would be tempting to select the HDInsight or Hadoop connector when you’re getting data. However, note HDFS in brackets beside the Azure HDInsight and Hadoop File options as this means that you’ll be connecting to the underlying data store, which can be Azure Data Lake Store or Azure Blob Storage – both of which use HDFS architectures.
But this doesn’t help when you want to access a Hive table. In order to access a Hive table you will first of all need to install the Hive ODBC driver from Microsoft. Once you’ve downloaded and installed the driver you’ll be able to make your connection to Hive using the ODBC connector in PowerBI.
You will need to input a connection string to connect even though it says optional. The format of the connection string is as follows:
Driver={Microsoft Hive ODBC Driver};Host=hdinsightclustername.azurehdinsight.net;Port=443;Schema=default; RowsFetchedPerBlock=10000; HiveServerType=2; AuthMech=6; DefaultStringColumnLength=200;
One the next screen you’ll be asked to enter a username and password. The credentials used here are not what you use to access Azure but the credentials you created when you set up the HDInsight cluster and use to login to the cluster.
Click connect and you’ll be able to pull through the tables you need into Power BI. Or, if you want to be selective in what is returned, you can write a HiveQL query in the ODBC dialog. It’s also worth noting that at the moment it’s only possible to do an import of Hive Data in Power BI and not perform Direct Query, so if your data set is huge you’ll want to summarise the data or be really selective in what is returned first.
Meet the Team – Zach Stagers, Senior Consultant
Up next in our series of Meet the Team blogs is Zach Stagers, Senior Consultant.
Nov
A Cost Comparison between PaaS and IaaS Cloud Architectures
Introduction This aim of this blog is to do a cost comparison between two cloud
Nov
Providing Careers at Adatis
At Adatis, we believe in developing and training our employees which aligns with 2 of
Nov
Meet the Team – Donna Bavin, People Operations Lead
We couldn’t achieve all the great work we do without our amazing team, so in
Nov
Deep Dive into the Common Data Model Video Presentation
At SQLBits last month some of our team gave fantastic sessions on a variety of
Oct
The Adatis Values – No is OK and Listen & Challenge
Our nine company values are super important to us and are at the heart of
Oct
Optical Character Recognition (OCR) using Tesseract and Databricks
The topic of Optical Character Recognition (OCR) is not an unexplored field to the Adatis
Oct
An experiment with Azure Custom Vision
An experiment with Azure Custom Vision Azure Cognitive Services is a comprehensive family of APIs,
Oct