t
thatdaniel
V1
2022/11/20阅读:21主题:默认主题
学习笔记-Hive select结构
学习笔记-Hive select结构
更多内容移步公众号:

1. Select语句结构
SELECT [ALL | DISTINCT] select_expr, select_expr, ...
FROM table_reference
[WHERE where_condition]
[GROUP BY col_list]
[HAVING having_condition]
[CLUSTER BY col_list | [DISTRIBUTE BY col_list] [SORT BY col_list]][ORDER BY
col_list]
[LIMIT number];
HQL 语句执行顺序:
from -->where --> select --> group by -->聚合函数--> having --> order by -->limit
MySQL 语句执行顺序:
from-->where --> group by -->聚合函数--> having--> select --> order by -->limit
2. Hive中的复合类查询
如下表:
create table emp(
userid bigint,
emp_name array<string>, -- 数组类型:外国人的名字包含 名 和 姓
emp_date map<string,date>, -- 将入职日期和出生日期组合,入职日期和出生日期字段类型(key_value)为string,
-- 其值本身(value_type)类型为date
other_info struct<deparment:string,gender:string>
row format delimited
fields terminated by '\t' -
collection terminated by ','
map keys terminated by ':' ;
查询语句:
2.1 Array数组查询
select emp_name[0] from emp -- 0 表示数组array前一个字段,1表示后一个字段
-- 最后一个名字以w开头
select * from emp where emp_name[1] like 'W%' -- 或者rlike "^W"
2.2 Map查询
-- 出生日期是在5几年
select * from emp
where emp_date['birth_date'] between to_date('1950-01-01') and to_date('1950-12-31') -- to_date,字符串转化成日期
2.3 Struct结构体
-- 性别为男的员工
select * from emp
where other_info.gender='M'
2. 4 正则查询

3. 排序
order by | sort by | distribute by | cluster by | |
---|---|---|---|---|
作用 | order by会对输入做全局 排序 | sort by 是单独在各自的reduce中进行排序 | 控制map 中的输出 在 reduce中是如何进行划分的 | 相当于 distribute by和 sort by 合用 |
缺点 | 只有一个Reduce,当输 入规模较大时,消耗较长的计算时间 | 不能保证全局有序 | 只是分,没有排序 | 只能做升序 |
cluster by 一般是sort by和distribute by后面字段相同时使用。
3.1 order by
select * from emp_partition order by emp_no desc -- 全局排序,不受reduce影响,输出值为1
3.2 sort by
sort by在reduce内进行排序
查看和设置reduce值:
set mapred.reduce.tasks; -- 查看reduce值,默认为-1,无限制;
set mapred.reduce.tasks=2 -- 将reduce值设置为2;如果手动没改过,系统会自动做均衡;
测试样例:
insert overwrite directory '/user/admin/output_sortby/'
row format delimited
fields terminated by '\t'
select * from emp_patition sort by emp_no desc
-- 将查询结果导入到地址/user/admin/output_sortby/中,以空格做分隔符;
结果为:

sort by 将查询内容按照reduce个数进行均分。
3.3 distribute by
distribute by的分组是按照hash值进行,对reduce个数进行取余;
如:上海hash值=15,reduce个数为5,15对5的余数为0;北京hash值=20,20对5的余数仍然为0,所以北京 上海会被放到一个reduce文件中。
insert overwrite directory '/user/admin/output_distributeby/'
row format delimited
fields terminated by '\t'
select * from emp_patition distribute by dept_name desc
结果如下:

结果仍为两组(reduce个数)但根据dept_name的hash值进行排序
3.4 sort by & distribute by
Distribute 类似 MR 中的partition 进行分区,结合sort by 使用(要放到sort by 的前面);
insert overwrite directory '/user/admin/output_distribute_sortby/'
row format delimited
fields terminated by '\t'
select * from emp_patition distribute by dept_name sort by emp_no desc
将dept_name分组后再根据emp_no排序。
注意,因为distribute by是根据hash值进行排序,在结果输出中难以将同一值的字段进行分组,比如3.3中的例子,上海和北京会被放到同一个组里面,需要不断对reduce个数进行调整,才能得到最理想结果。
作者介绍
t
thatdaniel
V1