...
No Format |
---|
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.hive.jdbc.HiveDriver";
/**
* @param args
* @throws SQLException
*/
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);
}
//replace "hive" here with the name of the user the queries should run as
Connection con = DriverManager.getConnection("jdbc:hive2://localhost:10000/default", "hive", "");
Statement stmt = con.createStatement();
String tableName = "testHiveDriverTable";
stmt.execute("drop table if exists " + tableName);
stmt.execute("create table " + tableName + " (key int, value string)");
// show tables
String sql = "show tables '" + tableName + "'";
System.out.println("Running: " + sql);
ResultSet 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);
stmt.execute(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
No Format |
---|
# Then on the command-line $ javac HiveJdbcClient.java # To run the program using remote hiveserver in non-kerberos mode, we need the following jars in the classpath # from hive/build/dist/lib # hive-jdbc*.jar # hive-service*.jar # libfb303-0.9.0.jar# libthrift-0.9.0.jar# log4j-1.2.16.jar# slf4j-api-1.6.1.jar# slf4j-log4j12-1.6.1.jar# commons-logging-1.0.4.jar# # # Following additional jars are needed for the kerberos secure mode - # hive-exec*.jar # commons-configuration-1.6.jar # and from hadoop - hadoop-*core.jar # To run the program in embedded mode, we need the following additional jars in the classpath # from hive/build/dist/lib # hive-exec*.jar # hive-metastore*.jar # 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 # # from hadoop/build # hadoop-*-core.jar # as well as hive/build/dist/conf, any HIVE_AUX_JARS_PATH set, and hadoop jars necessary to run MR jobs (eg lzo codec) $ 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. The script adds all the # additional jars needed for using HiveServer2 in embedded mode as well. #!/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=.:$HIVE_HOME/conf:`hadoop classpath` for i in ${HIVE_HOME}/lib/*.jar ; do CLASSPATH=$CLASSPATH:$i done java -cp $CLASSPATH HiveJdbcClient |
JDBC Data Types
The following table lists the data types implemented for HiveServer2 JDBC.
Hive Type | Java Type | Specification |
---|---|---|
TINYINT | byte | signed or unsigned 1-byte integer |
SMALLINT | short | signed 2-byte integer |
INT | int | signed 4-byte integer |
BIGINT | long | signed 8-byte integer |
FLOAT | double | single-precision number (approximately 7 digits) |
DOUBLE | double | double-precision number (approximately 15 digits) |
DECIMAL | java.math.BigDecimal | fixed-precision decimal value |
BOOLEAN | boolean | a single bit (0 or 1) |
STRING | String | character string or variable-length character string |
TIMESTAMP | java.sql.Timestamp | date and time value |
BINARY | String | binary data |
Complex Types |
|
|
ARRAY | String – json encoded | values of one data type |
MAP | String – json encoded | key-value pairs |
STRUCT | String – json encoded | structured values |
JDBC Client Setup for a Secure Cluster
...