学习笔记-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个数进行调整，才能得到最理想结果。