hive解析json

最新推荐文章于 2023-04-11 11:46:24 发布
最新推荐文章于 2023-04-11 11:46:24 发布
阅读量227 收藏
点赞数 1
分类专栏： hive 文章标签： json hive
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/weixin_47699191/article/details/119390510
版权 
-- json数据
{"OTHER_INFO":{"MOBILE_ADDR":"四川省内江市潘咱陛路1233号闷羞小区19单元484室","DATA2":{"CERT_ADDR":"宁夏回族自治区固原市薄精路3992号裸糕氏小区9单元1092室"},"DATA1":{"MOBILE_NUM":"15360425546"},"MOBILE_NUM":"15902875723"},"APP_NO":"RVVG5252500393","CHANNEL_SOURCE":"微信","BUSINESS_CODE":"TEST01","STEP_TYPE":"002","SESSION_ID":"YLYC99019292814246548503","CUST_INFO":{"GENDER_CD":"2","NAME_EN":"Edmund","MAIL":"m74e5czt2y@rvhts.pdu","CERT_TYPE":"2","CERT_NO":"446760199707032392","IDEN_VALID_DT":"99991231","CUST_NAME":"独孤拘害","AGE":"5","MOBILE":"15744158190"},"SCENARIO_INFO":{"ADDRESS":"云南省临沧市猫逐还路1265号兢顶朔小区3单元376室","CARD_NUM":"6223887916967795"},"APPLY_TIME":"1628067497730"}
{"OTHER_INFO":{"MOBILE_ADDR":"青海省海北藏族自治州砰役厩路3499号唇瞪小区9单元491室","DATA2":{"CERT_ADDR":"安徽省淮南市蚂鄙系路2476号时究小区10单元2465室"},"DATA1":{"MOBILE_NUM":"15210663115"},"MOBILE_NUM":"15290171152"},"APP_NO":"FZGR9373403233","CHANNEL_SOURCE":"微信","BUSINESS_CODE":"TEST01","STEP_TYPE":"002","SESSION_ID":"KYTD89059389151000106477","CUST_INFO":{"GENDER_CD":"2","NAME_EN":"Frederica","MAIL":"y0jogwn7vv@bnryy.4cp","CERT_TYPE":"2","CERT_NO":"423047199210041239","IDEN_VALID_DT":"99991231","CUST_NAME":"尉迟蛀","AGE":"5","MOBILE":"13857964198"},"SCENARIO_INFO":{"ADDRESS":"山东省济宁市迂佬藻路1489号展镁小区7单元1617室","CARD_NUM":"6229127368018050"},"APPLY_TIME":"1628067579234"}
{"OTHER_INFO":{"MOBILE_ADDR":"浙江省杭州市藻极路1103号缄奉小区5单元1080室","DATA2":{"CERT_ADDR":"甘肃省天水市褥茨路735号废输剑小区14单元490室"},"DATA1":{"MOBILE_NUM":"15065179482"},"MOBILE_NUM":"13427757308"},"APP_NO":"CQEL4819162613","CHANNEL_SOURCE":"APP","BUSINESS_CODE":"TEST01","STEP_TYPE":"004","SESSION_ID":"LECY94935395143750820224","CUST_INFO":{"GENDER_CD":"1","NAME_EN":"Ursula","MAIL":"ximjrvukl5@43sw4.xvg","CERT_TYPE":"1","CERT_NO":"141043198504099090","IDEN_VALID_DT":"99991231","CUST_NAME":"高晚","AGE":"1","MOBILE":"13658608487"},"SCENARIO_INFO":{"ADDRESS":"河北省唐山市圭布懂路4597号骡昏小区8单元544室","CARD_NUM":"6224871441029643"},"APPLY_TIME":"1628067745581"}
-- hive get_json_objec解析json练习
CREATE TABLE json_t( lines string ) comment'json测试表';

load data local inpath '/root/json.txt' overwrite into table json_t;

