Call Javaapi to access hive

JDBC Remote connection Hiveserver22016-04-26 15:59 This site (425) In previous learning and practice hive, the CLI or hive–e approach was used, allowing only HIVEQL to perform queries, updates, and so on, and this was a clumsy and singular approach. Fortunately, Hive provides a light client implementation that, through Hiveserver or HiveServer2, allows the client to manipulate data in hive without starting the CLI, both allowing remote clients to use multiple programming languages such as Java, Python submits the request to hive to retrieve the results. Hiveserver or HiveServer2 are based on thrift, but hivesever are sometimes called thrift
Server, but HiveServer2 does not. Why do you need HiveServer2 if you already exist hiveserver? This is because Hiveserver cannot handle concurrent requests from more than one client, which is due to limitations caused by the thrift interface used by Hiveserver and cannot be modified by modifying Hiveserver code. Therefore, rewriting the Hiveserver code in the Hive-0.11.0 version has been HiveServer2, which solves the problem. HIVESERVER2 supports multi-client concurrency and authentication, providing better support for open API clients such as JDBC and ODBC.

so this article will take HiveServer2 as an example, introduce and write the remote operation of Hive Java API.

The key configuration information for Hive is listed first and used in this article:

<property> <name>hive.metastore.warehouse.dir</name> <value>/usr/hive/warehouse</ Value>//(the location of the database in hive and the folder where the table resides in HDFs) <description>location of the default database for the warehouse& Lt;/description></property><property> <name>hive.server2.thrift.port</name> <value >10000</value>//(HiveServer2 remote connected port, default is 10000) <description>port number of H  IveServer2 Thrift interface. Can be overridden by setting $HIVE _server2_thrift_port</description></property><property> <name >hive.server2.thrift.bind.host</name> <value>**.**.**.**</value>//(Hive set  Group IP address) <description>bind host on which to run the HiveServer2 Thrift interface.  Can be overridden by setting $HIVE _server2_thrift_bind_host</description></property><property>  <name>hive.server2.long.polling.timeout</name><value>5000</value>//(the default is 5000L, here is modified to 5000, otherwise the program will error) <description>time in Milliseconds that HiveServer2 would wait, before responding to asynchronous calls the use long Polling</description&gt ;</property><property> <name>javax.jdo.option.ConnectionURL</name> &LT;VALUE&GT;JDBC: Mysql://localhost:3306/hive?createdatabaseifnotexist=true</value>//(hive metabase, I use local mysql as meta database) <                           DESCRIPTION&GT;JDBC connect string for a JDBC metastore</description></property><property> <name>javax.jdo.option.ConnectionDriverName</name>//(driver name of the connection metadata) <value>com.mysql . jdbc. Driver</value> <description>driver class name for a JDBC metastore</description></property> <property> <name>javax.jdo.option.ConnectionUserName</name>//(connecting metabase user name) <value>hiv E</value> <description>username To Use against Metastore database</description></property><property> <name> Javax.jdo.option.connectionpassword</name>//(Connect meta-database password) <value>hive</value> <descripti On>password to use against Metastore database</description></property>

After making sure that the above configuration is correct, start the HiveServer2 service below:

Start the metabase first, and at the command line type: Hive--service Metastore & (the & symbol indicates that the process will run in the background, because command lines are stuck after you execute this command, and if you do not add this symbol, CTRL + c return to the command line input interface will automatically shotdown the service)

Such as:



After the command line is stuck, look at the log file Hive.log, as shown below:

