一文速学-HiveSQL解析JSON数据详解+代码实战

本文涉及的产品
云数据库 RDS MySQL,集群系列 2核4GB
推荐场景:
搭建个人博客
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
云数据库 RDS MySQL,高可用系列 2核4GB
简介: 一文速学-HiveSQL解析JSON数据详解+代码实战

前言


JSON文件存储格式十分常见,在各个数据库中以及业务场景都有关于该文件的处理方式。但是有时候处理JSON文件在不同的数据库处理方法也不同,掌握一些高效的函数可以大大简化我们处理JSON数据格式的效率。面对一些复杂的存储形式,例如JSON数组存储这种就必须采取一定的处理方式,下面是处理HiveSQL解析JSON数据的函数与方法。


一、JSON数据


君欲擅其器,必先练其力。我们要对JSON文件有个熟悉的认知。


JSON是一个标记符的序列。这套标记符包含六个构造字符、字符串、数字和三个字面名。


JSON是一个序列化的对象或数组。


   数据为  键 / 值 (name/value)对;

   数据由逗号(,)分隔;

   大括号保存对象(object);

   方括号保存数组(Array);

值可以是对象、数组、数字、字符串或者三个字面值(false、null、true)中的一个。值中的字面值中的英文必须使用小写。


如:


"code":"100"


对象由花括号括起来的逗号分割的成员构成,成员是字符串键和上文所述的值由逗号分割的键值对组成:


{“code”:20,"type":"mysql"}


数组是由方括号括起来的一组值构成:


"datesource":[
    {"code":"20", "type":"mysql"},
   {"code":"20", "type":"mysql"},
    {"code":"20", "type":"mysql"}
]


二、Hive解析函数


以我们经常存储的JSON文件为实例去展示操作:

{"level":"2","time":1650973942596,"type":"0"}

HiveSQL自带两个函数可以处理JSON文件,但是一次只能处理一个JSON文件。


1.get_json_object


get_json_object的基础语法格式为:


get_json_object(json_string, '$.key')


功能:解析json的字符串json_string,返回key指定的内容。如果输入的json字符串无效,那么返回NULL。这个函数每次只能返回一个数据项。

SELECT 
GET_JSON_OBJECT('{"level":"2","time":1650973942596,"type":"0"}','$.level' ) as level ;

1b3bca4cf3f04a419a9137cc87df82be.png


如果要解析JSON的所有字段可以多写几条:

SELECT 
GET_JSON_OBJECT('{"level":"2","time":1650973942596,"type":"0"}','$.level' ) as level,
GET_JSON_OBJECT('{"level":"2","time":1650973942596,"type":"0"}','$.time' ) as times,
GET_JSON_OBJECT('{"level":"2","time":1650973942596,"type":"0"}','$.type' ) as types;

ccfc775ad92b476ab0c222b6722e5235.png


2.json_tuple


为了解决get_json_object一次解析不了整个JSON文件的问题,我们就有了json_tuple这个函数,一条便能处理一条JSON数据,基础语法为:


json_tuple(json_string, k1, k2 ...)


解析json的字符串json_string,可指定多个json数据中的key,返回对应的value。如果输入的json字符串无效,那么返回NULL。

SELECT 
json_tuple('{"level":"2","time":1650973942596,"type":"0"}','level','time','type') as (level,times,types);

9de4f9f7874f4548b23cbc9f69a1c8c0.png


但是以上这两个函数都无法处理JSON数组,需要我们使用正则替换和explode函数清洗出每条独立的JSON数据才能处理。


3.explode


explode的基础语法为:


explode(Array OR Map)


功能:explode()函数接收一个array或者map类型的数据作为输入,然后将array或map里面的元素按照每行的形式输出,即将hive一列中复杂的array或者map结构拆分成多行显示,也被称为列转行函数。

SELECT explode(array(
'
{"level":"2","time":1650973942596,"type":"0"}',
'{"level":"1","time":1650973942597,"type":"1"}',
'{"level":"3","time":1650973942598,"type":"2"}
'
))