0: jdbc:hive2://wxt01:10000> select lines from json_t;
OK
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--+
|                                                                                                                                                                                                                                                                                                                            lines                                                                                                                                                                                                                                                                                                                             |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--+
| {"OTHER_INFO":{"MOBILE_ADDR":"四川省内江市潘咱陛路1233号闷羞小区19单元484室","DATA2":{"CERT_ADDR":"宁夏回族自治区固原市薄精路3992号裸糕氏小区9单元1092室"},"DATA1":{"MOBILE_NUM":"15360425546"},"MOBILE_NUM":"15902875723"},"APP_NO":"RVVG5252500393","CHANNEL_SOURCE":"微信","BUSINESS_CODE":"TEST01","STEP_TYPE":"002","SESSION_ID":"YLYC99019292814246548503","CUST_INFO":{"GENDER_CD":"2","NAME_EN":"Edmund","MAIL":"m74e5czt2y@rvhts.pdu","CERT_TYPE":"2","CERT_NO":"446760199707032392","IDEN_VALID_DT":"99991231","CUST_NAME":"独孤拘害","AGE":"5","MOBILE":"15744158190"},"SCENARIO_INFO":{"ADDRESS":"云南省临沧市猫逐还路1265号兢顶朔小区3单元376室","CARD_NUM":"6223887916967795"},"APPLY_TIME":"1628067497730"}    |
| {"OTHER_INFO":{"MOBILE_ADDR":"青海省海北藏族自治州砰役厩路3499号唇瞪小区9单元491室","DATA2":{"CERT_ADDR":"安徽省淮南市蚂鄙系路2476号时究小区10单元2465室"},"DATA1":{"MOBILE_NUM":"15210663115"},"MOBILE_NUM":"15290171152"},"APP_NO":"FZGR9373403233","CHANNEL_SOURCE":"微信","BUSINESS_CODE":"TEST01","STEP_TYPE":"002","SESSION_ID":"KYTD89059389151000106477","CUST_INFO":{"GENDER_CD":"2","NAME_EN":"Frederica","MAIL":"y0jogwn7vv@bnryy.4cp","CERT_TYPE":"2","CERT_NO":"423047199210041239","IDEN_VALID_DT":"99991231","CUST_NAME":"尉迟蛀","AGE":"5","MOBILE":"13857964198"},"SCENARIO_INFO":{"ADDRESS":"山东省济宁市迂佬藻路1489号展镁小区7单元1617室","CARD_NUM":"6229127368018050"},"APPLY_TIME":"1628067579234"}  |
| {"OTHER_INFO":{"MOBILE_ADDR":"浙江省杭州市藻极路1103号缄奉小区5单元1080室","DATA2":{"CERT_ADDR":"甘肃省天水市褥茨路735号废输剑小区14单元490室"},"DATA1":{"MOBILE_NUM":"15065179482"},"MOBILE_NUM":"13427757308"},"APP_NO":"CQEL4819162613","CHANNEL_SOURCE":"APP","BUSINESS_CODE":"TEST01","STEP_TYPE":"004","SESSION_ID":"LECY94935395143750820224","CUST_INFO":{"GENDER_CD":"1","NAME_EN":"Ursula","MAIL":"ximjrvukl5@43sw4.xvg","CERT_TYPE":"1","CERT_NO":"141043198504099090","IDEN_VALID_DT":"99991231","CUST_NAME":"高晚","AGE":"1","MOBILE":"13658608487"},"SCENARIO_INFO":{"ADDRESS":"河北省唐山市圭布懂路4597号骡昏小区8单元544室","CARD_NUM":"6224871441029643"},"APPLY_TIME":"1628067745581"}            |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--+
3 rows selected (0.1 seconds)
0: jdbc:hive2://wxt01:10000> 

-- get_json_object()
SELECT
get_json_object(lines,'$.APP_NO') as APP_NO,
get_json_object(lines,'$.CHANNEL_SOURCE') as CHANNEL_SOURCE,
get_json_object(lines,'$.SESSION_ID') as SESSION_ID
FROM json_t
;
+-----------------+-----------------+---------------------------+--+
|     app_no      | channel_source  |        session_id         |
+-----------------+-----------------+---------------------------+--+
| RVVG5252500393  | 微信              | YLYC99019292814246548503  |
| FZGR9373403233  | 微信              | KYTD89059389151000106477  |
| CQEL4819162613  | APP             | LECY94935395143750820224  |
+-----------------+-----------------+---------------------------+--+

-- json_tuple
select
APP_NO,
CHANNEL_SOURCE,
SESSION_ID
from json_t
lateral view json_tuple(lines,'APP_NO','CHANNEL_SOURCE','SESSION_ID') b as APP_NO,CHANNEL_SOURCE,SESSION_ID;
+-----------------+-----------------+---------------------------+--+
|     app_no      | channel_source  |        session_id         |
+-----------------+-----------------+---------------------------+--+
| RVVG5252500393  | 微信              | YLYC99019292814246548503  |
| FZGR9373403233  | 微信              | KYTD89059389151000106477  |
| CQEL4819162613  | APP             | LECY94935395143750820224  |
+-----------------+-----------------+---------------------------+--+


