You are viewing a plain text version of this content. The canonical link for it is here.
Posted to reviews@spark.apache.org by "szehon-ho (via GitHub)" <gi...@apache.org> on 2024/05/22 20:55:45 UTC
[PR] [SPARK-36680][SQL] Supports Dynamic Table Options for Spark SQL [spark]
szehon-ho opened a new pull request, #46707:
URL: https://github.com/apache/spark/pull/46707
### What changes were proposed in this pull request?
Add WITH OPTIONS syntax to read relations
This is a continuation of https://github.com/apache/spark/pull/41683 based on @cloud-fan's nice suggestion.
That was itself a continuation of https://github.com/apache/spark/pull/34072.
### Why are the changes needed?
Currently there is no way to dynamically configure individual DataSource relations in Spark SQL query , compared to DataFrameReader
### Does this PR introduce _any_ user-facing change?
No
### How was this patch tested?
Unit test in DataSourceV2SQLSuite
### 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-36680][SQL] Supports Dynamic Table Options for Spark SQL [spark]
Posted by "huaxingao (via GitHub)" <gi...@apache.org>.
huaxingao commented on code in PR #46707:
URL: https://github.com/apache/spark/pull/46707#discussion_r1667738141
##########
sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/parser/AstBuilder.scala:
##########
@@ -2970,9 +2971,14 @@ class AstBuilder extends DataTypeAstBuilder with SQLConfHelper with Logging {
/**
* Create an [[UnresolvedRelation]] from an identifier reference.
*/
- private def createUnresolvedRelation(
- ctx: IdentifierReferenceContext): LogicalPlan = withOrigin(ctx) {
- withIdentClause(ctx, UnresolvedRelation(_))
+ private def createUnresolvedRelation(identifier: IdentifierReferenceContext,
+ optionsClause: Option[OptionsClauseContext] = None): LogicalPlan =
+ withOrigin(identifier) {
+ val options = optionsClause.map{clause =>
Review Comment:
nit: we need space before and after `{`
`optionsClause.map{clause` should be `optionsClause.map { clause`
--
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-36680][SQL] Supports Dynamic Table Options for Spark SQL [spark]
Posted by "beliefer (via GitHub)" <gi...@apache.org>.
beliefer commented on PR #46707:
URL: https://github.com/apache/spark/pull/46707#issuecomment-2147264205
> What's the dynamic table option? It seems static.
I got you mean now.
--
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-36680][SQL] Supports Dynamic Table Options for Spark SQL [spark]
Posted by "cloud-fan (via GitHub)" <gi...@apache.org>.
cloud-fan commented on code in PR #46707:
URL: https://github.com/apache/spark/pull/46707#discussion_r1628736440
##########
sql/api/src/main/antlr4/org/apache/spark/sql/catalyst/parser/SqlBaseParser.g4:
##########
@@ -823,13 +823,17 @@ identifierComment
relationPrimary
: identifierReference temporalClause?
- sample? tableAlias #tableName
+ sample? optionsClause? tableAlias #tableName
Review Comment:
should this come before table sample?
--
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-36680][SQL] Supports Dynamic Table Options for Spark SQL [spark]
Posted by "szehon-ho (via GitHub)" <gi...@apache.org>.
szehon-ho commented on PR #46707:
URL: https://github.com/apache/spark/pull/46707#issuecomment-2128287477
Fixed the review items, thanks @huaxingao , if you want to take another look
--
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-36680][SQL] Supports Dynamic Table Options for Spark SQL [spark]
Posted by "srielau (via GitHub)" <gi...@apache.org>.
srielau commented on code in PR #46707:
URL: https://github.com/apache/spark/pull/46707#discussion_r1669539931
##########
sql/api/src/main/antlr4/org/apache/spark/sql/catalyst/parser/SqlBaseParser.g4:
##########
@@ -823,13 +823,17 @@ identifierComment
relationPrimary
: identifierReference temporalClause?
- sample? tableAlias #tableName
+ sample? optionsClause? tableAlias #tableName
| LEFT_PAREN query RIGHT_PAREN sample? tableAlias #aliasedQuery
| LEFT_PAREN relation RIGHT_PAREN sample? tableAlias #aliasedRelation
| inlineTable #inlineTableDefault2
| functionTable #tableValuedFunction
;
+optionsClause
+ : WITH OPTIONS options=propertyList
Review Comment:
There is a discontinuity in the comments.... why did it flip flop? I'm also in favor of just WITH (or make it optional)
--
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-36680][SQL] Supports Dynamic Table Options for Spark SQL [spark]
Posted by "szehon-ho (via GitHub)" <gi...@apache.org>.
szehon-ho commented on PR #46707:
URL: https://github.com/apache/spark/pull/46707#issuecomment-2222109524
sounds good, thanks! i was planning to do follow up for write (INSERT) and can look at any other missing ones too.
--
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-36680][SQL] Supports Dynamic Table Options for Spark SQL [spark]
Posted by "shardulm94 (via GitHub)" <gi...@apache.org>.
shardulm94 commented on PR #46707:
URL: https://github.com/apache/spark/pull/46707#issuecomment-2221815885
Thanks @szehon-ho for this! This is really useful for `SELECT` statements. However we also have `UPDATE` and `DELETE` statements which can lead to a scans. Should we also add also add support for options in `UPDATE` and `DELETE`? Would be happy to raise a 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] [SPARK-36680][SQL] Supports Dynamic Table Options for Spark SQL [spark]
Posted by "szehon-ho (via GitHub)" <gi...@apache.org>.
szehon-ho commented on PR #46707:
URL: https://github.com/apache/spark/pull/46707#issuecomment-2218865386
Thanks, removed OPTIONS and other review comments. Can add OPTIONS as optional in subsequent 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] [SPARK-36680][SQL] Supports Dynamic Table Options for Spark SQL [spark]
Posted by "cloud-fan (via GitHub)" <gi...@apache.org>.
cloud-fan closed pull request #46707: [SPARK-36680][SQL] Supports Dynamic Table Options for Spark SQL
URL: https://github.com/apache/spark/pull/46707
--
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-36680][SQL] Supports Dynamic Table Options for Spark SQL [spark]
Posted by "huaxingao (via GitHub)" <gi...@apache.org>.
huaxingao commented on code in PR #46707:
URL: https://github.com/apache/spark/pull/46707#discussion_r1616404811
##########
sql/api/src/main/antlr4/org/apache/spark/sql/catalyst/parser/SqlBaseParser.g4:
##########
@@ -823,13 +823,17 @@ identifierComment
relationPrimary
: identifierReference temporalClause?
- sample? tableAlias #tableName
+ sample? optionsClause? tableAlias #tableName
| LEFT_PAREN query RIGHT_PAREN sample? tableAlias #aliasedQuery
| LEFT_PAREN relation RIGHT_PAREN sample? tableAlias #aliasedRelation
| inlineTable #inlineTableDefault2
| functionTable #tableValuedFunction
;
+optionsClause
+ : WITH options=propertyList?
Review Comment:
I think we don't need the `?`. We will either have `WITH options=propertyList` as a whole, or we won't have it at all. This way, we don't need to check `if (clause.options == null)` in `createUnresolvedRelation`, and there's no need to add the `noOptionsSpecifiedError`. In your negative tests, we don't need to include `SELECT * FROM $t1 WITH`. I believe the `WITH` will be correctly interpreted by other rules.
--
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-36680][SQL] Supports Dynamic Table Options for Spark SQL [spark]
Posted by "cloud-fan (via GitHub)" <gi...@apache.org>.
cloud-fan commented on code in PR #46707:
URL: https://github.com/apache/spark/pull/46707#discussion_r1669539861
##########
sql/api/src/main/antlr4/org/apache/spark/sql/catalyst/parser/SqlBaseParser.g4:
##########
@@ -823,13 +823,17 @@ identifierComment
relationPrimary
: identifierReference temporalClause?
- sample? tableAlias #tableName
+ sample? optionsClause? tableAlias #tableName
| LEFT_PAREN query RIGHT_PAREN sample? tableAlias #aliasedQuery
| LEFT_PAREN relation RIGHT_PAREN sample? tableAlias #aliasedRelation
| inlineTable #inlineTableDefault2
| functionTable #tableValuedFunction
;
+optionsClause
+ : WITH OPTIONS options=propertyList
Review Comment:
Shall we make `OPTIONS` optional so that it's more flexible?
--
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-36680][SQL] Supports Dynamic Table Options for Spark SQL [spark]
Posted by "huaxingao (via GitHub)" <gi...@apache.org>.
huaxingao commented on code in PR #46707:
URL: https://github.com/apache/spark/pull/46707#discussion_r1611068253
##########
sql/api/src/main/antlr4/org/apache/spark/sql/catalyst/parser/SqlBaseParser.g4:
##########
@@ -823,13 +823,17 @@ identifierComment
relationPrimary
: identifierReference temporalClause?
- sample? tableAlias #tableName
+ sample? optionsClause? tableAlias #tableName
Review Comment:
nit: align `#tableName` with `#aliasedQuery`?
--
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-36680][SQL] Supports Dynamic Table Options for Spark SQL [spark]
Posted by "szehon-ho (via GitHub)" <gi...@apache.org>.
szehon-ho commented on PR #46707:
URL: https://github.com/apache/spark/pull/46707#issuecomment-2147931004
@beliefer i didnt make the initial JIRA title :) , let me know if something makes more sense.
--
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-36680][SQL] Supports Dynamic Table Options for Spark SQL [spark]
Posted by "huaxingao (via GitHub)" <gi...@apache.org>.
huaxingao commented on PR #46707:
URL: https://github.com/apache/spark/pull/46707#issuecomment-2209372977
Thank you all for your thoughtful comments and suggestions. I appreciate the discussion around leveraging TVF-like syntax, and I understand the potential benefits of using a more SQL-standard approach. However, I would like to highlight why I believe the `WITH OPTIONS` syntax remains the most beneficial for our current goals:
1. Simplicity and Accessibility:
The `WITH OPTIONS` syntax is straightforward and directly communicates the intent of applying specific options to the SQL query. It's an intuitive approach that can be easily understood by users without extensive SQL background.
2. Separation of Concerns:
Using `WITH OPTIONS` cleanly separates the functional part of the SQL query from the configuration parameters. This clarity improves code readability and maintenance, as opposed to embedding options within a TVF syntax that could obscure the primary purpose of the query.
3. Avoiding Over-complexity:
While TVF-like syntax offers elegance, it introduces a level of complexity that may not be necessary. For instance, distinguishing between a table and a TVF in queries, as mentioned, can confuse users and complicate the parser's job. Our proposed syntax avoids this by keeping the query structure familiar and straightforward.
4. Focus on Practical Use Cases:
The `WITH OPTIONS` syntax is specifically designed to handle practical scenarios where users might need to dynamically configure queries based on different execution contexts, such as adjusting `fetchSize` for performance tuning. This approach is particularly beneficial in environments where such configurations are frequently modified.
I propose we continue with the `WITH OPTIONS` syntax for its clarity, ease of use, and flexibility. I would like to move this PR forward. 🙂
--
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-36680][SQL] Supports Dynamic Table Options for Spark SQL [spark]
Posted by "szehon-ho (via GitHub)" <gi...@apache.org>.
szehon-ho commented on code in PR #46707:
URL: https://github.com/apache/spark/pull/46707#discussion_r1631776812
##########
sql/api/src/main/antlr4/org/apache/spark/sql/catalyst/parser/SqlBaseParser.g4:
##########
@@ -823,13 +823,17 @@ identifierComment
relationPrimary
: identifierReference temporalClause?
- sample? tableAlias #tableName
+ sample? optionsClause? tableAlias #tableName
| LEFT_PAREN query RIGHT_PAREN sample? tableAlias #aliasedQuery
| LEFT_PAREN relation RIGHT_PAREN sample? tableAlias #aliasedRelation
| inlineTable #inlineTableDefault2
| functionTable #tableValuedFunction
;
+optionsClause
+ : WITH OPTIONS options=propertyList
Review Comment:
Change back to WITH OPTIONS , checking is it @cloud-fan 's suggestion?
##########
sql/api/src/main/antlr4/org/apache/spark/sql/catalyst/parser/SqlBaseParser.g4:
##########
@@ -823,13 +823,17 @@ identifierComment
relationPrimary
: identifierReference temporalClause?
- sample? tableAlias #tableName
+ sample? optionsClause? tableAlias #tableName
| LEFT_PAREN query RIGHT_PAREN sample? tableAlias #aliasedQuery
| LEFT_PAREN relation RIGHT_PAREN sample? tableAlias #aliasedRelation
| inlineTable #inlineTableDefault2
| functionTable #tableValuedFunction
;
+optionsClause
+ : WITH OPTIONS options=propertyList
Review Comment:
Changed back to WITH OPTIONS , checking is it @cloud-fan 's suggestion?
--
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-36680][SQL] Supports Dynamic Table Options for Spark SQL [spark]
Posted by "cloud-fan (via GitHub)" <gi...@apache.org>.
cloud-fan commented on code in PR #46707:
URL: https://github.com/apache/spark/pull/46707#discussion_r1628746131
##########
sql/core/src/test/scala/org/apache/spark/sql/connector/DataSourceV2SQLSuite.scala:
##########
@@ -3488,6 +3488,35 @@ class DataSourceV2SQLSuiteV1Filter
}
}
+ test("SPARK-36680: Supports Dynamic Table Options for Spark SQL") {
+ val t1 = s"${catalogAndNamespace}table"
+ withTable(t1) {
+ sql(s"CREATE TABLE $t1 (id bigint, data string) USING $v2Format")
+ sql(s"INSERT INTO $t1 VALUES (1, 'a'), (2, 'b')")
+
+ var df = sql(s"SELECT * FROM $t1")
+ var collected = df.queryExecution.optimizedPlan.collect {
+ case scan: DataSourceV2ScanRelation =>
+ assert(scan.relation.options.isEmpty)
+ }
+ assert (collected.size == 1)
+ checkAnswer(df, Seq(Row(1, "a"), Row(2, "b")))
+
+ df = sql(s"SELECT * FROM $t1 WITH (`split-size` = 5)")
Review Comment:
ok how about we add a TVF like syntax but it's not actual TVF, so that we can do `FROM BATCH(tbl, k1 => v1, ...)`, and `FROM STREAM(tbl, k1 => v1, ...)`. It's not actual TVF because the first parameter is table identifier, but we can make it a valid TVF with `FROM BATCH(TABLE tbl, k1 => v1, ...)`, which is more verbose.
--
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-36680][SQL] Supports Dynamic Table Options for Spark SQL [spark]
Posted by "huaxingao (via GitHub)" <gi...@apache.org>.
huaxingao commented on code in PR #46707:
URL: https://github.com/apache/spark/pull/46707#discussion_r1611069471
##########
sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/parser/AstBuilder.scala:
##########
@@ -2929,8 +2930,18 @@ class AstBuilder extends DataTypeAstBuilder with SQLConfHelper with Logging {
* Create an [[UnresolvedRelation]] from an identifier reference.
*/
private def createUnresolvedRelation(
- ctx: IdentifierReferenceContext): LogicalPlan = withOrigin(ctx) {
- withIdentClause(ctx, UnresolvedRelation(_))
+ identifier: IdentifierReferenceContext,
Review Comment:
nit: 4 space indentation
--
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-36680][SQL] Supports Dynamic Table Options for Spark SQL [spark]
Posted by "cloud-fan (via GitHub)" <gi...@apache.org>.
cloud-fan commented on PR #46707:
URL: https://github.com/apache/spark/pull/46707#issuecomment-2216209601
only one comment: https://github.com/apache/spark/pull/46707/files#r1669539861
--
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-36680][SQL] Supports Dynamic Table Options for Spark SQL [spark]
Posted by "shardulm94 (via GitHub)" <gi...@apache.org>.
shardulm94 commented on PR #46707:
URL: https://github.com/apache/spark/pull/46707#issuecomment-2222182510
Thanks @szehon-ho! I think there may be an interesting discussion here. E.g. consider the `DELETE` statement with some options passed
```
DELETE FROM tbl WITH (`split-size` = 5)
WHERE id > 10
```
What do these options apply to? The scan from the table, or the write to the table?
1. We can pass the options to both the read and the write, and let the datasource disambiguate. I don't think this is great because there will likely be a case in the future whether the same option with different values would need to be passed to read and write.
2. We could create some syntax disambiguating reads v/s writes. E.g. `WITH READ OPTIONS('a', 'b') WRITE OPTIONS ('c', 'd')`. I prefer this approach, I think this is more clear for the user, but it goes against previous discussions in this PR.
Other ideas welcome. Same scenario applies to `UPDATE` which also has single table identifier to read from and write to. `MERGE` and `INSERT` have separate source and target identifiers and so you could specify options separately per identifier.
--
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-36680][SQL] Supports Dynamic Table Options for Spark SQL [spark]
Posted by "advancedxy (via GitHub)" <gi...@apache.org>.
advancedxy commented on PR #46707:
URL: https://github.com/apache/spark/pull/46707#issuecomment-2271453058
> Other ideas welcome. Same scenario applies to UPDATE which also has single table identifier to read from and write to.
I noticed SQL Server uses with to specify hints: https://learn.microsoft.com/en-us/sql/t-sql/queries/hints-transact-sql-table?view=sql-server-ver16. We may refer that and design new syntaxes as
```SQL
with (options(k1=v1, k2=v2), read(k3=v3, k4=v4), write(k5=v5, k6=v6))
```
? However, I think `WITH READ OPTIONS('a', 'b') WRITE OPTIONS ('c', 'd')` is clear and sufficient for read/write options only.
--
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-36680][SQL] Supports Dynamic Table Options for Spark SQL [spark]
Posted by "huaxingao (via GitHub)" <gi...@apache.org>.
huaxingao commented on code in PR #46707:
URL: https://github.com/apache/spark/pull/46707#discussion_r1667739136
##########
sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/parser/AstBuilder.scala:
##########
@@ -2970,9 +2971,14 @@ class AstBuilder extends DataTypeAstBuilder with SQLConfHelper with Logging {
/**
* Create an [[UnresolvedRelation]] from an identifier reference.
*/
- private def createUnresolvedRelation(
- ctx: IdentifierReferenceContext): LogicalPlan = withOrigin(ctx) {
- withIdentClause(ctx, UnresolvedRelation(_))
+ private def createUnresolvedRelation(identifier: IdentifierReferenceContext,
+ optionsClause: Option[OptionsClauseContext] = None): LogicalPlan =
+ withOrigin(identifier) {
Review Comment:
nit: can we change to
```
private def createUnresolvedRelation(
ctx: IdentifierReferenceContext,
optionsClause: Option[OptionsClauseContext] = None): LogicalPlan = withOrigin(ctx) {
......
}
```
Also, we probably want to update the method doc to reflect the newly added `optionsClause: Option[OptionsClauseContext]` parameter.
--
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-36680][SQL] Supports Dynamic Table Options for Spark SQL [spark]
Posted by "huaxingao (via GitHub)" <gi...@apache.org>.
huaxingao commented on code in PR #46707:
URL: https://github.com/apache/spark/pull/46707#discussion_r1611066553
##########
sql/api/src/main/antlr4/org/apache/spark/sql/catalyst/parser/SqlBaseParser.g4:
##########
@@ -823,13 +823,17 @@ identifierComment
relationPrimary
: identifierReference temporalClause?
- sample? tableAlias #tableName
+ sample? optionsClause? tableAlias #tableName
| LEFT_PAREN query RIGHT_PAREN sample? tableAlias #aliasedQuery
| LEFT_PAREN relation RIGHT_PAREN sample? tableAlias #aliasedRelation
| inlineTable #inlineTableDefault2
| functionTable #tableValuedFunction
;
+optionsClause
+ : WITH OPTIONS options=propertyList
Review Comment:
Shall we use `WITH` instead of `WITH OPTIONS`? [postgresql syntax](https://www.postgresql.org/docs/current/sql-createtable.html) uses `WITH`
--
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-36680][SQL] Supports Dynamic Table Options for Spark SQL [spark]
Posted by "dtenedor (via GitHub)" <gi...@apache.org>.
dtenedor commented on code in PR #46707:
URL: https://github.com/apache/spark/pull/46707#discussion_r1629813333
##########
sql/core/src/test/scala/org/apache/spark/sql/connector/DataSourceV2SQLSuite.scala:
##########
@@ -3488,6 +3488,35 @@ class DataSourceV2SQLSuiteV1Filter
}
}
+ test("SPARK-36680: Supports Dynamic Table Options for Spark SQL") {
+ val t1 = s"${catalogAndNamespace}table"
+ withTable(t1) {
+ sql(s"CREATE TABLE $t1 (id bigint, data string) USING $v2Format")
+ sql(s"INSERT INTO $t1 VALUES (1, 'a'), (2, 'b')")
+
+ var df = sql(s"SELECT * FROM $t1")
+ var collected = df.queryExecution.optimizedPlan.collect {
+ case scan: DataSourceV2ScanRelation =>
+ assert(scan.relation.options.isEmpty)
+ }
+ assert (collected.size == 1)
+ checkAnswer(df, Seq(Row(1, "a"), Row(2, "b")))
+
+ df = sql(s"SELECT * FROM $t1 WITH (`split-size` = 5)")
Review Comment:
👍 the built-in polymorphic TVF sounds like a clear use-case here. Thanks @srielau @cloud-fan for the suggestions. ➕1️⃣ for a few examples as well to help us work together :)
--
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-36680][SQL] Supports Dynamic Table Options for Spark SQL [spark]
Posted by "szehon-ho (via GitHub)" <gi...@apache.org>.
szehon-ho commented on PR #46707:
URL: https://github.com/apache/spark/pull/46707#issuecomment-2158986859
@cloud-fan i made some changes to update the PR description and change back to WITH OPTIONS as @huaxingao mentioned to me. We can look at write options subsequently.
Did we want to move forward with this, or are there some new suggestions ? Thanks
--
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-36680][SQL] Supports Dynamic Table Options for Spark SQL [spark]
Posted by "cloud-fan (via GitHub)" <gi...@apache.org>.
cloud-fan commented on PR #46707:
URL: https://github.com/apache/spark/pull/46707#issuecomment-2221863093
@shardulm94 feel free to raise PRs! I think it makes sense to support DML as the `DataFrameWriter` API can also specify options
--
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-36680][SQL] Supports Dynamic Table Options for Spark SQL [spark]
Posted by "srielau (via GitHub)" <gi...@apache.org>.
srielau commented on code in PR #46707:
URL: https://github.com/apache/spark/pull/46707#discussion_r1669539931
##########
sql/api/src/main/antlr4/org/apache/spark/sql/catalyst/parser/SqlBaseParser.g4:
##########
@@ -823,13 +823,17 @@ identifierComment
relationPrimary
: identifierReference temporalClause?
- sample? tableAlias #tableName
+ sample? optionsClause? tableAlias #tableName
| LEFT_PAREN query RIGHT_PAREN sample? tableAlias #aliasedQuery
| LEFT_PAREN relation RIGHT_PAREN sample? tableAlias #aliasedRelation
| inlineTable #inlineTableDefault2
| functionTable #tableValuedFunction
;
+optionsClause
+ : WITH OPTIONS options=propertyList
Review Comment:
There is a discontinuity in the comments.... why did it flip flop? I'm also in favor of just WITH.
--
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-36680][SQL] Supports Dynamic Table Options for Spark SQL [spark]
Posted by "szehon-ho (via GitHub)" <gi...@apache.org>.
szehon-ho commented on PR #46707:
URL: https://github.com/apache/spark/pull/46707#issuecomment-2227104644
Yea its a good point, I would like WITH READ and WITH WRITE options as its most clear, but havent found SQL that looks like this, curious what everyone thinks? In the worst case, data source can distinguish, like you said.
That reminds me that maybe we need this in the merge/update apis as well.
--
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-36680][SQL] Supports Dynamic Table Options for Spark SQL [spark]
Posted by "dtenedor (via GitHub)" <gi...@apache.org>.
dtenedor commented on code in PR #46707:
URL: https://github.com/apache/spark/pull/46707#discussion_r1628744481
##########
sql/core/src/test/scala/org/apache/spark/sql/connector/DataSourceV2SQLSuite.scala:
##########
@@ -3488,6 +3488,35 @@ class DataSourceV2SQLSuiteV1Filter
}
}
+ test("SPARK-36680: Supports Dynamic Table Options for Spark SQL") {
+ val t1 = s"${catalogAndNamespace}table"
+ withTable(t1) {
+ sql(s"CREATE TABLE $t1 (id bigint, data string) USING $v2Format")
+ sql(s"INSERT INTO $t1 VALUES (1, 'a'), (2, 'b')")
+
+ var df = sql(s"SELECT * FROM $t1")
+ var collected = df.queryExecution.optimizedPlan.collect {
+ case scan: DataSourceV2ScanRelation =>
+ assert(scan.relation.options.isEmpty)
+ }
+ assert (collected.size == 1)
+ checkAnswer(df, Seq(Row(1, "a"), Row(2, "b")))
+
+ df = sql(s"SELECT * FROM $t1 WITH (`split-size` = 5)")
Review Comment:
It might be closer to the SQL standard to use TVF syntax for this purpose instead (possibly with named arguments). This example would look like:
df = sql(s"SELECT * FROM $t1(split-size => 5)")
--
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-36680][SQL] Supports Dynamic Table Options for Spark SQL [spark]
Posted by "srielau (via GitHub)" <gi...@apache.org>.
srielau commented on code in PR #46707:
URL: https://github.com/apache/spark/pull/46707#discussion_r1628771638
##########
sql/core/src/test/scala/org/apache/spark/sql/connector/DataSourceV2SQLSuite.scala:
##########
@@ -3488,6 +3488,35 @@ class DataSourceV2SQLSuiteV1Filter
}
}
+ test("SPARK-36680: Supports Dynamic Table Options for Spark SQL") {
+ val t1 = s"${catalogAndNamespace}table"
+ withTable(t1) {
+ sql(s"CREATE TABLE $t1 (id bigint, data string) USING $v2Format")
+ sql(s"INSERT INTO $t1 VALUES (1, 'a'), (2, 'b')")
+
+ var df = sql(s"SELECT * FROM $t1")
+ var collected = df.queryExecution.optimizedPlan.collect {
+ case scan: DataSourceV2ScanRelation =>
+ assert(scan.relation.options.isEmpty)
+ }
+ assert (collected.size == 1)
+ checkAnswer(df, Seq(Row(1, "a"), Row(2, "b")))
+
+ df = sql(s"SELECT * FROM $t1 WITH (`split-size` = 5)")
Review Comment:
Pure TVF tableName(named parameterlist), while elegant on one level may be rather confusing to users.
There is no more visual cue what is a TVF and what is a table , and of course we would need a tie breaker for whom to resolve for first. This adds unwanted complexity.
Adding a builtin Polymorphic TVF uses existing (and standard) language.
I would like to know some examples. Teh PR mentions federation (?).
--
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-36680][SQL] Supports Dynamic Table Options for Spark SQL [spark]
Posted by "szehon-ho (via GitHub)" <gi...@apache.org>.
szehon-ho commented on code in PR #46707:
URL: https://github.com/apache/spark/pull/46707#discussion_r1612518771
##########
sql/api/src/main/antlr4/org/apache/spark/sql/catalyst/parser/SqlBaseParser.g4:
##########
@@ -823,13 +823,17 @@ identifierComment
relationPrimary
: identifierReference temporalClause?
- sample? tableAlias #tableName
+ sample? optionsClause? tableAlias #tableName
| LEFT_PAREN query RIGHT_PAREN sample? tableAlias #aliasedQuery
| LEFT_PAREN relation RIGHT_PAREN sample? tableAlias #aliasedRelation
| inlineTable #inlineTableDefault2
| functionTable #tableValuedFunction
;
+optionsClause
+ : WITH OPTIONS options=propertyList
Review Comment:
Sounds good to me.
--
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-36680][SQL] Supports Dynamic Table Options for Spark SQL [spark]
Posted by "szehon-ho (via GitHub)" <gi...@apache.org>.
szehon-ho commented on PR #46707:
URL: https://github.com/apache/spark/pull/46707#issuecomment-2125968144
cc @huaxingao @cloud-fan could you guys take a look? Thanks @cloud-fan for the suggestion to use WITH syntax, it makes the logic much simpler.
--
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-36680][SQL] Supports Dynamic Table Options for Spark SQL [spark]
Posted by "beliefer (via GitHub)" <gi...@apache.org>.
beliefer commented on PR #46707:
URL: https://github.com/apache/spark/pull/46707#issuecomment-2144449396
What's the dynamic table option? It seems static.
--
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-36680][SQL] Supports Dynamic Table Options for Spark SQL [spark]
Posted by "huaxingao (via GitHub)" <gi...@apache.org>.
huaxingao commented on PR #46707:
URL: https://github.com/apache/spark/pull/46707#issuecomment-2145757901
@szehon-ho
Since `propertyList` is not optional any more, shall we remove the check `if (clause.options == null)` in `createUnresolvedRelation`, and also remove the changes in `error-conditions.json` and `QueryCompilationErrors`?
--
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-36680][SQL] Supports Dynamic Table Options for Spark SQL [spark]
Posted by "cloud-fan (via GitHub)" <gi...@apache.org>.
cloud-fan commented on code in PR #46707:
URL: https://github.com/apache/spark/pull/46707#discussion_r1628732028
##########
sql/core/src/test/scala/org/apache/spark/sql/connector/DataSourceV2SQLSuite.scala:
##########
@@ -3488,6 +3488,35 @@ class DataSourceV2SQLSuiteV1Filter
}
}
+ test("SPARK-36680: Supports Dynamic Table Options for Spark SQL") {
+ val t1 = s"${catalogAndNamespace}table"
+ withTable(t1) {
+ sql(s"CREATE TABLE $t1 (id bigint, data string) USING $v2Format")
+ sql(s"INSERT INTO $t1 VALUES (1, 'a'), (2, 'b')")
+
+ var df = sql(s"SELECT * FROM $t1")
+ var collected = df.queryExecution.optimizedPlan.collect {
+ case scan: DataSourceV2ScanRelation =>
+ assert(scan.relation.options.isEmpty)
+ }
+ assert (collected.size == 1)
+ checkAnswer(df, Seq(Row(1, "a"), Row(2, "b")))
+
+ df = sql(s"SELECT * FROM $t1 WITH (`split-size` = 5)")
Review Comment:
just want to brainstorm a bit here: what should be the SQL API for `spark.readStream.table`? `WITH STREAMING options`?
--
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-36680][SQL] Supports Dynamic Table Options for Spark SQL [spark]
Posted by "cloud-fan (via GitHub)" <gi...@apache.org>.
cloud-fan commented on PR #46707:
URL: https://github.com/apache/spark/pull/46707#issuecomment-2219545145
thanks, merging 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