Getting started with JDBC programming in hive

Hive with the JDBC Example

When developing a hive program using JDBC, you must first turn on the remote service interface for hive. Under the Hive installation directory, use the following command to open the bin:

Hive-service Hiveserver &//hive Low version offers the following services: Hiveserver

The services provided by Hive--service Hiveserver2 &//hive0.11.0 are: Hiveserver2

I use the Hive1.0 version here, so we use the Hiveserver2 service, below I use the Java code through the JDBC connection hiveserver.

18.1 test Data

The contents of the Djt.txt file under local directory/home/hadoop/(tab-separated between each line of data) are as follows:

1 Dajiangtai

2 Hadoop

3 Hive

4 HBase

5 Spark

18.2 Program Code

Import java.sql.Connection;

Import Java.sql.DriverManager;

Import Java.sql.ResultSet;

Import java.sql.SQLException;

Import java.sql.Statement;

public class HiveJdbcTest1 {

private static String drivername = "Org.apache.Hive.jdbc.HiveDriver";//hive driver name

private static String URL = "Jdbc:hive2://djt11:10000/default";//connection address of the Hive2 service, Hive0.11.0 above offers a whole new service: HiveServer2

private static String user = "Hadoop";//Users with Operation permissions on HDFs

private static String password = "";//in non-secure mode, specify a user to run the query, ignoring the password

private static String sql = "";

private static ResultSet Res;

public static void Main (string[] args) {

try {

Class.forName (drivername);//Load HiveServer2 driver

Connection conn = drivermanager.getconnection (URL, user, password);//database specified based on URL connection

Statement stmt = Conn.createstatement ();

Name of the table created

String tableName = "testhivedrivertable";

/** First step: Delete **/if the table exists

sql = "DROP table" + TableName;

Stmt.execute (SQL);

/** Second step: Create a **/if the table does not exist

sql = "CREATE TABLE" + TableName + "(key int, value string) row format delimited fields terminated by ' \ t ' STORED as TE Xtfile ";

Stmt.execute (SQL);

Perform a "Show tables" operation

sql = "Show tables '" + tableName + "'";

res = stmt.executequery (SQL);

if (Res.next ()) {

System.out.println (res.getstring (1));

}

Perform the describe table operation

sql = "describe" + tableName;

res = stmt.executequery (SQL);

System.out.println (res.getstring (1) + "\ T" + res.getstring (2));

}

Perform the "Load data into table" operation

String filepath = "/home/hadoop/djt.txt"; the local file path of the node where the//hive service resides

sql = "Load data local inpath '" + filepath + "' into table" + tableName;

Stmt.execute (SQL);

Perform a "select * query" operation

sql = "SELECT * from" + tableName;

res = stmt.executequery (SQL);

while (Res.next ()) {

System.out.println (Res.getint (1) + "\ T" + res.getstring (2));

}

Executes the regular Hive query action, which is converted to a MapReduce program to handle

sql = "SELECT COUNT (*) from" + TableName;

res = stmt.executequery (SQL);

while (Res.next ()) {

System.out.println (res.getstring (1));

Conn.close ();

conn = null;

} catch (ClassNotFoundException e) {

E.printstacktrace ();

System.exit (1);

} catch (SQLException e) {

E.printstacktrace ();

System.exit (1);

}

}

}

18.3 Run results (right- click-->run as-->run on Hadoop)

Execute the "show tables" Run Result:

Testhivedrivertable

Execute the "describe table" Run Result:

Key int

Value string

Execute the "SELECT * query" Run Result:

1 Dajiangtai

2 Hadoop

3 Hive

4 HBase

5 Spark

Execute the regular Hive query run result:

5

Hive JDBC uses

Hive Project Development Environment Setup (Eclipse\myeclipse + Maven)

Getting started with JDBC programming in hive