Connecting Hive to BI tools using a JDBC/ODBC driver

To query, analyze, and visualize data stored within the CDP Data Center using drivers provided by Cloudera, you connect Apache Hive to Business Intelligence (BI) tools.

How you connect to Hive depends on a number of factors: the location of Hive inside or outside the cluster, the HiveServer deployment, the type of transport, transport-layer security, and authentication. HiveServer is the server interface that enables remote clients to execute queries against Hive and retrieve the results using a JDBC or ODBC connection.
  • Choose a Hive authorization model.
  • Configure authenticated users for querying Hive through JDBC or ODBC driver. For example, set up a Ranger policy.
  1. Download the JDBC or ODBC driver from the Cloudera Downloads page.
  2. Depending on the type of driver you obtain, proceed as follows:
    • ODBC driver: follow instructions on the ODBC driver download site, and skip the rest of the steps in this procedure.
    • JDBC driver: put the driver in the classpath of your JDBC client, such as Tableau. For example, check the client documentation about where to put the driver.
  3. Find the JDBC URL for HiveServer using one of a number methods. For example:
    • In a CDP Data Warehouse, from the options menu of your Virtual Warehouse, click Copy JDBC URL.
    • In Cloudera Manager (CM), click Clusters > Hive click Actions, and select Download Client Configuration.
      Unpack hive_on_tez-clientconfig.zip, open beeline-site.xml, and copy the value of beeline.hs2.jdbc.url.hive_on_tez . This value is the JDBC URL. For example jdbc:hive2://my_hiveserver.com:2181/;serviceDiscoveryMode=zooKeeper;zooKeeperNamespace=hiveserver2
  4. In the BI tool, such as Tableau, configure the JDBC connection using the JDBC URL and driver class name, org.apache.hive.jdbc.HiveDriver.