概述
本博客所讲的内容基于spark2.1.0
spark sql是spark中一个用于处理结构化数据的模块。在spark中结构化数据被抽象为Dataset,它是一组有类型和Schema的数据集。而当Dataset中存放的数据类型为Row时,则Dataset成了DataFrame,DataFrame则类似于关系型数据库中的表。DataFrame可以通过api注册一个视图,从而使用类sql语句对其进行操作。
准备
IDE和scala插件的安装
本博客代码使用scala编写,所以在开始之前我们需要选择一个好的scala IDE,当前对scala有支持的IDE主要有以下3中IDE
- eclipse
- intellij idea
- NetBeans
由于博主主要使用intellij idea,所以以下的准备工作,主要是基于intellij idea。
- 通过该网站 http://www.jetbrains.com/idea/ 下载安装intellij idea。
- 安装scala插件
- 通过File->Settings打开设置面板,如下图所示:
- 通过 Plugins->Browse Repositories打开远程插件仓库。
- 搜索scala并找到插件安装(由于博主已经安装了该插件,所以图中并没有install选项)。安装后,重启IDE即可。
- 通过File->Settings打开设置面板,如下图所示:
创建scala项目
scala官方推荐使用sbt http://www.scala-sbt.org/ 来构建scala项目,但由于国内“墙”的原因,不翻墙基本没有办法使用sbt下载依赖,所以这里我们使用maven的方式来构建scala项目。构建步骤如下:
- 通过 File->New->Project打开创建项目面板
- 选择使用maven构建,并勾选 Create from archetype ,选择scala的 archetype如下图所示:
- 构建完成后,在pom文件中引入spark依赖:
<dependency> <groupId>org.apache.spark</groupId> <artifactId>spark-core_2.11</artifactId> <version>2.1.0</version> </dependency>
入门
SparkSession
Spark Sql中所有功能的入口点是SparkSession类。要创建一个基本SparkSession,只需使用SparkSession.builder():
import org.apache.spark.sql.SparkSession
val spark1 = SparkSession
.builder()
.appName("Spark SQL basic example") // 应用名称
.master("local[*]") // master地址
.config("spark.some.config.option", "some-value") // 配置信息
.getOrCreate()
// 于将RDDs转换为DataFrames的隐式转换
import spark.implicits._
简单的解释下这段代码:
- appName:应用名称,类似于应用id,一个集群不能提交两个以上名称相同的应用。
- marster:spark master的url地址
- local:本地单机的方式,一般只用作测试,可以写作local:单核,local[3]:三核,local[*]:根据计算机核心数自动分配,一般是cpu最大核心数。
- spark://IP:PORT : standalone的方式,详情可以参考 http://spark.apache.org/docs/latest/spark-standalone.html
- yarn : Spark on YARN的方式,详情可以参考 http://spark.apache.org/docs/latest/running-on-yarn.html
- mesos://host:port : Spark on Mesos的方式 详情可以参考 http://spark.apache.org/docs/latest/running-on-mesos.html
- config spark运行时的一些配置参数,spark的配置信息可以参考 http://spark.apache.org/docs/latest/configuration.html
创建DataFrames
创建SparkSession之后我们便可以使用它来加载结构化数据了,并将结构化数据转换成为DataFrame,spark支持多种格式的结构化数据,如:json,jdbc,cvs,文本,ORC,Parquet等
spark加载json数据
首先在文本中写入一些json数据:
{"id":"1", "name":"zhangsan", "age":26}
{"id":"2", "name":"lisi", "age":28}
注意:spark解析json文件时,一个json必须只占一行文本,否则便会解析出错。
scala代码:
val jsonDf = spark.read.json("data/person.json")
//加载json的第二种方式
//val jsonDf = spark.read.format("json").load("data/person.json")
jsonDf.show()
运行结果:
+---+---+-----+
|age| id| name|
+---+---+-----+
| 26| 1|luosl|
| 28| 2| lisi|
+---+---+-----+
spark加载jdbc数据
首先我们需要引入jdbc驱动:
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>5.1.39</version>
</dependency>
然后建立一张表,并插入一些数据:
create table person(
`id` varchar(100),
`name` varchar(100),
`age` int
);
insert into person values('1','zhuangsan',26);
INSERT INTO person VALUES('2','lisi',28);
scala代码:
val jdbcProps = new Properties()
import scala.collection.JavaConversions._
jdbcProps.putAll(Map("user"->"root","password"->"123456"))
val jdbcDf = spark.read.jdbc("jdbc:mysql://localhost:3306/test?characterEncoding=UTF-8&autoReconnect=true",
"person",jdbcProps)
// jdbc 的另一种加载方式
// val jdbcDf = spark.read.format("jdbc")
// .option("url", "jdbc:mysql://localhost:3306/test?characterEncoding=UTF-8&autoReconnect=true")
// .option("dbtable", "person")
// .option("user", "root")
// .option("password", "123456")
// .load()
jdbcDf.show()
结果:
+---+---------+---+
| id| name|age|
+---+---------+---+
| 1|zhuangsan| 26|
| 2| lisi| 28|
+---+---------+---+
加载scala collection和spark rdd
case class Person(id:String,name:String,age:Int)
def rddToDataFram(spark:SparkSession): Unit ={
// 在操作 scala集合或spark rdd时,需要引入隐式转换
import spark.implicits._
// 集合转datafram
val seq = Seq(Person("1","zhuangsan",26),
Person("2","lisi",24))
seq.toDF().show()
// rdd转datafram
val rdd = spark.sparkContext.parallelize(seq)
rdd.toDF().show()
}
除去上面几种格式,其他格式如:cvs,文本,ORC,Parquet,加载方式同json类似,只需要传入文件路径即可。
DataFrame的api操作
创建完成DataFrame,我么就可以对其进行一些操作了,dataframe有api,和sql两种操作方式们这里主要介绍api的方式。
import java.io.File
import org.apache.log4j.{Level, Logger}
import org.apache.spark.sql.SparkSession
/**
* Created by luosl on 2017/3/21.
*/
object DataFrameProcess extends App{
System.setProperty("hadoop.home.dir",new File("hadoop-common-2.2.0-bin-master").getAbsolutePath)
Logger.getLogger("org").setLevel(Level.ERROR)
val spark = SparkSession
.builder()
.appName("Spark dataframe process") // 应用名称
.master("local[*]") // master地址
.getOrCreate()
// 加载 dataframe
val df = spark.read.json("data/person.json")
import spark.implicits._
// 打印 df de schema
df.printSchema()
// root
// |-- age: long (nullable = true)
// |-- id: string (nullable = true)
// |-- name: string (nullable = true)
// 显示“name”列
df.select("name").show
// +--------+
// | name|
// +--------+
// |zhangsan|
// | lisi|
// | wangwu|
// | xiaomin|
// +--------+
// 显示 name 和 age+1
// $的作用是将String类型转换为ColumnName类型
df.select($"name",$"age"+1).show()
// +--------+---------+
// | name|(age + 1)|
// +--------+---------+
// |zhangsan| 27|
// | lisi| 29|
// | wangwu| 29|
// | xiaomin| 28|
// +--------+---------+
// 显示年龄大于27的人
df.filter($"age">27).show()
// +---+---+------+
// |age| id| name|
// +---+---+------+
// | 28| 2| lisi|
// | 28| 3|wangwu|
// +---+---+------+
// 根据age分组,并统计人数
df.groupBy($"age").count().show()
// +---+-----+
// |age|count|
// +---+-----+
// | 26| 1|
// | 27| 1|
// | 28| 2|
// +---+-----+
// 一个join的例子
case class Types(age:Int,types:String)
val typeDf = Seq(Types(26,"青年"),Types(27,"中年"),Types(28,"老年")).toDF()
df.join(typeDf,df("age") === typeDf("age")).groupBy($"types").count().show()
// +-----+-----+
// |types|count|
// +-----+-----+
// | 老年| 2|
// | 青年| 1|
// | 中年| 1|
// +-----+-----+
// 删除age列
df.drop($"age").show()
// +---+--------+
// | id| name|
// +---+--------+
// | 1|zhangsan|
// | 2| lisi|
// | 3| wangwu|
// | 3| xiaomin|
// +---+--------+
}
DataFrame的sql操作
一个dataframe可以注册为一个视图,从而通过sparksession对其进行一些类sql的查询,并返回一个dataframe的结果。
在spark中存在2中视图:
- TempView:临时视图,临时视图是会话范围的,如果创建它的会话终止,它将消失。
- GlobalTempView:全局临时视图,这种视图可以再会话之间共享。。全局临时视图绑定到系统保留的数据库global_temp,所以访问全局视图时,需要加上数据库前缀如:select * from global_temp.person_g
import java.io.File
import org.apache.log4j.{Level, Logger}
import org.apache.spark.sql.SparkSession
object DataFrameSql extends App{
System.setProperty("hadoop.home.dir",new File("hadoop-common-2.2.0-bin-master").getAbsolutePath)
Logger.getLogger("org").setLevel(Level.ERROR)
val spark = SparkSession
.builder()
.appName("Spark dataframe sql") // 应用名称
.master("local[*]") // master地址
.getOrCreate()
import spark.implicits._
case class Person(id:String,name:String,age:Int)
// 创建一个dataframe
val personDf = Seq(
Person("1","zhangsan",25),
Person("2","lisi",25),
Person("2","wangwu",27),
Person("2","xiaomin",28)
).toDF()
// 使用sql直接访问json文件
spark.sql("select * from json.`data/person.json`").show()
// +---+---+--------+
// |age| id| name|
// +---+---+--------+
// | 26| 1|zhangsan|
// | 28| 2| lisi|
// | 28| 3| wangwu|
// | 27| 3| xiaomin|
// +---+---+--------+
// 将dataframe注册成一个临时视图
personDf.createOrReplaceTempView("person")
spark.sql("select * from person where age>25").show()
// +---+-------+---+
// | id| name|age|
// +---+-------+---+
// | 2| wangwu| 27|
// | 2|xiaomin| 28|
// +---+-------+---+
// 注册为一个全局临时视图
personDf.createGlobalTempView("person_g")
spark.sql("select age,count(*) from global_temp.person_g group by age").show()
// +---+--------+
// |age|count(1)|
// +---+--------+
// | 28| 1|
// | 27| 1|
// | 25| 2|
// +---+--------+
}
udf
udf(User Defined Function)用户自定义函数允许用户自行定义函数,并在dataframe或sql使用,是对spark dataframe 的一种扩展。
import java.io.File
import org.apache.log4j.{Level, Logger}
import org.apache.spark.sql.SparkSession
object DataFrameUDF extends App{
System.setProperty("hadoop.home.dir",new File("hadoop-common-2.2.0-bin-master").getAbsolutePath)
Logger.getLogger("org").setLevel(Level.ERROR)
val spark = SparkSession
.builder()
.appName("Spark dataframe udfa") // 应用名称
.master("local[*]") // master地址
.getOrCreate()
import spark.implicits._
// 创建一个dataframe
val df = Seq("str1","str2sa").toDF()
// 注册一张临时视图
df.createOrReplaceTempView("tbl")
// 注册udf
spark.udf.register("len1",(str:String)=>str.length)
// 另一种注册udf的方式
def len(str:String) = str.length
spark.udf.register("len2",len _)
// 通过sql使用udf
spark.sql("select len1(value) from tbl").show()
// 在 dataframe中使用udf
df.filter("len2(value)>2").show()
}
udaf
udaf(User Defined Aggregate Function)用户自定义聚合函数,类似于mysql中的聚合函数,它能够将多行行输入进行聚合运算。一下例子,为mysql中 group_concat 的spark udaf实现:
import java.io.File
import org.apache.log4j.{Level, Logger}
import org.apache.spark.sql.{Row, SparkSession}
import org.apache.spark.sql.expressions.{MutableAggregationBuffer, UserDefinedAggregateFunction}
import org.apache.spark.sql.types.{DataType, StringType, StructField, StructType}
object DataFrameUDAF extends App{
System.setProperty("hadoop.home.dir",new File("hadoop-common-2.2.0-bin-master").getAbsolutePath)
Logger.getLogger("org").setLevel(Level.ERROR)
val spark = SparkSession
.builder()
.appName("Spark dataframe udaf") // 应用名称
.master("local[*]") // master地址
.getOrCreate()
import spark.implicits._
case class Person(name:String,work:String)
Seq(Person("luosl","it"),
Person("xiaomin","it"),
Person("lisi","教师")
).toDF().createOrReplaceTempView("person")
val groupConcat = new GroupConcat()
spark.udf.register("group_concat",groupConcat)
spark.sql("select work,group_concat(name) from person group by work").show()
// +----+-----------------+
// |work|groupconcat(name)|
// +----+-----------------+
// |教师| lisi|
// | it| luosl,xiaomin|
// +----+-----------------+
}
class GroupConcat extends UserDefinedAggregateFunction {
//UDAF与DataFrame列有关的输入样式,StructField的名字并没有特别要求,完全可以认为是两个内部结构的列名占位符。
override def inputSchema: StructType = StructType(Seq(StructField("str",StringType)))
//定义存储聚合运算时产生的中间数据结果的Schema
override def bufferSchema: StructType = StructType(Seq(StructField("buff",StringType)))
//标明了UDAF函数的返回值类型
override def dataType: DataType = StringType
//用以标记针对给定的一组输入,UDAF是否总是生成相同的结果
override def deterministic: Boolean = true
//对聚合运算中间结果的初始化
override def initialize(buffer: MutableAggregationBuffer): Unit = buffer.update(0,"")
//第二个参数input: Row对应着bufferSchema方法中的数据数据
override def update(buffer: MutableAggregationBuffer, input: Row): Unit = {
buffer.update(0,input.getString(0))
}
//负责合并两个聚合运算的buffer,再将其存储到MutableAggregationBuffer中
override def merge(buffer1: MutableAggregationBuffer, buffer2: Row): Unit = {
buffer1.getString(0).trim match {
case "" => buffer1.update(0,buffer2.getString(0))
case _ =>buffer1.update(0,buffer1.getString(0)+","+buffer2.getString(0))
}
}
//完成对聚合Buffer值的运算,得到最后的结果
override def evaluate(buffer: Row): Any = buffer.getString(0)
}
spark sql的元数据操作 catalog
catalog是spark sql元数据服务的接口,有点类似于jdbc中metadata.通过catalog我们可以对spark的元数据做事写管理查询操作,如:查询已经存在的表删除,修改数据库/视图,创建外部表等。下面例子列举了catalog部分功能:
import java.io.File
import org.apache.log4j.{Level, Logger}
import org.apache.spark.sql.SparkSession
object DataFrameCatalog extends App{
System.setProperty("hadoop.home.dir",new File("hadoop-common-2.2.0-bin-master").getAbsolutePath)
Logger.getLogger("org").setLevel(Level.ERROR)
val spark = SparkSession
.builder()
.appName("Spark dataframe catalog") // 应用名称
.master("local[*]") // master地址
.getOrCreate()
import spark.implicits._
val df = Seq(("1","zhangsan","26")).toDF("id","name","age")
df.createOrReplaceTempView("person_1")
df.createOrReplaceTempView("person_2")
//显示所有视图/表信息
spark.catalog.listTables().show()
// +--------+--------+-----------+---------+-----------+
// | name|database|description|tableType|isTemporary|
// +--------+--------+-----------+---------+-----------+
// |person_1| null| null|TEMPORARY| true|
// |person_2| null| null|TEMPORARY| true|
// +--------+--------+-----------+---------+-----------+
//显示表字段信息
spark.catalog.listColumns("person_1").show()
// +----+-----------+--------+--------+-----------+--------+
// |name|description|dataType|nullable|isPartition|isBucket|
// +----+-----------+--------+--------+-----------+--------+
// | id| null| string| true| false| false|
// |name| null| string| true| false| false|
// | age| null| string| true| false| false|
// +----+-----------+--------+--------+-----------+--------+
// 显示所有数据库信息
spark.catalog.listDatabases().show()
// +-------+----------------+--------------------+
// | name| description| locationUri|
// +-------+----------------+--------------------+
// |default|default database|file:/D:/我的工程/spa...|
// +-------+----------------+--------------------+
// 创建外表并查询
spark.catalog.createExternalTable("person_3","data/person.json","json")
spark.sql("select * from person_3").show()
// +---+---+--------+
// |age| id| name|
// +---+---+--------+
// | 26| 1|zhangsan|
// | 28| 2| lisi|
// | 28| 3| wangwu|
// | 27| 3| xiaomin|
// +---+---+--------+
// 删除person_1视图
spark.catalog.dropTempView("person_1")
spark.catalog.listTables().show()
// +--------+--------+-----------+---------+-----------+
// | name|database|description|tableType|isTemporary|
// +--------+--------+-----------+---------+-----------+
// |person_3| default| null| EXTERNAL| false|
// |person_2| null| null|TEMPORARY| true|
// +--------+--------+-----------+---------+-----------+
}
dataframe保存
dataframe支持保存为json,parquet,jdbc,orc,libsvm,csv,text,7种数据格式,其默认保存格式为parquet。其中libsvm是一种机器学习格式,并不是所有结构化数据都能直接转换成libsvm。
保存操作可以选择SaveMode,它指定了保存的方式:
- SaveMode.ErrorIfExists (默认): 将DataFrame保存到数据源时,如果数据已存在,则会抛出异常。
- SaveMode.Append: 将DataFrame保存到数据源时,如果data / table已存在,则DataFrame的内容将被追加到现有数据。
- SaveMode.Overwrite:将DataFrame保存到数据源时,如果数据/表已存在,则现有数据将被DataFrame的内容覆盖。
- SaveMode.Ignore:将DataFrame保存到数据源时,如果数据已存在,则保存操作不会执行,也不会更改现有数据。这类似于关系型数据库中的CREATE TABLE IF NOT EXISTS。
import java.io.File
import com.luosl.sparkexample.dataframe.DataFrameSql.Person
import org.apache.log4j.{Level, Logger}
import org.apache.spark.ml.feature.LabeledPoint
import org.apache.spark.ml.linalg.Vectors
import org.apache.spark.sql.{SaveMode, SparkSession}
object DataFrameSave extends App{
System.setProperty("hadoop.home.dir",new File("hadoop-common-2.2.0-bin-master").getAbsolutePath)
Logger.getLogger("org").setLevel(Level.ERROR)
val spark = SparkSession
.builder()
.appName("Spark dataframe save") // 应用名称
.master("local[*]") // master地址
.getOrCreate()
import spark.implicits._
Seq(
Person("1","zhangsan",25),
Person("2","lisi",25),
Person("2","wangwu",27),
Person("2","xiaomin",28)
).toDF().createOrReplaceTempView("person")
val df = spark.sql("select name,age from person")
// 以json Overwrite的方式保存
df.write.format("json").mode(SaveMode.Overwrite).save("save/person_json")
// 以jdbc Overwrite的方式保存
df.write.format("jdbc")
.option("url", "jdbc:mysql://localhost:3306/test?characterEncoding=UTF-8")
.option("dbtable", "person_jdbc")
.option("user", "root")
.option("password", "123456")
// 设置隔离级别
//spark jdbc 共有 NONE,READ_COMMITTED,READ_UNCOMMITTED,REPEATABLE_READ,SERIALIZABLE 五个隔离级别,
// 这五个隔离级别 并不是所有数据库都支持
// 默认为 READ_UNCOMMITTED,理论上从左到右 性能依次递减
.option("isolationLevel","NONE")
.mode(SaveMode.Overwrite).save()
// 以parquet Overwrite的方式保存
df.write.mode(SaveMode.Overwrite).save("save/person_parquet")
// 以orc Overwrite的方式保存
df.write.format("orc").mode(SaveMode.Overwrite).save("save/person_orc")
// 保存 libsvm
// libsvm 是一种支持向量机数据集,在sparkmllib表现形式中是一组标签点(LabeledPoint)集合
val lp1 = LabeledPoint(1.0, Vectors.dense(1.0, 2.0, 3.0))
val lp2 = LabeledPoint(0.0, Vectors.dense(4.0, 5.0, 6.0))
val svmDf = Seq(lp1,lp2).toDF("label","features")
svmDf.write.mode(SaveMode.Overwrite).format("libsvm").save("save/libsvm")
// 以csv Overwrite的方式保存
df.write.format("csv").mode(SaveMode.Overwrite).save("save/person_csv")
// 以text Overwrite的方式保存
// text保存的方式只支持单列的dataframe,所以保存前,我们需要将数据进行一定的处理
df.map(_.mkString("|")).write.format("text").mode(SaveMode.Overwrite).save("save/person_text")
}
参考:http://spark.apache.org/docs/latest/sql-programming-guide.html