When you try to connect to your hive server of Hadoop using Toad Data point to query your Hive tables. There is a chance most people get handed with a error like “No more data Available” and some unexpected errors like “Could not connect client socket: Already open”.
I thought its worth while writing this post explaining to explain how myself and one of the expert with Toad Data point has resolved this. His knowledge on the tool is extraordinary.
first we need to make sure we are using Hiveserver1 and not Hiveserver2 at this point of time which can be changed in future. To have success connectivity as of today we should use hiveserver1
Step 1: we have filtered down to the level to see what error is getting posted on the hiveserver that we are running. Generally that would be something like this which we had in our case.
java.sql.SQLException: null, message from server: “Host ‘EDGESERVERNAME_FULLYQUALIFIED’ is not allowed to connect to this MySQL server”
Step 2: This means for some reason that your server is not authorized to connect to the metastore of Hive, then we have to grant the persmission level, Grant all on . to user@edgehostname_fullyqualified; on the mysql which will make your edge server pass the authentication.
you can replace the edgehostname_fullyqualified with server/host name to which you are connecting to.
please do not provide any thing in the advance table for the ssh and username and password, it will use the default credentials.
Hope this helps the users who has similar issues. Its worth of checking these things up before we decide it could be we have some other problems,
Toad data view point just works out of the box if you can provide the correct details and have correct configuration on hadoop cluster for your hive metastore.
Thanks
Deepak Gattala