You are viewing a plain text version of this content. The canonical link for it is here.
Posted to reviews@spark.apache.org by "ueshin (via GitHub)" <gi...@apache.org> on 2024/10/25 23:29:49 UTC
[PR] [WIP][SPARK-50130][SQL][PYTHON] Add DataFrame APIs for subqueries [spark]
ueshin opened a new pull request, #48664:
URL: https://github.com/apache/spark/pull/48664
### What changes were proposed in this pull request?
Adds the following DataFrame APIs for subqueries to Spark Classic.
- `scalar()`
- `exists()`
Also, add `outer()` to `Column` to specify outer references.
#### Examples:
For the following tables `l` and `r`:
```py
>>> spark.table("l").printSchema()
root
|-- a: long (nullable = true)
|-- b: double (nullable = true)
>>> spark.table("r").printSchema()
root
|-- c: long (nullable = true)
|-- d: double (nullable = true)
```
```py
from pyspark.sql import functions as sf
spark.table("l").where(
sf.col("b")
< (
spark.table("r")
.where(sf.col("a").outer() == sf.col("c"))
.select(sf.max("d"))
.scalar()
)
)
spark.table("l").select(
"a",
(
spark.table("l")
.where(sf.col("a") == sf.col("a").outer())
.select(sf.sum("b"))
.scalar()
.alias("sum_b")
),
)
spark.table("l").where(
spark.table("r").where(sf.col("a").outer() == sf.col("c")).exists()
)
```
### Why are the changes needed?
Subquery APIs are missing in DataFrame API.
### Does this PR introduce _any_ user-facing change?
Yes, new DataFrame APIs for subqueries will be available.
### How was this patch tested?
Added the related tests.
### Was this patch authored or co-authored using generative AI tooling?
No.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
Re: [PR] [SPARK-50130][SQL][PYTHON] Add DataFrame APIs for scalar and exists subqueries [spark]
Posted by "ueshin (via GitHub)" <gi...@apache.org>.
ueshin commented on code in PR #48664:
URL: https://github.com/apache/spark/pull/48664#discussion_r1831705944
##########
python/pyspark/sql/dataframe.py:
##########
@@ -6476,6 +6476,153 @@ def transpose(self, indexColumn: Optional["ColumnOrName"] = None) -> "DataFrame"
"""
...
+ def scalar(self) -> Column:
+ """
+ Return a `Column` object for a SCALAR Subquery containing exactly one row and one column.
+
+ The `scalar()` method is useful for extracting a `Column` object that represents a scalar
+ value from a DataFrame, especially when the DataFrame results from an aggregation or
+ single-value computation. This returned `Column` can then be used directly in `select`
+ clauses or as predicates in filters on the outer DataFrame, enabling dynamic data filtering
+ and calculations based on scalar values.
+
+ .. versionadded:: 4.0.0
+
+ Returns
+ -------
+ :class:`Column`
+ A `Column` object representing a SCALAR subquery.
+
+ Examples
+ --------
+ Setup a sample DataFrame.
+
+ >>> data = [
+ ... (1, "Alice", 45000, 101), (2, "Bob", 54000, 101), (3, "Charlie", 29000, 102),
+ ... (4, "David", 61000, 102), (5, "Eve", 48000, 101),
+ ... ]
+ >>> employees = spark.createDataFrame(data, ["id", "name", "salary", "department_id"])
+
+ Example 1: Filter for employees with salary greater than the average salary.
+
+ >>> from pyspark.sql import functions as sf
+ >>> employees.where(
+ ... sf.col("salary") > employees.select(sf.avg("salary")).scalar()
+ ... ).select("name", "salary", "department_id").show()
+ +-----+------+-------------+
+ | name|salary|department_id|
+ +-----+------+-------------+
+ | Bob| 54000| 101|
+ |David| 61000| 102|
+ | Eve| 48000| 101|
+ +-----+------+-------------+
+
+ Example 2: Filter for employees with salary greater than the average salary in their
+ department.
+
+ >>> from pyspark.sql import functions as sf
+ >>> employees.where(
+ ... sf.col("salary")
+ ... > employees.where(sf.col("department_id") == sf.col("department_id").outer())
+ ... .select(sf.avg("salary")).scalar()
+ ... ).select("name", "salary", "department_id").show()
+ +-----+------+-------------+
+ | name|salary|department_id|
+ +-----+------+-------------+
+ | Bob| 54000| 101|
+ |David| 61000| 102|
+ +-----+------+-------------+
+
+ Example 3: Select the name, salary, and the proportion of the salary in the department.
+
+ >>> from pyspark.sql import functions as sf
+ >>> employees.select(
+ ... "name", "salary", "department_id",
+ ... sf.format_number(
+ ... sf.lit(100) * sf.col("salary") /
+ ... employees.where(sf.col("department_id") == sf.col("department_id").outer())
+ ... .select(sf.sum("salary")).scalar().alias("avg_salary"),
+ ... 1
+ ... ).alias("salary_proportion_in_department")
+ ... ).show()
+ +-------+------+-------------+-------------------------------+
+ | name|salary|department_id|salary_proportion_in_department|
+ +-------+------+-------------+-------------------------------+
+ | Alice| 45000| 101| 30.6|
+ | Bob| 54000| 101| 36.7|
+ |Charlie| 29000| 102| 32.2|
+ | Eve| 48000| 101| 32.7|
+ | David| 61000| 102| 67.8|
+ +-------+------+-------------+-------------------------------+
+ """
+ ...
+
+ def exists(self) -> Column:
+ """
+ Return a `Column` object for an EXISTS Subquery.
+
+ The `exists` method provides a way to create a boolean column that checks for the presence
+ of related records in a subquery. When applied within a `DataFrame`, this method allows you
+ to filter rows based on whether matching records exist in the related dataset. The resulting
+ `Column` object can be used directly in filtering conditions or as a computed column.
+
+ .. versionadded:: 4.0.0
+
+ Returns
+ -------
+ :class:`Column`
+ A `Column` object representing an EXISTS subquery
+
+ Examples
+ --------
+ Setup sample data for customers and orders.
+
+ >>> data_customers = [
+ ... (101, "Alice", "USA"), (102, "Bob", "Canada"), (103, "Charlie", "USA"),
+ ... (104, "David", "Australia")
+ ... ]
+ >>> data_orders = [
+ ... (1, 101, "2023-01-15", 250), (2, 102, "2023-01-20", 300),
+ ... (3, 103, "2023-01-25", 400), (4, 101, "2023-02-05", 150)
+ ... ]
+ >>> customers = spark.createDataFrame(
+ ... data_customers, ["customer_id", "customer_name", "country"])
+ >>> orders = spark.createDataFrame(
+ ... data_orders, ["order_id", "customer_id", "order_date", "total_amount"])
+
+ Example 1: Filter for customers who have placed at least one order.
+
+ >>> from pyspark.sql import functions as sf
+ >>> customers.where(
+ ... orders.where(sf.col("customer_id") == sf.col("customer_id").outer()).exists()
Review Comment:
Yes, let me add the example.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
Re: [PR] [SPARK-50130][SQL][PYTHON] Add DataFrame APIs for scalar and exists subqueries [spark]
Posted by "ueshin (via GitHub)" <gi...@apache.org>.
ueshin commented on PR #48664:
URL: https://github.com/apache/spark/pull/48664#issuecomment-2469336905
The failing tests are not related to this PR.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
Re: [PR] [WIP][SPARK-50130][SQL][PYTHON] Add DataFrame APIs for subqueries [spark]
Posted by "ueshin (via GitHub)" <gi...@apache.org>.
ueshin commented on PR #48664:
URL: https://github.com/apache/spark/pull/48664#issuecomment-2439040454
I'll update documents here, and work on Spark Connect in a separate PR.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
Re: [PR] [WIP][SPARK-50130][SQL][PYTHON] Add DataFrame APIs for scalar and exists subqueries [spark]
Posted by "ueshin (via GitHub)" <gi...@apache.org>.
ueshin commented on code in PR #48664:
URL: https://github.com/apache/spark/pull/48664#discussion_r1817452154
##########
sql/api/src/main/scala/org/apache/spark/sql/Column.scala:
##########
@@ -1382,6 +1382,17 @@ class Column(val node: ColumnNode) extends Logging {
*/
def over(): Column = over(Window.spec)
+ /**
+ * TODO: Add documentation
+ * @return
+ */
+ def outer(): Column = withOrigin {
+ node match {
+ case attr: UnresolvedAttribute if !attr.isMetadataColumn =>
+ Column(LazyOuterReference(attr.nameParts, attr.planId))
+ // TODO: other cases
Review Comment:
Is it ok to use metadata columns as outer?
Do we have other cases we can support `outer()`?
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
Re: [PR] [SPARK-50130][SQL][PYTHON] Add DataFrame APIs for scalar and exists subqueries [spark]
Posted by "xinrong-meng (via GitHub)" <gi...@apache.org>.
xinrong-meng commented on PR #48664:
URL: https://github.com/apache/spark/pull/48664#issuecomment-2467151229
[Run / Build modules: pyspark-mllib, pyspark-ml, pyspark-ml-connect](https://github.com/ueshin/apache-spark/actions/runs/11714014185/job/32627949792#logs) seems to have been canceled for some reason. Rerun the jobs.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
Re: [PR] [SPARK-50130][SQL][PYTHON] Add DataFrame APIs for scalar and exists subqueries [spark]
Posted by "hvanhovell (via GitHub)" <gi...@apache.org>.
hvanhovell commented on code in PR #48664:
URL: https://github.com/apache/spark/pull/48664#discussion_r1838077151
##########
sql/core/src/main/scala/org/apache/spark/sql/Dataset.scala:
##########
@@ -95,9 +95,14 @@ private[sql] object Dataset {
def ofRows(sparkSession: SparkSession, logicalPlan: LogicalPlan): DataFrame =
sparkSession.withActive {
val qe = sparkSession.sessionState.executePlan(logicalPlan)
- qe.assertAnalyzed()
- new Dataset[Row](qe, RowEncoder.encoderFor(qe.analyzed.schema))
- }
+ val encoder = if (qe.isLazyAnalysis) {
+ RowEncoder.encoderFor(new StructType())
Review Comment:
@ueshin this breaks collect (and other operations) on these dataframes. The alternative would be to defer the construction of the encoder until it is needed.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
Re: [PR] [SPARK-50130][SQL][PYTHON] Add DataFrame APIs for scalar and exists subqueries [spark]
Posted by "ueshin (via GitHub)" <gi...@apache.org>.
ueshin commented on code in PR #48664:
URL: https://github.com/apache/spark/pull/48664#discussion_r1838585511
##########
sql/api/src/main/scala/org/apache/spark/sql/Column.scala:
##########
@@ -1382,6 +1382,21 @@ class Column(val node: ColumnNode) extends Logging {
*/
def over(): Column = over(Window.spec)
+ /**
+ * Marks this column reference as an outer reference for subqueries.
+ *
+ * @group subquery
+ * @since 4.0.0
+ */
+ def outer(): Column = withOrigin {
+ node match {
+ case attr: UnresolvedAttribute if !attr.isMetadataColumn =>
+ Column(LazyOuterReference(attr.nameParts, attr.planId))
+ case _ =>
+ throw new IllegalArgumentException(
Review Comment:
This is a user-facing API, so I feel throwing an internal error is weird. I'm ok to change the exception type to other user-facing exception, though.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
Re: [PR] [SPARK-50130][SQL][PYTHON] Add DataFrame APIs for scalar and exists subqueries [spark]
Posted by "cloud-fan (via GitHub)" <gi...@apache.org>.
cloud-fan commented on code in PR #48664:
URL: https://github.com/apache/spark/pull/48664#discussion_r1837461263
##########
sql/api/src/main/scala/org/apache/spark/sql/internal/columnNodes.scala:
##########
@@ -167,6 +167,24 @@ private[sql] object UnresolvedAttribute {
apply(unparsedIdentifier, None, false, CurrentOrigin.get)
}
+/**
+ * Reference to an attribute in the outer context, used for Subqueries.
+ *
+ * @param nameParts
+ * name of the attribute.
+ * @param planId
+ * id of the plan (Dataframe) that produces the attribute.
+ */
+private[sql] case class LazyOuterReference(
+ nameParts: Seq[String],
+ planId: Option[Long] = None,
+ override val origin: Origin = CurrentOrigin.get)
+ extends ColumnNode {
+ override private[internal] def normalize(): LazyOuterReference =
+ copy(planId = None, origin = NO_ORIGIN)
+ override def sql: String = nameParts.map(n => if (n.contains(".")) s"`$n`" else n).mkString(".")
Review Comment:
let's be consistent with other parts in spark SQL and use `QuotingUtils.quoteIfNeeded`
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
Re: [PR] [WIP][SPARK-50130][SQL][PYTHON] Add DataFrame APIs for subqueries [spark]
Posted by "ueshin (via GitHub)" <gi...@apache.org>.
ueshin commented on PR #48664:
URL: https://github.com/apache/spark/pull/48664#issuecomment-2439040058
cc @allisonwang-db @cloud-fan @dtenedor
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
Re: [PR] [SPARK-50130][SQL][PYTHON] Add DataFrame APIs for scalar and exists subqueries [spark]
Posted by "HyukjinKwon (via GitHub)" <gi...@apache.org>.
HyukjinKwon closed pull request #48664: [SPARK-50130][SQL][PYTHON] Add DataFrame APIs for scalar and exists subqueries
URL: https://github.com/apache/spark/pull/48664
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
Re: [PR] [SPARK-50130][SQL][PYTHON] Add DataFrame APIs for scalar and exists subqueries [spark]
Posted by "hvanhovell (via GitHub)" <gi...@apache.org>.
hvanhovell commented on code in PR #48664:
URL: https://github.com/apache/spark/pull/48664#discussion_r1838079393
##########
sql/core/src/main/scala/org/apache/spark/sql/Dataset.scala:
##########
@@ -245,13 +262,17 @@ class Dataset[T] private[sql](
}
@transient private[sql] val logicalPlan: LogicalPlan = {
- val plan = queryExecution.commandExecuted
- if (sparkSession.sessionState.conf.getConf(SQLConf.FAIL_AMBIGUOUS_SELF_JOIN_ENABLED)) {
- val dsIds = plan.getTagValue(Dataset.DATASET_ID_TAG).getOrElse(new HashSet[Long])
- dsIds.add(id)
- plan.setTagValue(Dataset.DATASET_ID_TAG, dsIds)
+ if (queryExecution.isLazyAnalysis) {
+ queryExecution.logical
Review Comment:
What happens if the Dataset is a command?
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
Re: [PR] [SPARK-50130][SQL][PYTHON] Add DataFrame APIs for scalar and exists subqueries [spark]
Posted by "HyukjinKwon (via GitHub)" <gi...@apache.org>.
HyukjinKwon commented on PR #48664:
URL: https://github.com/apache/spark/pull/48664#issuecomment-2469436358
Merged to master.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
Re: [PR] [SPARK-50130][SQL][PYTHON] Add DataFrame APIs for scalar and exists subqueries [spark]
Posted by "cloud-fan (via GitHub)" <gi...@apache.org>.
cloud-fan commented on code in PR #48664:
URL: https://github.com/apache/spark/pull/48664#discussion_r1837471917
##########
sql/api/src/main/scala/org/apache/spark/sql/Column.scala:
##########
@@ -1382,6 +1382,21 @@ class Column(val node: ColumnNode) extends Logging {
*/
def over(): Column = over(Window.spec)
+ /**
+ * Marks this column reference as an outer reference for subqueries.
+ *
+ * @group subquery
+ * @since 4.0.0
+ */
+ def outer(): Column = withOrigin {
+ node match {
+ case attr: UnresolvedAttribute if !attr.isMetadataColumn =>
+ Column(LazyOuterReference(attr.nameParts, attr.planId))
+ case _ =>
+ throw new IllegalArgumentException(
Review Comment:
we should throw an internal error.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
Re: [PR] [SPARK-50130][SQL][PYTHON] Add DataFrame APIs for scalar and exists subqueries [spark]
Posted by "ueshin (via GitHub)" <gi...@apache.org>.
ueshin commented on code in PR #48664:
URL: https://github.com/apache/spark/pull/48664#discussion_r1838714632
##########
sql/core/src/main/scala/org/apache/spark/sql/Dataset.scala:
##########
@@ -95,9 +95,14 @@ private[sql] object Dataset {
def ofRows(sparkSession: SparkSession, logicalPlan: LogicalPlan): DataFrame =
sparkSession.withActive {
val qe = sparkSession.sessionState.executePlan(logicalPlan)
- qe.assertAnalyzed()
- new Dataset[Row](qe, RowEncoder.encoderFor(qe.analyzed.schema))
- }
+ val encoder = if (qe.isLazyAnalysis) {
+ RowEncoder.encoderFor(new StructType())
Review Comment:
@hvanhovell sure, submitted #48829.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
Re: [PR] [SPARK-50130][SQL][PYTHON] Add DataFrame APIs for scalar and exists subqueries [spark]
Posted by "allisonwang-db (via GitHub)" <gi...@apache.org>.
allisonwang-db commented on code in PR #48664:
URL: https://github.com/apache/spark/pull/48664#discussion_r1830254768
##########
sql/api/src/main/scala/org/apache/spark/sql/internal/columnNodes.scala:
##########
@@ -167,6 +167,16 @@ private[sql] object UnresolvedAttribute {
apply(unparsedIdentifier, None, false, CurrentOrigin.get)
}
+private[sql] case class LazyOuterReference(
Review Comment:
Let's add a javadoc here explaining this node?
##########
python/pyspark/sql/dataframe.py:
##########
@@ -6476,6 +6476,153 @@ def transpose(self, indexColumn: Optional["ColumnOrName"] = None) -> "DataFrame"
"""
...
+ def scalar(self) -> Column:
+ """
+ Return a `Column` object for a SCALAR Subquery containing exactly one row and one column.
+
+ The `scalar()` method is useful for extracting a `Column` object that represents a scalar
+ value from a DataFrame, especially when the DataFrame results from an aggregation or
+ single-value computation. This returned `Column` can then be used directly in `select`
+ clauses or as predicates in filters on the outer DataFrame, enabling dynamic data filtering
+ and calculations based on scalar values.
+
+ .. versionadded:: 4.0.0
+
+ Returns
+ -------
+ :class:`Column`
+ A `Column` object representing a SCALAR subquery.
+
+ Examples
+ --------
+ Setup a sample DataFrame.
+
+ >>> data = [
+ ... (1, "Alice", 45000, 101), (2, "Bob", 54000, 101), (3, "Charlie", 29000, 102),
+ ... (4, "David", 61000, 102), (5, "Eve", 48000, 101),
+ ... ]
+ >>> employees = spark.createDataFrame(data, ["id", "name", "salary", "department_id"])
+
+ Example 1: Filter for employees with salary greater than the average salary.
Review Comment:
Can we also mention here this is a non-correlated example?
##########
python/pyspark/sql/dataframe.py:
##########
@@ -6476,6 +6476,153 @@ def transpose(self, indexColumn: Optional["ColumnOrName"] = None) -> "DataFrame"
"""
...
+ def scalar(self) -> Column:
+ """
+ Return a `Column` object for a SCALAR Subquery containing exactly one row and one column.
+
+ The `scalar()` method is useful for extracting a `Column` object that represents a scalar
+ value from a DataFrame, especially when the DataFrame results from an aggregation or
+ single-value computation. This returned `Column` can then be used directly in `select`
+ clauses or as predicates in filters on the outer DataFrame, enabling dynamic data filtering
+ and calculations based on scalar values.
+
+ .. versionadded:: 4.0.0
+
+ Returns
+ -------
+ :class:`Column`
+ A `Column` object representing a SCALAR subquery.
+
+ Examples
+ --------
+ Setup a sample DataFrame.
+
+ >>> data = [
+ ... (1, "Alice", 45000, 101), (2, "Bob", 54000, 101), (3, "Charlie", 29000, 102),
+ ... (4, "David", 61000, 102), (5, "Eve", 48000, 101),
+ ... ]
+ >>> employees = spark.createDataFrame(data, ["id", "name", "salary", "department_id"])
+
+ Example 1: Filter for employees with salary greater than the average salary.
+
+ >>> from pyspark.sql import functions as sf
+ >>> employees.where(
+ ... sf.col("salary") > employees.select(sf.avg("salary")).scalar()
+ ... ).select("name", "salary", "department_id").show()
+ +-----+------+-------------+
+ | name|salary|department_id|
+ +-----+------+-------------+
+ | Bob| 54000| 101|
+ |David| 61000| 102|
+ | Eve| 48000| 101|
+ +-----+------+-------------+
+
+ Example 2: Filter for employees with salary greater than the average salary in their
+ department.
+
+ >>> from pyspark.sql import functions as sf
+ >>> employees.where(
+ ... sf.col("salary")
+ ... > employees.where(sf.col("department_id") == sf.col("department_id").outer())
+ ... .select(sf.avg("salary")).scalar()
+ ... ).select("name", "salary", "department_id").show()
+ +-----+------+-------------+
+ | name|salary|department_id|
+ +-----+------+-------------+
+ | Bob| 54000| 101|
+ |David| 61000| 102|
+ +-----+------+-------------+
+
+ Example 3: Select the name, salary, and the proportion of the salary in the department.
+
+ >>> from pyspark.sql import functions as sf
+ >>> employees.select(
+ ... "name", "salary", "department_id",
+ ... sf.format_number(
+ ... sf.lit(100) * sf.col("salary") /
+ ... employees.where(sf.col("department_id") == sf.col("department_id").outer())
+ ... .select(sf.sum("salary")).scalar().alias("avg_salary"),
+ ... 1
+ ... ).alias("salary_proportion_in_department")
+ ... ).show()
+ +-------+------+-------------+-------------------------------+
+ | name|salary|department_id|salary_proportion_in_department|
+ +-------+------+-------------+-------------------------------+
+ | Alice| 45000| 101| 30.6|
+ | Bob| 54000| 101| 36.7|
+ |Charlie| 29000| 102| 32.2|
+ | Eve| 48000| 101| 32.7|
+ | David| 61000| 102| 67.8|
+ +-------+------+-------------+-------------------------------+
+ """
+ ...
+
+ def exists(self) -> Column:
+ """
+ Return a `Column` object for an EXISTS Subquery.
+
+ The `exists` method provides a way to create a boolean column that checks for the presence
+ of related records in a subquery. When applied within a `DataFrame`, this method allows you
+ to filter rows based on whether matching records exist in the related dataset. The resulting
+ `Column` object can be used directly in filtering conditions or as a computed column.
+
+ .. versionadded:: 4.0.0
+
+ Returns
+ -------
+ :class:`Column`
+ A `Column` object representing an EXISTS subquery
+
+ Examples
+ --------
+ Setup sample data for customers and orders.
+
+ >>> data_customers = [
+ ... (101, "Alice", "USA"), (102, "Bob", "Canada"), (103, "Charlie", "USA"),
+ ... (104, "David", "Australia")
+ ... ]
+ >>> data_orders = [
+ ... (1, 101, "2023-01-15", 250), (2, 102, "2023-01-20", 300),
+ ... (3, 103, "2023-01-25", 400), (4, 101, "2023-02-05", 150)
+ ... ]
+ >>> customers = spark.createDataFrame(
+ ... data_customers, ["customer_id", "customer_name", "country"])
+ >>> orders = spark.createDataFrame(
+ ... data_orders, ["order_id", "customer_id", "order_date", "total_amount"])
+
+ Example 1: Filter for customers who have placed at least one order.
+
+ >>> from pyspark.sql import functions as sf
+ >>> customers.where(
+ ... orders.where(sf.col("customer_id") == sf.col("customer_id").outer()).exists()
Review Comment:
Does it work with NOT EXISTS?
##########
sql/core/src/main/scala/org/apache/spark/sql/Dataset.scala:
##########
@@ -106,8 +111,13 @@ private[sql] object Dataset {
sparkSession.withActive {
val qe = new QueryExecution(
sparkSession, logicalPlan, shuffleCleanupMode = shuffleCleanupMode)
- qe.assertAnalyzed()
- new Dataset[Row](qe, RowEncoder.encoderFor(qe.analyzed.schema))
+ val encoder = if (qe.isLazyAnalysis) {
+ RowEncoder.encoderFor(new StructType())
Review Comment:
I wonder if we can add a test to trigger this case
##########
python/pyspark/sql/dataframe.py:
##########
@@ -6476,6 +6476,153 @@ def transpose(self, indexColumn: Optional["ColumnOrName"] = None) -> "DataFrame"
"""
...
+ def scalar(self) -> Column:
+ """
+ Return a `Column` object for a SCALAR Subquery containing exactly one row and one column.
+
+ The `scalar()` method is useful for extracting a `Column` object that represents a scalar
+ value from a DataFrame, especially when the DataFrame results from an aggregation or
+ single-value computation. This returned `Column` can then be used directly in `select`
+ clauses or as predicates in filters on the outer DataFrame, enabling dynamic data filtering
+ and calculations based on scalar values.
+
+ .. versionadded:: 4.0.0
+
+ Returns
+ -------
+ :class:`Column`
+ A `Column` object representing a SCALAR subquery.
+
+ Examples
+ --------
+ Setup a sample DataFrame.
+
+ >>> data = [
+ ... (1, "Alice", 45000, 101), (2, "Bob", 54000, 101), (3, "Charlie", 29000, 102),
+ ... (4, "David", 61000, 102), (5, "Eve", 48000, 101),
+ ... ]
+ >>> employees = spark.createDataFrame(data, ["id", "name", "salary", "department_id"])
+
+ Example 1: Filter for employees with salary greater than the average salary.
+
+ >>> from pyspark.sql import functions as sf
+ >>> employees.where(
+ ... sf.col("salary") > employees.select(sf.avg("salary")).scalar()
+ ... ).select("name", "salary", "department_id").show()
+ +-----+------+-------------+
+ | name|salary|department_id|
+ +-----+------+-------------+
+ | Bob| 54000| 101|
+ |David| 61000| 102|
+ | Eve| 48000| 101|
+ +-----+------+-------------+
+
+ Example 2: Filter for employees with salary greater than the average salary in their
Review Comment:
ditto, let's mention that this is a correlated subquery example
##########
sql/core/src/main/scala/org/apache/spark/sql/Dataset.scala:
##########
@@ -245,13 +262,17 @@ class Dataset[T] private[sql](
}
@transient private[sql] val logicalPlan: LogicalPlan = {
- val plan = queryExecution.commandExecuted
- if (sparkSession.sessionState.conf.getConf(SQLConf.FAIL_AMBIGUOUS_SELF_JOIN_ENABLED)) {
- val dsIds = plan.getTagValue(Dataset.DATASET_ID_TAG).getOrElse(new HashSet[Long])
- dsIds.add(id)
- plan.setTagValue(Dataset.DATASET_ID_TAG, dsIds)
+ if (queryExecution.isLazyAnalysis) {
+ queryExecution.logical
Review Comment:
cc @cloud-fan
##########
sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/analysis/Analyzer.scala:
##########
@@ -2482,9 +2482,16 @@ class Analyzer(override val catalogManager: CatalogManager) extends RuleExecutor
*/
private def resolveSubQuery(
e: SubqueryExpression,
- outer: LogicalPlan)(
+ outer: LogicalPlan,
+ hasExplicitOuterRefs: Boolean = false)(
Review Comment:
nit: let's add this new parameter to the doc above?
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
Re: [PR] [WIP][SPARK-50130][SQL][PYTHON] Add DataFrame APIs for scalar and exists subqueries [spark]
Posted by "ueshin (via GitHub)" <gi...@apache.org>.
ueshin commented on code in PR #48664:
URL: https://github.com/apache/spark/pull/48664#discussion_r1817452154
##########
sql/api/src/main/scala/org/apache/spark/sql/Column.scala:
##########
@@ -1382,6 +1382,17 @@ class Column(val node: ColumnNode) extends Logging {
*/
def over(): Column = over(Window.spec)
+ /**
+ * TODO: Add documentation
+ * @return
+ */
+ def outer(): Column = withOrigin {
+ node match {
+ case attr: UnresolvedAttribute if !attr.isMetadataColumn =>
+ Column(LazyOuterReference(attr.nameParts, attr.planId))
+ // TODO: other cases
Review Comment:
Is it ok to use metadata columns as outer?
Are there other cases we can support `outer()`?
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
Re: [PR] [SPARK-50130][SQL][PYTHON] Add DataFrame APIs for scalar and exists subqueries [spark]
Posted by "cloud-fan (via GitHub)" <gi...@apache.org>.
cloud-fan commented on code in PR #48664:
URL: https://github.com/apache/spark/pull/48664#discussion_r1850341691
##########
sql/core/src/main/scala/org/apache/spark/sql/Dataset.scala:
##########
@@ -245,13 +262,17 @@ class Dataset[T] private[sql](
}
@transient private[sql] val logicalPlan: LogicalPlan = {
- val plan = queryExecution.commandExecuted
- if (sparkSession.sessionState.conf.getConf(SQLConf.FAIL_AMBIGUOUS_SELF_JOIN_ENABLED)) {
- val dsIds = plan.getTagValue(Dataset.DATASET_ID_TAG).getOrElse(new HashSet[Long])
- dsIds.add(id)
- plan.setTagValue(Dataset.DATASET_ID_TAG, dsIds)
+ if (queryExecution.isLazyAnalysis) {
+ queryExecution.logical
Review Comment:
you can't put a command inside a subquery expression (neither in SQL).
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
Re: [PR] [SPARK-50130][SQL][PYTHON] Add DataFrame APIs for scalar and exists subqueries [spark]
Posted by "ueshin (via GitHub)" <gi...@apache.org>.
ueshin commented on code in PR #48664:
URL: https://github.com/apache/spark/pull/48664#discussion_r1831879506
##########
sql/core/src/main/scala/org/apache/spark/sql/Dataset.scala:
##########
@@ -106,8 +111,13 @@ private[sql] object Dataset {
sparkSession.withActive {
val qe = new QueryExecution(
sparkSession, logicalPlan, shuffleCleanupMode = shuffleCleanupMode)
- qe.assertAnalyzed()
- new Dataset[Row](qe, RowEncoder.encoderFor(qe.analyzed.schema))
+ val encoder = if (qe.isLazyAnalysis) {
+ RowEncoder.encoderFor(new StructType())
Review Comment:
Every test using outer references will pass this case?
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org