hive基础-高级查询

高级查询

group by
按照某些字段值进行分组，相同值放在一起

select col1[,col2],count(1),sel_expr -- 聚合操作
from table
where condition
group by col1[,col2] --select中非聚合的列需要全部出现在group by中
having

注意：select后非聚合列必须出现在group by中
除了普通列就是一些聚合操作
group by后面也跟表达式，比如substr(col)，截取col的某些部分作为group by
where放在map中
having在reduce中
group放在reduce中
特性：

• 使用了reduce操作，受限于reduce数量，设置reduce参数mapred.reduce.tasks
• 输出文件个数与reduce数相同，文件大小与reduce处理数据量有关

使用set mapred.reduce.tasks=5再运行，可以看到mapreduce数量的不同

问题：

• 网络负载过重
• 数据倾斜，某个key过多，可以使用set hive.groupby.skewindata=true优化，会启动把较重的reduce启动两个mapreduce job
  

having是在reduce中进行，聚合之后进行过滤，比如求和值大于5的，B和D会被过滤掉
order by
按照某些字段排序

select col,other...
from table 
where condition
order by col1,col2[asc|desc]

注意：默认字典排序，全局排序，需要且只有一个reduce，与配置无关
如果数据量超级大，所有的数据都会汇总到一个reduce排序，耗时较长且容易内存溢出
join
两个表m,n之间按照on条件连接，m中有一条记录和n中的一条记录组成一条新纪录
join 等值连接，m和n中同时存在
left outer join左外连接，左表中的值无论右表是否存在都会输出，右表只有在左表值存在才会输出
right outer join和left outer join相反
left semi join 类似exists，判断某些值是否在另一个表中存在
mapjoin在map端完成join操作，不需要reduce，基于内存做join，属于优化操作，需要把表加载到内存中，需要表比较小；

select 
m.col as col,
m.col2 as col2,
n.col3 as col3
from (
    select test 
    where... (map端执行)
)m  --左表
[left outer|right outer|left semi] join
n --右表
on m.col=n.col
where condition --reduce执行

优化join的数据倾斜set hive.optimize.skewjoin=true;

mapjoin把小表加载到内存中，每个节点都需要这么保存；
使用了分布式缓存技术；
不消耗集群的reduce资源
较少reduce操作加快程序执行
降低网络负载
占用内存，加载到内存的表尽量小些，生成较多的小文件；

方式一：自动方式

set hive.auto.convert.join=ture;
hive.mapjoin.smalltable.filesize    #默认25mb，表小时自动加载进内存

方式二：手动指定/+mapjoin(n)/必须加

select /*+mapjoin(n)*/ m.col1,m.col2.n.col3 from m
join n
on m.col=n.col

简单总结下mapjoin使用场景：
1.关联操作中有一张非常小的表、
2.不等值的连接操作，普通的join不支持不等值连接

分桶
对于表或者分区，进一步组织成桶
hive针对一列分桶
hive采用列值哈希，然后除以桶的个数求余的方式决定该记录存放在哪个桶
好处：更高的查询效率，使用取样更高效
创建分桶

create table bucketed_user (
    id int,
    name string
)
clustered by (id) sorted by (name) into 4 buckets
row format delimited fields terminated by '\t' sorted as textfile;
set hive.enforce.bucketing=true;

分桶使用

select * from bucketed_user
tablesample(bucket 1 out of 2 on id)    #查询一半

bucket join
要求一个分桶的数是另外一个桶的倍数，并且按同一个字段分组

set hive.optimize.bucketmapjoin=true;
set hive.optimize.bucketmapjoin.sortedmerge=true;
set hive.input.format=org.apache.hadoop.hive.ql.io.BucketizedHiveInputFormat;

destibute by
数据打散，按照col列把数据分散到不同的reduce
与group by都是按照key值划分，都是使用reduce操作destibute by只是把数据分散开，而group by把相同的key数据聚集到一起，后续必须是聚合操作。

sort by
数据排序，按照col列把数据排序

select col1,col2 from m distribute by col1
sort by col1 asc,col2 desc;

保证reduce中的内容是局部有序的
order by是全局排序，sort by只是确保每个reduce上面输出的数据有序，如果只有一个reduce和order by作用一样。

cluster by

把有想同值的数据聚集到一起并排序
效果：

cluster by col
-- 相当于
distribute by col order by col

uniton all
多个表合并成一个表，hive不支持union
union有去重

select col 
from (
    select a as col from t1
    union all
    select b as col from t2
) tmp

只是通过map读数据放到另一个表中，合并成一个文件。
要求：

• 字段名字、类型、个数一样
• 子表不能有别名
• 如果需要从合并之后的表中查询数据，合并的表需要有别名

底层实现mapreduce

聚合操作

count计数
count(*)值不全为null才计入，count(1)只要有就会加
count(col)列值为非null才会加1
sum求和
转换成数字值，返回bigint
如果需要加1,sum(col)+cast(1 as bigint)
avg求平均值
转换成数字值，返回double
distinct不同值个数
计算不同值count(discint col)

三. 函数

show functions;        #显示当前有多少个函数可用
desc function concat; #显示函数的描述信息
desc function extended concat; #函数扩展描述