2016-04-26 04:44:53,956 INFO [main]: Metastore. Hivemetastore (HiveMetaStore.java:main (5060))-Starting Hive Metastore on port 90832016-04-26 04:44:54,174 WARN [main]: Conf.  Hiveconf (HiveConf.java:initialize (1390))-deprecated:hive.metastore.ds.retry.* no longer have any effect. Use hive.hmshandler.retry.* instead2016-04-26 04:44:54,326 INFO [main]: Metastore. Hivemetastore (HiveMetaStore.java:newRawStore (494))-0:opening Raw store with implemenation Class:o RG.APACHE.HADOOP.HIVE.METASTORE.OBJECTSTORE2016-04-26 04:44:54,412 INFO [main]: Metastore. ObjectStore (ObjectStore.java:initialize (245))-ObjectStore, initialize CALLED2016-04-26 04:44:57,240 WARN [main]: Conf.  Hiveconf (HiveConf.java:initialize (1390))-deprecated:hive.metastore.ds.retry.* no longer have any effect. Use hive.hmshandler.retry.* instead2016-04-26 04:44:57,246 INFO [main]: Metastore. ObjectStore (ObjectStore.java:getPMF (314))-Setting Metastore object pin classes with Hive.metastore.cache.pinobjtypes = "Table,Storagedescriptor,serdeinfo,partition,database,type,fieldschema,order "2016-04-26 04:45:03,597 INFO [main]: Metastore. ObjectStore (ObjectStore.java:setConf (228))-Initialized objectstore2016-04-26 04:45:03,806 WARN [main]: Metastore. ObjectStore (ObjectStore.java:checkSchema (6273))-Version information not found in Metastore. Hive.metastore.schema.verification is isn't enabled so recording the schema version 0.13.02016-04-26 04:45:04,811 INFO [mai N]: Metastore. Hivemetastore (HiveMetaStore.java:createDefaultRoles (552))-Added Admin role in metastore2016-04-26 04:45:04,828 INFO [ Main]: Metastore.  Hivemetastore (HiveMetaStore.java:createDefaultRoles (561))-Added public role in Metastore2016-04-26 04:45:04,984 INFO [main]: Metastore. Hivemetastore (HiveMetaStore.java:addAdminUsers (589))-No user is added in Admin role, since config is empty2016-04-26 04 : 45:05,361 INFO [main]: Metastore. Hivemetastore (HiveMetaStore.java:startMetaStore (5182))-Starting DB backed Metastore server2016-04-26 04:45:05,369 INFO [main]: Metastore. Hivemetastore (HiveMetaStore.java:startMetaStore (5194))-Started The new metaserver on port [9083] ... 2016-04-26 04:45:05,369 INFO [main]: Metastore. Hivemetastore (HiveMetaStore.java:startMetaStore (5196))-options.minworkerthreads = 2002016-04-26 04:45:05,370 INFO [ Main]: Metastore. Hivemetastore (HiveMetaStore.java:startMetaStore (5198))-options.maxworkerthreads = 1000002016-04-26 04:45:05,370 INFO [main]: Metastore. Hivemetastore (HiveMetaStore.java:startMetaStore (5200))-TCP KeepAlive = True

This proves that the Metastore has been turned on.

Next turn on the Hiveserver2 service:

On the command line, type: Hive--service Hiveserver2 &

Ditto, there will also be a command line stuck phenomenon. View the log file as follows:

