HBase clients discover the running HBase cluster using two properties, namely hbase.zookeeper.quorum and zookeeper.znode.parent. The former is used to connect to the zookeeper cluster, while the latter shows which znode keeps the data (and address for HMaster) for the cluster.
Ambari and HDP installations use different zookeeper.znode.parent values than the default (/hbase). On secure setup, the value is configured as /hbase-secure, and in unsecure mode it is set as /hbase-unsecure.
For Hive-HBase integration to work, thus Hive should be configured with these settings with either supplying these options via HIVE_OPTS="-hiveconf hbase.zookeeper.quorum=server1,server2,server3 zookeeper.znode.parent=/hbase-unsecure" or inside the hive script with
Hope this helps.