4ed36e14418540dd9d662e075257be7c.png

select explode(map('level',1,'time',1650973942596,'type',0))

d137021b68664fa99615680d9200c8ad.png


4.regexp_replace


regexp_replace就好比python里面的sub()匹配之后替换:

基础语法:


regexp_replace(string A, string B, string C)


功能:将字符串A中的符合java正则表达式B的部分替换为C。

select REGEXP_REPLACE('{"level":"2","time":1650973942596,"type":"0"}','2','1');

dff5003d8c45442cafb222cae8435f24.png


三、Hive解析JSON数组


我们先拿到一组JSON数组:


[{"level":"2","time":1650973942596,"type":"0"},


{"level":"1","time":1650973942597,"type":"1"},


{"level":"3","time":1650973942598,"type":"2"}]


我们想要把他们转换为一下格式,变成一下这种形式:


478b5481e52141988a00bea2eacd062a.png


第一步:

第一步我们要将数组外面的,给替换掉,以免后续我们按;划分展开。

SELECT 
    REGEXP_REPLACE('[{"level":"2","time":1650973942596,"type":"0"},{"level":"1","time":1650973942597,"type":"1"},{"level":"3","time":1650973942598,"type":"2"}]','\\}\\,\\{','\\}\\;\\{')


f50e0505651947cfa83014c7205b06be.png


第二步:


将数组两边的[]给去掉:


select
REGEXP_REPLACE( 
    REGEXP_REPLACE('[{"level":"2","time":1650973942596,"type":"0"},{"level":"1","time":1650973942597,"type":"1"},{"level":"3","time":1650973942598,"type":"2"}]','\\}\\,\\{','\\}\\;\\{')
    ,'\\[|\\]','')

第三步:


按分号我们进行划分:


SELECT 
    split(
    REGEXP_REPLACE( 
    REGEXP_REPLACE('[{"level":"2","time":1650973942596,"type":"0"},{"level":"1","time":1650973942597,"type":"1"},{"level":"3","time":1650973942598,"type":"2"}]','\\}\\,\\{','\\}\\;\\{')
    ,'\\[|\\]','')
    ,'\\;')


92f2736bec474771aff02eab61ddc553.png

第四步:


之后我们便可以使用explode进行平铺了:

select 
    explode(
    split(
    REGEXP_REPLACE( 
    REGEXP_REPLACE('[{"level":"2","time":1650973942596,"type":"0"},{"level":"1","time":1650973942597,"type":"1"},{"level":"3","time":1650973942598,"type":"2"}]','\\}\\,\\{','\\}\\;\\{')
    ,'\\[|\\]','')
    ,'\\;')
    )


06721ec3998b4e12abf6892bd6e49eea.png


第五步:


最后在此表的基础之上我们再使用get_json_object或者json_tuple函数就好了:


SELECT 
      GET_JSON_OBJECT(track,'$.level') as level,
      GET_JSON_OBJECT(track,'$.time') as times,
      GET_JSON_OBJECT(track,'$.type') as types
    from (
    select 
    explode(
    split(
    REGEXP_REPLACE( 
    REGEXP_REPLACE('[{"level":"2","time":1650973942596,"type":"0"},{"level":"1","time":1650973942597,"type":"1"},{"level":"3","time":1650973942598,"type":"2"}]','\\}\\,\\{','\\}\\;\\{')
    ,'\\[|\\]','')
    ,'\\;')
    )track )track

c7d327a23d1b4741ac16f4c53ad399bf.png

