关于Hive的一些零碎知识

不会Hive的啊扬

已于 2023-03-07 22:17:23 修改

阅读量220

点赞数

文章标签： hive Powered by 金山文档

于 2023-03-07 19:49:43 首次发布

本文链接：https://blog.csdn.net/m0_70949976/article/details/129386545

版权

1. 关于count(*)

资料链接：

mysql中：https://www.cnblogs.com/wynjauu/articles/10384057.html

hive中：https://blog.csdn.net/huobumingbai1234/article/details/80706011

总结：

1.无论是mysql还是hive，用count(*)和count(1)都没有任何区别，mysql中没有索引读块头，5.7之后读主键索引，hive中读行偏移量

2.count(列)需要拿出列的所有值，取非null的，所以需要扫描列，速度慢，但和count(*)需求不同所以没有可比性

3.在mysql中如果给列加上允许空值的索引则count(列)也会很快，和count(*)基本没有区别

3.在hive中因为是列式存储所以count(列)，列放在前后都无所谓，但mysql中将需要查个数的列尽量放在前面，因为不是列存需要向后找

2.关于select*

资料链接：

Mysql中：https://blog.csdn.net/qq_42914528/article/details/89411411

总结：

1.mysql中没索引情况下，select*和所有列没有太大区别，但有索引的情况下，select所有列更快，因为select*不仅要取索引值还要取data

2.hive目前还没有找到相关资料说明，不是太清楚，但除平时练习使用，其他情况都不推荐使用 SELECT * FROM XXX ，也是为了方便后期维护。

3.parquet文件格式

https://blog.csdn.net/zhang35/article/details/122284903

总结：

1.列式存储方便计算，但不方便按行取字段

2.相同格式的数据存储在一起，列式存储方便压缩

3.例如可以记录列中数据重复几次，映射字符串，记录时间戳差值来节省空间

4.巧用max(case when)函数进行行转列

资料链接：https://blog.csdn.net/u012045045/article/details/111037151

总结：

1.虽然concat_ws结合collect_set或直接使用group_concat()也能够进行行转列，但这种行转列只能将多行转为1列而不是多列

2.使用max行转列就是先多行分组，然后再用多个聚合函数max将组内的行进行判断

原理：

如果匹配上则拿出来，匹配不上则给null或者0，最后max一定是取出匹配上的那个

问题：

1.为什么非要用聚合函数，而不能直接判断？直接判断则会取组内第一个元素，也就是所有的判断中只会有一条满足

2.为什么非要用max聚合，min不可以吗？我们匹配不上的其他行都给null，而min是会取null的并不合适，而avg会过滤Null其实也可以但没有必要

举例：

班级姓名学科分数->班级姓名语文数学英语理综文综总成绩

另外加一行全班平均分并排名

--建表
CREATE TABLE tmp.test001(
    `class` STRING,
    `name` STRING,
    `course` STRING,
    `score` INT
)
LOCATION 'hdfs://HDFSNS/Data/d1/hive/warehouse/tmp.db/test001';
--插入数据
INSERT INTO tmp.test001(`class`,name,course,score) VALUES
("一班","张三","语文",56),
("一班","张三","数学",35),
("一班","张三","英语",87),
("一班","张三","物理",36),
("一班","张三","化学",78),
("一班","张三","生物",52),
("一班","李四","语文",72),
("一班","李四","数学",91),
("一班","李四","英语",59),
("一班","李四","物理",36),
("一班","李四","化学",78),
("一班","李四","生物",52);
--查询sql
select
*
from
(
    select
    `class`,
    name,
    `语文`,
    rank() over(partition by `class` order by `语文` desc) rk1,
    `数学`,
    rank() over(partition by `class` order by `数学` desc) rk2,
    `英语`,
    rank() over(partition by `class` order by `英语` desc) rk3,
    `理综`,
    rank() over(partition by `class` order by `理综` desc) rk4,
    `总成绩`,
    rank() over(partition by `class` order by `总成绩` desc) rk
    from
    (
        select
       `class`,
        name,
        max(if(course="语文",score,0)) as `语文`,
        max(if(course="数学",score,0)) as `数学`,
        max(if(course="英语",score,0)) as `英语`,
        sum(if(course in ("物理","化学","生物"),score,0)) as `理综`,
        sum(score) as `总成绩`
        from tmp.test001
        group by `class`, name
    )t
    union all
    select
    `class`,
    '全班平均分',
    avg(if(course="语文",score,null)) as `语文`,
    0,
    avg(if(course="数学",score,null)) as `数学`,
    0,
    avg(if(course="英语",score,null)) as `英语`,
    0,
    sum(if(course in ("物理","化学","生物"),score,0))/count(distinct name) as `理综`,
    0,
    sum(score)/count(distinct name) as `总成绩`,
    0
    from tmp.test001
    group by `class`
)tt
order by rk;
--注意：其他成绩可以用avg方法用null过滤掉空行，但理综和总成绩不可以，是几行加在一起，但分母是人数，比行数少

5.求年龄精确到月

#!/bin/bash
echo "
select
concat(
    if(substr('$1',6,5) >= substr('$2',6,5),
    cast((cast(substr('$1',1,4) as int) - cast(substr('$2',1,4) as int)) as string),
    cast((cast(substr('$1',1,4) as int) - cast(substr('$2',1,4) as int) -1) as string)), '岁',
    if(substr('$1',9,2) >= substr('$2',9,2),
    cast((cast(substr('$1',6,2) as int) + 12 - cast(substr('$2',6,2) as int)) % 12 as string),
    cast((cast(substr('$1',6,2) as int) + 12 - cast(substr('$2',6,2) as int) -1) % 12 as string)), '月'
)
"

