spark sql基础与示例

最新推荐文章于 2024-05-10 20:03:52 发布

不粘人的小妖精

最新推荐文章于 2024-05-10 20:03:52 发布

阅读量1.3k

点赞数

分类专栏： hadoop 文章标签： sparkSQL

hadoop 专栏收录该内容

2 篇文章 0 订阅

订阅专栏

转自https://www.jianshu.com/p/a27f5f5f14e5； https://blog.csdn.net/feloxx/article/details/72819964

一、简介

Spark SQL是Spark中处理结构化数据的模块。与基础的Spark RDD API不同，Spark SQL的接口提供了更多关于数据的结构信息和计算任务的运行时信息。在Spark内部，Spark SQL会能够用于做优化的信息比RDD API更多一些。Spark SQL如今有了三种不同的API：SQL语句、DataFrame API和最新的Dataset API。不过真正运行计算的时候，无论你使用哪种API或语言，Spark SQL使用的执行引擎都是同一个。这种底层的统一，使开发者可以在不同的API之间来回切换，你可以选择一种最自然的方式，来表达你的需求。
（本文针对spark1.6版本，示例语言为Scala）

二、概念

1. SQL。Spark SQL的一种用法是直接执行SQL查询语句，你可使用最基本的SQL语法，也可以选择HiveQL语法。Spark SQL可以从已有的Hive中读取数据。更详细的请参考Hive Tables 这一节。如果用其他编程语言运行SQL，Spark SQL将以DataFrame返回结果。你还可以通过命令行command-line 或者 JDBC/ODBC 使用Spark SQL。

2. DataFrame。是一种分布式数据集合，每一条数据都由几个命名字段组成。概念上来说，她和关系型数据库的表或者 R和Python中的data frame等价，只不过在底层，DataFrame采用了更多优化。DataFrame可以从很多数据源（sources）加载数据并构造得到，如：结构化数据文件，Hive中的表，外部数据库，或者已有的RDD。
DataFrame API支持Scala, Java, Python, and R。

3. Datasets。是Spark-1.6新增的一种API，目前还是实验性的。Dataset想要把RDD的优势（强类型，可以使用lambda表达式函数）和Spark SQL的优化执行引擎的优势结合到一起。Dataset可以由JVM对象构建（constructed ）得到，而后Dataset上可以使用各种transformation算子（map，flatMap，filter 等）。
Dataset API 对 Scala 和 Java的支持接口是一致的，但目前还不支持Python，不过Python自身就有语言动态特性优势（例如，你可以使用字段名来访问数据，row.columnName）。对Python的完整支持在未来的版本会增加进来。

三、创建并操作DataFrame

Spark应用可以用SparkContext创建DataFrame，所需的数据来源可以是已有的RDD（existing RDD
），或者Hive表，或者其他数据源（data sources.）以下是一个从JSON文件创建并操作DataFrame的小例子：

val sc: SparkContext // 已有的 SparkContext.
val sqlContext = new org.apache.spark.sql.SQLContext(sc)

val df = sqlContext.read.json("examples/src/main/resources/people.json")

// 将DataFrame内容打印到stdout
df.show()
// age  name
// null Michael
// 30   Andy
// 19   Justin

// 打印数据树形结构
df.printSchema()
// root
// |-- age: long (nullable = true)
// |-- name: string (nullable = true)

// select "name" 字段
df.select("name").show()
// name
// Michael
// Andy
// Justin

// 展示所有人，但所有人的 age 都加1
df.select(df("name"), df("age") + 1).show()
// name    (age + 1)
// Michael null
// Andy    31
// Justin  20

// 筛选出年龄大于21的人
df.filter(df("age") > 21).show()
// age name
// 30  Andy

// 计算各个年龄的人数
df.groupBy("age").count().show()
// age  count
// null 1
// 19   1
// 30   1

SQLContext.sql可以执行一个SQL查询，并返回DataFrame结果。

val sqlContext = ... // 已有一个 SQLContext 对象
val df = sqlContext.sql("SELECT * FROM table")

三、spark SQL与RDD互操作

Spark SQL有两种方法将RDD转为DataFrame。分别为反射机制和编程方式。

1. 利用反射推导schema。####

