Hive 查询

6. Hive 查询

6.1 基本查询（Select ... From)

6.1.1 常用函数

• 求总行数(count): select count(*) AS cnt from emp;
• 求工资的最大值(max)：select max(sal) max_sal from emp;
• 求工资的最小值(min): select min(sal) min_sal from emp;
• 求工资的总和(sum): select sum(sal) sum_sal from emp;
• 求工资的平均值(avg): select avg(sal) avg_sal from emp;

6.2 Where 语句

6.2.1 基础 where 查询

• 查询工资等于 5000 的所有员工：select * from emp where sal = 5000;
• 查询工资在 500 到 1000 的员工信息： select * from emp where sal between 500 and 1000;
• 查询 comm 为空的所有员工信息： select * from emp where comm is null;
• 查询工资是 1500 或 5000 的员工信息： select * from emp where sal IN(1500, 5000);

6.2.2 Like 和 RLike

• Like:
  • "% 代表零个或多个字符（任意个字符）"；
  • "_ 代表一个字符"；
• RLike:
  • 是 Hive 中对 “Like” 的扩展，可以通过 Java 的正则表达式来指定匹配条件。
• 查询以“2”开头工资的员工信息：select * from emp where sal LIKE '2%';
• 查询工资中含有“2”的员工信息：select * from emp where ename RLIKE '[A]';

6.3 分组

6.3.1 Group By 语句

• 计算 emp 表每个部门的平均工资：select t.deptno, avg(t.sal) avg_sal from emp t group by t.deptno;
• 计算 emp 表每个部门中每个岗位的最高薪水：select t.deptno,t.job,max(t.sal) max_sal from emp t group by t.deptno, t.job;

6.3.2 Having 语句

• 计算 emp 表每个部门的平均工资大于2000的部门：select t.deptno, avg(t.sal) avg_sal from emp t group by t.deptno having avg_sal > 2000;

6.4 排序

6.4.1 全局排序（Order By）

• 查询员工信息按工资升序排列：select * from emp order by sal;
• 查询员工信息按工资降序排列：select * from emp order by sal desc;

6.4.2 按照别名排序

• 按照员工工资的2倍排序：select ename, sal*2 twosal from emp order by twosal;

6.4.3 多个列排序

• 按照部门和工资升序排序：select ename, deptno, sal from emp order by deptno, sal;

6.4.4 每个 MapReduce 内部排序（Sort By）

• Sort By: 每个 Reduce 内部进行排序，对全局结果集来说，不排序。
• 设置 reduce 个数：set mapreduce.job.reduces=3;
• 查看设置的 reduce 个数：set mapreduce.job.reduces;
• 根据部门编号降序查看员工信息： select * from emp sort by empno desc;
• 将查询结果导入到文件中（按照部门编号降序排序）：insert overwrite local directory '本地路径' select * from emp sort by deptno desc;

6.4.5 分区排序（Distribute By）

• Distribute By: 类似于 MR 中 partition，进行分区，结合 sort by 使用；
  • 注意，Hive 要求 Distirbute By 语句要写在 sort by 语句之前；
• 对于 distribute by 进行测试，一定要分配**多 reduce **进行处理；
• 先按照部门编号分区，再按照员工编号降序排序：
  • set mapreduce.job.reduces=3
  • insert overwrite local directory '本地路径' select * from emp distribute by deptno sort by sal;

6.4.6 Cluster By

• 当 distribute by 和 sorts by 字段相同时，可以使用 cluster by 方式；
• 注意：不能指定排序规则为 ASC 或 DESC，只能倒序排序；
• 以下两种写法等价：
  • select * from emp cluster by deptno;
  • select * from emp distribute by deptno sort by deptno;

6.6 分桶及抽样调查

6.6.1 分桶表数据存储

• 分区针对的是数据的存储路径；分桶针对的是数据文件；
• 分区提供一个隔离数据和优化查询的便利方式。不过，并非所有的数据集都可形成合理的分区；
• 分桶是将数据集分解成更容易管理的若干部分的另一个技术。
  • 创建分桶表：create table stu_buck(id int, name string) clustered by(id) into 4 buckets row format delimited fields terminated by ' ';
  • 查看表结构：desc formated stu_buck;
  • 确认参数：set mapreduce.job.reduces=-1;
  • 先将数据导入到普通表里，然后将数据从普通表加载到分桶表：insert into table 分桶表 select * from 普通表;

