Hive建表指定JsonSerde：解析Json格式日志文件

数据准备

{"student":{"name":"xiaowang","age":11,"sex":"M"},"class":{"book":"语文","level":2,"score":81},"teacher":{"name":"t1","class":"语文"}}
{"student":{"name":"xiaoming","age":12,"sex":"M"},"class":{"book":"语文","level":2,"score":82},"teacher":{"name":"t2","class":"语文"}}
{"student":{"name":"xiaolan","age":13,"sex":"M"},"class":{"book":"语文","level":2,"score":83},"teacher":{"name":"t3","class":"语文"}}
{"student":{"name":"xiaohei","age":14,"sex":"M"},"class":{"book":"语文","level":2,"score":84},"teacher":{"name":"t1","class":"语文"}}
{"student":{"name":"xiaobai","age":15,"sex":"M"},"class":{"book":"语文","level":2,"score":86},"teacher":{"name":"t2","class":"语文"}}
{"student":{"name":"xiaohong","age":16,"sex":"M"},"class":{"book":"语文","level":2,"score":87},"teacher":{"name":"t3","class":"语文"}}

建表语句

建表时指定映射类：org.apache.hive.hcatalog.data.JsonSerDe

create external table test.test_student_json(
  student map<string,string> comment "学生信息",
  class map<string,string> comment "课程信息",
  teacher map<string,string> comment "授课老师信息")
row format serde 
'org.apache.hive.hcatalog.data.JsonSerDe'
location 'path';

查询数据

select * from test_student_json;

 

这样的数据我们是无法使用的，必须转化为我们常见的二维表格的字段，可以用 create + select语句建表

create table if not exists student as 
select   student['name'] ,student['age'],student['sex']
from test_student_json;