Spark SQL的Scala接口支持自动将包含case class对象的RDD转为DataFrame。对应的case class定义了表的schema。case class的参数名通过反射，映射为表的字段名。case class还可以嵌套一些复杂类型，如Seq和Array。RDD隐式转换成DataFrame后，可以进一步注册成表。随后，你就可以对表中数据使用 SQL语句查询了。

// sc 是已有的 SparkContext 对象
val sqlContext = new org.apache.spark.sql.SQLContext(sc)
// 为了支持RDD到DataFrame的隐式转换
import sqlContext.implicits._

// 定义一个case class.
// 注意：Scala 2.10的case class最多支持22个字段，要绕过这一限制，
// 你可以使用自定义class，并实现Product接口。当然，你也可以改用编程方式定义schema
case class Person(name: String, age: Int)

// 创建一个包含Person对象的RDD，并将其注册成table
val people = sc.textFile("examples/src/main/resources/people.txt").map(_.split(",")).map(p => Person(p(0), p(1).trim.toInt)).toDF()
people.registerTempTable("people")

// sqlContext.sql方法可以直接执行SQL语句
val teenagers = sqlContext.sql("SELECT name, age FROM people WHERE age >= 13 AND age <= 19")

// SQL查询的返回结果是一个DataFrame，且能够支持所有常见的RDD算子
// 查询结果中每行的字段可以按字段索引访问:
teenagers.map(t => "Name: " + t(0)).collect().foreach(println)

// 或者按字段名访问:
teenagers.map(t => "Name: " + t.getAs[String]("name")).collect().foreach(println)

// row.getValuesMap[T] 会一次性返回多列，并以Map[String, T]为返回结果类型
teenagers.map(_.getValuesMap[Any](List("name", "age"))).collect().foreach(println)
// 返回结果: Map("name" -> "Justin", "age" -> 19)

2. 编程方式定义Schema。####

如果不能事先通过case class定义schema（例如，记录的字段结构是保存在一个字符串，或者其他文本数据集中，需要先解析，又或者字段对不同用户有所不同），那么你可能需要按以下三个步骤，以编程方式的创建一个DataFrame：

从已有的RDD创建一个包含Row对象的RDD，用StructType创建一个schema，和步骤1中创建的RDD的结构相匹配，把得到的schema应用于包含Row对象的RDD，调用这个方法来实现这一步：SQLContext.createDataFrame
例如：

// sc 是已有的SparkContext对象
val sqlContext = new org.apache.spark.sql.SQLContext(sc)

// 创建一个RDD
val people = sc.textFile("examples/src/main/resources/people.txt")

// 数据的schema被编码与一个字符串中
val schemaString = "name age"

// Import Row.
import org.apache.spark.sql.Row;

// Import Spark SQL 各个数据类型
import org.apache.spark.sql.types.{StructType,StructField,StringType};

// 基于前面的字符串生成schema
val schema =
  StructType(
    schemaString.split(" ").map(fieldName => StructField(fieldName, StringType, true)))

// 将RDD[people]的各个记录转换为Rows，即：得到一个包含Row对象的RDD
val rowRDD = people.map(_.split(",")).map(p => Row(p(0), p(1).trim))

// 将schema应用到包含Row对象的RDD上，得到一个DataFrame
val peopleDataFrame = sqlContext.createDataFrame(rowRDD, schema)

// 将DataFrame注册为table
peopleDataFrame.registerTempTable("people")

// 执行SQL语句
val results = sqlContext.sql("SELECT name FROM people")

// SQL查询的结果是DataFrame，且能够支持所有常见的RDD算子
// 并且其字段可以以索引访问，也可以用字段名访问
results.map(t => "Name: " + t(0)).collect().foreach(println)

四、spark SQL与其它数据源的连接与操作

Spark SQL支持基于DataFrame操作一系列不同的数据源。DataFrame既可以当成一个普通RDD来操作，也可以将其注册成一个临时表来查询。把 DataFrame注册为table之后，你就可以基于这个table执行SQL语句了。本节将描述加载和保存数据的一些通用方法，包含了不同的 Spark数据源，然后深入介绍一下内建数据源可用选项。
　　在最简单的情况下，所有操作都会以默认类型数据源来加载数据（默认是Parquet，除非修改了spark.sql.sources.default 配置）。

val df = sqlContext.read.load("examples/src/main/resources/users.parquet")
df.select("name", "favorite_color").write.save("namesAndFavColors.parquet")

