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.
-
On the client end, download the latest version of the Hive JDBC driver from the
Cloudera Downloads page.
-
Install the driver on the client end.
Typically, you add the JAR file to the Libraries folder.
-
In CDP Private Cloud Base, in Cloudera Manager, click to go to the Hive on Tez service page.
-
From the Hive on Tez service page, click
Actions and select Download Client
Configuration.
-
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***]
-
Paste the copied JDBC URL into a text file.
-
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
-
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.
