Spark sql 利用COALESCE()函数full join 两张DataFrame_spark sql full join

我们经常使用spark时会对表合并

import spark.implicits._

import spark.implicits._

var data1 = Seq(  
 | ("1", "ming", "hlj"),  
 | ("2", "tian", "jl"),
 | ("3", "wang", "ln"),
 | ("4", "qi", "bj"),
 | ("5", "sun", "tj")
 | ).toDF("useid", "name", "live")  
 

var data2 = Seq(  
 | ("1", "1000", "sing"),  
 | ("3", "2000", "dance"),
 | ("5", "3000", "rap"),
 | ("7", "5000", "lanqiu"),
 | ("9", "8000", "zuqiu")
 | ).toDF("useid", "salary", "hobby")  
 
 
 data1.show()
data2.show()
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24

将表data1和data2进行full join，生成新的useid

COALESCE()函数 参考链接

COALESCE ( expression,value1,value2……,valuen)
COALESCE()函数的第一个参数expression为待检测的表达式，而其后的参数个数不定。
COALESCE()函数将会返回包括expression在内的所有参数中的第一个非空表达式。
如果expression不为空值则返回expression；否则判断value1是否是空值，
如果value1不为空值则返回value1；否则判断value2是否是空值。

data1 = data1.withColumnRenamed(s"useid", s"useid_1")
data2 = data2.withColumnRenamed(s"useid", s"useid_2")

data1.createOrReplaceTempView(s"data1")
data2.createOrReplaceTempView(s"data2")



spark.sql(s"select" +
      s" COALESCE(aa.useid_1,bb.useid_2) as useid,*" +
      s" from data1 aa full join data2 bb on aa.useid_1=bb.useid_2"+
      s"").drop("useid_1").drop("useid_2").show()


1
2
3
4
5
6
7
8
9
10
11
12
13
14