你也可以手动指定数据源，并设置一些额外的选项参数。数据源可由其全名指定（如，org.apache.spark.sql.parquet），而对于内建支持的数据源，可以使用简写名（json, parquet, jdbc）。任意类型数据源创建的DataFrame都可以用下面这种语法转成其他类型数据格式。

val df = sqlContext.read.format("json").load("examples/src/main/resources/people.json")
df.select("name", "age").write.format("parquet").save("namesAndAges.parquet")

Spark SQL还支持直接对文件使用SQL查询，不需要用read方法把文件加载进来。

val df = sqlContext.sql("SELECT * FROM parquet.`examples/src/main/resources/users.parquet`")

1. 连接JSON数据集####

Spark SQL在加载JSON数据的时候，可以自动推导其schema并返回DataFrame。用SQLContext.read.json读取一个包含String的RDD或者JSON文件，即可实现这一转换。

注意，通常所说的json文件只是包含一些json数据的文件，而不是我们所需要的JSON格式文件。JSON格式文件必须每一行是一个独立、完整的的JSON对象。因此，一个常规的多行json文件经常会加载失败。

// sc是已有的SparkContext对象
val sqlContext = new org.apache.spark.sql.SQLContext(sc)

// 数据集是由路径指定的
// 路径既可以是单个文件，也可以还是存储文本文件的目录
val path = "examples/src/main/resources/people.json"
val people = sqlContext.read.json(path)

// 推导出来的schema，可由printSchema打印出来
people.printSchema()
// root
//  |-- age: integer (nullable = true)
//  |-- name: string (nullable = true)

// 将DataFrame注册为table
people.registerTempTable("people")

// 跑SQL语句吧！
val teenagers = sqlContext.sql("SELECT name FROM people WHERE age >= 13 AND age <= 19")

// 另一种方法是，用一个包含JSON字符串的RDD来创建DataFrame
val anotherPeopleRDD = sc.parallelize(
  """{"name":"Yin","address":{"city":"Columbus","state":"Ohio"}}""" :: Nil)
val anotherPeople = sqlContext.read.json(anotherPeopleRDD)

2. 连接Hive表

Spark SQL支持从Apache Hive读写数据。然而，Hive依赖项太多，所以没有把Hive包含在默认的Spark发布包里。要支持Hive，需要在编译spark的时候增加-Phive和 -Phive-thriftserver标志。这样编译打包的时候将会把Hive也包含进来。注意，hive的jar包也必须出现在所有的worker节点上，访问Hive数据时候会用到（如：使用hive的序列化和反序列化SerDes时）。
　　Hive配置在conf/目录下hive-site.xml，core-site.xml（安全配置），hdfs-site.xml（HDFS配置）文件中。请注意，如果在YARN cluster（yarn-cluster mode）模式下执行一个查询的话，lib_mananged/jar/下面的datanucleus 的jar包，和conf/下的hive-site.xml必须在驱动器（driver）和所有执行器（executor）都可用。一种简便的方法是，通过 spark-submit命令的–jars和–file选项来提交这些文件。
　　如果使用Hive，则必须构建一个HiveContext，HiveContext是派生于SQLContext的，添加了在Hive Metastore里查询表的支持，以及对HiveQL的支持。用户没有现有的Hive部署，也可以创建一个HiveContext。如果没有在 hive-site.xml里配置，那么HiveContext将会自动在当前目录下创建一个metastore_db目录，再根据HiveConf设置创建一个warehouse目录（默认/user/hive/warehourse）。所以请注意，你必须把/user/hive/warehouse的写权限赋予启动spark应用程序的用户。

// sc是一个已有的SparkContext对象
val sqlContext = new org.apache.spark.sql.hive.HiveContext(sc)

sqlContext.sql("CREATE TABLE IF NOT EXISTS src (key INT, value STRING)")
sqlContext.sql("LOAD DATA LOCAL INPATH 'examples/src/main/resources/kv1.txt' INTO TABLE src")

// 这里用的是HiveQL
sqlContext.sql("FROM src SELECT key, value").collect().foreach(println)

3. 用JDBC连接其他数据库####

Spark SQL也可以用JDBC访问其他数据库。这一功能应该优先于使用JdbcRDD。因为它返回一个DataFrame，而DataFrame在Spark SQL中操作更简单，且更容易和来自其他数据源的数据进行交互关联。JDBC数据源在java和python中用起来也很简单，不需要用户提供额外的 ClassTag。（注意，这与Spark SQL JDBC server不同，Spark SQL JDBC server允许其他应用执行Spark SQL查询）
　　首先，你需要在spark classpath中包含对应数据库的JDBC driver，下面这行包括了用于访问postgres的数据库driver
SPARK_CLASSPATH=postgresql-9.3-1102-jdbc41.jar bin/spark-shell

