标签： HIVE

接最近写HIVE SQL的一点笔记【1】

多行数据字段连接成一个字段的方法，需配合group by 使用

select concat_ws(',',collect_list(oid)) as oid_list,tid 
from HIVE_TABLE_NAME_XXXX 
group by tid

执行结果效果如下

使用`collect_list`获取相同tid行的oid字段，并使用 `concat_ws` 函数拼接这个集合，中间用此处指定的字符串“，”逗号拼接。

collect_list会忽略集合中null值，如果集合内的所有值均为null，那么此时结果是一个空数组。

如果要对结果去重，可以使用collect_set替换collect_list方法

再有就是，只有group by的字段可以直接在select后面使用，其他字段可以使用MAX函数对字段取值，这里和Mysql的 any_value函数有点相似。

如果我们需要得到结果是一个JSON字符串，同样的可以这样操作

select concat('[',concat_ws(',',collect_list(info)),']') as order_json,tid 
from
	(
      select tid,concat('{"tid":',tid,',"oid":',oid,'}') as info
		from HIVE_TABLE_NAME_XXXX 
	)
group by tid

可以得到结果如下，order_json即为所需的json字符串

若需要对同一个tid内的oid按照指定顺序排序，则需要借助

DISTRIBUTE BY  column1 SORT BY column1,column2 desc

语句，我们试下改写下上面的sql

select concat('[',concat_ws(',',collect_list(info)),']') as order_json,tid 
from
	(
      select tid,concat('{"tid":',tid,',"oid":',oid,'}') as info
		from HIVE_TABLE_NAME_XXXX 
      DISTRIBUTE BY tid SORT BY tid,oid DESC
	)
group by tid

查询结果如下

可以看到确实按照oid降序排序了，这里DISTRIBUTE BY的作用是按照指定的字段分区，并根据后面的SORT BY排序

可以参见https://blog.csdn.net/fantasticqiang/article/details/80769316

如果排序是升序的话，可以用cluster by语句替代

1．DISTRIBUTE BY的分区规则是根据分区字段的hash码与reduce的个数进行模除后，余数相同的分到一个区。

2．Hive要求DISTRIBUTE BY语句要写在SORT BY语句之前。

几个点，一个个来

字段内容为JSON字符串，ETL过程中需要解析JSON字符串，并取出解析后的对象的某个值

处理方法，使用get_json_object()函数、get_json_object(string json_string, string path)。

函数可以接收两个参数，第一个参数为需要解析的字段或者字符串，第二个字段为解析后取值的表达式，表达式固定以`$`符号开头。

举个栗子

直接查对象：

select get_json_object('{"abc":123}','$.abc') as result;

返回结果：

查询数组：

select get_json_object('["abc","bcd"]','$[1]') as result

返回结果：

也可以组合嵌套使用:

select get_json_object('["abc",{"bcd":123}]','$[1].bcd') as result

select get_json_object('{"def":["abc",{"bcd":123}]}','$.def[1].bcd') as result

如果查询一个不存在的key，则返回 null，比如

select get_json_object('{"aaa":["abc",{"bcd":123}]}','$.def[1].bcd') as result

或者解析的字符串格式不对，无法被解析成JSON对象，比如

select get_json_object('{"aaa}]}','$.def[1].bcd') as result