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

[#1]

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

[#2]

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/

[#3]

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

[#4]

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

[#5]

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.

[#6]

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.

[#7]

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