Spark高级操作之Json复杂和嵌套数据结构的操作及进行Json文件的数据清洗
Json文件的数据清洗
- 日志文件
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工具解析第一行查看
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": "辑赤蹲慰鸽抿肘捎"
}
}]
}
- 上传Json文件至HDFS
[root@hadoop100 ~]# hdfs dfs -put /opt/kb09file/op.log /kb09workspace/
- 启动hadoop，spark-shell
[root@hadoop100 ~]# start-all.sh
[root@hadoop100 ~]# spark-shell
- 读取Json文件，并查看
scala> val fileRDD=sc.textFile("hdfs://hadoop100:9000/kb09workspace/op.log")
scala> fileRDD.collect.foreach(println)
- 日志文件分析
日志格式为：1593136280858 (用户标识) | json字符串的形式 //分隔符为 | 管道符
- 切割Json文件，并查看
scala> val jsonStrRDD=fileRDD.map(x=>x.split('|')).map(x=>(x(0),x(1)))
scala> jsonStrRDD.collect.foreach(println)
注意这里应该使用单引号而不是双引号。单引号表示是字符，双引号表示是字符串
- 保留用户标识并组合文件
scala> val jsonRDD= jsonStrRDD.map(x=>{var jsonStr=x._2;jsonStr=jsonStr.substring(0,jsonStr.length-1);jsonStr+",\"id\":\""+x._1+"\"}"})
- 把RDD转为DataFrame
scala> val jsonDF=jsonRDD.toDF
- 导入包
scala> import spark.implicits._ //隐式转换
scala> import org.apache.spark.sql.types._ //类型
scala> import org.apache.spark.sql.functions._ //内置方法
- 日志文件可以分为四个Json字符串,将json字符串{“cm”:“a1” , “ap”:“b1” , “et”:“c1” , “id”:“d1”} 结构化
| 表头 | cm | ap | et | id |
|---|---|---|---|---|
| 列 | a1 | b1 | c1 | d1 |
- 进行Json转换
scala> val jsonDF2=jsonDF.select(get_json_object($"value","$.cm").alias("cm"),get_json_object($"value","$.ap").alias("ap"),get_json_object($"value","$.et").alias("et"),get_json_object($"value","$.id").alias("id"))
scala> jsonDF2.printSchema
scala> jsonDF2.select($"id",$"ap",$"cm",$"et").show
- 将cm继续拆分，将每个字段拆分为一个列
scala> val jsonDF3=jsonDF2.select($"id",$"ap",get_json_object($"cm","$.ln").alias("ln"),get_json_object($"cm","$.sv").alias("sv"),get_json_object($"cm","$.os").alias("os"),get_json_object($"cm","$.g").alias("g"),get_json_object($"cm","$.mid").alias("mid"),get_json_object($"cm","$.nw").alias("nw"),get_json_object($"cm","$.l").alias("l"),get_json_object($"cm","$.vc").alias("vc"),get_json_object($"cm","$.hw").alias("hw"),get_json_object($"cm","$.ar").alias("ar"),get_json_object($"cm","$.uid").alias("uid"),get_json_object($"cm","$.t").alias("t"),get_json_object($"cm","$.la").alias("la"),get_json_object($"cm","$.md").alias("md"),get_json_object($"cm","$.vn").alias("vn"),get_json_object($"cm","$.ba").alias("ba"),get_json_object($"cm","$.sr").alias("sr"),$"et")
- 查看拆分结果
scala> jsonDF3.printSchema
scala> jsonDF3.show
- 对et字段分析，通过from_json方法把字符串et结构化
from_json 把字符串
"et":[
{"ett":"a1","en":"a2","kv":{"a3"}}
,{"ett":"a1","en":"b2","kv":{"b3"}}
,{"ett":"a1","en":"c2","kv":{"c3"}}
]
结构化：
ett en kv
a1 a2 a3
b1 b2 b3
c1 c2 c3
scala> val jsonDF4=jsonDF3.select($"id",$"ap",$"ln",$"sv",$"os",$"g",$"mid",$"nw",$"l",$"vc",$"hw",$"ar",$"uid",$"t",$"la",$"md",$"vn",$"ba",$"sr",from_json($"et",ArrayType(StructType(StructField("ett",StringType)::StructField("en",StringType)::StructField("kv",StringType)::Nil))).alias("event"))
- 查看结果
scala> jsonDF4.printSchema
scala> jsonDF4.show
jsonDF4大脑想到的结构
| id | ap | mid | nw | … | event |
|---|---|---|---|---|---|
| 159313 | app | 12345 | dsdwe | … | [[ett en kv],[b1 b2 b3],[c1 c2 c3]] |
想要的结果，将数组拆分成行，及数组中的每个字段拆分成列
| id | ap | mid | nw | … | ett | en | kv |
|---|---|---|---|---|---|---|---|
| 159313 | app | 12345 | dsdwe | … | a1 | a2 | a3 |
| 159313 | app | 12345 | dsdwe | … | b1 | b2 | b3 |
| 159313 | app | 12345 | dsdwe | … | c1 | c2 | c3 |
中间的结果，将数组拆分
- 将数组拆分
scala> val jsonDF5=jsonDF4.select($"id",$"ap",$"ln",$"sv",$"os",$"g",$"mid",$"nw",$"l",$"vc",$"hw",$"ar",$"uid",$"t",$"la",$"md",$"vn",$"ba",$"sr",explode($"event").alias("event"))
- 查看结果
scala> jsonDF5.printSchema
scala> jsonDF5.show
- 单独查看event列
scala> jsonDF5.select("event").show(false)
- 将event列拆分成ett、en、kv三列
scala> jsonDF5.select($"id",$"ap",$"ln",$"sv",$"os",$"g",$"mid",$"nw",$"l",$"vc",$"hw",$"ar",$"uid",$"t",$"la",$"md",$"vn",$"ba",$"sr",$"event.ett",$"event.en",$"event.kv").show(false)
- 单独查看en列
scala> jsonDF5.select("event.en").distinct.show(false)
- 会发现en有praise、notification、comment、ad、active_background、loading，通过filter进行过滤
//举列过滤出en列为loading的行
scala> val loadingDF = jsonDF5.select($"id",$"ap",$"ln",$"sv",$"os",$"g",$"mid",$"nw",$"l",$"vc",$"hw",$"ar",$"uid",$"t",$"la",$"md",$"vn",$"ba",$"sr",$"event.ett",$"event.en",$"event.kv").filter($"event.en"==="loading")
- 查看结果
scala> loadingDF.printSchema
scala> loadingDF.show(false)
- 查看kv列
scala> loadingDF.select("kv").show(false)
- 发现kv列还是json的字符串，进行拆分
scala> val lastDF=loadingDF.select($"id",$"ap",$"ln",$"sv",$"os",$"g",$"mid",$"nw",$"l",$"vc",$"hw",$"ar",$"uid",$"t",$"la",$"md",$"vn",$"ba",$"sr",$"ett",$"en",get_json_object($"kv","$.extend2").alias("extend2"),get_json_object($"kv","$.loading_time").alias("loading_time"),get_json_object($"kv","$.action").alias("action"),get_json_object($"kv","$.extend1").alias("extend1"),get_json_object($"kv","$.type").alias("type"),get_json_object($"kv","$.type1").alias("type1"),get_json_object($"kv","$.loading_way").alias("loading_way"))
- 查看结果
scala> lastDF.printSchema
scala> lastDF.show(false)
+-------------+---+------+------+-----+------------------+---+---+---+---+--------+---+---+-------------+-----+----------+-----+-------+---+-------------+-------+-------+------------+------+-------+----+-----+-----------+
|id |ap |ln |sv |os |g |mid|nw |l |vc |hw |ar |uid|t |la |md |vn |ba |sr |ett |en |extend2|loading_time|action|extend1|type|type1|loading_way|
+-------------+---+------+------+-----+------------------+---+---+---+---+--------+---+---+-------------+-----+----------+-----+-------+---+-------------+-------+-------+------------+------+-------+----+-----+-----------+
|1593136280858|app|-55.0 |V2.9.6|8.0.4|C6816QZ0@gmail.com|489|3G |es |4 |640*960 |MX |489|1593123253541|5.2 |sumsung-18|1.3.4|Sumsung|I |1593050051366|loading| |14 |3 | |2 |201 |1 |
|1593136280858|app|-114.9|V2.7.8|8.0.4|NW0S962J@gmail.com|490|3G |pt |8 |640*1136|MX |490|1593121224789|-44.4|Huawei-8 |1.0.1|Huawei |O |1593063223807|loading| |0 |3 | |1 |102 |1 |
+-------------+---+------+------+-----+------------------+---+---+---+---+--------+---+---+-------------+-----+----------+-----+-------+---+-------------+-------+-------+------------+------+-------+----+-----+-----------+
637
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
