【HQL】HQL汇总

一、背景

　　SQL几乎是初级分析师80%的工作内容，当然在我的规划里，中级分析师要做好维度建模工作以及最好有OLAP系统工具啥的，或者已经能主动分析写文章了，尽量减少SQL的无效使用。这是我自己的定位，其他人可能不一样。

　　anyway，SQL对分析师来说是一项很棒的工具。Hive是基于Hadoop的一个数据仓库工具，可以将结构化的数据文件映射为一张数据库表，并提供类SQL查询功能。^[1]HQL即Hive SQL，相对MySQL 函数更多更复杂一些。
　　在查询能使用哪个函数时，通常有三种情况：
　　1）记得某个函数名，但忘了参数怎么用，索引是函数名；

　　2）想要实现某个目的，不太清楚有什么函数能实现，索引是函数的作用；

　　3）想实现某个较为复杂的目的，不太清楚组合哪些函数能实现，这里通常涉及多个函数，索引是使用案例。

　　所以在后续汇总表中，前四列会体现这个问题。

　　本篇文章主要介绍几个部分：

　　1）数仓同步表与事务型数据库表的不同；

　　2）HQL语法；

　　3）工作中常用到的函数；

　　4）以使用案例作为函数作用名的案例汇总；

　　5）比较高阶的函数；

　　6）对照官网函数手册汇总的函数大全。

二、贴源层(ODS层-数仓同步表)

　　与MySQL这种事务型数据库不同的是，数据仓库是一个面向主题的（Subject Oriented）、集成的（Integrate）、相对稳定的（Non-Volatile）、反映历史变化（Time Variant）的数据集合，用于支持管理决策。前三个性质且不说，最后一个反映历史变化的是如何体现的呢？这在于同步线上事务表的方式。
　　线上表同步下来有三种方式，增量表、全量表、拉链表。
　　另外提一下，数仓都是在凌晨两三点T+1同步；数据表除了字段外，数仓还有个分区的概念，用于快速取某一块的数据，通常日期dt会作为分区，其他例如业务类型啥的也可以作为分区，分区可以理解为是一种方便快速取数的索引。查看表分区的语句是

show partitions 库名.表名

View Code

增量表

　　增量表即在第一天全量同步线上事务表到昨天的分区，之后每天只同步那些创建时间、更新时间是昨天的记录到昨天的分区。
　　如果某些表不会对记录进行更新，比如日志，进行增量同步就正常使用即可；

　　如果某些表会对记录进行更新，那么取当前状态就必须对记录主键ID按更新时间排序取最后一条记录；

　　取历史某一天快照就限制dt<=那天取最后一天记录；

　　取每天的状态就需要辅助日期表left join on 1=1 where 日期表.dt<=增量表.dt，再对记录按辅助日期表日期和更新时间排序，取辅助日期表日期的最后一条更新记录作为每天状态即可。

全量表

　　即每天都把线上事务表数据全量同步到昨天的分区。
　　取当前状态直接取昨天分区，取历史某一天快照就限制dt=那一天即可，取每一天状态限制dt在需要的一段时间即可。

拉链表

　　较难理解，其线上事务表通常是会对记录进行更新的。线下表通常有三个分区，dp、start_date（或dt）、end_date。dp有两个值，ACTIVE和EXPIRED，分别表示有效和过期，即该记录当前是有效的状态和该记录是过期的记录（历史的状态）；start_date表示该记录从哪一天开始有效，end_date表示该记录从哪一天无效。
　　拉链表在同步的第一天全量同步线上事务表(同步的第一天是无法保留该天之前的状态的)，之后的每天会把新纪录(一般创建时间是昨天)直接同步（dp=‘ACTIVE’ and start_date=昨天 and end_date='9999-12-31'），把老记录但有更新(通常创建时间更早，更新时间是昨天)的老记录设置为过期（dp=‘EXPIRED’ and end_date=昨天）并新增一条更新后记录（dp=‘ACTIVE’ and start_date=昨天 and end_date='9999-12-31'）。
　　所以拉链表取当前状态只需要限制dp=‘ACTIVE’(或者start_date <=昨天 and end_date >昨天)即可；

