Setting up a Hive client

As a Hive user who connects to HiveServer2 from a client, you need to know how to set up the client for using HPL/SQL.

  1. On the client end, download the latest version of the Hive JDBC driver from the Cloudera Downloads page.
  2. Install the driver on the client end.
    Typically, you add the JAR file to the Libraries folder.
  3. In CDP Private Cloud Base, in Cloudera Manager, click Clusters > Hive on Tez to go to the Hive on Tez service page.
  4. From the Hive on Tez service page, click Actions and select Download Client Configuration.
  5. Unpack the hive_on_tez-1-clientconfig.zip, open the beeline-site.xml file, and copy the value of beeline.hs2.jdbc.url.HIVE_ON_TEZ-1. This value is the JDBC URL.
    jdbc:hive2://[***HOST***]:[***PORT***]/default;serviceDiscoveryMode=zooKeeper;zooKeeperNamespace=hiveserver2;sslTrustStore=[***TRUSTSTORE-PATH***];trustStorePassword=[***TRUSTSTORE-PASSWORD***]
  6. Paste the copied JDBC URL into a text file.
  7. Append mode=hplsql to the end of the JDBC URL for connecting to Hive from the client.
    jdbc:hive2://[***HOST***]:[***PORT***]/default;serviceDiscoveryMode=zooKeeper;zooKeeperNamespace=hiveserver2;sslTrustStore=[***TRUSTSTORE-PATH***];trustStorePassword=[***TRUSTSTORE-PASSWORD***];mode=hplsql
  8. In the BI tool, configure the JDBC connection using the JDBC URL and driver class name, com.cloudera.hive.jdbc.HS2Driver, and use the URL in Beeline to connect to Hive and enable HPL/SQL.
    beeline -n <csso_username> -p <password> -u 
    "jdbc:hive2://[***HOST***]:[***PORT***]/default;serviceDiscoveryMode=zooKeeper;zooKeeperNamespace=hiveserver2;sslTrustStore=[***TRUSTSTORE-PATH***];trustStorePassword=[***TRUSTSTORE-PASSWORD***];mode=hplsql"
    At the Hive prompt, you can run HPL/SQL. You can use the forward slash (/) as a statement delimiter because a HPL/SQL statement can have multiple rows consisting of multiple SQL statements.
Set hive.security.authorization.sqlstd.confwhitelist.append=QUERY_EXECUTOR in Hive Service Advanced Configuration Snippet (Safety Valve) for hive-site.xml before running HPL/SQL queries.