Support Questions
Find answers, ask questions, and share your expertise

Can't fetch data in sql server from hive tables.

Highlighted

Can't fetch data in sql server from hive tables.

New Contributor

Hi team, I am using Ambari (hive) for our bigdata implementation and It has good data loaded. Now I want to connect some of the tables hive using SQL server openquery methodology. I installed Horton ODBC drivers and Connection got established successfully and I can see list of tables in linked server. Now problem is I can not fetch the data from tables it is giving me below error. ERROR DESC: - "Cannot process the object "select * from rlo.abc;". The OLE DB provider "MSDASQL" for linked server "HADOOP" indicates that either the object has no columns or the current user does not have permissions on that object." Where "rlo" is my database and "abc" is table name. I got stucked here and cant move forword.

Thanks and Regards Abhijeet Barbate

6 REPLIES 6
Highlighted

Re: Can't fetch data in sql server from hive tables.

Hello @Abhijeet Barbate. In your ODBC driver I assume your connection to HiveServer2 is using a username and password. Have you confirmed that the same username/password can connect to Hive via beeline and can execute the select statement & return results?

This blog post has been useful for our project in the past - it's a bit old but is well-written. It does a good job of documenting the linked server work and permissions required to get the linked server calls to work... https://blog.oraylis.de/2015/02/querying-hadoop-from-sql-server/

Highlighted

Re: Can't fetch data in sql server from hive tables.

This post as well is good. Refer to the section titled "SQL Server Linked to a Hadoop Server - Setup" http://www.codeproject.com/Articles/1067605/Hadoop-For-Net-AngularJS-Developers

Highlighted

Re: Can't fetch data in sql server from hive tables.

New Contributor

Hi @bpreachuk,

thanks for quick reply.Yes I was great article to follow and I followed the same blog to set up connection but finally stuck on above issue.and with same credentials I can pull the data from hive to Tableau server and can generate reports.

Regards

Abhijeet

Re: Can't fetch data in sql server from hive tables.

That's really strange. I'm just trying to think of other things that could be causing the issue:

  • Do you have the userID and password in your providerstring (@provstr) in the spAddLinkedServer call in SQL Server?
  • Are there permissions on the linked server object or on the SQL Server database that need to be set?
  • Could you try selecting from a different table in a different database on Hive?
  • Are there any messages showing up in the Error Logs in Hive or SQL Server?
  • Could there be a version issue with the ODBC driver and the HDP version?
  • DO you have the correct 32 bit or 64 bit version of the ODBC driver?

I don't have any other ideas beyond that. Linked server queries are tough to debug since there are so many places that they can fail. Cross-system linked server calls like this are even tougher.

I hope this helps.

Highlighted

Re: Can't fetch data in sql server from hive tables.

Hi @Abhijeet Barbate, can you post your openquery statement? Also, try performing a standard select against the linked tables and see if you get the same results.

Highlighted

Re: Can't fetch data in sql server from hive tables.

New Contributor

Hi Scott ,

Thanks for quick reply

Yes I tried with and without open query but no success.

Scott with same credentials I can pull the data from hive to Tableau server and can generate reports.

Regards

Abhijeet

Don't have an account?