官方jdbc方式访问hive服务器

说明

Hive某种意义上来说是一个数据库，也叫做数据仓库，只不过数据最终存储在hdfs上。而且sql最终都被翻译成mapreduce而已，当然查询效率也因此比较低。比较适合数据分析场合，实时性要求不高。访问hive客户端方式很多种，今天说一下jdbc方式访问hive。为了更好表达官网使用原意义，在这里代码部分只做红色备注，但是不做翻译。这样会更加准确。

实战

JDBC

This document describes the JDBC client for the original Hive Server (sometimes called Thrift server or HiveServer1). For information about the HiveServer2 JDBC client, see JDBC in the HiveServer2 Clients document. HiveServer2 use is recommended; the original HiveServer has several concurrency issues and lacks several features available in HiveServer2.

Version information

The original Hive Server was removed from Hive releases starting in version 1.0.0. See HIVE-6977.

For embedded mode, uri is just "jdbc:hive://". For standalone server, uri is "jdbc:hive://host:port/dbname" where host and port are determined by where the Hive server is run. For example, "jdbc:hive://localhost:10000/default". Currently, the only dbname supported is "default".

JDBC Client Sample Code

import  java.sql.SQLException; import  java.sql.Connection; import  java.sql.ResultSet; import  java.sql.Statement; import  java.sql.DriverManager;   public  class  HiveJdbcClient {    private  static  String driverName =  "org.apache.hadoop.hive.jdbc.HiveDriver" ;      public  static  void  main(String[] args)  throws  SQLException {      try  {        Class.forName(driverName);      }  catch  (ClassNotFoundException e) {        // TODO Auto-generated catch block        e.printStackTrace();        System.exit( 1 );      }      Connection con = DriverManager.getConnection( "jdbc:hive://localhost:10000/default" ,  "" ,  "" );      Statement stmt = con.createStatement();      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 ));      }    } }

Running the JDBC Sample Code

# Then on the command-line $ javac HiveJdbcClient.java   # To run the program in standalone mode, we need the following jars in the classpath # from hive/build/dist/lib #     hive_exec.jar #     hive_jdbc.jar #     hive_metastore.jar #     hive_service.jar #     libfb303.jar #     log4j-1.2.15.jar # # from hadoop/build #     hadoop-*-core.jar # # To run the program in embedded mode, we need the following additional jars in the classpath # from hive/build/dist/lib #     antlr-runtime-3.0.1.jar #     derby.jar #     jdo2-api-2.1.jar #     jpox-core-1.2.2.jar #     jpox-rdbms-1.2.2.jar # # as well as hive/build/dist/conf   $ java - cp  $CLASSPATH HiveJdbcClient   # Alternatively, you can run the following bash script, which will seed the data file # and build your classpath before invoking the client.   #!/bin/bash HADOOP_HOME= /your/path/to/hadoop HIVE_HOME= /your/path/to/hive   echo  -e  '1\x01foo'  >  /tmp/a .txt echo  -e  '2\x01bar'  >>  /tmp/a .txt   HADOOP_CORE={{ ls  $HADOOP_HOME /hadoop- *-core.jar}} CLASSPATH=.:$HADOOP_CORE:$HIVE_HOME /conf   for  i  in  ${HIVE_HOME} /lib/ *.jar ;  do      CLASSPATH=$CLASSPATH:$i done   java - cp  $CLASSPATH HiveJdbcClient

以上指的是用shell脚本来编译和运行jdbc程序，其实也可以在eclipse中直接运行上面那段java代码，要把相关jar包导入项目即可。特别注意hadoop2.x版本已经没有hadoop-core*.xml了，而是分散到不同包中，所以需要将hadoop2.x分散jar包汇总导入项目中。

JDBC Client Setup for a Secure Cluster

To configure Hive on a secure cluster, add the directory containing hive-site.xml to the CLASSPATH of the JDBC client.