val jdbcDF = sqlContext.read.format("jdbc").options(
  Map("url" -> "jdbc:postgresql:dbserver",
  "dbtable" -> "schema.tablename")).load()

注意：

JDBC driver class必须在所有client session或者executor上，对java的原生classloader可见。这是因为Java的DriverManager在打开一个连接之前，会做安全检查，并忽略所有对原声classloader不可见的driver。最简单的一种方法，就是在所有worker节点上修改 compute_classpath.sh，并包含你所需的driver jar包。
一些数据库，如H2，会把所有的名字转大写。对于这些数据库，在Spark SQL中必须也使用大写。

五、spark SQL示例

1、需要准备好四张表，既四个文本文件逗号分隔

2、为这四张表创建好schema，并注册成表

3、时间处理有小部分改动

准备的四张表

表（一）Student (学生表)

字段名	数据类型	可否为空	含义
Sno	Varchar2(3)	否	学号（主键）
Sname	Varchar2(8)	否	学生姓名
Ssex	Varchar2(2)	否	学生性别
Sbirthday	Date	可	学生出生年月
SClass	Varchar2(5)	可	学生所在班级

表（二）Course（课程表）

属性名	数据类型	可否为空	含义
Cno	Varchar2(5)	否	课程号（主键）
Cname	Varchar(10)	否	课程名称
Tno	Varchar2(3)	否	教工编号（外键）

表（三）Score(成绩表)

属性名	数据类型	可否为空	含义
Sno	Varchar2(3)	否	学号（外键）
Cno	Varchar2(5)	否	课程号（外键）
Degree	Number(4,1)	可	成绩

表（四）Teacher(教师表)

属性名	数据类型	可否为空	含义
Tno	Varchar2(3)	否	教工编号（主键）
Tname	Varchar2(4)	否	教工姓名
Tsex	Varchar2(2)	否	教工性别
Tbirthday	Date	可	教工出生年月
Prof	Varchar2(6)	可	职称
Depart	Varchar(10)	否	教工所在部门

四张表中的数据

例子代码，粘贴可用，注意注释掉不需要的地方即可

 
    [java]  
    view plaincopy
package com.cdpsql1  
  
import org.apache.spark.sql.{Row, SparkSession}  
import org.apache.spark.sql.types._  
  
import scala.collection.mutable  
import java.text.SimpleDateFormat  
  
/** 
  * 2017/05/26 
  * CDP 
  */  
  
object SparkSqlExample1 {  
  
