Spark之json嵌套数据结构的操作(json日志文件处理)

在工作中可能会需要处理复杂的json文件，甚至是类json文件(与json格式很像，需要我们调整为json格式再进行处理)，我们可以使用Spark SQL的一些实用函数帮助解决复杂嵌套的json数据格式。

本文主要讲解以下三种方法：
1）get_json_object
2）from_json
3）explode

需求：将复杂嵌套的json数据格式转化为DataFrame。

1、数据准备

在HDFS的/wctest/目录下有文件op.log，内容如下：

1593136280858|{
   "cm":{
   "ln":"-55.0","sv":"V2.9.6","os":"8.0.4","g":"C6816QZ0@gmail.com","mid":"489","nw":"3G","l":"es","vc":"4","hw":"640*960","ar":"MX","uid":"489","t":"1593123253541","la":"5.2","md":"sumsung-18","vn":"1.3.4","ba":"Sumsung","sr":"I"},"ap":"app","et":[{
   "ett":"1593050051366","en":"loading","kv":{
   "extend2":"","loading_time":"14","action":"3","extend1":"","type":"2","type1":"201","loading_way":"1"}},{
   "ett":"1593108791764","en":"ad","kv":{
   "activityId":"1","displayMills":"78522","entry":"1","action":"1","contentType":"0"}},{
   "ett":"1593111271266","en":"notification","kv":{
   "ap_time":"1593097087883","action":"1","type":"1","content":""}},{
   "ett":"1593066033562","en":"active_background","kv":{
   "active_source":"3"}},{
   "ett":"1593135644347","en":"comment","kv":{
   "p_comment_id":1,"addtime":"1593097573725","praise_count":973,"other_id":5,"comment_id":9,"reply_count":40,"userid":7,"content":"辑赤蹲慰鸽抿肘捎"}}]}
1593136280858|{
   "cm":{
   "ln":"-114.9","sv":"V2.7.8","os":"8.0.4","g":"NW0S962J@gmail.com","mid":"490","nw":"3G","l":"pt","vc":"8","hw":"640*1136","ar":"MX","uid":"490","t":"1593121224789","la":"-44.4","md":"Huawei-8","vn":"1.0.1","ba":"Huawei","sr":"O"},"ap":"app","et":[{
   "ett":"1593063223807","en":"loading","kv":{
   "extend2":"","loading_time":"0","action":"3","extend1":"","type":"1","type1":"102","loading_way":"1"}},{
   "ett":"1593095105466","en":"ad","kv":{
   "activityId":"1","displayMills":"1966","entry":"3","action":"2","contentType":"0"}},{
   "ett":"1593051718208","en":"notification","kv":{
   "ap_time":"1593095336265","action":"2","type":"3","content":""}},{
   "ett":"1593100021275","en":"comment","kv":{
   "p_comment_id":4,"addtime":"1593098946009","praise_count":220,"other_id":4,"comment_id":9,"reply_count":151,"userid":4,"content":"抄应螟皮釉倔掉汉蛋蕾街羡晶"}},{
   "ett":"1593105344120","en":"praise","kv":{
   "target_id":9,"id":7,"type":1,"add_time":"1593098545976","userid":8}}]}

我们发现这不是标准的json格式，需要我们自行调整。除了前面的id用竖杠隔开的，后面是标准的json格式，我们需要将竖杠前的id切分开来，利用字符串拼接到后边的json格式里。

//加载文件
val fileRDD=sc.textFile("hdfs://192.168.136.20:9000/wctest/op.log")
//切分并转化为二元组
val jsonStrRDD=fileRDD.map(x=>x.split('|')).map(x=>(x(0),x(1)))
//利用字符串拼接成标准的json格式
val jsonRDD=jsonStrRDD.map(x=>{
   var jsonStr=x._2;jsonStr=jsonStr.substring(0,jsonStr.length-1);jsonStr+",\"id\":\""+x._1+"\"}"})
//转化为DataFrame
val jsonDF=jsonRDD.toDF
//导包
import org.apache.spark.sql.types._
import org.apache.spark.sql.functions._

此时可以看看jsonDF的格式：

scala> jsonDF.show()
+--------------------+
|               value|
+--------------------+
|{
   "cm":{
   "ln":"-55....|
|{
   "cm":{
   "ln":"-114...|
+--------------------+

转成json的标准格式可以粘一条记录去JSON解析器中解析一下，看拼接的是否正确。
JSON解析器网址：https://www.json.cn/
为了方便查看，我们解析一条记录，格式如下：

{
   
    "cm":{
   
        "ln":"-55.0",
        "sv":"V2.9.6",
        "os":"8.0.4",
        "g":"C6816QZ0@gmail.com",
        "mid":"489",
        "nw":"3G",
        "l":"es",
        "vc":"4",
        "hw":"640*960",
        "ar":"MX",
        "uid":"489",
        "t":"1593123253541",
        "la":"5.2",
        "md":"sumsung-18",
        "vn":"1.3.4",
        "ba":"Sumsung",
        "sr":"I"
    },
    "ap":"app",
    "et":[
        {
   
            "ett":"1593050051366",
            "en":"loading",
            "kv":{
   
                "extend2":"",
                "loading_time":"14",
                "action":"3",
                "extend1":"",
                "type":"2",
                "type1":"201",
                "loading_way":"1"
            }
        },
        {
   
            "ett":"1593108791764",
            "en":"ad",
            "kv":{
   
                "activityId":"1",
                "displayMills":"78522",
                "entry":"1",
                "action":"1",
                "contentType":"0"
            }
        },
        {
   
            "ett":"1593111271266",
            "en":"notification",
            "kv":{
   
                "ap_time":"1593097087883",
                "action":"1",
                "type":"1",
                "content":""
            }
        },
        {
   
            "ett":"1593066033562",
            "en":"active_background",
            "kv":{
   
                "active_source":"3"
            }