6.6.2 分桶抽样查询

• 查询示例:select * from stu_buck tablesample(bucket 1 out of 4 on id);
• 注: tablesample 是抽样语句,语法: TABLESAMPLE(BUCKET x OUT OF Y)
  • y 必须是 table 总 bucket 数的倍数或者因子。hive 根据 y 的大小,决定抽样的比例。例如，table 总共分了 4 份,当 y = 2时,抽取 (4/2=)2个 bucket 的数据,当 y = 8 时,抽取 (4/8=)1/2个 bucket 的数据。
  • x 表示从哪个 bucket 开始抽取,如果需要取多个分区,以后的分区号为当前分区号加上 y。例如, table 总 bucket 数为 4, tablesample(bucket 1 out of 2),表示总共抽取 (4/2=)2个 bucket 的数据,抽取第1(x)个和第4(x+y)个bucket的数据。
  • 注意: x 的值必须小于等于 y 的值,否则,会报异常。

6.7 其他常用查询函数

6.7.1 空字段赋值

• NVL: 给值为 NULL 的数据赋值,格式为: NVL(string1, replace_with);如果 string1 为 NULL,则 NVL 函数返回 replace_with 的值,否则返回 string1 的值。

6.7.2 CASE WHEN

• 统计A部门和B部门的男女比例:

// 数据
张飞    A   男
关二爷  B   男
Smith   A   男
武则天  B   女
秋菊    A   女
考拉    B   女

// 建表语句
create table emp_gender(
    name string,
    dept_id string,
    gender string
)row format delimited fields terminated by '	';

// 查询语句
select dept_id,
sum(case gender when '男' then 1 else 0 end) male_count,
sum(case gender when '女' then 1 else 0 end) female_count
from emp_gender
group by dept_id;

// 结果:
dept_id     male_count      female_count
 A              2               1
 B              1               2

6.7.3 行转列

• CONCAT(string A/col, string B/col...)：返回输入字符串连接后的结果，支持任意个输入字符串；
  • 示例：select concat(deptid, dname) from dept;
• CONCAT_WS(separator, str1, str2, ...)：一个特殊形式的 CONCAT()。第一个参数是剩余参数间的分隔符。分隔符可以是与剩余参数一样的字符串。如果分隔符是 NULL，返回值也将为 NULL。这个函数会跳过分隔符参数后的任何 NULL 和空字符串；
  • 示例：select concat_ws(",", ename, job) from emp;
• COLLECT_SET(col)：函数只接受基本数据类型，它的主要作用是将某字段的值进行去重汇总，产生Array类型字段。

==== 需求：把星座和血型一样的人归类到一起 ====
// 期望结果：
天秤座，A   孙悟空|天蓬
天蝎座，A   小猪
天蝎座，B   诸葛亮
狮子座，B   渤哥|关二爷

// 原始数据
孙悟空  天秤座  A
小猪  天蝎座  A
渤哥  狮子座  B
天蓬  天秤座  A
诸葛亮  天蝎座  B
关二爷  狮子座  B

// 建表语句
create table person_info(
  name string,
  constellation string,
  blood_type string
)row format delimited fields terminated by '	';

// 查询语句
// 分析：
//  第一步：
//     select concat_ws(",", constellation, blood_type), name from person_info;
//  第二步：
  SELECT 
    t1.c_b,
    CONCAT_WS("|", COLLECT_SET (t1.name)) 
  FROM
    (SELECT 
      CONCAT_WS(",", constellation, blood_type) c_b,
      NAME 
    FROM
      person_info) t1 
  GROUP BY t1.c_b ;

6.7.4 列转行

• EXPLODE(col)：将 hive 一列中复杂的 array 或者 map 结构拆分成多行。
• LATERAL VIEW：
  • 用法：LATERAL VIEW udtf(expression) tableAlias AS columnAlias；
  • 解释：用于和 split，explode 等 UDTF 一起使用，它能够将一列数据拆成多行数据，在此基础上可以对拆分后的数据进行聚合。