SELECT
get_json_object(lines,'$.OTHER_INFO.MOBILE_ADDR') as MOBILE_ADDR,
get_json_object(lines,'$.OTHER_INFO.DATA2.CERT_ADDR') as CERT_ADDR,
get_json_object(lines,'$.OTHER_INFO.DATA1.MOBILE_NUM') as MOBILE_NUM_one,
get_json_object(lines,'$.OTHER_INFO.MOBILE_NUM') as MOBILE_NUM_two,
get_json_object(lines,'$.APP_NO') as APP_NO,
get_json_object(lines,'$.CHANNEL_SOURCE') as CHANNEL_SOURCE,
get_json_object(lines,'$.BUSINESS_CODE') as BUSINESS_CODE,
get_json_object(lines,'$.STEP_TYPE') as STEP_TYPE,
get_json_object(lines,'$.SESSION_ID') as SESSION_ID,
get_json_object(lines,'$.APPLY_TIME') as APPLY_TIME,
get_json_object(lines,'$.CUST_INFO.GENDER_CD') as GENDER_CD,
get_json_object(lines,'$.CUST_INFO.NAME_EN') as NAME_EN,
get_json_object(lines,'$.CUST_INFO.MAIL') as MAIL,
get_json_object(lines,'$.CUST_INFO.CERT_TYPE') as CERT_TYPE,
get_json_object(lines,'$.CUST_INFO.CERT_NO') as CERT_NO,
get_json_object(lines,'$.CUST_INFO.IDEN_VALID_DT') as IDEN_VALID_DT,
get_json_object(lines,'$.CUST_INFO.CUST_NAME') as CUST_NAME,
get_json_object(lines,'$.CUST_INFO.AGE') as AGE,
get_json_object(lines,'$.CUST_INFO.MOBILE') as MOBILE,
get_json_object(lines,'$.SCENARIO_INFO.ADDRESS') as ADDRESS,
get_json_object(lines,'$.SCENARIO_INFO.CARD_NUM') as CARD_NUM
FROM json_t
;

+---------------------------------+----------------------------------+-----------------+-----------------+-----------------+-----------------+----------------+------------+---------------------------+----------------+------------+------------+-----------------------+------------+---------------------+----------------+------------+------+--------------+------------------------------+-------------------+--+
|           mobile_addr           |            cert_addr             | mobile_num_one  | mobile_num_two  |     app_no      | channel_source  | business_code  | step_type  |        session_id         |   apply_time   | gender_cd  |  name_en   |         mail          | cert_type  |       cert_no       | iden_valid_dt  | cust_name  | age  |    mobile    |           address            |     card_num      |
+---------------------------------+----------------------------------+-----------------+-----------------+-----------------+-----------------+----------------+------------+---------------------------+----------------+------------+------------+-----------------------+------------+---------------------+----------------+------------+------+--------------+------------------------------+-------------------+--+
| 四川省内江市潘咱陛路1233号闷羞小区19单元484室     | 宁夏回族自治区固原市薄精路3992号裸糕氏小区9单元1092室  | 15360425546     | 15902875723     | RVVG5252500393  | 微信              | TEST01         | 002        | YLYC99019292814246548503  | 1628067497730  | 2          | Edmund     | m74e5czt2y@rvhts.pdu  | 2          | 446760199707032392  | 99991231       | 独孤拘害       | 5    | 15744158190  | 云南省临沧市猫逐还路1265号兢顶朔小区3单元376室  | 6223887916967795  |
| 青海省海北藏族自治州砰役厩路3499号唇瞪小区9单元491室  | 安徽省淮南市蚂鄙系路2476号时究小区10单元2465室     | 15210663115     | 15290171152     | FZGR9373403233  | 微信              | TEST01         | 002        | KYTD89059389151000106477  | 1628067579234  | 2          | Frederica  | y0jogwn7vv@bnryy.4cp  | 2          | 423047199210041239  | 99991231       | 尉迟蛀        | 5    | 13857964198  | 山东省济宁市迂佬藻路1489号展镁小区7单元1617室  | 6229127368018050  |
| 浙江省杭州市藻极路1103号缄奉小区5单元1080室      | 甘肃省天水市褥茨路735号废输剑小区14单元490室       | 15065179482     | 13427757308     | CQEL4819162613  | APP             | TEST01         | 004        | LECY94935395143750820224  | 1628067745581  | 1          | Ursula     | ximjrvukl5@43sw4.xvg  | 1          | 141043198504099090  | 99991231       | 高晚         | 1    | 13658608487  | 河北省唐山市圭布懂路4597号骡昏小区8单元544室   | 6224871441029643  |
+---------------------------------+----------------------------------+-----------------+-----------------+-----------------+-----------------+----------------+------------+---------------------------+----------------+------------+------------+-----------------------+------------+---------------------+----------------+------------+------+--------------+------------------------------+-------------------+--+