目录
打赏
0
0
0
0
33
分享
相关文章
实现一个带有昼夜背景切换的动态时钟:从代码到功能解析
本文介绍了一个使用Python和Tkinter库实现的动态时钟程序,具有昼夜背景切换、指针颜色随机变化及整点和半点报时功能。通过设置不同的背景颜色和随机变换指针颜色,增强视觉吸引力;利用多线程技术确保音频播放不影响主程序运行。该程序结合了Tkinter、Pygame、Pytz等库,提供了一个美观且实用的时间显示工具。欢迎点赞、关注、转发、收藏!
134 94
关于商品详情 API 接口 JSON 格式返回数据解析的示例
本文介绍商品详情API接口返回的JSON数据解析。最外层为`product`对象,包含商品基本信息(如id、name、price)、分类信息(category)、图片(images)、属性(attributes)、用户评价(reviews)、库存(stock)和卖家信息(seller)。每个字段详细描述了商品的不同方面,帮助开发者准确提取和展示数据。具体结构和字段含义需结合实际业务需求和API文档理解。
Python执行Shell命令并获取结果:深入解析与实战
通过以上内容,开发者可以在实际项目中灵活应用Python执行Shell命令,实现各种自动化任务,提高开发和运维效率。
56 20
微信小程序 app.json 配置文件解析与应用
本文介绍了微信小程序中 `app.json` 配置文件的详细
141 12
|
1月前
|
解析电商商品详情API接口系列,json数据示例参考
电商商品详情API接口是电商平台的重要组成部分,提供了商品的详细信息,支持用户进行商品浏览和购买决策。通过合理的API设计和优化,可以提升系统性能和用户体验。希望本文的解析和示例能够为开发者提供参考,帮助构建高效、可靠的电商系统。
41 12
深度解析1688 API对电商的影响与实战应用
在全球电子商务迅猛发展的背景下,1688作为知名的B2B电商平台,为中小企业提供商品批发、分销、供应链管理等一站式服务,并通过开放的API接口,为开发者和电商企业提供数据资源和功能支持。本文将深入解析1688 API的功能(如商品搜索、详情、订单管理等)、应用场景(如商品展示、搜索优化、交易管理和用户行为分析)、收益分析(如流量增长、销售提升、库存优化和成本降低)及实际案例,帮助电商从业者提升运营效率和商业收益。
194 20
如何在 Java 代码中使用 JSqlParser 解析复杂的 SQL 语句?
大家好,我是 V 哥。JSqlParser 是一个用于解析 SQL 语句的 Java 库,可将 SQL 解析为 Java 对象树,支持多种 SQL 类型(如 `SELECT`、`INSERT` 等)。它适用于 SQL 分析、修改、生成和验证等场景。通过 Maven 或 Gradle 安装后,可以方便地在 Java 代码中使用。
285 11
如何在Python中高效实现CSV到JSON的数据转换
在实际项目中,数据格式转换是常见问题,尤其从CSV到JSON的转换。本文深入探讨了多种转换方法,涵盖Python基础实现、数据预处理、错误处理、性能优化及调试验证技巧。通过分块处理、并行处理等手段提升大文件转换效率,并介绍如何封装为命令行工具或Web API,实现自动化批量处理。关键点包括基础实现、数据清洗、异常捕获、性能优化和单元测试,确保转换流程稳定高效。
101 82
抓取和分析JSON数据:使用Python构建数据处理管道
在大数据时代,电商网站如亚马逊、京东等成为数据采集的重要来源。本文介绍如何使用Python结合代理IP、多线程等技术,高效、隐秘地抓取并处理电商网站的JSON数据。通过爬虫代理服务,模拟真实用户行为,提升抓取效率和稳定性。示例代码展示了如何抓取亚马逊商品信息并进行解析。
100 1
抓取和分析JSON数据:使用Python构建数据处理管道
拍立淘按图搜索API接口返回数据的JSON格式示例
拍立淘按图搜索API接口允许用户通过上传图片来搜索相似的商品,该接口返回的通常是一个JSON格式的响应,其中包含了与上传图片相似的商品信息。以下是一个基于淘宝平台的拍立淘按图搜索API接口返回数据的JSON格式示例,同时提供对其关键字段的解释

推荐镜像

更多
AI助理

你好,我是AI助理

可以解答问题、推荐解决方案等