　　取历史某一天的状态限制start_date <=那一天 and end_date >那一天(这可能较难理解，start_date <=那一天表示去除这天之后同步的新记录，end_date >那一天表示去除这天之前过期的）；

　　取每一天状态就需要辅助日期表left join on 1=1 where 日期表.dt<=增量表.start_date and end_date>日期表.dt，以辅助表日期作为每天状态即可。

三、语法

略

四、常用函数

注：F&C指的是函数或使用案例，只是本文的设定。

**常用函数**
F&C	分类	作用	函数名	语法	参数	返回	理解	应用场景
F	通用	返回多个值中第一个非空值	coalesce	coalesce(expression_1, expression_2, ...,expression_n)	n个字符串或表达式	第一个非空值	返回参数中的第一个非空值；如果所有值都为NULL，那么返回NULL	1）不希望字段显示NULL，设一个默认值；2）一个目标值可能在多个地方，取出来多个值返回不为空的一个。
F	聚合	列值合并成一个集合(去重)	collect_set	collect_set (col)	某字段名	数组	将某列值去重后，以逗号分隔合并成一个数组	1）参加哪些活动组合、买过哪些商品组合、浏览过哪些模块组合的用户分布；2）用于手动计算活动重合率。
F	聚合	列值合并成一个集合(不去重)	collect_list	collect_list(col)	某字段名	数组	将某列值以逗号分隔合并成一个数组	1）用于聚合用户前n个行为路径
F	数组函数	对数组升序排序	sort_array	sort_array(Array<T>)	数组	数组	对数组升序排序	1）对collect_set出来的数组排序
F	数值函数	取余数	pmod	pmod(int a, int b),pmod(double a, double b)	被除数,除数	余数	返回正的a除以b的余数	1）在ab测试中辅助取用户尾号积偶数；2）取指定余数。
F	日期函数	返回下一个星期X对应的日期	next_day	next_day(string start_date, string day_of_week)	日期,指定星期X	下一个周几	返回日期的下一个星期X对应的日期，其中day_of_week参数可以是星期X英语的前2个、3个或全写的大写字符	1）hive给定函数getweek是周日-周六为一周，想要周一-周日为一周，只需指定下周一的日期即可(每周一至周日都会返回下一周的周一日期)。例如： date_sub(next_day('2019-03-03','MONDAY'),1)
F	文本函数	替换指定字符	regexp_replace	regexp_replace(string INITIAL_STRING, string PATTERN, string REPLACEMENT)	原字符串,被替换字符(支持正则),新字符串	替换后的字符串		例如： regexp_replace(json字段,'中文键名','key1')
F	文本函数	json格式取键值对的值	get_json_object	get_json_object(string json_string, string path)	json字符串,解析路径	解析出来的值	json格式就是Python里的字典格式，解析路径一般传入键名，多层json可类似if语句嵌套使用。	1）在mongo里是可以随意增加字段的，一般不是主要的字段会放在一个etc字段josn格式里，此时就需要解析json；2）解析路径支持字符、数字和下划线，有时候碰到键名是中文的，可先用regexp_replace(json字段,'中文键名','key1')替换成英文键名再解析。例如： get_json_object(practice,'$.userid')
F	日期函数	返回月的最后一天	last_day	last_day(string date)	日期字符串	日期	返回该日期所在月的最后一天	1）取月末最后一天分区，比如全量表需要去每个月最后一天分区例如；dt>=2018-01-01' and dt=last_day(dt)

五、使用案例

注：本节以“使用目的”作为索引写在“作用”一列上。

**使用案例**
F&C	分类	作用	函数名	语法	参数	返回	理解	应用场景
C	日期函数	返回星期几	pmod,datediff	pmod(datediff(dt, '2019-02-11'), 7)+1	'2019-02-11'是随意设置的某个比dt小的周一的日期,dt是指定的日期	1-7	首先datediff求出指定日期与之前某个周一的间隔日期，再除以7取余数得到0-6，最后+1即映射到周一-日	1）指定取星期几； 2）需要表头显示星期几
C	日期函数	时间戳返回日期格式	from_unixtime,cast	from_unixtime(cast(substring(updatetime,1,10) as bigint),'yyyy-MM-dd HH:mm:ss') as updatetime	updatetime是输入的时间戳列名	日期格式	from_unixtime(bigint unixtime[, string format]) 将bigint型时间戳转化为指定format的日期，cast将时间戳字符串转化成bigint型	1）经常遇到HDFS里存的时间格式是字符串型的时间戳，需要转为日期格式
C	数组	将数组转换成字符串输出	concat_ws	concat_ws(',',collect_set(某列))	数组，可以是collect_set产生的数组	字符串	collect_set返回的是数组["105750","1246345","907964"]，用concat_ws转换成字符串105750,1246345,907964	1）通常查询输出collect_set不会有问题，但查询结果直接写入中间表而对应的字段格式设置了string时，就会出现 no matching method for .udftostring with(array<string>) 报错。这是因为collect_set输出的实际是array，格式不匹配又不像输出txt自动转换成string，所以需要手动转换成字符串。
C	字符串	版本字符串转换成数值	split	split(app_version,'\.')[0]10000+split(app_version,'\.')[1]100+split(app_version,'\.')[2]>=70903	app_version	数值		1）版本通常是2.4.5的格式，但2.10.1字符串是比2.4.5小的，所以转出数值进行比较

六、高阶函数

1.窗口和分析型函数

窗口和分析型函数解决的是时间序列相关的系列问题，比如每个城市

2.复杂JSON解析

线上是mongo库时，同步到HDFS有时候会碰到复杂的数组和json结合的字段。常见的有几种：

数组-字典循环嵌套：

外层数组，数组的每个元素是一个字典，某个字典的某个键的值又是一个数组，如此重复

-- 假设有一张表，字段为 userid,cityid,json_string，其中json_string字段格式如下：

[{
"id": "5d9d4042f",
"InfoList": [{
"docId": "K_10110494593304"
}, {
"docId": "K_10100857624837"
}]
}]
-- 希望得到的结果是：
userid,cityid,json_string_id,json_string_InfoList,json_string_InfoList_docId

-- 解析方法
-- 单独使用explode只是将这个数组字段转为多行，通常需要连带其他字段一起，所以一般采用下面的方法
-- 此方法只是将InfoList解析出来了，要将docId解析出来还需要重复下面的方法，因为InfoList本身又是一个数组嵌套字典
--如果无法执行json_to_array，可能是因为hadoop里没加这个包，需要在select前设置类似这样的语句（具体语句询问数仓人员）

add jar /data/hadoop/hive_udf/hive_udf.jar;
create temporary function json_to_array as 'udf.JsonArrayFromJsonUDF';

SELECT
a1.dt,
a1.userid,
get_json_object(json_strings,'$.id')as id,
get_json_object(json_strings,'$.InfoList')as InfoList
FROM
(
select dt,userid,json_string FROM table1
where dt='2019-10-09'
)a1 lateral view explode(from_json(json_string,'array<string>')) json_string as json_strings

字典-字典循环嵌套：

最外层就是字典，里面有两个键值tab1和tab2；tab1和tab2又是字典，tab1字典键为tab1_1，tab2字典有两个键tab2_1和tab2_2；... ...

-- 假设有一张表，字段为 userid,cityid,dict_string，其中dict_string字段格式如下：

{
    "tab1": {
        "tab1_1": {
            "result_id": "d0b5f52fa4998d",
            "result_1": false
        }
    },
    "tab2": {
        "tab2_1": {
            "result_id": "d0b5f52fa4998d",
            "result_1": false
        },
        "tab2_2": {
            "result_id": "d0b5f52fa4998d",
            "result_1": false
        }
    }
}
-- 希望得到的结果：

userid,cityid,dict_1key(第一层tab1或tab2名称）,dict_2key(第二层tab1_1或tab2_1或tab2_2名称）,result_id,result_1

-- 解析方法
-- 如下，用了两次嵌套，单次流程如下
-- 1.首先map_values将字符串转成以最外层字典各个元素###分割的字符串，且键值以___连接；
-- 2.然后用split( ,'###')将最外层字典的各个元素转成逗号分隔的数组；
-- 3.再lateral view explode将各个元素转为多行；
-- 4.此时得到最外层各个元素在每一行的数据，再用split( ,'___')即可分割出第一层的键和值；
-- 5.其中第二层的值再重复上述操作；
-- 6.注意，第一步中会多出一个空元素，导致result_id多存在空货空字符串的情况，where限制一下。

--如果无法执行map_values，可能是因为hadoop里没加这个包，需要在select前设置类似这样的语句（具体语句询问数仓人员）

add jar /etc/hive/auxlib/udf-1.0-SNAPSHOT.jar;
create temporary function map_values as 'udf.KeyValuesUDF';

SELECT
t2.userid,
t2.cityid,
t2.dict_1key,
split(dict_1value,'___')[0] as dict_2key,
get_json_object(split(dict_1value,'___')[1],'$.result_id') as result_id,
get_json_object(split(dict_1value,'___')[1],'$.result_1') as result_1
FROM
(
SELECT
t1.userid,
t1.cityid,
split(dict_info,'___')[0] as dict_1key,
split(map_values(split(dict_info,'___')[1]),'###') as dict_1value_string
FROM
(
select
userid,
cityid,
split(map_values(dict_string),'###') as dict_array
from table2
where dt='2019-10-15'
)t1 lateral view explode(dict_array) dict_array as dict_info
)t2 lateral view explode(dict_1value_string) dict_1value_string as dict_1value
where split(dict_1value,'___')[0]!=''
and split(dict_1value,'___')[0] is not NULL

参考资料：[2] [3][4]

七、函数大全

英文网址：https://cwiki.apache.org/confluence/display/Hive/LanguageManual+UDF

中文参考：https://www.cnblogs.com/MOBIN/p/5618747.html

后续会整理出中文大全。

八、案例附录

九、常见报错原因

FAILED: SemanticException [Error 10007]: Ambiguous column reference lessondate in a0

在a0这个子查询中存在模糊的列。通常是在该子查询中，同一个列名出现了多次，check一下是不是，是的话该别名的别名一下就好。

Diagnostic Messages for this Task:
Error: java.lang.RuntimeException: org.apache.hadoop.hive.ql.metadata.HiveException: Hive Runtime Error while processing row [Error getting row data with exception java.lang.ArrayIndexOutOfBoundsException: 755

这个SQL开始是正常执行，死在最后一个job，最后一个job是个窗口函数，看报错信息是ArrayIndexOutOfBoundsException数组溢出。同样的SQL，在spark执行正常，只在hive执行出错。——目前原因未知。

Error in query: cannot resolve 'map_values(某表名.`某列名`)' due to data type mismatch: argument 1 requires map type, however, '某表名.`某列名`' is of string type.; line 27 pos 48;

这是spark报错，hive执行正常。报错信息是map_values的参数1要求是map类型，但给定的这个是字符串类型。加了个str_to_map，又报错说要的是string类型，而我给的是array<string>类型，，，嗯真难伺候。——目前原因未知

参考资料：

[1].会飞的猪仔.Hive详解

[2].yugouai.HIVE 窗口及分析函数应用场景

[3].张吉的博客.Hive 窗口与分析型函数

[4].官网.LanguageManual WindowingAndAnalytics （讲真，官网太水了）

Without summary,you can't master it.