select
get_json_object('{"name":"张三","age":"44"}','$.name') as name,
get_json_object('{"name":"张三","age":"44"}','$.age') as age
;
+-------+------+--+
| name  | age  |
+-------+------+--+
| 张三    | 44   |
+-------+------+--+

-- json格式不正确或不完整的放回null
select
get_json_object('{"name":"张三","age":""}','$.name') as name,
get_json_object('{"name":"张三","age":""}','$.age') as age
;
+-------+------+--+
| name  | age  |
+-------+------+--+
| 张三    |      |
+-------+------+--+

select
get_json_object('{"name":}','$.name') as name
;
+-------+--+
| name  |
+-------+--+
| NULL  |
+-------+--+

select
get_json_object('{"name":null}','$.name') as name
;
+-------+--+
| name  |
+-------+--+
| NULL  |
+-------+--+

select
get_json_object('{"name":张三}','$.name') as name
;
+-------+--+
| name  |
+-------+--+
| NULL  |
+-------+--+

select
get_json_object('{"name":zhangsang}','$.name') as name
;

select
get_json_object('{"age":4}','$.age') as age
;
+------+--+
| age  |
+------+--+
| 4    |
+------+--+

-- json不完整的返回null
select
get_json_object('{"name":"张三","age":"44"','$.name') as name,
get_json_object('{"name":"张三","age":"44"}','$.age') as age
;
+-------+------+--+
| name  | age  |
+-------+------+--+
| NULL  | 44   |
+-------+------+--+

-- json中有数组
{"code": 200,"msg": "OK","data": ["3148120404262382","1443363211304208","4322133941384257"]}

select
get_json_object('{"code": 200,"msg": "OK","data": ["3148120404262382","1443363211304208","4322133941384257"]}','$.code') as code,
get_json_object('{"code": 200,"msg": "OK","data": ["3148120404262382","1443363211304208","4322133941384257"]}','$.msg') as msg,
get_json_object('{"code": 200,"msg": "OK","data": ["3148120404262382","1443363211304208","4322133941384257"]}','$.data') as data
;
+-------+------+-------------------------------------------------------------+--+
| code  | msg  |                            data                             |
+-------+------+-------------------------------------------------------------+--+
| 200   | OK   | ["3148120404262382","1443363211304208","4322133941384257"]  |
+-------+------+-------------------------------------------------------------+--+

--获取json中的数组中第一个数据
select
get_json_object('{"code": 200,"msg": "OK","data": ["3148120404262382","1443363211304208","4322133941384257"]}','$.data[0]') as data
;
+-------------------+--+
|       data        |
+-------------------+--+
| 3148120404262382  |
+-------------------+--+

