spark中的from_json如何使用

转载

云端小仙童 2024-09-14 15:02:26

文章标签 spark JSON日志数据处理 json JSON 3G 文章分类 Spark 大数据

JSON数据清洗

1、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字符串，|后面部分才是JSON字符串，可把前面那串数字当做标号或者id

利用JSON解析工具解析JSON字符串后的内容如下图所示：

spark中的from_json如何使用_json

spark中的from_json如何使用_JSON日志数据处理_02

2、读取JSON文件数据

2.1、导包
第一步先导入所有需要的包

import org.apache.spark.sql.types._   //类型
import org.apache.spark.sql.functions._   //内置方法
import spark.implicits._   //隐式转换
import org.apache.spark.sql._

2.2、上传JSON文件到HDFS文件系统

hdfs dfs -put /opt/kb09file/op.log /data/kb09file

2.3、通过文件读取JSON数据存入RDD中

val fileRDD = sc.textFile("hdfs://192.168.247.201:9000/data/kb09file/op.log")

3、JSON数据清洗

3.1、转换为JSON格式

读取的JSON文件不完全是一个JSON格式，只是一个字符串
所以需要先将读取到的字符串转换成JSON格式，即将|前面的编号加到|后面的JSON字符串中
将转换后的 jsonRDD 转换为 jsonDataFrame
RDD 转 DataFrame 需要导入spark.implicits._ 包

val jsonStrRDD = fileRDD.map(x => x.split('|')).map(x => (x(0),x(1)))
val jsonRDD = jsonStrRDD.map(x => { var jsonStr=x._2; 
	jsonStr=jsonStr.substring(0,jsonStr.length-1);jsonStr+",\"id\":\""+x._1+"\"}"})
val jsonDF = jsonRDD.toDF

jsonDF.show

