Hive and JDBC Samples

IMPORTANT: When you develop a hive program using JDBC, you must first turn on the remote service interface for hive. Use the following command to open: Hive-service Hiveserver & 1). test Data
userinfo.txt File contents (tab key between each line of data):1 xiapi2 xiaoxue3 qingqing 2). Program Code

1packagecom.ljq.hive;Importjava.sql.Connection;2 ImportJava.sql.DriverManager;ImportJava.sql.ResultSet;3 ImportJava.sql.SQLException;Importjava.sql.Statement;4 ImportOrg.apache.log4j.Logger;5  Public classhivejdbcclient {6 Private StaticString drivername = "Org.apache.hadoop.hive.jdbc.HiveDriver"; 7 PrivateStaticstring url = "Jdbc:hive://192.168.11.157:10000/default"; 8 Privatestaticstring user = "Hive"; 9 Private StaticString password = "MySQL"; Ten PrivateStaticstring sql = "";  One Private StaticResultSet Res;  A Private StaticFinallogger log = Logger.getlogger (hivejdbcclient.class);  -  Public StaticVoidmain (string[] args) { - Try { the Class.forName (drivername); -Connection conn =drivermanager.getconnection (url,user, password); -Statement stmt =conn.createstatement (); -  //name of the table created +String tableName = "Testhivedrivertable"; -  /**First step: Delete existing first **/ +sql = "DROP table" +TableName; A stmt.executequery (SQL);  at  /**Step Two: Create a non-existent **/ -sql = "CreateTable" + TableName + "(key int, value string) row format delimited fields terminated by ' \ t '"; - stmt.executequery (SQL);  - //perform a "Show tables" Operation -sql = "Show tables '" + tableName + "'"; -System.out.println ("Running:" +sql); inres =stmt.executequery (SQL); -System.out.println ("Execute" showtables "Run Result:");  to              if(Res.next ()) { +System.out.println (res.getstring (1)); -              }                         the //perform the describe table operation *sql = "Describe" +TableName; $System.out.println ("Running:" +sql);Panax Notoginsengres =stmt.executequery (SQL); -System.out.println ("Execute" describetable "Run Result:");  the              while(Res.next ()) { +System.out.println (res.getstring (1) + "\ T" +res.getstring (2)); A             }                         the //perform the "Load data into table" Operation +String filepath = "/home/hadoop/ziliao/userinfo.txt"; -sql = "Load datalocal inpath '" + filepath + "' into table" +TableName; $System.out.println ("Running:" +sql); $res =stmt.executequery (SQL);  -             //perform a "select * query" Operation -sql = "Select *from" +TableName; theSystem.out.println ("Running:" +sql); -res =stmt.executequery (SQL);WuyiSystem.out.println ("Execute" select* query "Run Result:");  the             while(Res.next ()) { -System.out.println (Res.getint (1) + "T" + res.getstring (2)); Wu             } -  //perform the regular Hive query action Aboutsql = "SelectCount (1) from" +TableName; $System.out.println ("Running:" +sql); -res =stmt.executequery (SQL); -System.out.println ("Execute" regularhive query "Run Result:");  -             while(Res.next ()) { ASystem.out.println (res.getstring (1)); +            } the conn.close (); -conn =NULL; $}Catch(ClassNotFoundException e) { the e.printstacktrace (); theLog.error (drivername + "not found!", e); theSystem.exit (1); the}Catch(SQLException e) { - e.printstacktrace (); inLog.error ("Connection error!", e); theSystem.exit (1); the             } About        } the}

View Code 3). Run Results ( Right-click -->run As-->run on Hadoop)running:show Tables ' testhivedrivertable ' performs the "show tables" Run Result:testhivedrivertableRunning:describe testhivedrivertableexecute the "describe table" Run Result:key Intvalue Stringrunning:load data local inpath '/home/hadoop/ziliao/userinfo.txt ' into table testhivedrivertableRunning:select * fromtesthivedrivertableExecute "SELECT * query" Run Result: 1 xiapi2 xiaoxue3 qingqingrunning:select count (1) fromtesthivedrivertableExecute "Regular hive query" Run Result: 3

Hive and JDBC Samples