Hive高阶函数之行转列&JSON数据解析

---

如果想看列转行请点击以下链接，此文章中包含了列转行
HIVE的高频面试UDTF函数

1. 行转列

行转列, 指的 多行转换为一列数据

涉及到的函数

concat(str1,str2,…) – 字段或字符串拼接
concat_ws(sep, str1,str2) – 以分隔符拼接每个字符串
collect_set(col) – 将某字段的值进行去重汇总，产生array类型字段
collect_list(col) – 将某字段的值进行汇总(不去重)，产生array类型字段

1.1 业务需求

---

• 文本数据

10	CLARK
10	KING
10	MILLER
20	SMITH
20	JONES
20	SCOTT
20	ADAMS
20	FORD
30	ALLEN
30	WARD
30	MARTIN
30	BLAKE
30	TURNER
30	JAMES

• 想要实现的结果

10      CLARK|KING|MILLER
20      SMITH|JONES|SCOTT|ADAMS|FORD
30      ALLEN|WARD|MARTIN|BLAKE|TURNER|JAMES

---

1.2 业务实现

在服务器/root/hivedata下新建dept1.txt,并把数据写进去

#进入目录，编写并保存文件
cd /root/hivedata
vim dept1.txt

按i，把上文1.1中文本数据粘贴进来，wq保存。
在Hive中建dept1表

create table day04_hive.dept1
(
    dept_id   int comment 'dept的id',
    dept_name string comment 'dept姓名'
) comment 'dept1表'
    row format delimited
        fields terminated by '\t';

从本地加载数据

load data local inpath '/root/hivedata/dept1.txt' into table day04_hive.dept1;

校验数据：select * from dept1;

我们想要把dept_id 去重，可以使用group by,想要实现把dept_name列合并的，可以使用collect_set()函数,中间加上‘|’，代码如下

select
       dept_id,
       concat_ws('|',collect_set(dept_name)) as dept_name
from day04_hive.dept1
group by dept_id ;

2. JSON解析处理

json本质上就是一个有格式的字符串. json常见的格式主要有二种:
一种格式: {key:value,key:value}
另一种格式: [值1,值2,值3]
如何解析JSON呢？

2.1 业务需求

---

• 文本数据

{"device":"device_30","deviceType":"kafka","signal":98.0,"time":1616817201390}
{"device":"device_40","deviceType":"route","signal":99.0,"time":1616817201887}
{"device":"device_21","deviceType":"bigdata","signal":77.0,"time":1616817202142}
{"device":"device_31","deviceType":"kafka","signal":98.0,"time":1616817202405}
{"device":"device_20","deviceType":"bigdata","signal":12.0,"time":1616817202513}
{"device":"device_54","deviceType":"bigdata","signal":14.0,"time":1616817202913}
{"device":"device_10","deviceType":"db","signal":39.0,"time":1616817203356}
{"device":"device_94","deviceType":"bigdata","signal":59.0,"time":1616817203771}
{"device":"device_32","deviceType":"kafka","signal":52.0,"time":1616817204010}

字段的解释：

• 设备名称【device】

• 设备类型【deviceType】

• 信号强度【signal】

• 信号发送时间【time】

• 想要实现的效果
  

---

2.2 业务实现

在服务器/root/hivedata下新建dept1.txt,并把数据写进去

#进入目录，编写并保存文件
cd /root/hivedata
vim device.json

按i，把上文2.1中文本数据粘贴进来，wq保存。
在Hive中建device_json表

create table  device_json(
    json string
);

从本地加载数据

load data local inpath '/root/hivedata/device.json' into table day04_hive.device_json;

校验数据：select * from device_json;

方案1：get_json_object (udf)

select 
    get_json_object(json,'$.device') as device, 
    get_json_object(json,'$.deviceType') as deviceType,
    get_json_object(json,'$.signal') as signal,
    get_json_object(json,'$.time') as timestr
from device_json;

存在弊端:
执行多次重复的函数, 重复的数据, 需要加载多次json数据

方案2： json_tuple(udtf)

select 
   json_tuple(json,'device','deviceType','signal','time')
from device_json;

建议将以上的SQL更改为:
select 
   device,
   deviceType,
   signal,
   timestr
from device_json lateral view json_tuple(json,'device','deviceType','signal','time') t1 as device,deviceType,signal,timestr;

方案3：建表时自动解析

create table  device_json_1(
    device string,
    deviceType string ,
    signal string,
    timestr string
)
row format SERDE 'org.apache.hive.hcatalog.data.JsonSerDe';

SerDe 是 Serializer（序列化器）和 Deserializer（反序列化器）的合成词。
加载数据

load data local inpath '/root/hivedata/device.json' into table device_json_1;

验证数据

我们发现最后列是空值，这种情况是因为什么？

原因: org.apache.hive.hcatalog.data.JsonSerDe 在查询数据的时候, 通过这个类对json进行解析, 解析的时候, 会按照表的字段名称当做key 寻找json中value数据, 所以要求key必须和json中key保持一致, 否则会映射失败

所以修改建表语句中信号发送时间
timestr string --> time string
就可以了。

如果有帮助到你，请点赞收藏