说明：

这是一段sql，但是我为了测试方便，使用脚本传值来生成sql，以此来快速赋值测试sql逻辑

为什么取月份要先加12再模12，因为年份是没有进制的，只会越来越大，但月份是有的，12为一个循环，例如今年的2月肯定是比去年的4月大10个月的，而不是-2

6.sql中为什么不建议使用full join

hive中尽量不用full join的原因是它在底层会先Left join，然后再right join，最后再Union去重，效率很差。

7.宽表构建列为什么不建议join

对于新手来讲，可能觉得，构建宽表时把需要的列直接join起来不就好了吗，多省事，但生产环境中往往不这样使用，为什么？

join时容易产生笛卡尔积

join的效率很差，而且容易造成数据倾斜

那应该采用什么方法？

一般能使用join的地方就可以使用union all，我们只需要把每段sql中的其他列给0，最后每段sql使用union all，最后再求sum即可

8.关于grouping sets

grouping sets可以完成多段sql按不同条件分组再Union的情况，写起来更方便，而且只需要读一次原表即可，节省了IO

具体用法参考https://blog.csdn.net/qq_41081716/article/details/113805912

9.关于timestamp

timestamp到底是什么？是时间戳吗，是Linux里的ts那种时间戳吗？并不是，是一种时间格式

Mysql的timestamp类型在确定范围时不可以直接写>某个日期，因为他是时间格式，不是hive的时间字符串，会自动补全时间00:00:00

所以会出现hive可以取出这个时间的数据，但mysql取不到，所以应该>=

10.关于spark引擎的谓词下推

首先需要明白何为谓词下推？即在join操作时不需要将from内的表提前where过滤作为子表来节省读取的数据量，而是直接在where条件里过滤，程序在读文件的时候就可以直接过滤出我们想要的那部分，而不是先拿出全表数据，再过滤出想要的数据

1.当join时where中条件为and且筛选不同表的时候，spark可以做谓词下推，提前过滤再做join

2.当join时where中条件为or且筛选不同表的时候，spark不会做谓词下推，因为如果下推，可能会提前过滤走虽然不满足左表条件，但链接后的行满足右表条件的行，这样是有问题的

3.当join时where中条件为or或为and但筛选同一个表的时候，spark都会做谓词下推，因为和另一个没关系，结果集不受影响

4.当join时where中条件为or且筛选不同表但该条件也是链接条件的时候，spark会做谓词下推，因为链接条件必定要满足都满足，要不满足都不满足，此时的or其实和and没有区别

5.当join时where中的或条件里为分区条件时，spark会做谓词下推，因为会将不符合查询分区条件的目录直接排除在待扫描的目录之外，来节省链接开销，此时将不再考虑上述2中的特殊情况

参考链接：https://www.cnblogs.com/vivotech/p/10862789.html

11.关于union all数量过多

当union all数量过多时，程序会直接在分配容器时立马报错结束，而不是在运行到某个exctor的stage结束，也就是说两次失败执行的时间都很短，这就说明是driver端出现了问题

问题原因：在Spark程序中，SparkContext，DAGScheduler都是运行在Driver端的。对应rdd的Stage切分也是在Driver端运行，如果用户自己写的程序有过多的步骤，切分出过多的Stage，这部分信息消耗的是Driver的内存，这个时候就需要调大Driver的内存

解决方式：将driver端内存从3g增加到5g，excutor不用动，之后就可以正常运行了

链接：https://zhidao.baidu.com/question/697597894953785404.html

12.关于group by和partition by联用

原数据：

张三河南省郑州市

李四河南省郑州市

王五河南省焦作市

赵六湖北省武汉市

首先试想一个问题：

如果对于一个包含省份和城市的数据，我先对城市进行group by，再对省份进行partition by，结果会怎样？

求出每个省份的人数；2.求出每个城市的人数；3.求出每个人所在的省份有几口人；4.求出每个人所在的城市有几口人

select 
city，
count(1) over(partition by province) as cnt
from t
group by province，city
--虽然按城市分组和省份城市一起分组结果都是一样，都是按最细粒度城市来分，
--但还是要这么写，因为开窗函数的partition里要用到省份，开窗函数不是聚合函数

其实上面的答案全部错误，为什么？

group by分组也是一种去重，他会一组数据中的城市先distinct，保证每个城市只有一份，接着，他会按照partion by进行分区，按省份分区，也就是当前城市所在的省份有多少个城市，他就统计几个，所以最后统计出来的是每个市区所在省份的城市数量

目标数据：

郑州市 2

焦作市 2

武汉市 1

13.关于group by里有些字段为什么不可以省略

之前写过一段这样的sql，在当时就很不理解：

1.为什么size为最外层函数，为什么size的右括号不括在patientid外面而是括在最后？

2.什么patientid要在group by中出现？collect_set明明是聚合函数啊，聚合函数里面的字段不是随便用吗？

原因：

因为开窗函数是over 连带前面那个函数，是collect开窗，不是size开窗，size只是对开窗的结果做处理，当然这里的size想进行开窗也不是不可以，外层继续跟over就好了，但是没必要

只要开窗了，他就已经不是聚合函数了，只不过这个开窗函数有分区内聚合的功能而已，这个函数为collect加over里的所有，所以开窗函数里的字段必须要在group by中出现，这是原因1，而且他不这么干也不行啊，他拿什么开窗，拿group by分组后的结果，其实也就是去重后的结果，去重后的结果没有该字段，那他下一步又要用到，是没法操作的，所以必须将其放入group by中