用Java代码通过JDBC连接Hiveserver2

1.在终端启动hiveserver2
#hiveserver2

2.使用beeline连接hive
另外打开一个终端，输入如下命令（xavierdb必须是已经存在的数据库）
#beeline -u jdbc:hive2://localhost:10000/xavierdb -n hive -p hive

3.添加maven依赖

<!-- https://mvnrepository.com/artifact/org.apache.hadoop.hive/hive-jdbc -->
        <dependency>
            <groupId>org.apache.hive</groupId>
            <artifactId>hive-jdbc</artifactId>
            <version>1.1.0</version>
        </dependency>

        <dependency>
            <groupId>junit</groupId>
            <artifactId>junit</artifactId>
            <version>4.9</version>
        </dependency>

        <dependency>
            <groupId>org.apache.hadoop</groupId>
            <artifactId>hadoop-common</artifactId>
            <version>2.6.0</version>
        </dependency>

        <!-- https://mvnrepository.com/artifact/org.apache.hadoop/hadoop-client -->
        <dependency>
            <groupId>org.apache.hadoop</groupId>
            <artifactId>hadoop-client</artifactId>
            <version>2.6.0</version>
        </dependency>

        <!-- https://mvnrepository.com/artifact/org.apache.hive/hive-metastore -->
        <dependency>
            <groupId>org.apache.hive</groupId>
            <artifactId>hive-metastore</artifactId>
            <version>1.1.0</version>
        </dependency>

        <!-- https://mvnrepository.com/artifact/org.apache.hive/hive-metastore -->
        <dependency>
            <groupId>org.apache.hive</groupId>
            <artifactId>hive-exec</artifactId>
            <version>1.1.0</version>
        </dependency>

maven依赖

出现过的错误： Error: Could not open client transport with JDBC Uri: jdbc:hive2://localhost:10000/default

解决办法：检查发现运行beeline时Driver版本Driver: Hive JDBC (version 1.1.0-cdh5.16.1)比maven依赖中的Driver版本低，将maven版本调至1.1.0问题解决

Java API测试：

注意：这里的url必须是beeline值中使用的url

package TestOption;


import org.junit.Test;
import org.junit.After;
import org.junit.Before;

import java.sql.*;

/**
 * @Author:Xavier
 * @Data:2019-02-18 11:43
 **/


public class HiveOption {

private static String driverName = "org.apache.hive.jdbc.HiveDriver";
private static String url = "jdbc:hive2://172.19.224.213:10000/xavierdb";

private static Connection con = null;
private static Statement state = null;
private static ResultSet res = null;

//加载驱动,创建连接
 @Before
 public void init() throws ClassNotFoundException, SQLException {
 Class.forName(driverName);
con = DriverManager.getConnection(url, "hive", "hive");
state = con.createStatement();
 }

//创建数据库
 @Test
 public void CreateDb() throws SQLException {

state.execute("create database xavierdb1");

 }

// 查询所有数据库
 @Test
 public void showtDb() throws SQLException {
res = state.executeQuery("show databases");
while (res.next()) {
 System.out.println(res.getString(1));
 }
 }

// 删除数据库
 @Test
 public void dropDb() throws SQLException {
state.execute("drop database if exists xavierdb1");
 }


/*
 *
 *
 * 内部表基本操作
 *
 *
 * */

 // 创建表
 @Test
 public void createTab() throws SQLException {

state.execute("create table if not exists student ( " +
"name string , " +
"age int , " +
"agent string ," +
"adress struct<street:STRING,city:STRING>) " +
"row format delimited " +
"fields terminated by ',' " +//字段与字段之间的分隔符
 "collection items terminated by ':'" +//一个字段各个item的分隔符
 "lines terminated by '\n' ");//行分隔符
 }

// 查询所有表
 @Test
 public void showTab() throws SQLException {
res = state.executeQuery("show tables");
while (res.next()) {
 System.out.println(res.getString(1));
 }
 }

// 查看表结构
 @Test
 public void descTab() throws SQLException {
res = state.executeQuery("desc emp");
while (res.next()) {
 System.out.println(res.getString(1) + "\t" + res.getString(2));
 }
 }

// 加载数据
 @Test
 public void loadData() throws SQLException {
 String infile = " '/root/studentData' ";
state.execute("load data local inpath " + infile + "overwrite into table student");
 }

// 查询数据
 @Test
 public void selectTab() throws SQLException {
res = state.executeQuery("select * from student1");
while (res.next()) {
 System.out.println(
res.getString(1) + "-" +
res.getString(2) + "-" +
res.getString(3) + "-" +
res.getString(4));
 }
 }

// 统计查询（会运行mapreduce作业，资源开销较大）
 @Test
 public void countData() throws SQLException {
res = state.executeQuery("select count(1) from student");
while (res.next()) {
 System.out.println(res.getInt(1));
 }
 }

// 删除表
 @Test
 public void dropTab() throws SQLException {
state.execute("drop table emp");
 }


/*
 * 外部表基本操作
 *
 *外部表删除后，hdfs文件系统上的数据还在，
 *重新创建同路径外部表后，其数据仍然存在
 *
 * */

 //创建外部表
 @Test
 public void createExTab() throws SQLException {

state.execute("create external table if not exists student1 ( " +
"name string , " +

转载于:https://www.cnblogs.com/xavier-xd/p/10399581.html