Connect to Hive via JDBC

Hive is the base component for data warehousing applications in big Data technology clusters, and is a benchmark for other similar data warehouse applications. The underlying data manipulation can be handled in a scripted manner with hive-client. If you need to develop an application, you need to connect using the JDBC driver for hive. This article is based on the example on the hive wiki and explains in detail how to use JDBC to connect to the hive database. Hive Wiki Original address:

Https://cwiki.apache.org/confluence/display/Hive/HiveClient

Https://cwiki.apache.org/confluence/display/Hive/HiveServer2+Clients#HiveServer2Clients-JDBC

First hive must be started as a service, and our platform chooses the HDP platform, hdp2.2 the hive Server2 mode when the platform is started by default. Hiveserver2 is a more advanced service model than Hiveserver, providing advanced features such as concurrency control, security, and so on that Hiveserver cannot provide. The server starts up in different modes, and the client code is encoded in slightly different ways, as described in the code.

After the service starts, edit the code in the Eclipse environment. The code is as follows:

Importjava.sql.SQLException;Importjava.sql.Connection;ImportJava.sql.ResultSet;Importjava.sql.Statement;ImportJava.sql.DriverManager; Public classhivejdbcclient {/*the Hiverserver version uses this driver*/  //private static String drivername = "Org.apache.hadoop.hive.jdbc.HiveDriver";  /*the Hiverserver2 version uses this driver*/  Private StaticString drivername = "Org.apache.hive.jdbc.HiveDriver";  Public Static voidMain (string[] args)throwsSQLException {Try{class.forname (drivername); } Catch(ClassNotFoundException e) {e.printstacktrace (); System.exit (1); }    /*hiverserver version JDBC URL format*/    //Connection con = drivermanager.getconnection ("Jdbc:hive://Hostip:10000/default "," "," "");    /*hiverserver2 version JDBC URL format*/Connection con= Drivermanager.getconnection ("Jdbc:hive2://hostip:10000/default", "Hive", "Hive"); Statement stmt=con.createstatement (); //parameter Setting Test//Boolean reshivepropertytest = stmt//. Execute ("SET TEZ.RUNTIME.IO.SORT.MB = +");        BooleanReshivepropertytest =stmt. Execute ("Set Hive.execution.engine=tez");    System.out.println (reshivepropertytest); String TableName= "Testhivedrivertable"; Stmt.executequery ("DROP table" +tableName); ResultSet Res= Stmt.executequery ("CREATE TABLE" + TableName + "(key int, value string)"); //Show TablesString sql = "Show tables '" + tableName + "'"; System.out.println ("Running:" +SQL); Res=stmt.executequery (SQL); if(Res.next ()) {System.out.println (res.getstring (1)); }    //Describe tablesql = "Describe" +TableName; System.out.println ("Running:" +SQL); Res=stmt.executequery (SQL);  while(Res.next ()) {System.out.println (res.getstring (1) + "\ T" + res.getstring (2)); }     //load data into table//Note:filepath have to is local to the hive server//Note:/tmp/a.txt is a ctrl-a separated file with the fields per lineString filepath = "/tmp/a.txt"; SQL= "Load data local inpath '" + filepath + "' into table" +TableName; System.out.println ("Running:" +SQL); Res=stmt.executequery (SQL); //SELECT * Querysql = "SELECT * from" +TableName; System.out.println ("Running:" +SQL); Res=stmt.executequery (SQL);  while(Res.next ()) {System.out.println (string.valueof (Res.getint (1) + "\ T" + res.getstring (2)); }        //Regular hive Querysql = "SELECT COUNT (1) from" +TableName; System.out.println ("Running:" +SQL); Res=stmt.executequery (SQL);  while(Res.next ()) {System.out.println (res.getstring (1)); }  }}

The following jar packages can be placed in Eclipse BuildPath, which can be placed on the Classpath path at startup.

650) this.width=650; "title=" image "style=" border-left-0px; border-right-width:0px; Background-image:none; border-bottom-width:0px; padding-top:0px; padding-left:0px; padding-right:0px; border-top-width:0px "border=" 0 "alt=" image "src=" http://s3.51cto.com/wyfs02/M01/73/3B/ Wkiom1x33zfjey2yaab5nliisng203.jpg "width=" 244 "height=" 126 "/>

Where jdbcdriver can be used Hive-jdbc.jar, the other jars must also be included, or with the Jdbc-standalone jar package, other jar packages may not be included with this jar. Where the Hadoop-common package must be included.

Wait for the result to run correctly after execution. If an exception occurs, it is resolved according to the prompt. There are several exceptions to the hint that are ambiguous:

1. If classpath or BuildPath does not contain Hadoop-common-0.23.9.jar, the following error occurs

