Hive Client JDBC

怼烎@ 2022-02-21 13:12 225阅读 0赞

### 前言 ###

在前面的章节内, 我们介绍了`Hive`的安装、远程链接和基本使用. 本章, 我们介绍下如何在`Java`内完成这些操作.

### 正文 ###

*  pom.xml 文件

<dependencies>
    		<dependency>
    			<groupId>org.apache.hive</groupId>
    			<artifactId>hive-jdbc</artifactId>
    			<version>2.3.4</version>
    			
    		</dependency>
    		
    		
    		 <dependency>
                <groupId>org.apache.hadoop</groupId>
                <artifactId>hadoop-common</artifactId>
                <version>2.7.5</version>
            </dependency>
    
            <dependency>
                <groupId>org.apache.hadoop</groupId>
                <artifactId>hadoop-hdfs</artifactId>
                <version>2.7.5</version>
            </dependency>
    
            <dependency>
                <groupId>org.apache.hadoop</groupId>
                <artifactId>hadoop-mapreduce-client-jobclient</artifactId>
                <version>2.7.5</version>
            </dependency>
            
            <dependency>
                <groupId>org.apache.hive</groupId>
                <artifactId>hive-exec</artifactId>
                <version>2.3.4</version>
            </dependency>
            <dependency>
                <groupId>org.apache.hive</groupId>
                <artifactId>hive-jdbc</artifactId>
                <version>2.3.4</version>
            </dependency>
            <dependency>
                <groupId>org.apache.hive</groupId>
                <artifactId>hive-cli</artifactId>
                <version>2.3.4</version>
            </dependency>
            <dependency>
                <groupId>org.apache.hive</groupId>
                <artifactId>hive-common</artifactId>
                <version>2.3.4</version>
            </dependency>
            <dependency>
                <groupId>org.apache.hive</groupId>
                <artifactId>hive-metastore</artifactId>
                <version>2.3.4</version>
            </dependency>
            <dependency>
                <groupId>org.apache.hive</groupId>
                <artifactId>hive-service</artifactId>
                <version>2.3.4</version>
            </dependency>
    
    		
    
    		
    		<dependency>
    			<groupId>junit</groupId>
    			<artifactId>junit</artifactId>
    			<version>4.12</version>
    			<scope>test</scope>
    		</dependency>
    	</dependencies>

基本API:

*  创建链接

private static String driverName = "org.apache.hive.jdbc.HiveDriver";
        private static String url = "jdbc:hive2://localhost:10000";
        private static String user = "Hadoop";
        private static String password = "123456";
    
        private static Connection conn = null;
        private static Statement stmt = null;
        private static ResultSet rs = null;
    
        // 加载驱动、创建连接
        @Before
        public void init() throws Exception {
            Class.forName(driverName);
            conn = DriverManager.getConnection(url,user,password);
            stmt = conn.createStatement();
        }

*  创建所有库

// 创建数据库
        @Test
        public void createDatabase() throws Exception {
            String sql = "create database hive_jdbc_test";
            System.out.println("Running: " + sql);
            stmt.execute(sql);
        }

*  查询所有数据库

// 查询所有数据库
        @Test
        public void showDatabases() throws Exception {
            String sql = "show databases";
            System.out.println("Running: " + sql);
            rs = stmt.executeQuery(sql);
            while (rs.next()) {
                System.out.println(rs.getString(1));
            }
        }

*  创建表

// 创建表
        @Test
        public void createTable() throws Exception {
            String sql = "create table emp(\n" +
                            "empno int,\n" +
                            "ename string,\n" +
                            "job string,\n" +
                            "mgr int,\n" +
                            "hiredate string,\n" +
                            "sal double,\n" +
                            "comm double,\n" +
                            "deptno int\n" +
                            ")\n" +
                         "row format delimited fields terminated by '\\t'";
            System.out.println("Running: " + sql);
            stmt.execute(sql);
        }

*  查看所有表

// 查询所有表
        @Test
        public void showTables() throws Exception {
            String sql = "show tables";
            System.out.println("Running: " + sql);
            rs = stmt.executeQuery(sql);
            while (rs.next()) {
                System.out.println(rs.getString(1));
            }
        }

*  查看表结构

// 查看表结构
        @Test
        public void descTable() throws Exception {
            String sql = "desc emp";
            System.out.println("Running: " + sql);
            rs = stmt.executeQuery(sql);
            while (rs.next()) {
                System.out.println(rs.getString(1) + "\t" + rs.getString(2));
            }
        }

*  加载数据(插入数据)

// 加载数据
        @Test
        public void loadData() throws Exception {
            String filePath = "/home/hadoop/data/emp.txt";
            String sql = "load data local inpath '" + filePath + "' overwrite into table emp";
            System.out.println("Running: " + sql);
            stmt.execute(sql);
        }

*  查询数据

// 查询数据
        @Test
        public void selectData() throws Exception {
            String sql = "select * from emp";
            System.out.println("Running: " + sql);
            rs = stmt.executeQuery(sql);
            System.out.println("员工编号" + "\t" + "员工姓名" + "\t" + "工作岗位");
            while (rs.next()) {
                System.out.println(rs.getString("empno") + "\t\t" + rs.getString("ename") + "\t\t" + rs.getString("job"));
            }
        }

*  统计计算

// 统计查询（会运行mapreduce作业）
        @Test
        public void countData() throws Exception {
            String sql = "select count(1) from emp";
            System.out.println("Running: " + sql);
            rs = stmt.executeQuery(sql);
            while (rs.next()) {
                System.out.println(rs.getInt(1) );
            }
        }

*  删除数据库

// 删除数据库
        @Test
        public void dropDatabase() throws Exception {
            String sql = "drop database if exists hive_jdbc_test";
            System.out.println("Running: " + sql);
            stmt.execute(sql);
        }

*  删除数据库表

// 删除数据库表
        @Test
        public void deopTable() throws Exception {
            String sql = "drop table if exists emp";
            System.out.println("Running: " + sql);
            stmt.execute(sql);
        }

*  释放资源

// 释放资源
        @After
        public void destory() throws Exception {
            if ( rs != null) {
                rs.close();
            }
            if (stmt != null) {
                stmt.close();
            }
            if (conn != null) {
                conn.close();
            }
        }

--------------------

### Reference ###

\[1\]. [Hive 之 Java API 操作][Hive _ Java API]  
\[2\]. [Hive 使用Jdbc连接进行连接][Hive _ Java API]  
\[3\]. [(Official) HiveServer2 Clients][Official_ HiveServer2 Clients]

[Hive _ Java API]: https://blog.csdn.net/hg_harvey/article/details/77688703
[Official_ HiveServer2 Clients]: https://cwiki.apache.org/confluence/display/Hive/HiveServer2+Clients