文章目录
一.SQL on Hadoop
SQL是一种传统的用来进行数据分析的标准
- Hive是原始的SQL-on-Hadoop解决方案
- Impala:和Hive一样,提供了一种可以针对已有Hadoop数据编写SQL查询的方法
- Presto:类似于Impala,未被主要供应商支持
- Shark:Spark SQL的前身,设计目标是作为Hive的补充
- Phoenix:基于HBase的开源SQL查询引擎
二.Spark SQL
1.Spark SQL前身
- Shark的初衷:让Hive运行在Spark之上
- 是对Hive的改造,继承了大量Hive代码,给优化和维护带来了大量的麻烦
2.Spark SQL架构
- Spark SQL是Spark的核心组件之一(2014.4 Spark1.0)
- 能够直接访问现存的Hive数据
- 提供JDBC/ODBC接口供第三方工具借助Spark进行数据处理
- 提供了更高层级的接口方便地处理数据
- 支持多种操作方式:SQL、API编程
- 支持多种外部数据源:Parquet、JSON、RDBMS等
3.Spark SQL运行原理
- Catalyst优化器是Spark SQL的核心
- Catalyst Optimizer:Catalyst优化器,将逻辑计划转为物理计划
Catalyst优化器
SELECT name FROM(SELECT id, name FROM people) p WHERE p.id = 1
三.Spark SQL API
SparkContext
SQLContext:Spark SQL的编程入口
HiveContext:SQLContext的子集,包含更多功能
SparkSession(Spark 2.x推荐)
- SparkSession:合并了SQLContext与HiveContext
- 提供与Spark功能交互单一入口点,并允许使用DataFrame和Dataset API对Spark进行编程
1.Dataset (Spark 1.6+)
特定域对象中的强类型集合
scala> spark.createDataset(1 to 3).show
scala> spark.createDataset(List(("a",1),("b",2),("c",3))).show
scala> spark.createDataset(sc.parallelize(List(("a",1,1),("b",2,2)))).show
/*
1、createDataset()的参数可以是:Seq、Array、RDD
2、上面三行代码生成的Dataset分别是:
Dataset[Int]、Dataset[(String,Int)]、Dataset[(String,Int,Int)]
3、Dataset=RDD+Schema,所以Dataset与RDD有大部共同的函数,如map、filter等
*/
使用Case Class创建Dataset
case class Point(label:String,x:Double,y:Double)
case class Category(id:Long,name:String)
val points=Seq(Point("bar",3.0,5.6),Point("foo",-1.0,3.0)).toDS
val categories=Seq(Category(1,"foo"), Category(2,"bar")).toDS
points.join(categories,points("label")===categories("name")).show
//典型应用场景RDD->Dataset
case class Point(label:String,x:Double,y:Double)
case class Category(id:Long,name:String)
val pointsRDD=sc.parallelize(List(("bar",3.0,5.6),("foo",-1.0,3.0)))
val categoriesRDD=sc.parallelize(List((1,"foo"),(2,"bar")))
val points=pointsRDD.map(line=>Point(line._1,line._2,line._3)).toDS
val categories=categories.map(line=>Category(line._1,line._2)).toDS
points.join(categories,points("label")===categories("name")).show
/*
1、无特殊说明时,下文中“spark”均指SparkSession实例
2、如果是spark-shell下,会自动创建“sc”和“spark”
*/
val spark = SparkSession.builder.master("local[*]").appName("test").getOrCreate()
2.DataFrame (Spark 1.4+)
- DataFrame=Dataset[Row]
- 类似传统数据的二维表格
- 在RDD基础上加入了Schema(数据结构信息)
- DataFrame Schema支持嵌套数据类型:struct,map,array
- 提供更多类似SQL操作的API
创建DataFrame
/** 将JSON文件转成DataFrame
* people.json内容如下
* {"name":"Michael"}
* {"name":"Andy", "age":30}
* {"name":"Justin", "age":19}
*/
val df = spark.read.json("file:///home/hadoop/data/people.json")
// 使用show方法将DataFrame的内容输出
df.show
DataFrame API常用操作
val df = spark.read.json("file:///home/hadoop/data/people.json")
// 使用printSchema方法输出DataFrame的Schema信息
df.printSchema()
// 使用select方法来选择我们所需要的字段
df.select("name").show()
// 使用select方法选择我们所需要的字段,并未age字段加1
df.select(df("name"), df("age") + 1).show()
// 使用filter方法完成条件过滤
df.filter(df("age") > 21).show()
// 使用groupBy方法进行分组,求分组后的总数
df.groupBy("age").count().show()
//sql()方法执行SQL查询操作
df.createOrReplaceTempView("people")
spark.sql("SELECT * FROM people").show
RDD–>DataFrame
//方式一:通过反射获取RDD内的Schema
case class Person(name:String,age:Int)
import spark.implicits._
val people=sc.textFile("file:///home/hadooop/data/people.txt")
.map(_.split(","))
.map(p => Person(p(0), p(1).trim.toInt)).toDF()
people.show
people.createOrReplaceTempView("people")
val teenagers = spark.sql("SELECT name, age FROM people WHERE age >= 13 AND age <= 19")
teenagers.show()
//方式二:通过编程接口指定Schema
case class Person(name:String,age:Int)
val people=sc.textFile("file:///data/people.txt")
// 以字符串的方式定义DataFrame的Schema信息
val schemaString = "name age"
//导入所需要的类
import org.apache.spark.sql.Row
import org.apache.spark.sql.types.{StructType, StructField, StringType}
// 根据自定义的字符串schema信息产生DataFrame的Schema
val schema = StructType(schemaString.split(" ").map(fieldName =>StructField(fieldName,StringType, true)))
//将RDD转换成Row
val rowRDD = people.map(_.split(",")).map(p => Row(p(0), p(1).trim))
// 将Schema作用到RDD上
val peopleDataFrame = spark.createDataFrame(rowRDD, schema)
// 将DataFrame注册成临时表
peopleDataFrame.createOrReplaceTempView("people")
val results = spark.sql("SELECT name FROM people")
results.show
DataFrame ->RDD
/** people.json内容如下
* {"name":"Michael"}
* {"name":"Andy", "age":30}
* {"name":"Justin", "age":19}
*/
val df = spark.read.json("file:///home/hadoop/data/people.json")
//将DF转为RDD
df.rdd.collect
四.Spark SQL支持的外部数据源
1.Parquet文件:是一种流行的列式存储格式,以二进制存储,文件中包含数据与元数据
//Spark SQL写parquet文件
import org.apache.spark.sql.types.{StructType, StructField, StringType,ArrayType,IntegerType}
val schema=StructType(Array(StructField("name",StringType),StructField("favorite_color",StringType),StructField("favorite_numbers",ArrayType(IntegerType))))
val rdd=sc.parallelize(List(("Alyssa",null,Array(3,9,15,20)),("Ben","red",null)))
import org.apache.spark.sql.Row
val rowRDD=rdd.map(p=>Row(p._1,p._2,p._3))
val df=spark.createDataFrame(rowRDD,schema)
df.write.parquet("/data/users") //在该目录下生成parquet文件
//Spark SQL读parquet文件
val df=spark.read.parquet("/data/users") //该目录下存在parquet文件
df.show
df.printSchema
2.Hive表
/*
Spark SQL与Hive集成:
1、hive-site.xml拷贝至${SPARK_HOME}/conf下
2、检查hive.metastore.uris是否正确
3、启动元数据服务:$hive --service metastore
4 启动spark-shell
spark-shell –master spark://hadoop01:7077
--driver-class-path /opt/install/spark/jars/mysql-connector-java-5.1.35-bin.jar
*/
#创建一个Hive表
hive>create table toronto(full_name string, ssn string, office_address string);
hive>insert into toronto(full_name, ssn, office_address) values('John S. ', '111-222-333 ', '123 Yonge Street ');
//集成Hive后spark-shell下可直接访问Hive表
val df=spark.table("toronto")
df.printSchema
df.show
//IDEA中使用,需将hive-site.xml拷贝至resources 或者使用config指定
val spark = SparkSession.builder()
.master("local[*]")
.enableHiveSupport()
//.config("hive.metastore.uris","thrift://192.168.11.220:9083")
.getOrCreate()
val df = spark.sql("select * from toronto")
df.filter($"ssn".startsWith("111")).write.saveAsTable("t1")
3.RDBMS表
$spark-shell --jars /opt/spark/ext_jars/mysql-connector-java-5.1.38.jar
val url = "jdbc:mysql://localhost:3306/retail_db"
val tableName = "customers"
// 设置连接用户、密码、数据库驱动类
val prop = new java.util.Properties
prop.setProperty("user", "root")
prop.setProperty("password", "sunyong")
prop.setProperty("driver","com.mysql.jdbc.Driver")
// 取得该表数据
val jdbcDF = spark.read.jdbc(url,tableName,prop)
jdbcDF.show(1)
//DF存为新的表在MySQL中,模式可为append追加或overwrite覆盖 ignore数据存在则忽略 默认ErrorlfExists
jdbcDF.write.mode("append").jdbc(url,"t1",prop)
//存储为临时表
jdbcDF.createOrReplaceTempView("cust")
//存储到hive表中
spark.sql("create table demo.customers as select * from cust")
spark.table("demo.customers").show(1)
五.Spark SQL函数
内置函数(org.apache.spark.sql.functions._)
类别 | 函数举例 |
---|---|
聚合函数 | count(),countDistinct(),avg(),max(),min() |
集合函数 | sort_array、explode |
日期、时间函数 | hour、quarter、next_day |
数学函数 | asin、atan、sqrt、tan、round |
开窗函数 | row_number |
字符串函数 | concat、format_number、regexp_extract |
其他函数 | isNaN、sha、randn、callUDF |
内置函数的使用
//模拟用户访问日志信息
val accessLog = Array(
"2016-12-27,001",
"2016-12-27,001",
"2016-12-27,002",
"2016-12-28,003",
"2016-12-28,004",
"2016-12-28,002",
"2016-12-28,002",
"2016-12-28,001"
)
import org.apache.spark.sql.Row
import org.apache.spark.sql.types.{IntegerType, StringType, StructField, StructType}
val accessLogRDD = sc.parallelize(accessLog).map(row => {
val splited = row.split(",")
Row(splited(0), splited(1).toInt)
})
val structTypes = StructType(Array(
StructField("day", StringType, true),
StructField("userId", IntegerType, true)
))
//根据数据以及Schema信息生成DataFrame
val accessLogDF = spark.createDataFrame(accessLogRDD, structTypes)
//导入Spark SQL内置的函数
import org.apache.spark.sql.functions._
//求每天所有的访问量(pv)
accessLogDF.groupBy("day").agg(count("userId").as("pv")).select("day", "pv").collect.foreach(println)
//求每天的去重访问量(uv)
accessLogDF.groupBy("day").agg(countDistinct('userId).as("uv")).select("day", "uv").collect.foreach(println)
自定义函数
- 1)定义函数
- 2)注册函数
- SparkSession.udf.register():只在sql()中有效
- functions.udf():对DataFrame API均有效
- 3)函数调用
//第一种方式 只在sql()中有效
spark.udf.register("concatFL",(x:String,y:String)=>x+" "+y)
spark.sql("select concatFL(customer_fname,customer_lname) as fullName from demo.customers").show(1)
//第二种方式 对DataFrame API均有效
import org.apache.spark.sql.expressions.UserDefinedFunction
import org.apache.spark.sql.functions._
val concatFL:UserDefinedFunction = udf((x:String,y:String)=>x+" "+y)
spark.table("demo.customers").select(concatFL($"customer_fname",$"customer_lname") as "fullName").show(1)
自定义函数实例
- 需求:用户行为喜好个数统计
- 输入数据格式:
hobbies.txt:
alice jogging,Coding,cooking
lina travel,dance
- 输出数据格式:
alice jogging,Coding,cooking 3
lina travel,dance 2
case class Hobbies(name: String, hobbies: String)
val info = sc.textFile("/data/hobbies.txt")
//需要手动导入一个隐式转换,否则RDD无法转换成DF
import spark.implicits._
val hobbyDF = info.map(_.split("\t")).map(p => Hobbies(p(0), p(1))).toDF
hobbyDF.show
hobbyDF.createOrReplaceTempView("hobbies")
//注册自定义函数,注意是匿名函数
spark.udf.register("hobby_num", (s: String) => s.split(',').size)
spark.sql("select name, hobbies, hobby_num(hobbies) as hobby_num from hobbies").show
六.Spark SQL CLI
Spark SQL CLI是在本地模式下使用Hive元存储服务和执行从命令行所输入查询语句的简便工具
注意,Spark SQL CLI无法与thrift JDBC服务器通信
Spark SQL CLI等同于Hive CLI(old CLI)、Beeline CLI(new CLI)
启动Spark SQL CLI,请在Spark目录中运行以下内容:./bin/spark-sql
将hive-site.xml、hdfs-site.xml、core-site.xml复制到$SPARK_HOME/conf目录下
Spark SQL 示例
$spark-sql
spark-sql> show databases;
default
spark-sql> show tables;
default toronto false
spark-sql> select * from toronto where ssn like '111%';
John S. 111-222-333 123 Yonge Street
spark-sql> create table montreal(full_name string, ssn string, office_address string);
spark-sql> insert into montreal values('Winnie K. ', '111-222-333 ', '62 John Street');
spark-sql> select t.full_name, m.ssn, t.office_address, m.office_address from toronto t inner join montreal m on t.ssn = m.ssn;
John S. 111-222-333 123 Yonge Street 62 John Street