Re: Is there "EXCEPT ALL" in Spark SQL?

[jeff saremi]

EXCEPT is not the same as EXCEPT ALL

Had they implemented EXCEPT ALL in SparkSQL one could have easily obtained 
EXCEPT by adding a disctint() to the results



From: hareesh makam 
Sent: Thursday, July 6, 2017 12:48:18 PM
To: jeff saremi
Cc: user@spark.apache.org
Subject: Re: Is there "EXCEPT ALL" in Spark SQL?

There is Except in DataFrame API.

df1.except(df2)

Same can be used in SQL as well.


public 
DataFrame<https://spark.apache.org/docs/1.6.1/api/java/org/apache/spark/sql/DataFrame.html>
 
except(DataFrame<https://spark.apache.org/docs/1.6.1/api/java/org/apache/spark/sql/DataFrame.html>
 other)

Returns a new 
DataFrame<https://spark.apache.org/docs/1.6.1/api/java/org/apache/spark/sql/DataFrame.html>
 containing rows in this frame but not in another frame. This is equivalent to 
EXCEPT in SQL.


-Hareesh


On 6 July 2017 at 12:22, jeff saremi 
mailto:jeffsar...@hotmail.com>> wrote:

I tried this query in 1.6 and it failed:


SELECT * FROM Table1 EXCEPT ALL SELECT * FROM Table2



Exception in thread "main" java.lang.RuntimeException: [1.32] failure: ``('' 
expected but `all' found


thanks

Jeff

Re: Is there "EXCEPT ALL" in Spark SQL?

[hareesh makam]

There is Except in DataFrame API.

df1.except(df2)

Same can be used in SQL as well.

public DataFrame

except(DataFrame

other)

Returns a new DataFrame

containing
rows in this frame but not in another frame. This is equivalent to EXCEPT in
SQL.


-Hareesh


On 6 July 2017 at 12:22, jeff saremi  wrote:

> I tried this query in 1.6 and it failed:
>
> SELECT * FROM Table1 EXCEPT ALL SELECT * FROM Table2
>
>
> Exception in thread "main" java.lang.RuntimeException: [1.32] failure:
> ``('' expected but `all' found
>
>
> thanks
>
> Jeff
>

Re: Is there "EXCEPT ALL" in Spark SQL?

[upendra 1991]

To add to it, is there any specific documentation or reference where we could 
check out what SQL functions and features are available in spark spl for a 
specific sparksql version.?

Thanks,Upendra 
 
  On Thu, Jul 6, 2017 at 2:22 PM, jeff saremi wrote:

I tried this query in 1.6 and it failed:




SELECT * FROM Table1 EXCEPT ALL SELECT * FROM Table2


Exception in thread "main" java.lang.RuntimeException: [1.32] failure: ``('' 
expected but `all' found





thanks

Jeff