Cloudera Docs

Connect a Virtual Warehouse and Microsoft Power BI Desktop

You learn how to connect to a Hive or Impala Virtual Warehouse from Microsoft Power BI. You can then use Power BI to visualize the data in the Virtual Warehouse on Cloudera Public Cloud.

Although you configure Power BI using a Microsoft UI labeled Hive LLAP, you can use this procedure to connect to a Hive Virtual Warehouse or to an Impala Virtual Warehouse. After making the connection, you can use Power BI to create reports based on data in the Virtual Warehouse.

  • You have access to a CDP Environment and a Hive LLAP or Impala Virtual Warehouse that contains tables of data.

  • You know the CDP workload user name and password you set in User Management in the Management Console. You need to use your workload user name and its associated password to log into the Virtual Warehouse.

  1. Log in to the Data Warehouse service as DWUser.
  2. Go to the Virtual Warehouses tab, locate the Hive Virtual Warehouse you want to connect to, and click > Copy JDBC URL.
    This copies the JDBC URL to your system's clipboard.
  3. Paste the copied JDBC URL into a text file. It should look similar to the following: 
    jdbc:hive2://<your-virtual-warehouse>.<your-environment>.<dwx.company.com>/default;transportMode=http;httpPath=cliservice;ssl=true;retries=3
  4. From the text file where you just pasted the URL, copy the host name from the JDBC URL to your clipboard. For example, in the URL shown in the step above, the host name is: 
    <your-virtual-warehouse>.<your-environment>.<dwx.company.com>
  5. Open Power BI Desktop, select Get Data > More .
  6. Select Hive LLAP.
  7. In the Hive LLAP dialog, configure Power BI to connect to the Hive or Impala Virtual Warehouse in Cloudera Public Cloud as follows:
    • In Server, paste the contents of your clipboard from step 4, add a colon and 443.
    • In Database, enter the database name, for example default.
    • In Thrift Transport Protocol, select HTTP.
    • In Data Connectivity mode, select DirectQuery.

    Click OK.

  8. Enter your CDP workload user name and password.
  9. Click Connect.
    The PowerBI Navigator appears.
  10. Select the tables you want to use in a report, and click Load.
    In Fields, the table appears and is now available as a data source for your Power BI report.