在互联网公司实习中使用Hive SQL的一些体会和注意点

小七七

要么不做，要么尽力做好

在互联网公司实习已经一个多月了，在这一个多月的时间里，大部分都是在接业务方需求，取数，做报表的过程中，从第二个月初就开始慢慢接分析需求了。

现在主要想给大家分享下，我在公司实习时使用hive sql获取数据过程中的一些注意点：

1、将字符串日期转为整型日期

hive数据库中存储的时间是string类型的，但是我们在取数的时候，习惯把字符串型的日期转换为整型的日期，如将'2020-09-03'转换成'20200903'，转换方式如下：

（1）regexp_replace(substr(create_time,1,10),'-','')
（2）regexp_replace(to_date(create_time),'-','')

2、将整型日期转为字符串日期

公司使用的hive数据库中，无论是增量表还是全量表，都是以天作为分区字段的，且分区字段采用的是整型格式，如果我们需要求新增用户、次留用户等，会涉及到datediff()、date_sub()函数的计算，这时候需要将整型的日期转换成字符串类型的日期格式。

（1）将INT类型的日期值使用cast()函数转成STRING类型，用Hive内置的unix_timestamp函数转成时间戳类型，最后将时间戳用from_unixtime转成yyyy-MM-dd的日期类型。

from_unixtime(unix_timestamp(cast(day as string),'yyyymmdd'),'yyyy-mm-dd')；

（2）将INT类型的日期值转成STRING类型，再对字符串进行截取处理，用-拼接起来

concat(substr(cast(day as string),1,4),'-',substr(cast(day as string),5,2),'-',substr(cast(day as string),7,2))

3、在json字符串中提取出想要的字段和值，可以使用get_json_object()

cast(get_json_object(label,'$.trace_id') as bigint)

4、将查询结果中的空值给个默认值，使用nvl()函数

--如
nvl(area,'OTHER')

这个语句的含义是：如果大区表中存在为空的记录，则可以使用OTHER来代替

5、如果查询结果是按照男女分组，但需要男女合计的结果，则可以使用在分组条件中使用with cube函数，则本来应该只有两条记录的，使用with cube；

select gender,
          count(distinct user_id) 
from XXX 
where day=20200902 --分区字段
group by  gender with cube;

则结果应该为：男 XX

女 XX

XX --此字段未合计字段，即是男和女的人数相加的结果

6、在连接表中使用on 1=1的条件

（1）使用on 1=1的条件是在两个表中计算出的指标的情况，是没有聚合函数的情况下，才可以正常使用；

（2）如表1是select sum(字段) from 表1，表2是select count(字段) from 表2 on 1=1，这种情况会出现数据膨胀的情况

7、在连接表时，需要注意表的连接条件的字段类型是否是一致的，如果出现不一致的情况，需要进行转换。

如：表1和表2的连接条件为user_id，如果表1的user_id是bigint型，表2的user_id是string型，如果直接连接，则会导致数据出现的膨胀的情况，则需要对数据进行转换，如cast(user_id as bigint),，将string类型的user_id转换成整型的user_id。

select count(distinct t1.user_id) as urs
select user_id
from A
) as t1
left join
select cast(user_id as bigint) as user_id
from B
) as t2
on t1.user_id=t2.user_id

8、工作中常用的时间和日期函数

（1）返回查询时刻的当前日期

current_date

（2）返回查询时刻的当前时间

current_timestamp()

（3）返回开始日期startdate与结束日期enddate之前相差的天数

datediff(string enddate, string startdate)

（4）在startdate基础上加上几天，返回加上几天之后的一个日期

date_add(DATE startdate, INT days)

（5）在startdate基础上减去几天，返回减去几天之后的一个日期,功能与date_add很类似

date_sub(DATE startdate, INT days)

（6）将date/timestamp/string类型的值转换为一个具体格式化的字符串。

date_format(DATE|TIMESTAMP|STRING ts, STRING fmt)
select date_format('2020-09-03', 'yy/MM/dd')
select date_format('2020-09-03', 'yy-MM-dd')
select date_format('2020-09-03', 'yyMMdd')
select date_format('2020-05-20', 'yyyy年MM月dd日 HH时mm分ss秒')

9、如何避免出现数据倾斜的情况？

我现在主要接触的是hive数据库，在取数的过程中要注意避免出现数据倾斜的情况，在我的工作中，可能会出现数据倾斜的情况主要有：在分类字段中，其中A字段占60%，但只有一个取值，B字段占39%，也只有一个取值，而C字段占1%，但取值非常多。

如：性别字段，其中男占60%，而女占39%，而其他很多取值占1%，如果直接做连接的话，可能会出现数据倾斜的情况。

针对这种情况，我们需要先在最内层，将其他多种取值全部放到other字段中，如下：

select  if(gender in ('男','女'),gender,'OTHER') as gender
from XXX
# 将其他的取值全部归为other，然后再去做连接

10、在计算用户留存的时候，尽量避免在where条件中使用datediff()日期函数进行计算

我们平常时在看SQL的面试题和其他的题目的时候，在计算用户留存或其他的情况，会习惯在where条件中使用用datediff()，两个日期相减，得到用户第二天还使用APP的用户，但以我的实习经历来看，我的leader也会引导我，避免在where中使用datediff()日期函数，正确的用法如下：

select t1.day
          ,count(distinct t1.user_id) as new_urs
          ,count(distinct t2.user_id) as new_retention_urs
(select day
          ,user_id
          ,date_add(from_unixtime(unix_timestamp(cast(day as string),'yyyymmdd'),'yyyy-mm-dd'),1) as next_date
from A表
where XX
) as t1
left join
(select day
          ,user_id
         ,from_unixtime(unix_timestamp(cast(day as string),'yyyymmdd'),'yyyy-mm-dd') as date
from B表
where XX
) as t2
on t1.user_id=t2.user_id and t1.next_date=t2.date
# A表当天日期加上1，就是第二天，所以用A表的next_date等于B表的date，就可以计算出用户的留存了

11、如何在分区表中删除数据？

有时候，我们在tableau上做报表后，需要重新回滚数据，或不需要前面日期的数据，我们就可以在hive中将之前的数据删除，这里主要是将分区日期删除，那么对应分区的数据就会被删除。

alter table A库.A表 drop partition (day<=20201025)
# 将2020年10月25日以及之前的数据删除掉。

12、如何将行转列

最近接触了新的需求，有些表格将一些字段存储在一个列表中，我需要将其转换为行，然后进行关联，就涉及到了行转列的使用。

如将useridList->[123,345,234,2334]转为
2334

这就需要使用到行转列，注意，这里需要将括号去掉。

select userid2
select substr(useridList，2，length(useridList)-2) as userid
from a
) t1
lateral view explode(split(userid,',')) t1 as userid2

13、hive中修改表字段类型/添加新字段

alter table 表名 change column 原字段名称 现字段名称 数据类型
# 添加新字段
alter table 表名 add columns(字段名称 字段类型 comment '字段的解释')

14、查询分区/求中位数

show partitions 表名
# 求中位数
percentile_approx(字段名,0.5)

15、解决with cube分组过多的情况

set hive.new.job.grouping.set.cardinality=32; 
#其中32是根据你分组的2^(分组个数)来决定的

16、解决日志表中存在带“$”符号的字段取值问题（2021/10/13更新）

由于采用的埋点方式不一样，在工作中埋点底层表会存在一些带$符号的字段，但由于数据库存的数据类型是string类型，而不是map类型，对于带$符号的字段值提取就要采用其他的方式，如下：

select day
       ,count(distinct userid) as urs