{"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" }
-- 去到从json中解析出第一个weight字段
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" }','$.store.fruit[0].weight') as weight
;
+---------+--+
| weight  |
+---------+--+
| 8       |
+---------+--+
小涛手记
关注
  • 1
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
hive解析json_hive json解析函数
2401_84182073的博客
04-11 1088
发知识点，真正体系化！**
hive解析json格式数据所需jar包
05-05
hive解析json时所需jar包。具体使用： add jar ../../../target/json-serde-1.3-jar-with-dependencies.jar; CREATE TABLE json_nested_test ( country string, languages array, religions map,array<int>>) ...
如何将hive中字符串解析（字符串的连接、解析）
limiaoiao的博客
08-16 2749
场景：（1）将表中的字段转化成json数组，合并列，然后再合并行。建测试表，1315 、11729为表中某字段的值分属于不同的行，123、456为另一字段的对应行数字。准备测试数据，样例如下：[{"c1":"1315","c2":"123"},{"c1":"11729","c2":"456"}]。（2）将上述样例解析为表中的字段值1315、123。 1.准备测试数据的方法： 1)用：连接 s...
Hive如何解析Json数据
weixin_44318460的博客
11-03 1844
如何使用hive解析json数据
一文速学-HiveSQL解析JSON数据详解+代码实战
master_hunter的博客
08-10 7480
JSON文件存储格式十分常见，在各个数据库中以及业务场景都有关于该文件的处理方式。但是有时候处理JSON文件在不同的数据库处理方法也不同，掌握一些高效的函数可以大大简化我们处理JSON数据格式的效率。面对一些复杂的存储形式，例如JSON数组存储这种就必须采取一定的处理方式，下面是处理HiveSQL解析JSON数据的函数与方法。...
hive json字段解析
MusicDancing的博客
04-11 626
在写Hive SQL时，需要从一个json列中解析出多个key的信息，可以使用get_json_object或json_tuple。
hive-json-serde
05-17
hive的数组解析json中的数组，Map解析json中的对象：{“ pluginList”：[{“ name”：“ 1”，“ browser”：“ 1”，“ on”：“ 2”}，{“ name“：” 1“，” browser“：” 3“，” on“：” 2“}]}创建表sql：...
06.hive中的json解析函数--json-tuple.mp4
04-29
06.hive中的json解析函数--json-tuple.mp4
解析Json函数UDTF函数1.doc
最新发布
03-05
解析Json函数UDTF函数1
解析Json函数UDTF函数2.doc
03-05
解析Json函数UDTF函数2
Hive中如何处理JSON格式数据
gggSam的博客
08-30 3067
Hive中如何处理JSON格式数据 Hive 处理json数据总体来说有三个办法： 使用内建的函数get_json_object、json_tuple 使用自定义的UDF(一进一出)，自定义UDTF(一进多出) 第三方的SerDe–》JSONSerder 1、使用内建函数处理 get_json_object(string json_string, string path) 返回值：String 说明：解析json字符串json_string，返回path指定的内容；如果输入的json字符串无效，那么返回
HIVE解析json格式数据
无知无畏的博客
06-29 409
1.最近遇到一个需求，数据是这样的： | id | conditions | |312|{“diyItems”:[{“a”:510723474755551232,“b”:1,“c”:80,“d”:507,“e”:2,“f”:45,“g”:30},{“a”:495134318704852992,“b”:2,“c”:90,“d”:229,“e”:0,“f”:48,“g”:118},{“a”:568838501110611968,“b”:3,“c”:90,“d”:642,“e”:0,“f”:44,“g”:69}]
hive中解析json数据(map数组型)
djz19890117的专栏
01-25 5万+
假如待解析表的字段(map数组型json)的数据项都是一样多的，也就是说数组的长度一样，比如长度都为2，数据样例[{"bssid":"6C:59:40:21:05:C4","ssid":"MERCURY_05C4"},{"bssid":"AC:9C:E4:04:EE:52","appid":"10003","ssid":"and-Business"}]map嵌套的情况：{"person":"tom","food":{"fruit":"apple","meat":"pig"}}1、简单json的解析。
【Hive】解析json（get_json_object）
热门推荐
喜东东的博客
05-25 20万+
get_json_object(string json_string, string path) 说明： 第一个参数填写json对象变量，第二个参数使用$表示json变量标识，然后用 . 或 [] 读取对象或数组；如果输入的json字符串无效，那么返回NULL。 每次只能返回一个数据项。 举例： data 为 test表中的字段，数据结构如下： data = { &quot;store&quot;: ...
hive解析json格式
jin6872115的博客
04-09 1561
{ "store": { "fruit": [ { "weight": 8, "type": "apple" }, { "weight": 9, "ty
Hive解析Json数据
烂笔头的专栏
04-17 2008
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...
(转)hive中解析json数组
weixin_34138521的博客
03-26 3368
2019独角兽企业重金招聘Python工程师标准>>> ...
hive 解析json
08-23
- *1* *2* [学会Hive解析Json数组](https://blog.csdn.net/ytp552200ytp/article/details/125277965)[target="_blank" data-report-click={"spm":"1018.2226.3001.9630","extra":{"utm_source":"vip_chatgpt_common_...

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值