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