What does “Correlated scalar subqueries must be Aggregated” mean?

by

You have to make sure that your sub-query by definition (and not by data) only returns a single row. Otherwise Spark Analyzer complains while parsing the SQL statement.

So when catalyst can’t make 100% sure just by looking at the SQL statement (without looking at your data) that the sub-query only returns a single row, this exception is thrown.

If you are sure that your subquery only gives a single row you can use one of the following aggregation standard functions, so Spark Analyzer is happy:

  • first
  • avg
  • max
  • min

More Related Contents:

  1. Finding duplicates from large data set using Apache Spark
  2. Find maximum row per group in Spark DataFrame
  3. How to connect Spark SQL to remote Hive metastore (via thrift protocol) with no hive-site.xml?
  4. While writing to hdfs path getting error java.io.IOException: Failed to rename
  5. Multiple Aggregate operations on the same column of a spark dataframe
  6. Partitioning in spark while reading from RDBMS via JDBC
  7. Spark SQL – load data with JDBC using SQL statement, not table name
  8. How to access element of a VectorUDT column in a Spark DataFrame?
  9. How to optimize partitioning when migrating data from JDBC source?
  10. Filtering a spark dataframe based on date
  11. How to save/insert each DStream into a permanent table
  12. Spark DataFrame Schema Nullable Fields
  13. Spark load data and add filename as dataframe column
  14. PySpark: how to resample frequencies
  15. How does createOrReplaceTempView work in Spark?
  16. Why does Spark think this is a cross / Cartesian join
  17. How to find count of Null and Nan values for each column in a PySpark dataframe efficiently?
  18. Spark SQL broadcast hash join
  19. What is the difference between Apache Spark SQLContext vs HiveContext?
  20. What should be the optimal value for spark.sql.shuffle.partitions or how do we increase partitions when using Spark SQL?
  21. pyspark: count distinct over a window
  22. Spark lists all leaf node even in partitioned data
  23. Array Intersection in Spark SQL
  24. reduce result datasets into single dataset
  25. How to exclude multiple columns in Spark dataframe in Python
  26. How to get Kafka offsets for structured query for manual and reliable offset management?
  27. Stratified sampling with pyspark
  28. How to use groupBy to collect rows into a map?
  29. When to use Spark DataFrame/Dataset API and when to use plain RDD?
  30. What is an optimized way of joining large tables in Spark SQL

Leave a Comment