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,deptwhere 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+1000when 'MANAGER'   then sal+800else sal+400end
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();//运行SQLrs = 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.xmlhadoop.proxyuser.root.hosts*hadoop.proxyuser.root.groups*	

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;