HIVE 处理json结构数据

HIVE 处理json结构数据 

Table of Contents

• 1. 问题
• 2. 知识点
  • 2.1. lateral view
  • 2.2. explode
  • 2.3. json_tuple
• 3. 示例

1 问题

宽表存储数据，必然会面临一个问题：一对多，数据是N倍的存储。将会多占用N倍空间。而这些源数据入库的时候，在文件中存放时，也可能使用json格式。

遇到这种情况，使用json_tuple来解析json数据是一个非常不错的方法。如果只是查询json中某一个Key的值，那么使用get_json_object是个更好的选择。

2 知识点

 

2.1 lateral view

Lateral view 通常和split， explode等udtf一起封装使用，它能够将一行数据拆分成多行数据，在此基础上可以对拆分后的数据进行聚合.

该函数的作用就是通过udtf相关函数把一行拆分成一行或者多行的结果进行聚合，产生一个支持别名表的虚拟表。

语法结构如下：

ATERAL VIEW udtf(expression) alias_table_name AS columns(',', columns)*

2.2 explode

explode 将数组、列表、MAP中每个元素转换为同一列的不同行，可以理解为行转列，处理的结果以表格的形式输出。explode 只能处理数组（列表）或者map结构。 使用方法非常简单:explode(数据或者列表或者MAP结构数据)。

比如数组: [1,2,3].可以转换为

1
2
3

而map结构，比如字典：{'a':1,'b':2,'c':3} 可以转换成：

a	1
b	2
c	3

各位可以执行下面两个查询语句块，了解explode的效果：

select array(1,2,3);
select explode(array(1,2,3));

下面通过str_to_map¹ 来实现map结构

select str_to_map(concat('a:',1,'&b:',2,'&c:',3),'&',':');
select str_to_map(concat('a:',1,'^b:',2,'^c:',3),'\\^',':');
select explode(str_to_map(concat('a:',1,'&b:',2,'&c:',3),'&',':'));

2.3 json_tuple

json_tuple 从json格式数据中，通过指定的keys 来读取相应的values. 读取的结果以表格的形式输出。语法结构如下：

使用方法如下：json_tuple(数据源，json中的key1,key2,….). 比如：

select json_tuple('{"a":1,"b":2,"c":3}','a','b');

3 示例

下面是将json数据转换成表结构的示例。

• 测试

  -- 建表
  drop table if exists jsontest;
  
  CREATE TABLE IF NOT EXISTS jsonTest
  (teacher_name varchar(10),
  major varchar(10),
  students_info string comment "学生信息"
  )
  comment "学生课程信息"
  row format serde 'org.apache.hive.hcatalog.data.JsonSerDe'
  LOCATION
    'hdfs://nameservice1/user/hive/warehouse/bigdata.db/jsontest';
  
  insert into jsontest values
  ('t1','语文','{"grade":1,"info":{"name":"xinzi","age":14,"sex":"M"}}|{"grade":3,"info":{"name":"lisi","age":14,"sex":"M"}}'),
  ('t2','maths','{"grade":2,"info":{"name":"zhangs","age":14,"sex":"F"}}')
  ;
  
  -- 查看已有数据
  select row_number() over() , a.* from jsontest a;
  
  
  
  -- 创建视图
  drop view if exists v_jsontest;
  create view if not exists v_jsontest
  as
  select teacher_name,major,a.grade,b.name,b.age,b.gender from jsontest lateral view explode(split(students_info,'\\|')) stabcdefghi as stin
  lateral view json_tuple(stin,'grade','info') a as grade,info
  lateral view json_tuple(a.info,'name','age','sex') b as name,age,gender;
  

• 原表数据

  hive> select * from jsontest;
  OK
  t1	语文	{"grade":1,"info":{"name":"xinzi","age":14,"sex":"M"}}|{"grade":3,"info":{"name":"lisi","age":14,"sex":"M"}}
  t2	maths	{"grade":2,"info":{"name":"zhangs","age":14,"sex":"F"}}
  Time taken: 0.054 seconds, Fetched: 2 row(s)
  

• 转换后结果

  hive> select * from v_jsontest;
  ..........
  OK
  t1	语文	1	xinzi	14	M
  t1	语文	3	lisi	14	M
  t2	maths	2	zhangs	14	F
  Time taken: 12.933 seconds, Fetched: 3 row(s)
  

Footnotes:

¹

关于map，可以通过函数str_to_map来实现，也可以在建表时定义。这里使用str_to_map 来进行测试. str_to_map(字符串参数, 分隔符1, 分隔符2),使用两个分隔符将文本拆分为键值对。 分隔符1将文本分成K-V对，分隔符2分割每个K-V对。对于分隔符1默认分隔符是 ','，对于分隔符2默认分隔符是 '='。

Author: halberd.lee

Created: 2020-04-09 Thu 13:48

Validate