First we need to open the Hiveserver service: Hive--service Hiveserver
We then load the driver and then make the connection, then create a statement, then execute the query, and then the result set, as we do with the normal database. The code is as follows (be sure to write to the SQL statement, be careful, the following need to be noted where I have marked out:)
Package Playhive;import Java.sql.connection;import Java.sql.drivermanager;import java.sql.resultset;import Java.sql.sqlexception;import Java.sql.statement;public class Hivejdbcclient {private final static String drivername = " Org.apache.hadoop.hive.jdbc.HiveDriver ";p rivate final static String localfilepath="/home/hadoop/test/hive/test.txt ";p rivate final static string hdfsfilepath=" hdfs://192.168.0.1:9000/user/hadoop/";p rivate final static string Tablename= "Testhivedrivertable";p rivate final static String partitionname= "Testhivedriverpartition";p rivate final static string Bucketname= "Testhivedriverbucket";p rivate static string sql = ";p rivate static Connection Connection;p Riv Ate static Statement statement;private static ResultSet resultset;static {try {class.forname (drivername);} catch ( ClassNotFoundException e) {System.out.println (e); System.exit (1);} try {connection = drivermanager.getconnection ("Jdbc:hive://192.168.0.1:50000/default", "Hive", "Hadoop"); First to open the Hiveserver service: Hive--sErvice hiveserverstatement = Connection.createstatement ();} catch (Exception e) {e.printstacktrace ();}} public static void CreateTable () throws SQLException {sql = "drop table" + TableName; System.out.println ("delete table****"); statement.executequery (sql); sql = "CREATE TABLE" + TableName + "(Key Int,value St Ring) "+" row format delimited fields terminated by ' \ t ' "; SYSTEM.OUT.PRINTLN ("CREATE TABLE:" +tablename); statement.executequery (SQL); showtable ();d escribetable ();} public static void Showtable () throws SQLException {sql = "Show tables" + TableName; SYSTEM.OUT.PRINTLN ("Show Table:" +tablename); resultset=statement.executequery (SQL); while (Resultset.next ()) { System.out.println (resultset.getstring (1));}} public static void Describetable () throws sqlexception{sql= "describe" +tablename; SYSTEM.OUT.PRINTLN ("Describe table:" +tablename); resultset=statement.executequery (SQL); while (Resultset.next ()) { System.out.println (resultset.getstring (1) + "\ T" +resultset.getstring (2));}} public static void LoaddatatOtable (Boolean isLocal) throws Sqlexception{sql=islocal? " Load data local inpath ' "+localfilepath+" ' overwrite into table ' +tablename: "Load Data inpath '" +hdfsfilepath+ "' Overwri Te into table "+tablename; SYSTEM.OUT.PRINTLN ("Load data into table:" +tablename); statement.executequery (SQL);} public static void QueryTable () throws Sqlexception{sql= "select * from" +TABLENAME; System.out.println ("Execute Query:select *query"); resultset=statement.executequery (SQL); while (Resultset.next ()) { System.out.println (resultset.getstring (1) + ' \ t ' +resultset.getstring (2)}} public static void Regulartablequery () throws Sqlexception{//sql= "SELECT count (1) from" +tablename+ ";"; Sql= "Select Key,max (N) from (select Key,count (value) as-N from" +tablename+ "group by Key) Sbuq GROUP by Key"; SYSTEM.OUT.PRINTLN ("Execute Query:"), ResultSet =statement.executequery (SQL), while (Resultset.next ()) { System.out.println (resultset.getstring (1));}} public static void CreatePartition () throws sqlexception{sql= "drop table" +partitionnamE System.out.println ("delete Partition"); Statement.execute (SQL); sql= "CREATE TABLE" +partitionname+ "(key int) Partitioned by (value string) "+" row format delimited fields terminated by ' \ t ' "; SYSTEM.OUT.PRINTLN ("Create partition:" +partitionname); Statement.execute (SQL);} public static void Insertdatatopartition () throws sqlexception{//here must be select key from "+tablename;key cannot be written as value, Otherwise, the inserted value will be nullsql= "insert overwrite table" +partitionname+ "Partition (value= ' Qinqin ') select key from" + Tablename;statement.execute (SQL); System.out.println ("Insert data to" +partitionname+ "Success");} public static void Selectfrompartition () throws Sqlexception{sql= "select * from" +partitionname+ "where value= ' Qinqin '"; SYSTEM.OUT.PRINTLN ("Query in Partition:select *" +partitionname); resultset=statement.executequery (SQL); Resultset.next ()) {System.out.println (resultset.getstring (1));}} public static void Createbucket () throws sqlexception{sql= "drop table" +bucketname; System.out.println ("Delete bucket"); statement.executequery (SQL); sql=" CREATE TABLE "+bucketname+" (key Int,value String) clustered by (key) to 3 buckets " + "row format delimited fields terminated by ' \ t '"; SYSTEM.OUT.PRINTLN ("Create bucket:" +bucketname); Statement.execute (SQL);} public static void Insertdatatobucket () throws sqlexception{sql= "Insert overwrite table" +bucketname+ "select Key,value F Rom "+tablename; System.out.println ("Insert data into bucket:" +bucketname); statement.executequery (SQL);} public static void Selectfrombucket () throws Sqlexception{sql= "select * from" +bucketname+ "tablesample (buckets 1 out of 3 On key) "; System.out.println ("Select from Bucket:" +bucketname), resultset=statement.executequery (SQL); while (Resultset.next ( ) {System.out.println (resultset.getstring (1) + "\ T" +resultset.getstring (2));}} public static void CloseConnection () {try {connection.close ();} catch (SQLException e) {e.printstacktrace (); System.exit (1);}} public static void Tableoperation () throws sqlexception{hivejdbcclient.createtable (); HiveJdbcclient.loaddatatotable (TRUE); Hivejdbcclient.querytable (); Hivejdbcclient.regulartablequery ();} public static void Partitionoperation () throws Sqlexception{hivejdbcclient.createpartition (); Hivejdbcclient.insertdatatopartition (); Hivejdbcclient.selectfrompartition ();} public static void Bucketoperation () throws Sqlexception{hivejdbcclient.createbucket (); Hivejdbcclient.insertdatatobucket (); Hivejdbcclient.selectfrombucket ();} public static void Main (string[] args) {try {System.out.println ("table operation************************************* ");//hivejdbcclient.tableoperation (); SYSTEM.OUT.PRINTLN ("Partition operation***********************************************"); Hivejdbcclient.partitionoperation (); System.out.println ("Bucket operation***********************************************");// Hivejdbcclient.bucketoperation (); Hivejdbcclient.closeconnection ();} catch (SQLException e) {e.printstacktrace ();}}}
Hive creates tables, partitions, buckets through JDBC