2016-04-26 04:53:24,212 INFO [main]: server. HiveServer2 (HiveStringUtils.java:startupShutdownMessage (605))-startup_msg:/*********************************** Startup_msg:starting hiveserver2startup_msg:host = master/(IP you configured previously) Startup_msg:args = []s Tartup_msg:version = 0.13.0startup_msg:classpath =/opt/modules/hadoop-2.2.0/etc/hadoop:/opt/modules/hadoop-2.2.0/ share/hadoop/common/lib//(...) Classpath content in the middle, log information is too long ... ) Startup_msg:build = File:///Users/hbutani/svn/branch-0.13-r Unknown; Compiled by ' Hbutani ' on Tue Apr 13:55:42 PDT 2014************************************************************/ 2016-04-26 04:53:24,553 WARN [main]: Conf.  Hiveconf (HiveConf.java:initialize (1390))-deprecated:hive.metastore.ds.retry.* no longer have any effect. Use hive.hmshandler.retry.* instead2016-04-26 04:53:25,258 INFO [main]: Metastore. Hivemetastore (HiveMetaStore.java:newRawStore (494))-0:opening Raw store with implemenation Class:o Rg.apache.hadoop.hive.mEtastore. OBJECTSTORE2016-04-26 04:53:25,325 INFO [main]: Metastore. ObjectStore (ObjectStore.java:initialize (245))-ObjectStore, initialize CALLED2016-04-26 04:53:28,312 WARN [main]: Conf.  Hiveconf (HiveConf.java:initialize (1390))-deprecated:hive.metastore.ds.retry.* no longer have any effect. Use hive.hmshandler.retry.* instead2016-04-26 04:53:28,313 INFO [main]: Metastore. ObjectStore (ObjectStore.java:getPMF (314))-Setting Metastore object pin classes with Hive.metastore.cache.pinobjtypes = "Table,storagedescriptor,serdeinfo,partition,database,type,fieldschema,order" 2016-04-26 04:53:31,537 INFO [main] : Metastore. ObjectStore (ObjectStore.java:setConf (228))-Initialized objectstore2016-04-26 04:53:32,064 INFO [main]: Metastore. Hivemetastore (HiveMetaStore.java:createDefaultRoles (552))-Added Admin role in metastore2016-04-26 04:53:32,079 INFO [ Main]: Metastore. Hivemetastore (HiveMetaStore.java:createDefaultRoles (561))-Added public role in Metastore2016-04-26 04:53:32,205 INFO [main]: Metastore. Hivemetastore (HiveMetaStore.java:addAdminUsers (589))-No user is added in Admin role, since config is empty2016-04-26 04 : 53:33,887 INFO [main]: session. sessionstate (SessionState.java:start (358))-No Tez session required at this point. HIVE.EXECUTION.ENGINE=MR.2016-04-26 04:53:34,168 WARN [main]: Conf.  Hiveconf (HiveConf.java:initialize (1390))-deprecated:hive.metastore.ds.retry.* no longer have any effect. Use hive.hmshandler.retry.* instead2016-04-26 04:53:34,241 INFO [main]: service. Compositeservice (SessionManager.java:init)-Hiveserver2:async execution thread pool size:1002016-04-26 04:53:34,241 INFO [main]: service. Compositeservice (SessionManager.java:init)-Hiveserver2:async execution Wait queue size:1002016-04-26 04:53:34,242 INFO [main]: service. Compositeservice (SessionManager.java:init)-Hiveserver2:async execution thread keepalive time:102016-04-26 04:53 : 34,244 INFO [main]: service. Abstractservice (AbstractService.java:inIt (Service:operationmanager)-inited.2016-04-26 04:53:34,247 INFO [main]: Service. Abstractservice (AbstractService.java:init)-Service:sessionmanager is inited.2016-04-26 04:53:34,247 INFO [main] : Service. Abstractservice (AbstractService.java:init)-Service:cliservice is inited.2016-04-26 04:53:34,247 INFO [main]: Service. Abstractservice (AbstractService.java:init)-Service:thriftbinarycliservice is inited.2016-04-26 04:53:34,247 INFO [main]: service. Abstractservice (AbstractService.java:init)-Service:hiveserver2 is inited.2016-04-26 04:53:34,248 INFO [main]: Service. Abstractservice (AbstractService.java:start (104))-Service:operationmanager is started.2016-04-26 04:53:34,248 INFO [ Main]: service. Abstractservice (AbstractService.java:start (104))-Service:sessionmanager is started.2016-04-26 04:53:34,248 INFO [ Main]: service. Abstractservice (AbstractService.java:start (104))-Service:cliservice is started.2016-04-26 04:53:34,698 INFO [main]: MetastOre. Hivemetastore (HiveMetaStore.java:addAdminUsers (589))-No user is added in Admin role, since config is empty2016-04-26 04 : 53:34,699 INFO [main]: Metastore. Hivemetastore (HiveMetaStore.java:logInfo (624))-0:get_databases:default2016-04-26 04:53:34,701 INFO [main]: Hivemetastore.audit (HiveMetaStore.java:logAuditEvent (306))-ugi=hhip=unknown-ip-addrcmd=get_databases: DEFAULT2016-04-26 04:53:34,725 INFO [main]: Metastore. Hivemetastore (HiveMetaStore.java:newRawStore (494))-0:opening Raw store with implemenation Class:o RG.APACHE.HADOOP.HIVE.METASTORE.OBJECTSTORE2016-04-26 04:53:34,728 INFO [main]: Metastore. ObjectStore (ObjectStore.java:initialize (245))-ObjectStore, initialize CALLED2016-04-26 04:53:34,745 INFO [main]: Metastore. ObjectStore (ObjectStore.java:setConf (228))-Initialized objectstore2016-04-26 04:53:34,795 INFO [main]: service. Abstractservice (AbstractService.java:start (104))-Service:thriftbinarycliservice is started.2016-04-26 04:53:34,796 INFO [main]:Service. Abstractservice (AbstractService.java:start (104))-Service:hiveserver2 is started.2016-04-26 04:53:34,947 WARN [ THREAD-5]: Conf.  Hiveconf (HiveConf.java:initialize (1390))-deprecated:hive.metastore.ds.retry.* no longer have any effect. Use hive.hmshandler.retry.* instead2016-04-26 04:53:35,584 INFO [Thread-5]: Thrift. Thriftcliservice (ThriftBinaryCLIService.java:run)-Thriftbinarycliservice listening on/(your IP): 10000

You can also see if the Hiveserver2 is turned on by the following command:

[Email protected] desktop]$ netstat-nl |grep 10000tcp        0      0 (your IP): 10000        0.0.0.0:*                   LISTEN

