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
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/
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.
That's really strange. I'm just trying to think of other things that could be causing the issue:
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.
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.