   def main(args: Array[String]): Unit = {  
  
    /** *************************************************************************************************************** 
      * sparksession 
      */  
    val spark = SparkSession  
      .builder()  
      .master("local")  
      .appName("test")  
      .config("spark.sql.shuffle.partitions", "5")  
      .getOrCreate()  
  
    /** *************************************************************************************************************** 
      * 表结构 
      */  
    val StudentSchema: StructType = StructType(mutable.ArraySeq(  //学生表  
      StructField("Sno", StringType, nullable = false),           //学号  
      StructField("Sname", StringType, nullable = false),         //学生姓名  
      StructField("Ssex", StringType, nullable = false),          //学生性别  
      StructField("Sbirthday", StringType, nullable = true),      //学生出生年月  
      StructField("SClass", StringType, nullable = true)          //学生所在班级  
    ))  
    val CourseSchema: StructType = StructType(mutable.ArraySeq(   //课程表  
      StructField("Cno", StringType, nullable = false),           //课程号  
      StructField("Cname", StringType, nullable = false),         //课程名称  
      StructField("Tno", StringType, nullable = false)            //教工编号  
    ))  
    val ScoreSchema: StructType = StructType(mutable.ArraySeq(    //成绩表  
      StructField("Sno", StringType, nullable = false),           //学号（外键）  
      StructField("Cno", StringType, nullable = false),           //课程号（外键）  
      StructField("Degree", IntegerType, nullable = true)         //成绩  
    ))  
    val TeacherSchema: StructType = StructType(mutable.ArraySeq(  //教师表  
      StructField("Tno", StringType, nullable = false),           //教工编号（主键）  
      StructField("Tname", StringType, nullable = false),         //教工姓名  
      StructField("Tsex", StringType, nullable = false),          //教工性别  
      StructField("Tbirthday", StringType, nullable = true),      //教工出生年月  
      StructField("Prof", StringType, nullable = true),           //职称  
      StructField("Depart", StringType, nullable = false)         //教工所在部门  
    ))  
  
    /** *************************************************************************************************************** 
      * 获取当前时间函数 
      */  
    def getDate(time: String) = {  
      val now: Long=System.currentTimeMillis()  
      var df: SimpleDateFormat = new SimpleDateFormat(time)  
      df.format(now)  
    }  
  
    /** *************************************************************************************************************** 
      * 读取数据 
      */  
    val StudentData = spark.sparkContext.textFile("input/sqltable/Student").map{  
      lines =>  
        val line = lines.split(",")  
        Row(line(0),line(1),line(2),line(3),line(4))  
    }  
    val CourseData = spark.sparkContext.textFile("input/sqltable/Course").map{  
      lines =>  
        val line = lines.split(",")  
        Row(line(0),line(1),line(2))  
    }  
    val ScoreData = spark.sparkContext.textFile("input/sqltable/Score").map{  
      lines =>  
        val line = lines.split(",")  
        Row(line(0),line(1),line(2).toInt)  
    }  
    val TeacherData = spark.sparkContext.textFile("input/sqltable/Teacher").map{  
      lines =>  
        val line = lines.split(",")  
        Row(line(0),line(1),line(2),line(3),line(4),line(5))  
    }  
  
    /** *************************************************************************************************************** 
      * 转换成表 
      */  
    val StudentTable = spark.createDataFrame(StudentData, StudentSchema)  
    StudentTable.createOrReplaceTempView("Student")  
    val CourseTable = spark.createDataFrame(CourseData, CourseSchema)  
    CourseTable.createOrReplaceTempView("Course")  
    val ScoreTable = spark.createDataFrame(ScoreData, ScoreSchema)  
    ScoreTable.createOrReplaceTempView("Score")  
    val TeacherTable = spark.createDataFrame(TeacherData, TeacherSchema)  
    TeacherTable.createOrReplaceTempView("Teacher")  
  
  
    /** *************************************************************************************************************** 
      * 走sql节奏 
      * 表名，字段名，区分大小写 
      */  
    1、 查询Student表中的所有记录的Sname、Ssex和Class列。  
    spark.sql("SELECT sname, ssex, sclass FROM Student").show()  
  
    2、 查询教师所有的单位即不重复的Depart列。  
    spark.sql("SELECT DISTINCT depart FROM Teacher").show()  
  
    3、 查询Student表的所有记录  
    spark.sql("SELECT * FROM Student").show()  
  
    4、 查询Score表中成绩在60到80之间的所有记录。  
    //spark.sql("SELECT * FROM Score WHERE degree BETWEEN 60 and 80").show()  
    spark.sql("SELECT * FROM Score WHERE degree >= 60 and degree <= 80").show()  
  
    5、 查询Score表中成绩为85，86或88的记录。  
    spark.sql("SELECT * FROM Score WHERE degree = '85' OR degree = '86' OR degree = '88'").show()  
  
    6、 查询Student表中“95031”班或性别为“女”的同学记录。  
    spark.sql("SELECT * FROM Student WHERE sclass = '95031' OR ssex = 'female'").show()  
  
    7、 以Class降序,升序查询Student表的所有记录。  
    spark.sql("SELECT * FROM Student ORDER BY sclass DESC").show()  
    spark.sql("SELECT * FROM Student ORDER BY sclass").show()  
  
    8、 以Cno升序、Degree降序查询Score表的所有记录。  
    spark.sql("SELECT * FROM Score t ORDER BY t.sno ASC, t.degree DESC").show()  
  
    9、 查询“95031”班的学生人数。  
    spark.sql("SELECT t.sclass totalnum FROM Student t WHERE sclass = '95031'").show()  
    spark.sql("SELECT t.sclass AS totalnum FROM Student t WHERE sclass = '95031'").show()  
  
    10、 查询Score表中的最高分的学生学号和课程号。（子查询或者排序）  
     oracle    =>  WHERE rownum = 1  
     spark sql =>  LIMIT 1  
    spark.sql("SELECT * FROM (SELECT * FROM Score ORDER BY degree DESC LIMIT 1)").show()  
    spark.sql("SELECT t.sno, t.cno FROM Score t ORDER BY degree DESC").show()  
    spark.sql("SELECT * FROM Score WHERE degree IN(SELECT MAX(degree) FROM Score t)").show()  
  
    11、 查询每门课的平均成绩。  
    spark.sql("SELECT AVG(degree) average FROM Score t WHERE cno = '3-245'").show()  
    spark.sql("SELECT AVG(degree) average FROM Score WHERE cno = '3-105'").show()  
    spark.sql("SELECT AVG(degree) average FROM Score WHERE cno = '6-166'").show()  
    spark.sql("SELECT cno, AVG(degree) FROM Score t GROUP BY cno").show()  
  
    12、查询Score表中至少有5名学生选修的并以3开头的课程的平均分数。  
    spark.sql("SELECT cno, AVG(degree) FROM Score WHERE cno LIKE '3%' GROUP BY cno HAVING COUNT(1) >= 5").show()  
  
    13、查询分数大于70，小于90的Sno列。  
    spark.sql("SELECT sno FROM Score WHERE degree BETWEEN 70 AND 90").show()  
  
    14、查询所有学生的Sname、Cno和Degree列。  
    spark.sql("SELECT s.sname, t.cno, t.degree FROM Score t, Student s WHERE t.sno = s.sno").show()  
    spark.sql("SELECT s.sname, t.cno, t.degree FROM Score t JOIN Student s ON t.sno = s.sno").show()  
  
    15、查询所有学生的Sno、Cname和Degree列。  
    spark.sql("SELECT s.sname, t.cno, t.degree FROM Score t JOIN Student s ON t.sno = s.sno").show()  
  
    16、查询所有学生的Sname、Cname和Degree列。  
    spark.sql("SELECT s.sname, t.degree, c.cname FROM Score t, Student s, Course c WHERE t.sno = s.sno AND t.cno = c.cno").show()  
    spark.sql("SELECT s.sname, t.degree, c.cname FROM Score t " +  
      "JOIN Student s on t.sno = s.sno " +  
      "JOIN Course c on c.cno = t.cno").show()  
  
    17、 查询“95033”班学生的平均分。  
    spark.sql("SELECT AVG(degree) average FROM Score WHERE sno IN (SELECT sno FROM Student WHERE sclass = '95033')").show()  
  
    19、  查询选修“3-105”课程的成绩高于“109”号同学成绩的所有同学的记录。  
    spark.sql("SELECT * FROM Score WHERE cno = '3-105' AND degree > (SELECT degree FROM score WHERE sno = '109' AND cno = '3-105')").show()  
  
    20、查询score中选学多门课程的同学中分数为非最高分成绩的记录。  
    spark.sql("SELECT * FROM Score WHERE sno IN " +  
      "(SELECT sno FROM Score t GROUP BY t.sno HAVING COUNT(1) > 1) AND degree != (SELECT MAX(degree) FROM Score)").show()  
    spark.sql("SELECT * FROM Score WHERE degree != (SELECT MAX(degree) FROM Score)").show()  
  
    21、 查询成绩高于学号为“109”、课程号为“3-105”的成绩的所有记录。  
    spark.sql("SELECT * FROM Score t WHERE t.degree > (SELECT degree FROM Score WHERE sno = '109' AND cno = '3-105')").show()  
  
    22、查询和学号为108的同学同年出生的所有学生的Sno、Sname和Sbirthday列。  
     oracle    =>  to_char(t.sbirthday,'yyyy')  
     spark sql =>  substring(t.sbirthday, 0, 4)  
    spark.sql("SELECT sno, sname , sbirthday " +  
              "FROM Student " +  
              "WHERE substring(sbirthday, 0, 4) = ( " +  
                "SELECT substring(t.sbirthday, 0, 4) " +  
                "FROM Student t " +  
                "WHERE sno = '108')").show()  
  
    23、查询“张旭“教师任课的学生成绩。  
    spark.sql("SELECT t.tno, c.cno, c.cname, s.degree FROM Teacher t " +  
      "JOIN Course c ON t.tno = c.tno " +  
      "JOIN Score s ON c.cno = s.cno WHERE t.tname = 'Zhang xu'").show()  
  
    24、查询选修某课程的同学人数多于5人的教师姓名。  
    spark.sql("SELECT tname FROM Teacher e " +  
      "JOIN Course c ON e.tno = c.tno " +  
      "JOIN(SELECT cno FROM Score GROUP BY cno HAVING COUNT(cno) > 5) t ON c.cno = t.cno").show()  
  
    25、查询95033班和95031班全体学生的记录。  
    spark.sql("SELECT * FROM Student WHERE sclass IN('95031', '95033')").show()  
    spark.sql("SELECT * FROM Student WHERE sclass LIKE '9503%'").show()  
  
    26、  查询存在有85分以上成绩的课程Cno.  
    spark.sql("SELECT cno FROM Score WHERE degree > 85 GROUP BY cno").show()  
  
    27、查询出“计算机系“教师所教课程的成绩表。  
    spark.sql("SELECT t.sno, t.cno, t.degree FROM Score t " +  
      "JOIN Course c ON t.cno = c.cno " +  
      "JOIN Teacher e ON c.tno = e.tno WHERE e.depart = 'department of computer'").show()  
  
    28、查询“计算机系”与“电子工程系“不同职称的教师的Tname和Prof。  
    spark.sql("SELECT tname, prof " +  
              "FROM Teacher " +  
              "WHERE prof NOT IN (SELECT a.prof " +  
                "FROM (SELECT prof " +  
                  "FROM Teacher " +  
                  "WHERE depart = 'department of computer' " +  
                  ") a " +  
                  "JOIN (SELECT prof " +  
                    "FROM Teacher " +  
                    "WHERE depart = 'department of electronic engineering' " +  
                    ") b ON a.prof = b.prof) ").show()  
    spark.sql("SELECT tname, prof " +  
              "FROM Teacher " +  
              "WHERE depart = 'department of electronic engineering' " +  
                "AND prof NOT IN (SELECT prof " +  
                  "FROM Teacher " +  
                  "WHERE depart = 'department of computer') " +  
                "OR depart = 'department of computer' " +  
                  "AND prof NOT IN (SELECT prof " +  
                  "FROM Teacher " +  
                  "WHERE depart = 'department of electronic engineering')").show()  
  
    29、查询选修编号为“3-105“课程且成绩至少高于选修编号为“3-245”的同学的Cno、Sno和Degree,并按Degree从高到低次序排序。  
    spark.sql("SELECT t.sno, t.cno, degree " +  
              "FROM SCORE t " +  
              "WHERE degree > ( " +  
                "SELECT MIN(degree) " +  
                "FROM score " +  
                "WHERE cno = '3-245' " +  
                ") " +  
              "AND t.cno = '3-105' " +  
              "ORDER BY degree DESC").show()  
  
    30、查询选修编号为“3-105”且成绩高于选修编号为“3-245”课程的同学的Cno、Sno和Degree.  
    // oracle方式 spark.sql("select t.sno, t.cno, t.degree from SCORE t where t.degree > select degree from score where cno='3-245' or cno='3-105'").show()  
    spark.sql("SELECT t.sno, t.cno, t.degree FROM Score t WHERE t.degree > (SELECT MAX(degree) FROM Score WHERE cno = '3-245' ) AND t.cno = '3-105'").show()  
  
    31、 查询所有教师和同学的name、sex和birthday.  
    spark.sql("SELECT sname, ssex, sbirthday FROM Student " +  
              "UNION SELECT tname, tsex, tbirthday FROM Teacher").show()  
  
     32、查询所有“女”教师和“女”同学的name、sex和birthday. union  
    spark.sql("SELECT sname, ssex, sbirthday " +  
              "FROM Student " +  
              "WHERE ssex = 'female' " +  
              "UNION " +  
              "SELECT tname, tsex, tbirthday " +  
              "FROM Teacher " +  
              "WHERE tsex = 'female'").show()  
  
    33、 查询成绩比该课程平均成绩低的同学的成绩表。  
    spark.sql("SELECT s.* " +  
              "FROM score s " +  
              "WHERE s.degree < ( " +  
                "SELECT AVG(degree) " +  
                "FROM score c " +  
                "WHERE s.cno = c.cno)").show()  
  
    34、 查询所有任课教师的Tname和Depart. in  
    spark.sql("SELECT tname, depart " +  
              "FROM teacher t " +  
              "WHERE t.tno IN ( " +  
                "SELECT tno " +  
                "FROM course c " +  
                "WHERE c.cno IN (" +  
                  "SELECT cno " +  
                  "FROM score))").show()  
  
    35 、 查询所有未讲课的教师的Tname和Depart. not in  
    spark.sql("SELECT tname, depart " +  
              "FROM teacher t " +  
              "WHERE t.tno NOT IN ( " +  
                "SELECT tno " +  
                "FROM course c " +  
                "WHERE c.cno IN ( " +  
                  "SELECT cno " +  
                  "FROM score))").show()  
  
    36、查询至少有2名男生的班号。  group by, having count  
    spark.sql("SELECT SClass " +  
              "FROM Student t " +  
              "WHERE Ssex = 'male' " +  
              "GROUP BY SClass " +  
              "HAVING COUNT(Ssex) >= 2").show()  
  
    37、查询Student表中不姓“王”的同学记录。 not like  
    spark.sql("SELECT * FROM Student t WHERE Sname NOT LIKE('Wang%')").show()  
  
    38、查询Student表中每个学生的姓名和年龄。  
    将函数运用到spark sql中去计算，可以直接拿String的类型计算不需要再转换成数值型 默认是会转换成Double类型计算  
    spark.sql("SELECT Sname, ("+ getDate("yyyy") +" - substring(sbirthday, 0, 4)) AS age FROM STUDENT t").show()  
    浮点型转整型  
    spark.sql("SELECT Sname, (CAST("+ getDate("yyyy") +" AS INT) - CAST(substring(sbirthday, 0, 4) AS INT)) AS age " +  
              "FROM Student t").show()  
  
    39、查询Student表中最大和最小的Sbirthday日期值。 时间格式最大值,最小值  
    spark.sql("SELECT MAX(t.sbirthday) AS maximum FROM Student t").show()  
    spark.sql("SELECT MIN(t.sbirthday) AS minimum FROM Student t").show()  
  
    40、以班号和年龄从大到小的顺序查询Student表中的全部记录。 查询结果排序  
    spark.sql("SELECT * " +  
              "FROM Student " +  
              "ORDER BY SClass DESC, CAST("+ getDate("yyyy") +" AS INT) - CAST(substring(Sbirthday, 0, 4) AS INT) DESC").show()  
  
    41、查询“男”教师及其所上的课程。 select join  
    spark.sql("SELECT TSex, CName " +  
              "FROM Teacher t " +  
                "JOIN course c ON t.tno = c.tno " +  
              "WHERE TSex = 'male'").show()  
  
    42、查询最高分同学的Sno、Cno和Degree列。 子查询  
    spark.sql("SELECT * " +  
              "FROM Score " +  
              "WHERE degree = ( " +  
                "SELECT MAX(degree) " +  
                "FROM SCORE t)").show()  
  
    43、查询和“李军”同性别的所有同学的Sname.  
    spark.sql("SELECT sname " +  
              "FROM STUDENT t " +  
              "WHERE ssex IN ( " +  
                "SELECT ssex " +  
                "FROM student " +  
                "WHERE sname = 'Liu Jun')").show()  
  
    44、查询和“李军”同性别并同班的同学Sname.  
    spark.sql("SELECT sname " +  
              "FROM Student t " +  
              "WHERE ssex IN (  " +  
                "SELECT ssex " +  
                  "FROM student " +  
                  "WHERE sname = 'Liu Jun') " +  
                "AND sclass IN (SELECT sclass " +  
                  "FROM student " +  
                  "WHERE sname = 'Liu Jun')").show()  
  
    45、查询所有选修“计算机导论”课程的“男”同学的成绩表。  
    spark.sql("SELECT t.sno, t.cno, t.degree " +  
              "FROM Score t " +  
                "JOIN Course c ON t.cno = c.cno " +  
                "JOIN Student s ON s.sno = t.sno " +  
              "WHERE s.SSex = 'male' " +  
                "AND c.CName = 'Introduction to computer'").show()  
  }  
}  

不粘人的小妖精

关注

0
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
spark sql基础与示例

转自https://www.jianshu.com/p/a27f5f5f14e5； https://blog.csdn.net/feloxx/article/details/72819964一、简介Spark SQL是Spark中处理结构化数据的模块。与基础的Spark RDD API不同，Spark SQL的接口提供了更多关于数据的结构信息和计算任务的运行时信息。在Spark内部，Spar...
复制链接

扫一扫