Connecting to HDP 2.2 Hive using JDBC driver

In the http://wpcertification.blogspot.com/2014/04/connecting-to-hive-using-jdbc-client.html i blogged about how to connect to Hive using Apache Hive. I wanted to figure out how to connect to Hive 0.13 in Hortonworks Sandbox 2.2, so i followed these steps You can download the complete source code for this project from here First i did create App.java class like this. When connecting to HDP 2.2 i had to use HiveServer2. I followed instructions on HDP Documents

	package com.spnotes.hive;
	import java.sql.Connection;
	import java.sql.DriverManager;
	import java.sql.ResultSet;
	/**
	* Hello world!
	*
	*/
	public class App
	{
	private static String driverName = "org.apache.hive.jdbc.HiveDriver";
	public static void main( String[] args )throws Exception{
	try {
	Class.forName(driverName);
	Connection connection = null;
	System.out.println("Before getting connection");
	connection= DriverManager.getConnection("jdbc:hive2://localhost:10000/default", "root", "hadoop");
	System.out.println("After getting connection " + connection);
	ResultSet resultSet = connection.createStatement().executeQuery("select * from default.employee");
	while (resultSet.next()) {
	System.out.println(resultSet.getString(1) + " " + resultSet.getString(2));
	}
	} catch (Exception e) {
	e.printStackTrace();
	System.exit(1);
	}
	}
	}

view raw App.java hosted with ❤ by GitHub

I used following maven build file, you can see i am including Hive, Hadoop and Log4j jar. The log4j.jar lets me enable logging

	<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
	xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
	<modelVersion>4.0.0</modelVersion>
	<groupId>com.spnotes.hive</groupId>
	<artifactId>HelloHive</artifactId>
	<version>1.0-SNAPSHOT</version>
	<packaging>jar</packaging>
	<name>HelloHive</name>
	<url>http://maven.apache.org</url>
	<properties>
	<project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
	</properties>
	<dependencies>
	<dependency>
	<groupId>junit</groupId>
	<artifactId>junit</artifactId>
	<version>3.8.1</version>
	<scope>test</scope>
	</dependency>
	<dependency>
	<groupId>org.apache.hive</groupId>
	<artifactId>hive-jdbc</artifactId>
	<version>0.13.0.2.1.1.0-385</version>
	</dependency>
	<dependency>
	<groupId>org.apache.hadoop</groupId>
	<artifactId>hadoop-common</artifactId>
	<version>2.4.0.2.1.1.0-385</version>
	</dependency>
	<dependency>
	<groupId>log4j</groupId>
	<artifactId>apache-log4j-extras</artifactId>
	<version>1.2.17</version>
	</dependency>
	</dependencies>
	<build>
	<plugins>
	<plugin>
	<artifactId>maven-assembly-plugin</artifactId>
	<configuration>
	<archive>
	<manifest>
	<mainClass>com.spnotes.hive.App</mainClass>
	</manifest>
	</archive>
	<descriptorRefs>
	<descriptorRef>jar-with-dependencies</descriptorRef>
	</descriptorRefs>
	</configuration>
	</plugin>
	</plugins>
	</build>
	<repositories>
	<repository>
	<releases>
	<enabled>true</enabled>
	<updatePolicy>always</updatePolicy>
	<checksumPolicy>warn</checksumPolicy>
	</releases>
	<snapshots>
	<enabled>false</enabled>
	<updatePolicy>never</updatePolicy>
	<checksumPolicy>fail</checksumPolicy>
	</snapshots>
	<id>HDPReleases</id>
	<name>HDP Releases</name>
	<url>http://repo.hortonworks.com/content/repositories/releases/</url>
	<layout>default</layout>
	</repository>
	</repositories>
	</project>

view raw pom.xml hosted with ❤ by GitHub

Once my code was complete i tried connecting to Hive and got following error around access control.

java.sql.SQLException: Error while compiling statement: FAILED: HiveAccessControlException Permission denied. 
Principal [name=root, type=USER] does not have following privileges on Object [type=TABLE_OR_VIEW, name=default.sample_07] : [SELECT]
 at org.apache.hive.jdbc.Utils.verifySuccess(Utils.java:121)
 at org.apache.hive.jdbc.Utils.verifySuccessWithInfo(Utils.java:109)
 at org.apache.hive.jdbc.HiveStatement.execute(HiveStatement.java:231)
 at org.apache.hive.jdbc.HiveStatement.executeQuery(HiveStatement.java:355)
 at com.spnotes.hive.App.main(App.java:24)
 at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
 at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
 at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
 at java.lang.reflect.Method.invoke(Method.java:601)
 at com.intellij.rt.execution.application.AppMain.main(AppMain.java:134)

I had to use following command in hive console to give access to root user for querying employee table.

hive> grant select on table sample_08 to user employee;