Shulou(Shulou.com)06/03 Report--

Hive is the basic component for data warehouse applications in big data's technology cluster, and it is also the benchmark for other similar data warehouse applications. Basic data operations can be handled in hive-client through scripting. If you need to develop an application, you need to use the jdbc driver of hive to connect. Based on the example on hive wiki, this article explains in detail how to connect to a hive database using jdbc. Hive wiki original address:

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

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

First of all, hive must be started as a service. Our platform chooses hdp platform, and hdp2.2 platform starts in hive server2 mode by default. Hiveserver2 is a more advanced service model than hiveserver, which provides advanced functions such as concurrency control and security mechanisms that hiveserver cannot provide. The server starts in different modes, and the client code is encoded in a slightly different way, as shown in the code.

After the service is started, edit the code in the eclipse environment. The code is as follows:

Import java.sql.SQLException;import java.sql.Connection;import java.sql.ResultSet;import java.sql.Statement;import java.sql.DriverManager; public class HiveJdbcClient {/ * hiverserver version uses this driver * / private static String driverName = "org.apache.hadoop.hive.jdbc.HiveDriver"; / * hiverserver2 version uses this driver * / private static String driverName = "org.apache.hive.jdbc.HiveDriver" Public static void main (String [] args) throws SQLException {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 = 128"); boolean resHivePropertyTest = 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 tables String sql = "show tables'" + tableName + "'"; System.out.println ("Running:" + sql); res = stmt.executeQuery (sql) If (res.next ()) {System.out.println (res.getString (1));} / describe table sql = "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 has to be local to the hive server / / NOTE: / tmp/a.txt is a ctrl-A separated file with two fields per line String filepath = "/ tmp/a.txt"; sql = "load data local inpath'" + filepath + "'into table" + tableName; System.out.println ("Running:" + sql); res = stmt.executeQuery (sql) / select * query sql = "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 query sql = "select count (1) from" + tableName System.out.println ("Running:" + sql); res = stmt.executeQuery (sql); while (res.next ()) {System.out.println (res.getString (1));}

You can put the following jar package in eclipse buildpath or in the classpath path at startup.

Among them, jdbcdriver can use hive-jdbc.jar, so other jar must also be included, or use jdbc-standalone jar package, with this jar package other jar packages can not be included. The hadoop-common package must contain.

Wait for the results to run correctly after execution. If an exception occurs, it will be resolved according to the prompt. The solutions to several ambiguous exceptions are as follows:

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. (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:381) 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 jammed:

If you use the hiveserver version of JDBCdriver to connect to hiverserver2, this problem may occur. When hiveserver2 is requested to return data according to the protocol requirements after the JDBCDriver connection, hiveserver2 does not return any data, so JDBCdriver will not return.

3. Error TezTask, return error number 1.

Exception in thread "main" java.sql.SQLException: Error while processing statement: FAILED: Execution Error, 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 indicates that user authentication failed, and the user name and password must be specified when connecting. It may be set by the server and can be executed without user authentication. The user name and password of hdp default installation configuration is hive,hive.

3. Error TezTask, return error number 2.

TaskAttempt 3 failed Info= [Error: Failure while running task:java.lang.IllegalArgumentException: tez.runtime.io.sort.mb 256 should be larger than 0 and should be less than the available task memory (MB): 133 at 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 while in RUNNING state., Vertex killed as 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, return code 2 from org.apache.hadoop.hive.ql.exec.tez.TezTask

Code 2, which means an error is a parameter error, generally means that 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 you can set its size before modifying the configuration file or executing the query.

After the above settings and parameter correction, the application can use jdbc to connect to the hive database correctly.

In addition, you can use the squirrel-sql GUI client to manage hivedb, and the driver setting mode is the same as the corresponding jar package, driver class, url and so on in the code. If the test successfully establishes the alias, you can start to connect to the hive, which is more convenient to manage and operate the hive database.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.