JDBC Interface in Hive

1, Hive open JDBC Interface, development need to import jar as follows

Commons-logging-1.0.4.jar
Hadoop-common-2.6.0.jar
Hive_exec.jar
Hive_jdbc.jar
Hive_metastore.jar
Hive_service.jar
Httpclient-4.2.5.jar
Httpcore-4.2.5.jar
Libfb303.jar
Log4j-1.2.16.jar
Slf4j-api-1.7.5.jar
Slf4j-log4j12-1.7.5.jar

2, the development of the example program is as follows

Package COM.GW; Import Java.io.InputStream; Import Java.net.URL; Import java.sql.Connection; Import Java.sql.DriverManager; Import Java.sql.ResultSet; Import java.sql.SQLException; Import java.sql.Statement; Import java.util.Properties; Import Org.apache.log4j.Logger; /** * @author Wangweifeng * @description: Connect to Hive via JDBC: Create a table, delete a table, describe a table, import data from a local file system, execute a query * */ public class Hivejdbcclient { public static Statement jdbcstatemt; private static ResultSet res = NULL; Private static final Logger log = Logger.getlogger (Hivejdbcclient.class); Create a jdbcstatemt with singleton mode public static Statement Getjdbcstatemt () throws Exception { if (jdbcstatemt = = null) { Properties prop = new properties (); URL url = thread.currentthread (). Getcontextclassloader () . GetResource ("config.properties"); InputStream in = Url.openstream (); Prop.load (in); String drivername = Prop.getproperty ("drivername"); String Jdbcurl = Prop.getproperty ("Jdbcurl"); String userName = Prop.getproperty ("UserName"); String Password = prop.getproperty ("password"); 1. Loading drivers using class classes Class.forName (drivername); 2. Connect to the database Connection con = drivermanager.getconnection (Jdbcurl, UserName, password); 3. Statement interface needs to be instantiated via connection interface JDBCSTATEMT = Con.createstatement (); } return jdbcstatemt; } Turn off the JDBC connection public static void Closestatement () throws Exception { if (!jdbcstatemt.isclosed ()) { Jdbcstatemt.close (); } } public static void Main (string[] args) { try { JDBCSTATEMT = Getjdbcstatemt (); } catch (Exception e) { E.printstacktrace (); } String Hql = ""; /* String tableName = "Demiclass"; */ /* String tableName = "Demidate"; */ /* String tableName = "Demidept"; */ String tableName = "Txsxsmxfacta"; String rowterminated = "ROW FORMAT delimited fields TERMINATED by ', '"; String lineterminated = "LINES TERMINATED by ' \ n '"; 1. Delete a table HQL = "DROP TABLE IF EXISTS" + tableName; try { Jdbcstatemt.execute (HQL); System.out.println ("Table deleted successfully! "); } catch (SQLException E1) { SYSTEM.OUT.PRINTLN ("Table Delete failed! "); E1.printstacktrace (); } 2. Create a table /* * HQL = "CREATE EXTERNAL TABLE" + TableName + * "(Clscode string,cty_big string,cty_sml STRING)" + rowterminated + * lineterminated; */ /* * HQL = "CREATE EXTERNAL TABLE" + TableName + * "(xsdate string,year string,month string,day STRING)" + * rowterminated + lineterminated; */ /* * HQL = "CREATE EXTERNAL TABLE" + TableName + * "(Depcode string,branch_nam string,group_name string,depname STRING)" * + rowterminated + lineterminated; */ HQL = "CREATE EXTERNAL TABLE" + TableName + "(Clscode string,depcode string,xsdate string,jxcount int,dxcount int,lxcount int,jxtotal DOUBLE,DXTOTAL DOUBLE, Lxtotal DOUBLE) " + rowterminated + lineterminated; try { Jdbcstatemt.execute (HQL); SYSTEM.OUT.PRINTLN ("Table created successfully! "); } catch (SQLException e) { SYSTEM.OUT.PRINTLN ("Table creation failed! "); E.printstacktrace (); } 3. Perform "Show tables" operation HQL = "SHOW TABLES '" + tableName + "'"; System.out.println ("Running:" + Hql); try { res = Jdbcstatemt.executequery (HQL); if (Res.next ()) { System.out.println (res.getstring (1)); } } catch (SQLException e) { E.printstacktrace (); } 4. Perform "Describe table" operation HQL = "DESCRIBE" + tableName; System.out.println ("Running:" + Hql); try { res = Jdbcstatemt.executequery (HQL); System.out.println ("execute" + HQL + "Run Result:"); while (Res.next ()) { System.out.println (res.getstring (1) + "\ T" + res.getstring (2)); } } catch (SQLException e) { E.printstacktrace (); } 5. Execute "Load data into table" operation /* * String FilePath = * "/home/hadoop/desktop/createtable_and_data/demiclass.csv"; HQL = * "LOAD DATA LOCAL inpath '" + FilePath + * "' OVERWRITE into TABLE demiclass"; */ /* * String FilePath = * "/home/hadoop/desktop/createtable_and_data/demidate.csv"; HQL = * "LOAD DATA LOCAL inpath" + filepath+ * "' OVERWRITE into TABLE demidate"; */ /* * String FilePath = * "/home/hadoop/desktop/createtable_and_data/demidept.csv"; HQL = * "LOAD DATA LOCAL inpath" + filepath+ * "' OVERWRITE into TABLE demidept"; */ String FilePath = "/home/hadoop/desktop/createtable_and_data/txsxsmxfacta.csv"; HQL = "LOAD DATA LOCAL inpath" + FilePath + "' OVERWRITE into TABLE txsxsmxfacta"; System.out.println ("Running:" + Hql); try { Jdbcstatemt.executequery (HQL); SYSTEM.OUT.PRINTLN ("Data import successful! "); } catch (Exception SQLException) { System.out.println ("bug!"); } 6. Perform the "SELECT * Query" operation Perform a "select * query" operation HQL = "SELECT * from" + tableName; System.out.println ("Running:" + Hql); try { res = Jdbcstatemt.executequery (HQL); System.out.println ("Execute" SELECT * query "Run result:"); while (Res.next ()) { /* * SYSTEM.OUT.PRINTLN (Res.getint (1) + "T" + res.getstring (2) + * "\ T" + res.getstring (3)); */ /* * SYSTEM.OUT.PRINTLN (res.getstring (1) + "T" + res.getstring (2) * + "\ T" + res.getstring (3) + "\ T" + res.getstring (4)); */ System.out.println (res.getstring (1) + "T" + res.getstring (2) + "\ T" + res.getstring (3) + "\ T" + res.getint (4) + "\ T" + Res.getint (5) + "\ T" + res.getint (6) + "\ T" + res.getdouble (7) + "\ T" + res.getdouble (8) + "\ T" + res.getdouble (9) + "\ T"); } } catch (SQLException e) { E.printstacktrace (); } 7, perform a connection operation, find a day, a department, HQL = "SELECT DD." Xsdate, DDP. Branch_nam, DC. Cty_big, FAC. Dxcount, FAC. Dxtotal from Txsxsmxfacta FAC joins Demiclass DC on (FAC. Clscode = DC. Clscode) " + "Join Demidate DD on (FAC. Xsdate = dd. xsdate) Join Demidept DDP on (FAC. Depcode = DDP. Depcode) WHERE DD. Xsdate between ' 2004-04-27 ' and ' 2004-05-20 '; try { res = Jdbcstatemt.executequery (HQL); while (Res.next ()) { System.out.println (res.getstring (1) + "T" + res.getstring (2) + "\ T" + res.getstring (3) + "\ T" + res.getint (4) + "\ T" + res.getdouble (5)); } } catch (Exception e) { E.printstacktrace (); } try { Closestatement (); } catch (Exception e) { E.printstacktrace (); } } }

JDBC Interface in Hive