Hive解析字段为Json的数据

川长思鸟来 2021-09-27 11:30 522阅读 0赞

HIVE直接读入json的函数有两个：

（1）get\_json\_object(string json\_string, string path)

返回值: string

说明：解析json的字符串json\_string,返回path指定的内容。如果输入的json字符串无效，那么返回NULL。

举例：

hive> select get_json_object(‘{“store”:{“fruit”:[{"weight":8,"type":"apple"},{"weight":9,"type":"pear"}], “bicycle”:{“price”:19.95,”color”:”red”}}, “email”:”amy@only_for_json_udf_test.net”, “owner”:”amy” } ‘,’$.owner’) from dual;

结果：amy

这个函数每次只能返回一个数据项。

（2）json\_tuple(jsonStr, k1, k2, ...)

参数为一组键k1，k2……和JSON字符串，返回值的元组。该方法比 get\_json\_object 高效，因为可以在一次调用中输入多个键

select a.timestamp, b.*
    from log a lateral view json_tuple(a.appevent, 'eventid', 'eventname') b as f1, f2;

处理数据样例：

{
    	"GPS_LAT": 39.8965125,
    	"GPS_LONG": 116.3493225,
    	"GPS_SPEED": 20.9993625,
    	"GPS_STATE": "A",
    	"GPS_TIME": "2014-01-02 00:00:16",
    	"IMEI": "508597",
    	"after_oxygen_sensor": 132,
    	"air_condion_state": 3,
    	"bdoneNo_after_mileage": 0,
    	"bdoneNo_zero_mileage": 8044,
    	"db_speed": 22,
    	"direction_angle": 358.2585,
    	"front_oxygen_sensor": 64,
    	"instant_fuel": 233,
    	"speed": 1210,
    	"torque": 33,
    	"total_fuel": 0
    }

处理HIVE语句：

create table 2014jrtest as select json_tuple(line,'GPS_LAT','GPS_LONG','GPS_SPEED','GPS_STATE','GPS_TIME','IMEI','after_oxygen_sensor','air_condion_state','bdoneNo_after_mileage','bdoneNo_zero_mileage','db_speed','direction_angle','front_oxygen_sensor','instant_fuel','speed','torque','total_fuel') from 2014test;

作者：石晓文的学习日记  
链接：https://www.jianshu.com/p/8a868d2ac307  
來源：简书  
简书著作权归作者所有，任何形式的转载都请联系作者获得授权并注明出处。