Often Hortonworks delivered Hive enabled clusters, are not setup to allow you to easily connect a SQL tool to HIVE.
Follow these steps initially to connect a SQL tool such as SQL Developer / Tableau / DV Desktop etc
In order to connect to Hive from DVD, we need to configure BDCS-CE’s Hive Thrift Server to use the binary transport protocol. By default, BDCS-CE’s hive thrift server is configured to use the http transport protocol. These changes are done using the Ambari web console.
Here are the steps:
1.Login To Ambari console
2.Once connected to Ambari, click on “Hive” on the left-hand list of services
3.Then click on the “Configs” tab
4.In the search box, type “server2”
5.Click on the Advanced sub-tab
6.In the General section, change the “HiveServer2 Port” to 10002 (it defaults to 10000, but that port will already be in use for something else)
7.Also in the General section, change the “hive.server2.transport.mode” to binary.
8.In the Custom hive-site section, delete the “hive.server2.thrift.bind.host” property (by clicking on the red minus symbol)
9.Click save
10.In the notes field, enter “transport mode”
11.Click save again
12.If you see a “Configurations” pop-up, click “Proceed Anyway”
13.Click OK to acknowledge that changes were made successfully
14.Then click Restart, then Restart All Affected
15.Then click Confirm Restart All
You should now find you can connect using a SQL tool with the correct JDBC drivers enabled to the Hive server port on 10002.
Now, you need to decide how you want to connect to the Hive Thrift Server port, which you set to port 10002. You can either choose to use a SSH tunnel (which is very secure) or choose to open port 10002 to the outside world (which can be less secure).
- If you want to use a SSH tunnel, refer to the note “xtra Connecting to Ambari” which has an example of setting up a SSH tunnel (but you would use port 10002 instead of Ambari’s 8080).
- If you want to open up port 10002, you will need to create a new access rule for port 10002. s.