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 Public Cloud, in Cloudera Manager, navigate to Data Hub Clusters > Endpoints tab, and copy the Hive Server endpoint.
  4. Paste the copied JDBC URL into a text file.
    jdbc:hive2://<host:port>/default;transportMode=http;httpPath=cliservice;ssl=true;retries=3
  5. Append mode=hplsql to the end of the JDBC URL for connecting to Hive from the client.
    jdbc:hive2://<host:port>/default;transportMode=http;httpPath=cliservice;ssl=true;retries=3;mode=hplsql
  6. On the client end, use the JDBC URL in Beeline to connect to Hive and enable HPL/SQL.
    beeline -n <csso_username> -p <password> -u
            "jdbc:hive2://<host:port>/default;transportMode=http;httpPath=cliservice;ssl=true;retries=3;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.