==== 需求：将电影分类中的数组数据展开 ====
// 期望结果：
<疑犯追踪>  悬疑
<疑犯追踪>  动作
<疑犯追踪>  科幻
<疑犯追踪>  剧情
<Lie to me>    悬疑
<Lie to me>    警匪
<Lie to me>    动作
<Lie to me>    心理
<Lie to me>    剧情
<战狼 2>    战争
<战狼 2>    动作
<战狼 2>    灾难


// 原始数据 movie.txt
<疑犯追踪>    悬疑,动作,科幻,剧情
<Lie to me>    悬疑,警匪,动作,心理,剧情
<战狼2>    战争,动作,灾难

// 建表语句
create table movie_info(
  movie string,
  category array<string>
) row format delimited fields terminated by '	'
collection items terminated by ',';

// 查询语句
select movie,category_name
from movie_info
LATERAL VIEW EXPLODE(category) tmpTable as category_name;

6.7.5 窗口函数

• 相关函数说明：
  • OVER()：指定分析函数工作的数据窗口大小，这个数据窗口大小可能会随着行的变化而变化；
  • CURRENT ROW：当前行；
  • n PRECEDING：往前 n 行数据；
  • n FOLLOWING：往后 n 行数据；
  • UNBOUNDED：起点
    • UNBOUNDED PRECEDING：表示从前面的起点；
    • UNBOUNDED FOLLOWING：表示到后面的终点；
  • LAG(col, n)：往前第 n 行数据；
  • LEAD(col, n)：往后第 n 行数据；
  • NTILE(n)：把有序分区中的行分发到指定数据的组中，各个组有编号，编号从1开始，对于每一行，NTILE 返回此行所属的组的编号。注意：n 必须为 int 类型；

==== 需求：
        1. 查询在2017年4月份购买过的顾客及总人数
        2. 查询顾客的购买明细及月购买总额
        3. 上述的场景，要将cost按照日期进行累加
        4. 查询顾客上次的购买时间
        5. 查询前 20% 时间的订单信息


// 原始数据：
name,orderdate,cost
jack,2017-01-01,10
tony,2017-01-02,15
jack,2017-02-03,23
tony,2017-01-04,29
jack,2017-01-05,46
jack,2017-04-06,42
tony,2017-01-07,50
jack,2017-01-08,55
mart,2017-04-08,62
mart,2017-04-09,68
neil,2017-05-10,12
mart,2017-04-11,75
neil,2017-06-12,80
mart,2017-04-13,94

// 建表语句
create table business(
  name string,
  orderdate string,
  cost int
) row format delimited fields terminated by ',';

// ===== 需求一：查询在2017年4月份购买过的顾客及总人数
select name, count(*) over()
from business
where substring(orderdate,1,7)="2017-04"
group by name;

// ===== 需求二：查询顾客的购买明细及月购买总额
select *,sum(cost) over(distribute by month(orderdate)) from business;
或者
select *,sum(cost) over(partition by month(orderdate)) from business;

// ===== 需求三：将cost按照日期进行累加
select *,sum(cost) over(sort by orderdate rows between UNBOUNDED PRECEDING and CURRENT ROW) from business;

// ===== 需求四：查询顾客上次的购买时间
select *,lag(orderdate,1) over(distribute by name sort by orderdate) from business;

// ===== 需求五：查询前 20% 时间的订单信息
// 第一步分析：
//      select *,ntile(5) over(sort by orderdate) from business;
// 第二步：
select *
from (
  select name,orderdate,cost,ntile(5) over(sort by orderdate) group_id from business
) t where group_id = 1;

6.7.6 Rank

• RANK()：排序相同时会重复，总数不会变；
• DENSE_RANK()：排序相同时会重复，总数会减少；
• ROW_NUMBER()：根据顺序计算；

// 原始数据 score.txt
AAA    语文    87
AAA    数学    95
AAA    英语    68
BBB    语文    94
BBB    数学    56
BBB    英语    84
CCC    语文    64
CCC    数学    86
CCC    英语    84
DDD    语文    65
DDD    数学    85
DDD    英语    78

// 建表语句
create table score_info(
  name string,
  subject string,
  score int
) row format delimited fields terminated by '	';

// ===== 需求一：计算每门学科成绩排名
select *,
rank() over(partition by subject order by score desc),
dense_rank() over(partition by subject order by score desc),
row_number() over(partition by subject order by score desc) from score_info;