Alternative to count in Spark sql to check if a query return empty result

user_1233359 0
Viewed 1157

I know count action can be expensive in Spark, so to improve performance I'd like to have a different way just to check if a query can return any results

Here is what I did

var df = spark.sql("select * from table_name where condition = 'blah' limit 1");
var dfEmpty = df.head(1).isEmpty;

Is it a valid solution or is there any potential uncaught error if I use above solution to check query result? It is a lot faster though.

apache-spark-sql apache-spark
2 Answers

isEmpty is head of the data.. this is quite resonable to check empty or not and it was given by spark api and is optimized... Hence, I'd prefer this...

Also in the query I think limit 1 is not required.


  /**
   * Returns true if the `Dataset` is empty.
   *
   * @group basic
   * @since 2.4.0
   */
  def isEmpty: Boolean = withAction("isEmpty", limit(1).groupBy().count().queryExecution) { plan =>
    plan.executeCollect().head.getLong(0) == 0
  }
user_6470530

I think this is ok, I guess you could also omit the limit(1) because this is also part of the implementation of df.isEmpty. See also How to check if spark dataframe is empty?.

Note that the solution with df.isEmpty does may not evaluate all columns. E.g. if you have an UDF for 1 column, this will probabely not execute and could throws exceptions on a real query. df.head(1).isEmpty on the other hand will evaluate all columns for 1 rows.

user_11385230
Related