【Spark】join连接测试

1、代码

package com.gl.test


import org.apache.spark.sql.{Dataframe, SparkSession}

object Test {

  case class Stu(name: String, sex: String)
  case class Info(name: String, address: String,phone:String)
  def main(args: Array[String]): Unit = {
    val spark = SparkSession.builder().master("local[1]").appName("testDate").getOrCreate()

    import spark.implicits._
    val sc = spark.sparkContext
    sc.setLogLevel("ERROR")

    val StudentDF: Dataframe = Seq(
      Stu("Join","M"),
      Stu("Tom","M"),
      Stu("Marry","F"),
      Stu("Alex","F")
    ).toDF()

    StudentDF.createTempView("stuTable")
    val InfoDF: Dataframe = Seq(
      Info("Join","shangHai","188****"),
      Info("Tom","BeiJing","177****"),
      Info("Carry","nanJing","155****")
    ).toDF()

    InfoDF.createTempView("infoTable")

    
    println("################################左连接#############################")
    spark.sql("select * from stuTable t1 left join infoTable t2 on t1.name=t2.name").show()
    StudentDF.join(InfoDF,Seq("name"),"left").show()
    StudentDF.joinWith(InfoDF,StudentDF("name")===InfoDF("name"),"left").show()

    
    println("################################内连接#############################")
    spark.sql("select * from stuTable t1,infoTable t2 where t1.name=t2.name").show()
    spark.sql("select * from stuTable t1 inner join infoTable t2 on t1.name=t2.name").show()
    StudentDF.join(InfoDF,StudentDF("name")===InfoDF("name")).show() //默认内连接
    StudentDF.joinWith(InfoDF,StudentDF("name")===InfoDF("name")).show() //默认内连接
    StudentDF.join(InfoDF,StudentDF("name")===InfoDF("name"),"inner")
      .select(StudentDF("name"),StudentDF("sex"),InfoDF("address"),InfoDF("phone"))
      .show()


    
    println("################################右连接#############################")
    spark.sql("select * from stuTable t1 right join infoTable t2 on t1.name=t2.name").show()

    
    println("################################全连接#############################")
    spark.sql("select * from stuTable t1 full join infoTable t2 on t1.name=t2.name").show()

    
    println("################################左外连接#############################")
    StudentDF.join(InfoDF,StudentDF("name")===InfoDF("name"),"leftouter").show()

    
    println("################################右外连接#############################")
    StudentDF.join(InfoDF,StudentDF("name")===InfoDF("name"),"rightouter").show()

    
    println("################################全外连接#############################")
    StudentDF.join(InfoDF,StudentDF("name")===InfoDF("name"),"fullouter").show()

    spark.stop()
  }
}

2、输出

################################左连接#############################
+-----+---+----+--------+-------+
| name|sex|name| address|  phone|
+-----+---+----+--------+-------+
| Join|  M|Join|shangHai|188****|
|  Tom|  M| Tom| BeiJing|177****|
|Marry|  F|null|    null|   null|
| Alex|  F|null|    null|   null|
+-----+---+----+--------+-------+

+-----+---+--------+-------+
| name|sex| address|  phone|
+-----+---+--------+-------+
| Join|  M|shangHai|188****|
|  Tom|  M| BeiJing|177****|
|Marry|  F|    null|   null|
| Alex|  F|    null|   null|
+-----+---+--------+-------+

+---------+--------------------+
|       _1|                  _2|
+---------+--------------------+
| [Join,M]|[Join,shangHai,18...|
|  [Tom,M]|[Tom,BeiJing,177*...|
|[Marry,F]|                null|
| [Alex,F]|                null|
+---------+--------------------+

################################内连接#############################
+----+---+----+--------+-------+
|name|sex|name| address|  phone|
+----+---+----+--------+-------+
|Join|  M|Join|shangHai|188****|
| Tom|  M| Tom| BeiJing|177****|
+----+---+----+--------+-------+

+----+---+----+--------+-------+
|name|sex|name| address|  phone|
+----+---+----+--------+-------+
|Join|  M|Join|shangHai|188****|
| Tom|  M| Tom| BeiJing|177****|
+----+---+----+--------+-------+

+----+---+----+--------+-------+
|name|sex|name| address|  phone|
+----+---+----+--------+-------+
|Join|  M|Join|shangHai|188****|
| Tom|  M| Tom| BeiJing|177****|
+----+---+----+--------+-------+

+--------+--------------------+
|      _1|                  _2|
+--------+--------------------+
|[Join,M]|[Join,shangHai,18...|
| [Tom,M]|[Tom,BeiJing,177*...|
+--------+--------------------+

+----+---+--------+-------+
|name|sex| address|  phone|
+----+---+--------+-------+
|Join|  M|shangHai|188****|
| Tom|  M| BeiJing|177****|
+----+---+--------+-------+

################################右连接#############################
+----+----+-----+--------+-------+
|name| sex| name| address|  phone|
+----+----+-----+--------+-------+
|Join|   M| Join|shangHai|188****|
| Tom|   M|  Tom| BeiJing|177****|
|null|null|Carry| nanJing|155****|
+----+----+-----+--------+-------+

################################全连接#############################
+-----+----+-----+--------+-------+
| name| sex| name| address|  phone|
+-----+----+-----+--------+-------+
|  Tom|   M|  Tom| BeiJing|177****|
| Alex|   F| null|    null|   null|
| Join|   M| Join|shangHai|188****|
| null|null|Carry| nanJing|155****|
|Marry|   F| null|    null|   null|
+-----+----+-----+--------+-------+

################################左外连接#############################
+-----+---+----+--------+-------+
| name|sex|name| address|  phone|
+-----+---+----+--------+-------+
| Join|  M|Join|shangHai|188****|
|  Tom|  M| Tom| BeiJing|177****|
|Marry|  F|null|    null|   null|
| Alex|  F|null|    null|   null|
+-----+---+----+--------+-------+

################################右外连接#############################
+----+----+-----+--------+-------+
|name| sex| name| address|  phone|
+----+----+-----+--------+-------+
|Join|   M| Join|shangHai|188****|
| Tom|   M|  Tom| BeiJing|177****|
|null|null|Carry| nanJing|155****|
+----+----+-----+--------+-------+

################################全外连接#############################
+-----+----+-----+--------+-------+
| name| sex| name| address|  phone|
+-----+----+-----+--------+-------+
|  Tom|   M|  Tom| BeiJing|177****|
| Alex|   F| null|    null|   null|
| Join|   M| Join|shangHai|188****|
| null|null|Carry| nanJing|155****|
|Marry|   F| null|    null|   null|
+-----+----+-----+--------+-------+