Reply
Highlighted
Regular Visitor
Posts: 43
Registered: ‎12-18-2016
Accepted Solution

Need help on connecting to hive and imphala data sources

Does power bi support direct query for cloudera hive and does it support imphala in import mode with support for data refresh using on-premises gateway? As per the documentation, there is no support to refresh data from gateway for imphala. I couldn't find any information for cloudera hive.( I found some examples where hive is used in import mode but couldn't find any help on scheduling data refresh for the same.)

Accepted Solutions
Moderator
Posts: 3,119
Registered: ‎03-10-2016

Re: Need help on connecting to hive and imphala data sources

Hi @mannu,

I am not familiar with the difference between cloudera hive and imphala. However, as per this official article, it supports to use Import or DirectQuery mode to connect to imphala in Power BI Desktop, but based on this sentence” Future plans include enabling refresh support using the Power BI Gateway”, it seems that there is limitation about refresh.

In addition, based on my research, you can create ODBC data source for cloudera hive, then connect to the ODBC data source in Power BI Desktop, in this case, only import mode is available, for more details, please review this blog:https://community.hortonworks.com/articles/61185/visualizing-hive-data-using-microsoft-power-bi.html. After publish your reports from Power BI Desktop to Power BI Service, add ODBC data source under the gateway following the guide in this similar thread, then check if you can refresh your dataset.

Thanks,
Lydia Zhang

View solution in original post

Moderator
Posts: 3,119
Registered: ‎03-10-2016

Re: Need help on connecting to hive and imphala data sources

[ Edited ]

Hi @mannu,

Firstly, for Impala, as per that official article, data refresh feature should be included in future.

 

Secondly, there is no built-in connector for Hive in Power BI Desktop, so you can consider to use ODBC entry instead. And yes, when using the ODBC entry in Power BI Desktop, only import mode is avaliable.

Thirdly, what do you mean that you don't have admin permission so that you are unable to test on-premises gateway? On-premises gateway requires Power BI pro license, if you don't have pro license, you can try Power BI pro trial. Also have you checked thi similar thread? You are able to use on-premises gateway to refresh ODBC data source in Power BI Service.

Thanks,
Lydia Zhang

View solution in original post


All Replies
Moderator
Posts: 3,119
Registered: ‎03-10-2016

Re: Need help on connecting to hive and imphala data sources

Hi @mannu,

I am not familiar with the difference between cloudera hive and imphala. However, as per this official article, it supports to use Import or DirectQuery mode to connect to imphala in Power BI Desktop, but based on this sentence” Future plans include enabling refresh support using the Power BI Gateway”, it seems that there is limitation about refresh.

In addition, based on my research, you can create ODBC data source for cloudera hive, then connect to the ODBC data source in Power BI Desktop, in this case, only import mode is available, for more details, please review this blog:https://community.hortonworks.com/articles/61185/visualizing-hive-data-using-microsoft-power-bi.html. After publish your reports from Power BI Desktop to Power BI Service, add ODBC data source under the gateway following the guide in this similar thread, then check if you can refresh your dataset.

Thanks,
Lydia Zhang

Regular Visitor
Posts: 43
Registered: ‎12-18-2016

Re: Need help on connecting to hive and imphala data sources

[ Edited ]

Thanks @v-yuezhe-msft Lydia for your response.

I referred the urls that you mentioned.

 

For Impala, I observed that

If we use ODBC driver (in Power BI Desktop), we can use only import mode(infact the option to choose import and direct query is not available). However, if we opt for built in Impala connector, we should be able to use import / direct query .

 

For Hive,

 

The only documentation I found was the article from hortonworks that you shared as well. With ODBC driver, I could only use import mode. (option to select import/direct query was not presented in UI)

 

I do not have admin privilege. So unable to test the working of on-premises gateway and hence the question. But as per the documention,  data refresh is not supported for Impala as of Mar 2017.

 

There is no documentation that details on data refresh using on-premises gateway for Hive. But then, to my knowledge, data refresh is not supported on data sources that use ODBC connectors (Not sure where I read it).

 

If you do have any additional information please do share it.

 

 The urls below provides info on differences between hive and impala in the world of hadoop.

https://www.quora.com/What-is-the-difference-between-Apache-HIVE-and-Impala

https://hortonworks.com/blog/impala-vs-hive-performance-benchmark/

 

Moderator
Posts: 3,119
Registered: ‎03-10-2016

Re: Need help on connecting to hive and imphala data sources

[ Edited ]

Hi @mannu,

Firstly, for Impala, as per that official article, data refresh feature should be included in future.

 

Secondly, there is no built-in connector for Hive in Power BI Desktop, so you can consider to use ODBC entry instead. And yes, when using the ODBC entry in Power BI Desktop, only import mode is avaliable.

Thirdly, what do you mean that you don't have admin permission so that you are unable to test on-premises gateway? On-premises gateway requires Power BI pro license, if you don't have pro license, you can try Power BI pro trial. Also have you checked thi similar thread? You are able to use on-premises gateway to refresh ODBC data source in Power BI Service.

Thanks,
Lydia Zhang

Regular Visitor
Posts: 43
Registered: ‎12-18-2016

Re: Need help on connecting to hive and imphala data sources

Thanks @v-yuezhe-msft Lydia for the response.

 

Moderator
Posts: 3,119
Registered: ‎03-10-2016

Re: Need help on connecting to hive and imphala data sources

Hi @mannu,

Could you please mark helpful replies as answer to close this thread? This way, other community members would easily find the solution when they get same issue.

Thanks,
Lydia Zhang