【SparkSQL】扩展 ----连接操作

---

文章目录

---

一、什么是连接

1.介绍

​​返回顶部​​

---

2.简单连接案例

• 使用join算子进行连接



• 
  



• 
  

// 配置环境
val spark = SparkSession.builder()
  .master("local[6]")
  .appName("groupBy")
  .getOrCreate()
  //导入隐式转换
import spark.implicits._
// 创建数据集
private val person = Seq((0,"lucy",0),(1,"lili",2),(2,"tim",2),(3,"jack",1),(4,"jacky",3)).toDF("id","name","cityid")
// 创建视图person
person.createOrReplaceTempView("person")
// 创建数据集
private val city = Seq((0,"beijing"),(1,"nanjing"),(2,"shanghai"),(4,"shandong")).toDF("id","city")
// 创建视图city
city.createOrReplaceTempView("city")

@Test
def join(): Unit ={

// 连接两张表
val persontb = person.join( city,person.col("cityid") === city.col("id") )
    .select(person.col("id"),person.col("name"),city.col("city"))
    //.show()

// 使用sql语句查询
persontb.createOrReplaceTempView("person_city")
spark.sql(" select id,name,city from person_city where city='beijing' ")
    .show()
}

• 最后的SQL语句是作用于用户表和城市表两张表之上的。

​​返回顶部​​

---

二、常见的连接操作（方式）

交叉连接 — cross join 笛卡尔积

• select * from a,b;
• select * from a cross join;

person.crossJoin(city)
  .where(person.col("cityid")===city.col("id"))
  .show()

spark.sql("select p.id,p.name,c.city from person p cross join city c " +
  "where p.cityid = c.id")
  .show()

spark.sql("select p.id,p.name,c.city from person p,city c " +
  "where p.cityid = c.id")
  .show()

​​返回顶部​​

---

内连接 — inner join

person.join(city,person.col("cityid")===city.col("id"),"inner")
  .show()

spark.sql("select p.id,p.name,c.city from person p inner join city c " +
  "on p.cityid = c.id")
   .show()

原表：

• 只显示id中有交集的记录



• 
  

​​返回顶部​​

---

左外连接（left）、右外连接（right）

/**
 * 左外连接
 */
@Test
def leftjoin(): Unit ={
  person.join(city,person.col("cityid")===city.col("id"),"left")
    .show()

  spark.sql("select p.id,p.name,c.city from person p left join city c " +
    "on p.cityid = c.id")
    .show()
}

原表：

• 保留左表person的全部记录

---

/**
 *右外连接
 */
@Test
def rightjoin(): Unit ={
  person.join(city,person.col("cityid")===city.col("id"),"right")
    .show()

  spark.sql("select p.id,p.name,c.city from person p right join city c " +
    "on p.cityid = c.id")
    .show()
}

原表：

• 保留右表所有的记录

​​返回顶部​​

---

全外连接（outer、full、fullouter）

@Test
def fulloutjoin(): Unit ={
 person.join(city,person.col("cityid")===city.col("id"),"full")
   .show()

 spark.sql("select p.id,p.name,c.city from person p full outer join city c " +
   "on p.cityid = c.id")
   .show()
}

原表：

• 保留两张表全部记录，不符合连接部分的对应记录部分为null

​​返回顶部​​

---

LeftAnti ---- 只显示左表未连接上的记录

person.join(city,person.col("cityid")===city.col("id"),"leftanti")
 .show()

spark.sql("select p.id,p.name from person p left anti join city c " +
 "on p.cityid = c.id")
 .show()

原表：

​​返回顶部​​

---

LeftSemi ---- 只显示左表连接上的记录

person.join(city, person.col("cityid") === city.col("id"), "leftsemi")
  .show()

spark.sql("select p.id,p.name from person p left semi join city c " +
  "on p.cityid = c.id")
  .show()

原表：

​​返回顶部​​

---