Connect Microsoft Power BI desktop to Cloudera Impala or Hive with Kerberos

Microsoft Power BI desktop is free and is able to successfully connect to a Cloudera Impala or hive database with Kerberos security enabled. The below blog only shows Impala driver but you can use same procedure with Hive driver also.

The basic steps are:

  • Install the MIT Kerberos client for Windows and make sure you have successfully got a ticket to the Cloudera CDH cluster.
  • Install the Power BI desktop
  • Create a ODBC System DSN. I used the MicroStrategy Impala ODBC driver client version 1.02.09.1013 (created by Simba Technologies) but best to download the Cloudera ODBC Driver for Impala or Hive from Cloudera website.
  • While creating the ODBC DSN use the following parameters:
  • Host=Impala datanode hostname, Port=21050, Database=default, Authentication Mechanism=Kerberos, Realm=Your kerberos realm, Host FQDN=_HOST, Service Name=impala. Leave the Delegate Kerberos Credentials and Use Keytab as blank. Transport Buffer size=1000, Delegation UID leave blank.

  • Test the connect if successful otherwise it wont work. Make sure you got a new kerberos ticket in the MIT Kerberos client using your userid and password.
  • After ODBC Test is successful then go to the Power BI desktop and click on Get Data.
  • Select More->Other->ODBC option->connect. It will show a dropdown for your recently created ODBC DSN for Impala with whatever name you specified.
  • Press OK and you should be able to see your database and tables in Cloudera Impala CDH cluster and do any visualization in PowerBI.

9 thoughts on “Connect Microsoft Power BI desktop to Cloudera Impala or Hive with Kerberos

  1. Hi, thank you for the great article. I followed it but I have from PowerBi this error:
    Details: “ODBC: ERROR [HY000] [Cloudera][Support] (50366) Failed to load the security library: krb5_64.dll
    ERROR [HY000] [Cloudera][Support] (50366) Failed to load the security library: krb5_64.dll”

    I tested ODBC connection from ODBC Query Tool and I can execute (I have right credentials) a query. Could I ask you, if you have any idea how to solve it?
    Thanks.

    Like

  2. Try to uninstall MIT Kerberos for Windows and reinstall and configure it as given in the post https://plenium.wordpress.com/2018/08/28/tableau-desktop-connect-to-cloudera-hadoop-using-kerberos/ . You may need to reboot after MIT client install and KRB5CCNAME environment variable setting. After that do a Test of the Microsoft ODBC 64bit DSN and see if it connects to Cloudera Hive or Impala. If Test is successful then Power BI ODBC DSN should work.

    Like

  3. You have the check all the 32bit or 64bit versions. First check if Power BI is 32bit or 64bit by going to File->Accounts. Then the downloaded Cloudera ODBC DSN has to match 32bit or 64bit same as Power BI. Also the MIT for WIndows has to match 32 or 64bit. Looks like Power BI was unable to load 64bit DLL: krb5_64.dll

    Like

    1. I have
      Windows 10 Pro 64-bit operating system
      PowerBI version is Version: 2.78.5740.721 64-bit (February 2020)
      MIT Kerberos for Windows 4.1
      Cloudera ODBC Driver for Impala 2.6.8 (C6 Compatible)
      I will try to install it on my colleague machine to eliminate the possibility of a strange configuration on my environment.

      Like

      1. btw. I used Cloudera ODBC Driver and Power Bi to connect to another Impala without Kerberos and it works.

        Like

  4. You can do a quick test with DBeaver SQL Tool to check the ODBC and Kerberos connection to see if the problems is with ODBC or PowerBI. Also in my PC the C:\Program Files\MIT\Kerberos\bin\krb5_64.dll is the location of the file. I also see this in my windows PATH=………;C:\Program Files\MIT\Kerberos\bin;……..If not present it may need to be appended to the PATH environment variable. You can also check in Windows Event Viewer if any errors related to loading krb5_64.dll as sometimes antivirus programs prevent the loading of DLLs.
    https://plenium.wordpress.com/2019/10/15/connect-dbeaver-sql-tool-to-cloudera-hive-impala-with-kerberos/

    Like

    1. I checked ODBC and Kerberos using DBeaverSQL (thanks for the tip to great database tool) and it worked. Then I checked the paths and it was ok too. Then I tried to reinstall PowerBI again. Now I don’t use installations which install PowerBI as windows app, but I directly downloaded
      PBIDesktopSetup_x64.exe and install it. Now is destination C:\ProgramFiles\”Microsoft Power BI Desktop” not C:\ProgramFiles\WindowsApps\ … and it works :). Thank you very much for your help.

      Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.