<dependency> <groupId>org.apache.spark</groupId> <artifactId>spark-sql_2.11</artifactId> <version>2.4.5</version> </dependency>
package com.shujia.sql
import org.apache.spark.SparkContext
import org.apache.spark.rdd.RDD
import org.apache.spark.sql.{DataFrame, SparkSession}
object Demo01SparkSessio {
def main(args: Array[String]): Unit = {
//创建一个Spark SQL的入口 SparkSession
val spark: SparkSession = SparkSession
.builder()
.appName("Demo01SparkSessio")
.master("local")
.getOrCreate()
//获取SparkContext
val sc: SparkContext = spark.sparkContext
//通过SparkContext读取文件
val stuRDD: RDD[String] = sc.textFile("Spark/data/students.txt")
//打印
stuRDD.take(10).foreach(println)
//通过SparkSession读取文件
val stuDF: DataFrame = spark
.read
.format("csv") //指定读取文件的格式
.option("sep", ",") //指定分隔符
.schema("id String,name String,age Int,gender String,clazz String")
.load("Spark/data/students.txt")
//打印
stuDF.show(10)//指定打印的行数
stuDF.show(5,false)//如果某列数据太长 默认会被截断
stuDF.show()//如果什么都不指定 默认打印20条
}
}
1500100001,施笑槐,22,女,文科六班
1500100002,吕金鹏,24,男,文科六班
1500100003,单乐蕊,22,女,理科六班
1500100004,葛德曜,24,男,理科三班
1500100005,宣谷芹,22,女,理科五班
1500100006,边昂雄,21,男,理科二班
1500100007,尚孤风,23,女,文科六班
1500100008,符半双,22,女,理科六班
1500100009,沈德昌,21,男,理科一班
1500100010,羿彦昌,23,男,理科六班
+----------+------+---+------+--------+
| id| name|age|gender| clazz|
+----------+------+---+------+--------+
|1500100001|施笑槐| 22| 女|文科六班|
|1500100002|吕金鹏| 24| 男|文科六班|
|1500100003|单乐蕊| 22| 女|理科六班|
|1500100004|葛德曜| 24| 男|理科三班|
|1500100005|宣谷芹| 22| 女|理科五班|
|1500100006|边昂雄| 21| 男|理科二班|
|1500100007|尚孤风| 23| 女|文科六班|
|1500100008|符半双| 22| 女|理科六班|
|1500100009|沈德昌| 21| 男|理科一班|
|1500100010|羿彦昌| 23| 男|理科六班|
+----------+------+---+------+--------+
only showing top 10 rows
+----------+------+---+------+--------+
|id |name |age|gender|clazz |
+----------+------+---+------+--------+
|1500100001|施笑槐|22 |女 |文科六班|
|1500100002|吕金鹏|24 |男 |文科六班|
|1500100003|单乐蕊|22 |女 |理科六班|
|1500100004|葛德曜|24 |男 |理科三班|
|1500100005|宣谷芹|22 |女 |理科五班|
+----------+------+---+------+--------+
only showing top 5 rows+----------+------+---+------+--------+
| id| name|age|gender| clazz|
+----------+------+---+------+--------+
|1500100001|施笑槐| 22| 女|文科六班|
|1500100002|吕金鹏| 24| 男|文科六班|
|1500100003|单乐蕊| 22| 女|理科六班|
|1500100004|葛德曜| 24| 男|理科三班|
|1500100005|宣谷芹| 22| 女|理科五班|
|1500100006|边昂雄| 21| 男|理科二班|
|1500100007|尚孤风| 23| 女|文科六班|
|1500100008|符半双| 22| 女|理科六班|
|1500100009|沈德昌| 21| 男|理科一班|
|1500100010|羿彦昌| 23| 男|理科六班|
|1500100011|宰运华| 21| 男|理科三班|
|1500100012|梁易槐| 21| 女|理科一班|
|1500100013|逯君昊| 24| 男|文科二班|
|1500100014|羿旭炎| 23| 男|理科五班|
|1500100015|宦怀绿| 21| 女|理科一班|
|1500100016|潘访烟| 23| 女|文科一班|
|1500100017|高芷天| 21| 女|理科五班|
|1500100018|骆怜雪| 21| 女|文科六班|
|1500100019|娄曦之| 24| 男|理科三班|
|1500100020|杭振凯| 23| 男|理科四班|
+----------+------+---+------+--------+
only showing top 20 rows
package com.shujia.sql
import org.apache.spark.rdd.RDD
import org.apache.spark.sql.{DataFrame, Row, SparkSession}
object Demo02WordCount {
def main(args: Array[String]): Unit = {
//创建SparkSession
val spark: SparkSession = SparkSession
.builder()
.appName("Demo02WordCount")
.master("local")
.config("spark.sql.shuffle.partitions",2)//默认200
.getOrCreate()
//统计每个单词的数量
//读取单词数据并构建DataFrame
val lineDF: DataFrame = spark
.read
.format("csv")
.option("sep", "|")
.schema("line String")
.load("Spark/data/wordCnt/input")
// lineDF.show()
//将DataFrame注册成表 即可使用Spark SQL进行查询
lineDF.createOrReplaceTempView("word_count")
//SQL 的格式
spark.sql(
"""
|select t1.word
| ,count(*) as cnt
|from
|(
| select explode(split(line, ",")) as word
| from word_count
| ) t1 group by t1.word
|""".stripMargin).show()
//导入Spark SQL提供的所有的方法
import org.apache.spark.sql.functions._
//导入Spark sql的隐式转换
import spark.implicits._
//DSL 的方式
lineDF
.select(explode(split($"line",","))as "word")
.groupBy($"word")
.agg(count("*")as "cnt")
.show()
//RDD方式
val lineRDD: RDD[Row] = lineDF.rdd
lineRDD
//Row 表示DF中的一行数据 是具有结构的 可以通过getAS方法提取每一列数据
.map(row=>{
val line: String = row.getAs[String]("line")
line.split(",")
line
}).map(word=>(word,1))
.reduceByKey(_+_)
.foreach(println)
while (true) {
}
}
}
+------+---+
| word|cnt|
+------+---+
| java| 56|
| spark| 28|
|hadoop| 28|
+------+---+
+------+---+
| word|cnt|
+------+---+
| java| 56|
| spark| 28|
|hadoop| 28|
+------+---+
(java,spark,java,hadoop,16)
(java,spark,java,hadoop,java,spark,java,hadoop,java,spark,java,hadoop,java,spark,java,hadoop,1)
(java,spark,java,hadoop,java,spark,java,hadoop,1)
(java,spark,java,hadoop,java,spark,java,hadoop,java,spark,java,hadoop,2)
package com.shujia.sql
import org.apache.spark.sql.expressions.Window
import org.apache.spark.sql.{Column, DataFrame, SparkSession}
import org.apache.spark.storage.StorageLevel
object Demo03Burk {
def main(args: Array[String]): Unit = {
val spark: SparkSession = SparkSession
.builder()
.master("local")
.appName("Demo03Burk")
.config("spark.sql.shuffle.partitions", 1)
.getOrCreate()
import spark.implicits._
import org.apache.spark.sql.functions._
//1、读取数据
val burk: DataFrame = spark.read
.format("csv")
.option("sep", ",")
.schema("burk STRING,year STRING,tsl01 DOUBLE,tsl02 DOUBLE,tsl03 DOUBLE,tsl04 DOUBLE,tsl05 DOUBLE,tsl06 DOUBLE,tsl07 DOUBLE,tsl08 DOUBLE,tsl09 DOUBLE,tsl10 DOUBLE,tsl11 DOUBLE,tsl12 DOUBLE")
.load("Spark/data/burk.txt")
//对多次使用的 DF 进行缓存 -- 优化
burk.persist(StorageLevel.MEMORY_ONLY_SER)
//将 DF 注册成一个临时的视图
burk.createOrReplaceTempView("burk")
/**
*
* 1、统计每个公司每年按月累计收入 行转列 --> sum窗口函数
* 输出结果
* 公司代码,年度,月份,当月收入,累计收入
*
*/
spark.sql(
"""
|
|select
|burk,year,month,pic,sum(pic) over(partition by burk,year order by month) as sumPic
|from (
|select
|burk,year,month,pic
|from
|burk lateral view explode(map(1,tsl01,2,tsl02,3,tsl03,4,tsl04,5,tsl05,6,tsl06,7,tsl07,8,tsl08,9,tsl09,10,tsl10,11,tsl11,12,tsl12)) t1 as month,pic
|) as a
|
""".stripMargin)
//.show()
/**
* 再以DSL实现
*
*/
// 将map拿到外面定义
// map中要的是 字段(列)的对象的可变参数
// 但是这里 1 -- 基本数据类型
// 所以这边需要用 expr("1") 去转换一下
// 将 1 转成列的对象
val m: Column = map(
expr("1"), $"tsl01",
expr("2"), $"tsl02",
expr("3"), $"tsl03",
expr("4"), $"tsl04",
expr("5"), $"tsl05",
expr("6"), $"tsl06",
expr("7"), $"tsl07",
expr("8"), $"tsl08",
expr("9"), $"tsl09",
expr("10"), $"tsl10",
expr("11"), $"tsl11",
expr("12"), $"tsl12"
)
burk
//将多列转换多行
.select($"burk", $"year", explode(m) as Array("month", "pic"))
//计算累计金额
.withColumn("sumPic", sum($"pic") over Window.partitionBy($"burk", $"year").orderBy($"month"))
//.show(100)
/**
* 2、统计每个公司当月比上年同期增长率 行转列 --> lag窗口函数
* 公司代码,年度,月度,增长率(当月收入/上年当月收入 - 1)
*/
burk
//将多列转换多行
.select($"burk", $"year", explode(m) as Array("month", "pic"))
//湖区上年同期的收入金额
.withColumn("lastPic", lag($"pic", 1, 0) over Window.partitionBy($"burk", $"month").orderBy($"year"))
//计算增长率
.withColumn("p", $"pic" / $"lastPic" - 1)
//整理数据 -- 这里只有代码中的if,没有if函数,所以使用case……when……[在DSL中是when(表达式,为true返回的值).otherwise(为false返回的值)]
.select($"burk", $"year", $"month", when($"p".isNull, 1.0).otherwise(round($"p", 5)) as "p")
.show(1000)
}
}
+------+----+-----+--------+
| burk|year|month| p|
+------+----+-----+--------+
| null|null| 1| 1.0|
| null|null| 1| 1.0|
| null|null| 2| 1.0|
| null|null| 2| 1.0|
| null|null| 3| 1.0|
| null|null| 3| 1.0|
| null|null| 4| 1.0|
| null|null| 4| 1.0|
| null|null| 5| 1.0|
| null|null| 5| 1.0|
| null|null| 6| 1.0|
| null|null| 6| 1.0|
| null|null| 7| 1.0|
| null|null| 7| 1.0|
| null|null| 8| 1.0|
| null|null| 8| 1.0|
| null|null| 9| 1.0|
| null|null| 9| 1.0|
| null|null| 10| 1.0|
| null|null| 10| 1.0|
| null|null| 11| 1.0|
| null|null| 11| 1.0|
| null|null| 12| 1.0|
| null|null| 12| 1.0|
|846271|2010| 1| 1.0|
|846271|2011| 1| 1.0E-5|
|846271|2012| 1| 1.0E-5|
|846271|2013| 1| 1.0E-5|
|846271|2014| 1| 1.0E-5|
|846271|2015| 1| 1.0E-5|
|846271|2016| 1| 1.0E-5|
|846271|2017| 1| 1.0E-5|
|846271|2018| 1| 1.0E-5|
|846271|2019| 1| 1.0E-5|
|846271|2010| 2| 1.0|
|846271|2011| 2| 4.0E-5|
|846271|2012| 2| 3.00644|
|846271|2013| 2| 1.0E-5|
|846271|2014| 2| 1.0E-5|
|846271|2015| 2| 1.0E-5|
|846271|2016| 2| 1.0E-5|
|846271|2017| 2| 1.0E-5|
|846271|2018| 2| 1.0E-5|
|846271|2019| 2| 1.0E-5|
|846271|2010| 3| 1.0|
|846271|2011| 3| 0.12844|
|846271|2012| 3| 0.03799|
|846271|2013| 3| 0.05483|
|846271|2014| 3| 0.05203|
|846271|2015| 3| 0.04941|
|846271|2016| 3| 0.04712|
|846271|2017| 3| 0.04496|
|846271|2018| 3| 0.04307|
|846271|2019| 3| 0.04125|
|846271|2010| 4| 1.0|
|846271|2011| 4|-0.49851|
|846271|2012| 4|-0.68465|
|846271|2013| 4|-0.22764|
|846271|2014| 4|-0.29493|
|846271|2015| 4|-0.41801|
|846271|2016| 4|-0.71825|
|846271|2017| 4| 0.55095|
|846271|2018| 4| 1.64365|
|846271|2019| 4| 0.62174|
|846271|2010| 5| 1.0|
|846271|2011| 5|-0.47891|
|846271|2012| 5| 2.09946|
|846271|2013| 5|-0.04364|
|846271|2014| 5|-0.04565|
|846271|2015| 5| -0.55|
|846271|2016| 5| 1.00969|
|846271|2017| 5| -0.0529|
|846271|2018| 5|-0.05582|
|846271|2019| 5|-0.05915|
|846271|2010| 6| 1.0|
|846271|2011| 6|-0.09739|
|846271|2012| 6| 4.01833|
|846271|2013| 6|-0.02985|
|846271|2014| 6|-0.03078|
|846271|2015| 6|-0.63245|
|846271|2016| 6| 1.54791|
|846271|2017| 6|-0.03391|
|846271|2018| 6| -0.0351|
|846271|2019| 6|-0.03636|
|846271|2010| 7| 1.0|
|846271|2011| 7| 0.37132|
|846271|2012| 7| 2.08573|
|846271|2013| 7|-0.02479|
|846271|2014| 7|-0.02543|
|846271|2015| 7|-0.76061|
|846271|2016| 7| 7.0E-5|
|846271|2017| 7| 0.42417|
|846271|2018| 7| 2.0E-5|
|846271|2019| 7|-0.57959|
|846271|2010| 8| 1.0|
|846271|2011| 8| 3.0E-5|
|846271|2012| 8| 4.78447|
|846271|2013| 8|-0.02216|
|846271|2014| 8|-0.02265|
|846271|2015| 8|-0.88001|
|846271|2016| 8| 0.50744|
|846271|2017| 8| 3.0E-5|
|846271|2018| 8| 3.0E-5|
|846271|2019| 8| 0.4242|
|846271|2010| 9| 1.0|
|846271|2011| 9| 5.0E-5|
|846271|2012| 9| 9.81418|
|846271|2013| 9|-0.02054|
|846271|2014| 9|-0.02097|
|846271|2015| 9|-0.84905|
|846271|2016| 9|-0.36127|
|846271|2017| 9| 5.0E-5|
|846271|2018| 9| 5.0E-5|
|846271|2019| 9|-0.21458|
|846271|2010| 10| 1.0|
|846271|2011| 10| 3.0E-5|
|846271|2012| 10| 7.24585|
|846271|2013| 10|-0.01945|
|846271|2014| 10|-0.01984|
|846271|2015| 10|-0.82697|
|846271|2016| 10| -0.2708|
|846271|2017| 10| 3.0E-5|
|846271|2018| 10| 3.0E-5|
|846271|2019| 10|-0.42349|
|846271|2010| 11| 1.0|
|846271|2011| 11| 9.0E-5|
|846271|2012| 11|25.04872|
|846271|2013| 11|-0.01867|
|846271|2014| 11|-0.01902|
|846271|2015| 11|-0.89164|
|846271|2016| 11| 0.3716|
|846271|2017| 11|-0.27088|
|846271|2018| 11| 3.0E-5|
|846271|2019| 11| 0.37147|
|846271|2010| 12| 1.0|
|846271|2011| 12| 5.0E-5|
|846271|2012| 12|14.55035|
|846271|2013| 12|-0.01807|
|846271|2014| 12|-0.01841|
|846271|2015| 12|-0.01875|
|846271|2016| 12|-0.01911|
|846271|2017| 12|-0.01948|
|846271|2018| 12|-0.01987|
|846271|2019| 12|-0.02027|
|853101|2010| 1| 1.0|
|853101|2011| 1|-0.80593|
|853101|2012| 1| 5.0E-5|
|853101|2013| 1| 0.05723|
|853101|2014| 1|-0.05404|
|853101|2015| 1| 4.15219|
|853101|2016| 1| 1.0E-5|
|853101|2017| 1| 1.0E-5|
|853101|2018| 1| 1.0E-5|
|853101|2019| 1| 1.0E-5|
|853101|2010| 2| 1.0|
|853101|2011| 2|-0.17783|
|853101|2012| 2| 5.0E-5|
|853101|2013| 2|-0.27032|
|853101|2014| 2| 0.3706|
|853101|2015| 2| 0.21635|
|853101|2016| 2| 4.0E-5|
|853101|2017| 2| 4.0E-5|
|853101|2018| 2| 4.0E-5|
|853101|2019| 2| 4.0E-5|
|853101|2010| 3| 1.0|
|853101|2011| 3| -0.2286|
|853101|2012| 3| 7.0E-5|
|853101|2013| 3| 0.14936|
|853101|2014| 3|-0.12984|
|853101|2015| 3| 0.29642|
|853101|2016| 3| 5.0E-5|
|853101|2017| 3|-0.11375|
|853101|2018| 3| 1.37902|
|853101|2019| 3|-0.57961|
|853101|2010| 4| 1.0|
|853101|2011| 4|-0.16141|
|853101|2012| 4| 6.0E-5|
|853101|2013| 4| 1.37902|
|853101|2014| 4|-0.57961|
|853101|2015| 4| 0.19256|
|853101|2016| 4| 5.0E-5|
|853101|2017| 4| 1.9E-4|
|853101|2018| 4| 0.40019|
|853101|2019| 4| 0.42417|
|853101|2010| 5| 1.0|
|853101|2011| 5| 1.73489|
|853101|2012| 5|-0.49849|
|853101|2013| 5|-0.16177|
|853101|2014| 5| 1.37902|
|853101|2015| 5|-0.57961|
|853101|2016| 5| 6.0E-5|
|853101|2017| 5|-0.12978|
|853101|2018| 5| 0.91907|
|853101|2019| 5| 3.0E-5|
|853101|2010| 6| 1.0|
|853101|2011| 6| 0.67087|
|853101|2012| 6|-0.47888|
|853101|2013| 6| 0.37067|
|853101|2014| 6| 0.40019|
|853101|2015| 6| 0.42417|
|853101|2016| 6| 2.0E-5|
|853101|2017| 6|-0.57959|
|853101|2018| 6| 0.10785|
|853101|2019| 6| 5.0E-5|
|853101|2010| 7| 1.0|
|853101|2011| 7|-0.53418|
|853101|2012| 7|-0.09726|
|853101|2013| 7|-0.12978|
|853101|2014| 7| 0.91907|
|853101|2015| 7| 3.0E-5|
|853101|2016| 7| 3.0E-5|
|853101|2017| 7| 0.4242|
|853101|2018| 7| -0.2708|
|853101|2019| 7| 3.0E-5|
|853101|2010| 8| 1.0|
|853101|2011| 8| 3.0E-5|
|853101|2012| 8| 3.0E-5|
|853101|2013| 8|-0.40108|
|853101|2014| 8| 0.10785|
|853101|2015| 8| 5.0E-5|
|853101|2016| 8| 5.0E-5|
|853101|2017| 8|-0.21458|
|853101|2018| 8| 0.91907|
|853101|2019| 8| 3.0E-5|
|853101|2010| 9| 1.0|
|853101|2011| 9| 5.0E-5|
|853101|2012| 9| 5.0E-5|
|853101|2013| 9| 1.14772|
|853101|2014| 9| -0.2708|
|853101|2015| 9| 3.0E-5|
|853101|2016| 9| 3.0E-5|
|853101|2017| 9|-0.42349|
|853101|2018| 9| 0.10785|
|853101|2019| 9| 5.0E-5|
|853101|2010| 10| 1.0|
|853101|2011| 10| 3.0E-5|
|853101|2012| 10| 3.0E-5|
|853101|2013| 10| 3.0E-5|
|853101|2014| 10| 0.3716|
|853101|2015| 10|-0.27088|
|853101|2016| 10| 3.0E-5|
|853101|2017| 10| 0.37147|
|853101|2018| 10| -0.2708|
|853101|2019| 10| 3.0E-5|
|853101|2010| 11| 1.0|
|853101|2011| 11| 9.0E-5|
|853101|2012| 11| 9.0E-5|
|853101|2013| 11| 9.0E-5|
|853101|2014| 11| 1.61876|
|853101|2015| 11|-0.61807|
|853101|2016| 11| 9.0E-5|
|853101|2017| 11| 1.61804|
|853101|2018| 11|-0.61797|
|853101|2019| 11| 9.0E-5|
|853101|2010| 12| 1.0|
|853101|2011| 12| 5.0E-5|
|853101|2012| 12| 5.0E-5|
|853101|2013| 12| 5.0E-5|
|853101|2014| 12| 5.0E-5|
|853101|2015| 12| 5.0E-5|
|853101|2016| 12| 5.0E-5|
|853101|2017| 12| 5.0E-5|
|853101|2018| 12| 5.0E-5|
|853101|2019| 12| 5.0E-5|
+------+----+-----+--------+