Setting up a CDW client

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

  • You have created a Database Catalog that is populated with data.

    You can select an option to load sample airline data when you create the catalog.

  • You have an existing Hive Virtual Warehouse, or you added a new one, that is configured to connect to the Database Catalog with data.
  1. On the client end, download the latest version of the Hive JDBC driver from Cloudera Downloads page.
  2. Install the driver on the client end.
    Typically, you add the JAR file to the Libraries folder.
  3. Log into the CDP web interface and navigate to the Data Warehouse service.
  4. In the Data Warehouse service, click Virtual Warehouse in the left navigation panel.
  5. On the Virtual Warehouses page, click the options menu of the Hive VW you want to connect to, and select Copy JDBC URL.
  6. Paste the copied JDBC URL into a text file.
    jdbc:hive2://<your-virtual-warehouse>.<your-environment>.<dwx.company.com>/default;transportMode=http;httpPath=cliservice;ssl=true;retries=3
  7. Append mode=hplsql to the end of the JDBC URL for connecting to CDW from the client.
    jdbc:hive2://<your-virtual-warehouse>.<your-environment>.<dwx.company.com>/default;transportMode=http;httpPath=cliservice;ssl=true;retries=3;mode=hplsql                
  8. On the client end, use the URL in Beeline to connect to CDW and enable HPL/SQL.
    beeline -n <csso_username> -p <password> -u "jdbc:hive2://<your-virtual-warehouse>.<your-environment>.<dwx.company.com>/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.