HiveJdbc（javaApi）及遇到的坑

一、环境：

hive: 2.1.1

mysql: 5.6.35

hadoop: 2.7.3

mysql连接器：mysql-connector-java-5.1.25-bin.jar

二、准备工作

1、[root@cmaster bin]# ./hive

2、[root@cmaster bin]# ./hiveserver2

三、javaApi

package com.hive.test;

import java.sql.*;

public class HiveJdbc {
    private static String driverName = "org.apache.hive.jdbc.HiveDriver";
    // Hive 0.11.0版本以后org.apache.hive.jdbc.HiveDriver
    // jdbchive为hive中的数据库
    private static String url = "jdbc:hive2://ip:10000/hive_test";
    // Hi55ve 0.11.0版本以后jdbc:hive2://localhost:10000/default
    private static String userName = "root";
    private static String passWord = "123456";
    //##进入虚拟机的用户名和密码
    static String sql = "";

    public static void main(String[] args) {
        try {
            Class.forName(driverName);
            Connection con = DriverManager.getConnection(url, userName, passWord);
            Statement stmt = con.createStatement();

            // 在jdbchive下创建一个student表
            createTable(stmt, "student");
            // 为study表增加数据
            loadData(stmt, "/test/student.txt", "student");
            // 查询student表
            selectData(stmt, "student");
            // 查看student表的属性描述
            describeTable(stmt, "student");
            // 查看创建的表
            showTables(stmt);
            // 对student表计数
            countData(stmt, "student");
            // 删除student表
            dropTable(stmt, "student");
        } catch (ClassNotFoundException e) {
            System.out.println("没有找到驱动类");
            e.printStackTrace();
        } catch (SQLException e) {
            System.out.println("连接Hive的信息有问题");
            e.printStackTrace();
        }

    }

    // 创建一个表
    private static void createTable(Statement stmt, String tableName) throws SQLException {
        sql = "create table " + tableName + "(key int,value string) row format delimited"
                + " fields terminated by '\t'";
        stmt.execute(sql);
    }

    // 为student表增加数据
    private static void loadData(Statement stmt, String filePath, String tableName) throws SQLException {
        sql = "load data local inpath '" + filePath + "'into table " + tableName;
        stmt.execute(sql);
    }

    // 查询表数据
    private static void selectData(Statement stmt, String tableName) throws SQLException {
        sql = "select * from " + tableName;
        ResultSet res = stmt.executeQuery(sql);
        while (res.next()) {
            System.out.println(res.getInt(1) + "\t" + res.getString(2));
        }
    }

    // 查看表的属性描述
    private static void describeTable(Statement stmt, String tableName) throws SQLException {
        sql = "describe " + tableName;
        ResultSet res = stmt.executeQuery(sql);
        while (res.next()) {
            System.out.println(res.getString(1) + "\t" + res.getString(2));
        }
    }

    // 查看创建的表
    private static void showTables(Statement stmt) throws SQLException {
        sql = "show tables";
        ResultSet res = stmt.executeQuery(sql);
        while (res.next()) {
            System.out.println(res.getString(1));
        }
    }

    // 对表的记录计数
    private static void countData(Statement stmt, String tableName) throws SQLException {
        sql = "select count(*) from " + tableName;
        ResultSet res = stmt.executeQuery(sql);
        while (res.next()) {
            System.out.println(res.getInt(1));
        }
    }

    // 删除表
    private static void dropTable(Statement stmt, String tableName) throws SQLException {
        sql = "drop table if EXISTS " + tableName;
        stmt.execute(sql);

    }
}

四、遇到的坑

1、当执行删除表操作时会报如下错误：

FAILED: SemanticException Unable to fetch table study. You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'OPTION SQL_SELECT_LIMIT=DEFAULT' at line 1

错误原因：

之后类似OPTION SQL_SELECT_LIMIT=DEFAULT 都是一个mysql的BUG：

解决办法：

将原本hive安装包下面的mysql-connector-java-5.1.7-bin.jar替换成mysql-connector-java-5.1.25-bin.jar

链接：http://dev.mysql.com/downloads/file.php?id=412737

2、其他坑：

其他遇到的坑基本都在这个连接里面：

搭建hive所遇到的坑