【大数据离线开发】8.4 Hive的查询、操作以及自定义函数

8.5 Hive的查询

执行SQL（HQL）。HQL是SQL的一个子集

案例：创建部门表，对数据进行查询

创建部门表
create table deptno(
	deptno int,
	dname string,
	loc string
)
row format delimited fileds terminated by ',';

导入数据
load data local inpath '/root/temp/dept.csv' into table dept;

8.5.1 简单查询

查询所有的员工信息
select * from emp1;

8.5.2 过滤和排序

查询员工信息：员工号  姓名  薪水
select empno,ename,sal,deptno from emp1;

8.5.3 多表查询

查询部门名称、员工的姓名
	select dept.dname,emp1.ename		
	from emp1,dept
	where emp1.deptno=dept.deptno;

8.5.4 子查询

子查询：hive只支持：from和where后面的子查询

查询部门名称是SALES的员工信息
select * 
from emp1
where emp1.deptno in (select deptno from dept where dname='SALES');

8.5.5 Hive的函数

内置函数：select max(sal) from emp1;

• 数学函数

  • round
  • ceil
  • floor

• 字符函数

  • lower

  • upper

  • length

  • concat

  • substr

  • trim

  • lpad

  • rpad

• 收集函数

  • size

• 日期函数

  • to_date

  • year

  • month

  • day

  • weekofyear

  • datediff

  • date_add

  • date_sub

• 条件函数

  • if
  • coalesce
  • case… when…

• 聚合函数

  • count
  • sum
  • min
  • max
  • avg

8.5.6 条件函数

条件函数（条件表达式：就是一个if else语句）
if
coalesce
case… when…

需求：做一个报表：涨工资，总裁1000 经理800 其他400

select empno,ename,job,sal,
	case job when 'PRESIDENT' then sal+1000
         	when 'MANAGER'   then sal+800
			else sal+400
	end
from emp1;
		
Oracle数据库：decode函数也是条件函数
select empno,ename,job,sal "Before"
	decode(job,'PRESIDENT', sal+1000,
				'MANAGER', sal+800,
				sal+400) "After"
from emp;

8.6 Hive的Java API

准备工作：

1. 首先启动Hive远程服务：hiveserver2 &
2. 需要Hive lib目录下的jar包

TestMain.java

package demo.jdbc;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;

public class TestMain {

	public static void main(String[] args) {
		String sql = "select * from mytest1";

		Connection conn = null;
		Statement st = null;
		ResultSet rs = null;
		
		try{
			//获取链接
			conn = JDBCUtils.getConnection();
			
			//得到运行环境
			st = conn.createStatement();
			
			//运行SQL
			rs = st.executeQuery(sql);
			//处理
			while(rs.next()){
				//取员工姓名, 不能通过列名来取，通过序号
				int id = rs.getInt("tid");
				String name = rs.getString("tname");
				System.out.println(id+"\t"+name);
			}
		}catch(Exception ex){
			ex.printStackTrace();
		}finally{
			//释放资源
			JDBCUtils.release(conn, st, rs);
		}
	}

}

JDBCUtils.java

package demo.jdbc;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;


/*
 * 工具类：
 * 1、获取数据库Hive链接
 * 2、释放资源
 */
public class JDBCUtils {

	//Hive数据库的驱动
	private static String driver = "org.apache.hive.jdbc.HiveDriver";                                
	
	//Hive位置
	private static String url = "jdbc:hive2://192.168.157.111:10000/default";

	
	//注册驱动
	static{
		try {
			Class.forName(driver);
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
			throw new ExceptionInInitializerError(e);
		}
	}

	//获取链接
	public static Connection getConnection(){
		try {
			return DriverManager.getConnection(url);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return null;
	}

	public static void release(Connection conn,Statement st,ResultSet rs){
		if(rs != null){
			try {
				rs.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}finally{
				rs = null;
			}
		}
		
		if(st != null){
			try {
				st.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}finally{
				st = null;
			}
		}
		
		if(conn != null){
			try {
				conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}finally{
				conn = null;
			}
		}
	}
}

1、JDBC：Java的标准的访问数据库的接口

启动Hive Server: hiveserver2

java.lang.RuntimeException: org.apache.hadoop.ipc.RemoteException:User:
root is not allowed to impersonate anonymous
在老版本的Hive中，是没有这个问题的
把Hadoop HDFS的访问用户（代理用户） —> *

core-site.xml
	<property>
		 <name>hadoop.proxyuser.root.hosts</name>
		 <value>*</value>
	</property>

	<property>
		 <name>hadoop.proxyuser.root.groups</name>
		 <value>*</value>
	</property>	

2、ODBC、Thrift Client

8.7 Hive的自定义函数

• Hive的自定义函数（UDF：user define function）本质就是一个Java程序

  • 可以直接应用于select语句，对查询结构做格式化处理后，再输出内容

• Hive自定义函数的实现细节

  • 自定义UDF需要继承org.apache.hadoop.hive.ql.UDF
  • 需要实现evaluate函数，evaluate函数支持重载

Hive自定义函数案例

案例一：拼接两个字符串

1、实现关系型数据库中的concat函数: 拼加字符串
select concat('Hello  ' ,'World') from dual;  ----> Hello World

案例二：判断员工表中工资的级别

2、根据员工的薪水，判断薪水的级别
	(*) sal < 1000   ---> Grade A
	(*) 1000<= sal < 3000 ---> Grade B
	(*) sal >= 3000 ---> Grade C

Hive自定义函数的部署

将程序打包放到目标机器上去
将jar包加入hive的classpath
add jar /root/temp/myudf.jar;
   
创建临时函数别名（函数名称）
create temporary function myconcat as 'udf.MyConcatString';
create temporary function checksal as 'udf.CheckSalaryGrade';

Hive自定义函数的调用

查询HQL语句：
select myconcat(ename,job) from emp;
select ename,sal,checksalary(sal) from emp;

销毁临时函数：
DROP TEMPORARY FUNCTION checksalary;