Clever Geek Handbook

Spark SQL: handling NULL in IF

I'm trying to execute IF

[spark coalesce] on top of the left outer connected pin, but it NULL

doesn't seem to handle as expected. Here are my base tables, example query, output and expected result -

Base tables:

t1:
 a, 100
 b, 101
 s, 102

t2:
 101

Query:

select ax, a.x1, IF (b.x1 - NULL, a.x1, b.x1) from t1 a LEFT OUTER JOIN t2 b to a.x1 = b.x1;

Output:

a, 100, zero
  b, 101 101
  c, 102, null

Expected

:

a, 100.100
  b, 101101
  c, 102.102

I also tried to wrap the above query and then IF on top of it. But unsuccessfully. Please suggest me to miss something.

+3


source to share


2 answers


This seems to work

File: tbl1

1   a
2   b
3   c


File: tbl2

1   c
3   d

case class c_tbl1(c1: String,c2: String)

sc.textFile("tbl1").map { row => 
val parts = row.split("\t")
c_tbl1(parts(0),parts(1)) }.registerTempTable("t_tbl1")

case class c_tbl2(c1: String,c2: String)

sc.textFile("tbl2").map { row => 
val parts = row.split("\t")
c_tbl2(parts(0),parts(1)) }.registerTempTable("t_tbl2")

sqlContext.sql("""select t.c1,t.c2,IF(t2.c1 is null,1,2),t2.c2 from t_tbl1 t left outer join t_tbl2 t2 on t.c1=t2.c1""".stripMargin).collect.foreach(println)


[1,a,2,c]
[2,b,1,null]
[3,c,2,d]
+1


source


Try the Case statement, not sure if this CASE statement is supported by Spark SQL: -



select a.x, a.x1,
      CASE WHEN b.x1 IS NULL THEN a.x1
           ELSE b.x1
      END as bx1
from t1 a LEFT OUTER JOIN t2 b on a.x1=b.x1;
+1


source






More articles:


All Articles