+--------------------+
|               value|
+--------------------+
|{"cm":{"ln":"-55....|
|{"cm":{"ln":"-114...|
+--------------------+

3.2、get_json_object 函数清洗JSON数据

使用 get_json_object 函数解析获取JSON数据，清洗 value 列
使用 get_json_object 函数前需要导入 org.apache.spark.sql.functions._ 包

//将json字符串{"cm":"a1","ap":"b1";"et":"c1";"id":"d1"} 结构化
// 表头   cm    ap   et   id
// 列     a1    b1   c1   d1       
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")
)

jsonDF2.show

+--------------------+---+--------------------+-------------+
|                  cm| ap|                  et|           id|
+--------------------+---+--------------------+-------------+
|{"ln":"-55.0","sv...|app|[{"ett":"15930500...|1593136280858|
|{"ln":"-114.9","s...|app|[{"ett":"15930632...|1593136280858|
+--------------------+---+--------------------+-------------+

再使用 get_json_object 清洗 jsonDF2.show 中的 cm 列

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"
)

jsonDF3.show

+-------------+---+------+------+-----+------------------+---+---+---+---+--------+---+---+-------------+-----+--------------------+
|           id| ap|    ln|    sv|   os|                 g|mid| nw|  l| vc|      hw| ar|uid|            t|   la|                  et|
+-------------+---+------+------+-----+------------------+---+---+---+---+--------+---+---+-------------+-----+--------------------+
|1593136280858|app| -55.0|V2.9.6|8.0.4|C6816QZ0@gmail.com|489| 3G| es|  4| 640*960| MX|489|1593123253541|  5.2|[{"ett":"15930500...|
|1593136280858|app|-114.9|V2.7.8|8.0.4|NW0S962J@gmail.com|490| 3G| pt|  8|640*1136| MX|490|1593121224789|-44.4|[{"ett":"15930632...|
+-------------+---+------+------+-----+------------------+---+---+---+---+--------+---+---+-------------+-----+--------------------+

3.3、from_json 函数清洗JSON嵌套数据

清洗 jsonDF3 中的 et 列
将JSON字符串{“cm”:“ap”;“et”:“cc”;“id”:“dd”}结构化

var 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")
)

jsonDF4.show

+-------------+---+------+------+-----+------------------+---+---+---+---+--------+---+---+-------------+-----+--------------------+
|           id| ap|    ln|    sv|   os|                 g|mid| nw|  l| vc|      hw| ar|uid|            t|   la|               event|
+-------------+---+------+------+-----+------------------+---+---+---+---+--------+---+---+-------------+-----+--------------------+
|1593136280858|app| -55.0|V2.9.6|8.0.4|C6816QZ0@gmail.com|489| 3G| es|  4| 640*960| MX|489|1593123253541|  5.2|[[1593050051366, ...|
|1593136280858|app|-114.9|V2.7.8|8.0.4|NW0S962J@gmail.com|490| 3G| pt|  8|640*1136| MX|490|1593121224789|-44.4|[[1593063223807, ...|
+-------------+---+------+------+-----+------------------+---+---+---+---+--------+---+---+-------------+-----+--------------------+

3.4、explode 函数清洗 JSON 数据

清洗 jsonDF4 中 event 列
explode 函数需要两步完成
第一步：

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")
)

第二步：

val jsonDF6 =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"
)

jsonDF6.show

+-------------+---+------+------+-----+------------------+---+---+---+---+--------+---+---+-------------+-----+-------------+-----------------+--------------------+
|           id| ap|    ln|    sv|   os|                 g|mid| nw|  l| vc|      hw| ar|uid|            t|   la|          ett|               en|                  kv|
+-------------+---+------+------+-----+------------------+---+---+---+---+--------+---+---+-------------+-----+-------------+-----------------+--------------------+
|1593136280858|app| -55.0|V2.9.6|8.0.4|C6816QZ0@gmail.com|489| 3G| es|  4| 640*960| MX|489|1593123253541|  5.2|1593050051366|          loading|{"extend2":"","lo...|
|1593136280858|app| -55.0|V2.9.6|8.0.4|C6816QZ0@gmail.com|489| 3G| es|  4| 640*960| MX|489|1593123253541|  5.2|1593108791764|               ad|{"activityId":"1"...|
|1593136280858|app| -55.0|V2.9.6|8.0.4|C6816QZ0@gmail.com|489| 3G| es|  4| 640*960| MX|489|1593123253541|  5.2|1593111271266|     notification|{"ap_time":"15930...|
|1593136280858|app| -55.0|V2.9.6|8.0.4|C6816QZ0@gmail.com|489| 3G| es|  4| 640*960| MX|489|1593123253541|  5.2|1593066033562|active_background|{"active_source":...|
|1593136280858|app| -55.0|V2.9.6|8.0.4|C6816QZ0@gmail.com|489| 3G| es|  4| 640*960| MX|489|1593123253541|  5.2|1593135644347|          comment|{"p_comment_id":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|1593063223807|          loading|{"extend2":"","lo...|
|1593136280858|app|-114.9|V2.7.8|8.0.4|NW0S962J@gmail.com|490| 3G| pt|  8|640*1136| MX|490|1593121224789|-44.4|1593095105466|               ad|{"activityId":"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|1593051718208|     notification|{"ap_time":"15930...|
|1593136280858|app|-114.9|V2.7.8|8.0.4|NW0S962J@gmail.com|490| 3G| pt|  8|640*1136| MX|490|1593121224789|-44.4|1593100021275|          comment|{"p_comment_id":4...|
|1593136280858|app|-114.9|V2.7.8|8.0.4|NW0S962J@gmail.com|490| 3G| pt|  8|640*1136| MX|490|1593121224789|-44.4|1593105344120|           praise|{"target_id":9,"i...|
+-------------+---+------+------+-----+------------------+---+---+---+---+--------+---+---+-------------+-----+-------------+-----------------+--------------------+

3.5、from_json 函数、explode 函数清洗

此为最后一步清理，清洗 jsonDF6 中的 kv 列
该步骤实质上是前面介绍的 3.3、3.4 的综合使用
一共分为三步
第一步：from_json 函数清洗

val jsonDF7 = jsonDF6.select($"id",$"ap",$"ln",$"sv",$"os",$"g",
		$"mid",$"nw",$"l",$"vc",$"hw",$"ar",$"uid",$"t",$"la",$"ett",$"en",
		from_json($"kv",
		ArrayType(StructType(StructField("extend2",
		StringType)::StructField("loading_time",
		StringType)::StructField("action",
		StringType)::StructField("extend1",
		StringType)::StructField("type",
		StringType)::StructField("type1",
		StringType)::StructField("activityId",
		StringType)::StructField("displayMills",
		StringType)::StructField("entry",
		StringType)::StructField("contentType",
		StringType)::StructField("ap_time",
		StringType)::StructField("content",
		StringType)::StructField("p_comment_id",
		StringType)::StructField("addtime",
		StringType)::StructField("praise_count",
		StringType)::StructField("other_id",
		StringType)::StructField("comment_id",
		StringType)::StructField("reply_count",
		StringType)::StructField("userid",
		StringType)::StructField("target_id",
		StringType)::StructField("add_time",
		StringType)::StructField("loading_way",
		StringType)::Nil)))
		.alias("kv"))

第二步：explode 函数清洗

val jsonDF8 = jsonDF7.select($"id",
		$"ap",$"ln",$"sv",$"os",$"g",
		$"mid",$"nw",$"l",$"vc",$"hw",
		$"ar",$"uid",$"t",$"la",$"ett",$"en",
		explode($"kv").alias("kv"))

第三步：生成最终清洗完成 DF

val jsonDF9 = jsonDF8.select($"id",$"ap",$"ln",$"sv",$"os",$"g",
		$"mid",$"nw",$"l",$"vc",$"hw",$"ar",$"uid",$"t",$"la",$"ett",$"en",
		$"kv.extend2",
		$"kv.loading_time",
		$"kv.action",
		$"kv.extend1",
		$"kv.type",
		$"kv.type1",
		$"kv.activityId",
		$"kv.displayMills",
		$"kv.entry",
		$"kv.contentType",
		$"kv.ap_time",
		$"kv.content",
		$"kv.p_comment_id",
		$"kv.addtime",
		$"kv.praise_count",
		$"kv.other_id",
		$"kv.comment_id",
		$"kv.reply_count",
		$"kv.userid",
		$"kv.target_id",
		$"kv.add_time",
		$"kv.loading_way")

jsonDF9.show

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

4、数据保存到hive、mysql

4.1、数据保存到 hive
将 jsonDF9 转为临时视图

jsonDF9.createTempView("test")

创建 hive 数据库

spark.sql("create database json")

将临时视图映射到 hive 数据库中

spark.sql("create table json.js as select * from test")

导入成功如下图所示：

spark中的from_json如何使用_3G_03

4.2、保存到 mysql

导包并创建连接

import java.util.Properties
		val url = "jdbc:mysql://192.168.247.201:3306/kb09db"
	    val prop = new Properties()
	    prop.setProperty("user","root")
	    prop.setProperty("password","ok")
	    prop.setProperty("driver","com.mysql.jdbc.Driver")

jsonDF9 写入 mysql 中

jsonDF9.write.mode("overwrite").jdbc(url,"json",prop)

导入 mysql 后查看结果如下：

spark中的from_json如何使用_JSON_04

本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。

上一篇：结构方程模型 R语言 sem分析后如何作图

下一篇：java重置iterator

提问和评论都可以，用心的回复会被更多人看到评论

发布评论

相关文章

官方博客	全部文章	热门标签	班级博客
了解我们	网站地图	意见反馈

鸿蒙开发者社区	51CTO学堂
51CTO	软考资讯