This proves that the Hiveserver2 service has been opened!
(Note: Be sure to check the log information, because the command line does not error, if the start fails, the corresponding exception will be displayed in the log information, log file Hive.log path in $hive_home/conf/hive-log4j.properties configuration)

Here's how to start writing the Java API:

First, list the jar packages that the program relies on:

Hadoop-2.2.0/share/hadoop/common/hadoop-common-2.2.0.jar$hive_home/lib/hive-exec-0.11.0.jar $HIVE _home/lib/ Hive-jdbc-0.11.0.jar $HIVE _home/lib/hive-metastore-0.11.0.jar $HIVE _home/lib/hive-service-0.11.0.jar $HIVE _home/ Lib/libfb303-0.9.0.jar $HIVE _home/lib/commons-logging-1.0.4.jar $HIVE _home/lib/slf4j-api-1.6.1.jar

The Java code is posted below:

Jdbctohiveutils.java

Import Java.sql.connection;import Java.sql.drivermanager;import Java.sql.preparedstatement;import Java.sql.sqlexception;public class Jdbctohiveutils {private static String drivername = " Org.apache.hive.jdbc.HiveDriver ";p rivate static String url=" Jdbc:hive2://**.**.**.**:10000/default ";    Fill in the Hive IP, previously configured in the configuration file ipprivate static Connection conn;public static Connection getconnnection () {try               {              Class.forName (drivername);              conn = Drivermanager.getconnection (Url, "hh", "");        The user name here must be a user with permission to operate HDFs, or the program will prompt "permission Deny" Exception               }catch (classnotfoundexception e)  {               E.printstacktrace ();               System.exit (1);                } catch (SQLException e) {e.printstacktrace ();} Return conn;} public static PreparedStatement prepare (Connection conn, String sql) {PreparedStatement PS = null;try {PS = conn.preparest Atement (SQL);} catch (SQLException e) {e.printstacktrace ();} return PS;}}

Queryhiveutils.java

Import Java.sql.connection;import Java.sql.preparedstatement;import Java.sql.resultset;import Java.sql.sqlexception;import Java.sql.statement;public class Queryhiveutils {private static Connection conn= Jdbctohiveutils.getconnnection ();p rivate static preparedstatement ps;private static ResultSet rs;public static void GetAll (String tablename) {string sql= "select * from" +TABLENAME; SYSTEM.OUT.PRINTLN (SQL); try {ps=jdbctohiveutils.prepare (conn, SQL); rs=ps.executequery (); int columns= Rs.getmetadata (). getColumnCount (); while (Rs.next ())    {for    (int i=1;i<=columns;i++)        { System.out.print (rs.getstring (i));        System.out.print ("\t\t");    }    System.out.println ();}} catch (SQLException e) {//TODO auto-generated catch Blocke.printstacktrace ();}}}

Querhivetest.java

public class Queryhivetest {public static void main (string[] args) {String tablename= "test1";                Queryhiveutils.getall (tablename);}}

The results of the operation are as follows:

SELECT * from test11 Zhang Sanman 20.02 Li four female 35.03 Harry male null4 Zhao Liu null70.0

Call Javaapi to access hive