大数据全系列 教程
1869个小节阅读:466.7k
目录
408考研
JAVA全系列 教程
面向对象的程序设计语言
Python全系列 教程
Python3.x版本,未来主流的版本
人工智能 教程
顺势而为,AI创新未来
大厂算法 教程
算法,程序员自我提升必经之路
C++ 教程
一门通用计算机编程语言
微服务 教程
目前业界流行的框架组合
web前端全系列 教程
通向WEB技术世界的钥匙
大数据全系列 教程
站在云端操控万千数据
AIGC全能工具班
A A
White Night
原始数据data/demo/jsonArrayFile.json
xxxxxxxxxx
{"name":"zhangsan","age":18,"scores":[{"xueqi":1,"yuwen":98,"shuxue":90,"yingyu":100},{"xueqi":2,"yuwen":98,"shuxue":78,"yingyu":100}]}
{"name":"lisi","age":19,"scores":[{"xueqi":1,"yuwen":58,"shuxue":50,"yingyu":78},{"xueqi":2,"yuwen":56,"shuxue":76,"yingyu":13}]}
{"name":"wangwu","age":17,"scores":[{"xueqi":1,"yuwen":18,"shuxue":90,"yingyu":45},{"xueqi":2,"yuwen":76,"shuxue":42,"yingyu":45}]}
{"name":"zhaoliu","age":20,"scores":[{"xueqi":1,"yuwen":68,"shuxue":23,"yingyu":63},{"xueqi":2,"yuwen":23,"shuxue":45,"yingyu":87}]}
{"name":"tianqi","age":22,"scores":[{"xueqi":1,"yuwen":88,"shuxue":91,"yingyu":41},{"xueqi":2,"yuwen":56,"shuxue":79,"yingyu":45}]}
目标查询出的数据显示如下格式:
xxxxxxxxxx
+--------+---+-----+-----+------+------+
| name|age|xueqi|yuwen|shuxue|yingyu|
+--------+---+-----+-----+------+------+
|zhangsan| 18| 1| 98| 90| 100|
|zhangsan| 18| 2| 98| 78| 100|
| lisi| 19| 1| 58| 50| 78|
| lisi| 19| 2| 56| 76| 13|
| wangwu| 17| 1| 18| 90| 45|
| wangwu| 17| 2| 76| 42| 45|
| zhaoliu| 20| 1| 68| 23| 63|
| zhaoliu| 20| 2| 23| 45| 87|
| tianqi| 22| 1| 88| 91| 41|
| tianqi| 22| 2| 56| 79| 45|
+--------+---+-----+-----+------+------+
代码编写:
xxxxxxxxxx
package com.itbaizhan.sql.examples
import org.apache.spark.sql.{DataFrame, SparkSession}
object ReadJsonArrayFile {
def main(args: Array[String]): Unit = {
val spark: SparkSession = SparkSession.builder()
.master("local[*]")
.appName("ReadJsonArrayFile")
.getOrCreate()
spark.sparkContext.setLogLevel("Error")
//1.读取json文件
val frame: DataFrame = spark.read.json("data/demo/jsonArrayFile.json")
frame.printSchema()
frame.show(false)
//方式一:
frame.createOrReplaceTempView("temp")
/*spark.sql(
"""
|select name,age,explode(scores) as el
|from temp
|""".stripMargin)
.createTempView("temp1")
spark.sql(
"""
|select name,age,el.xueqi,el.yuwen,el.shuxue,el.yingyu
|from temp1
|""".stripMargin).show()*/
spark.sql(
"""
|select name,age,el.xueqi,el.yuwen,el.shuxue,el.yingyu
|from (select name,age,explode(scores) as el from temp)
|""".stripMargin).show()
//方式二:explode和col函数
import org.apache.spark.sql.functions._
import spark.implicits._ //识别$"age"
val df1: DataFrame = frame.select(frame.col("name"),
frame.col("age"),
explode(frame.col("scores")).as("el")
)
df1.select('name,$"age",
col("el.xueqi"),
$"el.yuwen", $"el.shuxue", $"el.yingyu"
).show()
spark.close()
}
}