Exception in thread "main" java.lang.noclassdeffounderror:org/apache/hadoop/conf/Configuration at Org.apache.hive.jdbc.HiveConnection.createBinaryTransport (Hiveconnection.java:393) at Org.apache.hive.jdbc.HiveConnection.openTransport (Hiveconnection.java:187) at org.apache.hive.jdbc.HiveConnection.<init> (hiveconnection.java:163) at Org.apache.hive.jdbc.HiveDriver.connect (Hivedriver.java:105) at Java.sql.DriverManager.getConnection (Drivermanager.java:664) at Java.sql.DriverManager.getConnection (Drivermanager.java:247) at Hivejdbcclient.main (Hivejdbcclient.java:28) caused by:java.lang.ClassNotFoundException:org.apache.hadoop.conf.Configuration at Java.net.URLClassLoader.findClass (URLClassLoader.java:58w) at Java.lang.ClassLoader.loadClass (Classloader.java:424) at Sun.misc.launcher$appclassloader.loadclass (Launcher.java:331) at Java.lang.ClassLoader.loadClass (Classloader.java:357)    ... 7 more

2. HIVE JDBC Connection server is stuck:

If you use the Hiveserver version jdbcdriver connection Hiverserver2, this problem may occur, specifically after the Jdbcdriver connection, according to the requirements of the protocol request Hiveserver2 return data, Hiveserver2 does not return any data, so the JDBC driver will not return the card.

3. Teztask error, return error number 1.

 while return code 1 from org.apache.hadoop.hive.ql.exec.tez.TezTask    at Org.apache.hive.jdbc.HiveStatement.execute (Hivestatement.java:296)    at Org.apache.hive.jdbc.HiveStatement.executeQuery (Hivestatement.java:392) at    Hivejdbcclient.main ( Hivejdbcclient.java:40)

Error number 1 on behalf of the user authentication failed, the connection must specify the user name password, it is possible through the server settings can be performed without user authentication, HDP default installation configuration user name password is hive,hive

3. Teztask error, return error number 2.

Taskattempt 3 failed, info=[error:failure whileRunning TASK:JAVA.LANG.ILLEGALARGUMENTEXCEPTION:TEZ.RUNTIME.IO.SORT.MB should be larger than 0 and should is less tha n the available task memory (MB): 133At com.google.common.base.Preconditions.checkArgument (Preconditions.java:88) at Org.apache.tez.runtime.library.common.sort.impl.ExternalSorter.getInitialMemoryRequirement ( Externalsorter.java:291) at Org.apache.tez.runtime.library.output.OrderedPartitionedKVOutput.initialize ( Orderedpartitionedkvoutput.java:95) at Org.apache.tez.runtime.logicalioprocessorruntimetask$initializeoutputcallable.call ( Logicalioprocessorruntimetask.java:430) at Org.apache.tez.runtime.logicalioprocessorruntimetask$initializeoutputcallable.call ( Logicalioprocessorruntimetask.java:409) at Java.util.concurrent.FutureTask.run (Futuretask.java:266) at Java.util.concurrent.executors$runnableadapter.call (Executors.java:511) at Java.util.concurrent.FutureTask.run (Futuretask.java:266) at Java.util.concurrent.ThreadPoolExecutor.runWorker (Threadpoolexecutor.java:1142) at Java.util.concurrent.threadpoolexecutor$worker.run (Threadpoolexecutor.java:617) at Java.lang.Thread.run (Thread.java:745] ], Vertex failed as one or more tasks failed. Failedtasks:1, Vertex vertex_1441168955561_1508_2_00 [Map 1] killed/failed due to:NULL]vertex killed, Vertexname=reducer 2, Vertexid=vertex_1441168955561_1508_2_01, Diagnostics=[vertex received Kill whileIn RUNNING state, Vertex killed as and other Vertex failed. failedtasks:0, Vertex vertex_1441168955561_1508_2_01 [Reducer 2] killed/failed due to:NULL]dag failed due to vertex failure. Failedvertices:1 killedvertices:1failed:execution Error,returnCode 2 from Org.apache.hadoop.hive.ql.exec.tez.TezTask

Code 2, which means that the error is a parameter error, generally refers to the corresponding value is not appropriate, the above stack indicates that the TEZ.RUNTIME.IO.SORT.MB parameter 256 is larger than the available memory, so modify the configuration file or execute the query before you set its size.

With the above settings and parameter corrections, the application can use JDBC to connect to the hive database correctly.

Another can be used Squirrel-sql GUI client Management Hivedb, the driver settings and code in the corresponding jar package, driver class, URL, etc. using the same way set, test successfully established alias can start to connect hive, can be more convenient to manage and manipulate the hive database.

Connect to Hive via JDBC