You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@spark.apache.org by gu...@apache.org on 2023/04/05 00:12:25 UTC
[spark] branch branch-3.4 updated: [SPARK-43009][SQL][3.4] Parameterized `sql()` with `Any` constants
This is an automated email from the ASF dual-hosted git repository.
gurwls223 pushed a commit to branch branch-3.4
in repository https://gitbox.apache.org/repos/asf/spark.git
The following commit(s) were added to refs/heads/branch-3.4 by this push:
new 532d44642ff [SPARK-43009][SQL][3.4] Parameterized `sql()` with `Any` constants
532d44642ff is described below
commit 532d44642ff3888b260ba805a28ec64fb3f471f9
Author: Max Gekk <ma...@gmail.com>
AuthorDate: Wed Apr 5 09:12:08 2023 +0900
[SPARK-43009][SQL][3.4] Parameterized `sql()` with `Any` constants
### What changes were proposed in this pull request?
In the PR, I propose to change API of parameterized SQL, and replace type of argument values from `string` to `Any` in Scala/Java/Python and `Expression.Literal` in protobuf API. Language API can accept `Any` objects from which it is possible to construct literal expressions.
This is a backport of https://github.com/apache/spark/pull/40623
#### Scala/Java:
```scala
def sql(sqlText: String, args: Map[String, Any]): DataFrame
```
values of the `args` map are wrapped by the `lit()` function which leaves `Column` as is and creates a literal from other Java/Scala objects (for more details see the `Scala` tab at https://spark.apache.org/docs/latest/sql-ref-datatypes.html).
#### Python:
```python
def sql(self, sqlQuery: str, args: Optional[Dict[str, Any]] = None, **kwargs: Any) -> DataFrame:
```
Similarly to Scala/Java `sql`, Python's `sql()` accepts Python objects as values of the `args` dictionary (see more details about acceptable Python objects at https://spark.apache.org/docs/latest/sql-ref-datatypes.html). `sql()` converts dictionary values to `Column` literal expressions by `lit()`.
#### Protobuf:
```proto
message SqlCommand {
// (Required) SQL Query.
string sql = 1;
// (Optional) A map of parameter names to literal expressions.
map<string, Expression.Literal> args = 2;
}
```
For example:
```scala
scala> val sqlText = """SELECT s FROM VALUES ('Jeff /*__*/ Green'), ('E\'Twaun Moore') AS t(s) WHERE s = :player_name"""
sqlText: String = SELECT s FROM VALUES ('Jeff /*__*/ Green'), ('E\'Twaun Moore') AS t(s) WHERE s = :player_name
scala> sql(sqlText, args = Map("player_name" -> lit("E'Twaun Moore"))).show(false)
+-------------+
|s |
+-------------+
|E'Twaun Moore|
+-------------+
```
### Why are the changes needed?
The current implementation the parameterized `sql()` requires arguments as string values parsed to SQL literal expressions that causes the following issues:
1. SQL comments are skipped while parsing, so, some fragments of input might be skipped. For example, `'Europe -- Amsterdam'`. In this case, `-- Amsterdam` is excluded from the input.
2. Special chars in string values must be escaped, for instance `'E\'Twaun Moore'`
### Does this PR introduce _any_ user-facing change?
No since the parameterized SQL feature https://github.com/apache/spark/pull/38864 hasn't been released yet.
### How was this patch tested?
By running the affected tests:
```
$ build/sbt "test:testOnly *ParametersSuite"
$ python/run-tests --parallelism=1 --testnames 'pyspark.sql.tests.connect.test_connect_basic SparkConnectBasicTests.test_sql_with_args'
$ python/run-tests --parallelism=1 --testnames 'pyspark.sql.session SparkSession.sql'
```
Authored-by: Max Gekk <max.gekkgmail.com>
(cherry picked from commit 156a12ec0abba8362658a58e00179a0b80f663f2)
Closes #40666 from MaxGekk/parameterized-sql-any-3.4-2.
Authored-by: Max Gekk <ma...@gmail.com>
Signed-off-by: Hyukjin Kwon <gu...@apache.org>
---
.../scala/org/apache/spark/sql/SparkSession.scala | 29 ++-
.../src/main/protobuf/spark/connect/commands.proto | 7 +-
.../main/protobuf/spark/connect/relations.proto | 7 +-
.../sql/connect/planner/SparkConnectPlanner.scala | 10 +-
python/pyspark/sql/connect/plan.py | 9 +-
python/pyspark/sql/connect/proto/commands_pb2.py | 50 ++---
python/pyspark/sql/connect/proto/commands_pb2.pyi | 25 ++-
python/pyspark/sql/connect/proto/relations_pb2.py | 226 ++++++++++-----------
python/pyspark/sql/connect/proto/relations_pb2.pyi | 25 ++-
python/pyspark/sql/connect/session.py | 2 +-
python/pyspark/sql/session.py | 20 +-
.../sql/tests/connect/test_connect_basic.py | 4 +-
.../scala/org/apache/spark/sql/SparkSession.scala | 36 ++--
.../org/apache/spark/sql/ParametersSuite.scala | 76 ++++---
14 files changed, 293 insertions(+), 233 deletions(-)
diff --git a/connector/connect/client/jvm/src/main/scala/org/apache/spark/sql/SparkSession.scala b/connector/connect/client/jvm/src/main/scala/org/apache/spark/sql/SparkSession.scala
index 548545b969d..2d6781dd69c 100644
--- a/connector/connect/client/jvm/src/main/scala/org/apache/spark/sql/SparkSession.scala
+++ b/connector/connect/client/jvm/src/main/scala/org/apache/spark/sql/SparkSession.scala
@@ -34,6 +34,7 @@ import org.apache.spark.sql.catalyst.encoders.{AgnosticEncoder, RowEncoder}
import org.apache.spark.sql.catalyst.encoders.AgnosticEncoders.{BoxedLongEncoder, UnboundRowEncoder}
import org.apache.spark.sql.connect.client.{SparkConnectClient, SparkResult}
import org.apache.spark.sql.connect.client.util.{Cleaner, ConvertToArrow}
+import org.apache.spark.sql.connect.common.LiteralValueProtoConverter.toLiteralProto
import org.apache.spark.sql.types.StructType
/**
@@ -213,15 +214,16 @@ class SparkSession private[sql] (
* @param sqlText
* A SQL statement with named parameters to execute.
* @param args
- * A map of parameter names to string values that are parsed as SQL literal expressions. For
- * example, map keys: "rank", "name", "birthdate"; map values: "1", "'Steven'",
- * "DATE'2023-03-21'". The fragments of string values belonged to SQL comments are skipped
- * while parsing.
+ * A map of parameter names to Java/Scala objects that can be converted to SQL literal
+ * expressions. See <a href="https://spark.apache.org/docs/latest/sql-ref-datatypes.html">
+ * Supported Data Types</a> for supported value types in Scala/Java. For example, map keys:
+ * "rank", "name", "birthdate"; map values: 1, "Steven", LocalDate.of(2023, 4, 2). Map value
+ * can be also a `Column` of literal expression, in that case it is taken as is.
*
* @since 3.4.0
*/
@Experimental
- def sql(sqlText: String, args: Map[String, String]): DataFrame = {
+ def sql(sqlText: String, args: Map[String, Any]): DataFrame = {
sql(sqlText, args.asJava)
}
@@ -232,19 +234,24 @@ class SparkSession private[sql] (
* @param sqlText
* A SQL statement with named parameters to execute.
* @param args
- * A map of parameter names to string values that are parsed as SQL literal expressions. For
- * example, map keys: "rank", "name", "birthdate"; map values: "1", "'Steven'",
- * "DATE'2023-03-21'". The fragments of string values belonged to SQL comments are skipped
- * while parsing.
+ * A map of parameter names to Java/Scala objects that can be converted to SQL literal
+ * expressions. See <a href="https://spark.apache.org/docs/latest/sql-ref-datatypes.html">
+ * Supported Data Types</a> for supported value types in Scala/Java. For example, map keys:
+ * "rank", "name", "birthdate"; map values: 1, "Steven", LocalDate.of(2023, 4, 2). Map value
+ * can be also a `Column` of literal expression, in that case it is taken as is.
*
* @since 3.4.0
*/
@Experimental
- def sql(sqlText: String, args: java.util.Map[String, String]): DataFrame = newDataFrame {
+ def sql(sqlText: String, args: java.util.Map[String, Any]): DataFrame = newDataFrame {
builder =>
// Send the SQL once to the server and then check the output.
val cmd = newCommand(b =>
- b.setSqlCommand(proto.SqlCommand.newBuilder().setSql(sqlText).putAllArgs(args)))
+ b.setSqlCommand(
+ proto.SqlCommand
+ .newBuilder()
+ .setSql(sqlText)
+ .putAllArgs(args.asScala.mapValues(toLiteralProto).toMap.asJava)))
val plan = proto.Plan.newBuilder().setCommand(cmd)
val responseIter = client.execute(plan.build())
diff --git a/connector/connect/common/src/main/protobuf/spark/connect/commands.proto b/connector/connect/common/src/main/protobuf/spark/connect/commands.proto
index 604421fdd4f..5af6ef5bbad 100644
--- a/connector/connect/common/src/main/protobuf/spark/connect/commands.proto
+++ b/connector/connect/common/src/main/protobuf/spark/connect/commands.proto
@@ -53,11 +53,8 @@ message SqlCommand {
// (Required) SQL Query.
string sql = 1;
- // (Optional) A map of parameter names to string values that are parsed as
- // SQL literal expressions. For example, map keys: "rank", "name", "birthdate";
- // map values: "1", "'Steven'", "DATE'2023-03-21'". The fragments of string values
- // belonged to SQL comments are skipped while parsing.
- map<string, string> args = 2;
+ // (Optional) A map of parameter names to literal expressions.
+ map<string, Expression.Literal> args = 2;
}
// A command that can create DataFrame global temp view or local temp view.
diff --git a/connector/connect/common/src/main/protobuf/spark/connect/relations.proto b/connector/connect/common/src/main/protobuf/spark/connect/relations.proto
index 976bd68e7fe..68ce84f2cbe 100644
--- a/connector/connect/common/src/main/protobuf/spark/connect/relations.proto
+++ b/connector/connect/common/src/main/protobuf/spark/connect/relations.proto
@@ -108,11 +108,8 @@ message SQL {
// (Required) The SQL query.
string query = 1;
- // (Optional) A map of parameter names to string values that are parsed as
- // SQL literal expressions. For example, map keys: "rank", "name", "birthdate";
- // map values: "1", "'Steven'", "DATE'2023-03-21'". The fragments of string values
- // belonged to SQL comments are skipped while parsing.
- map<string, string> args = 2;
+ // (Optional) A map of parameter names to literal expressions.
+ map<string, Expression.Literal> args = 2;
}
// Relation that reads from a file / table or other data source. Does not have additional
diff --git a/connector/connect/server/src/main/scala/org/apache/spark/sql/connect/planner/SparkConnectPlanner.scala b/connector/connect/server/src/main/scala/org/apache/spark/sql/connect/planner/SparkConnectPlanner.scala
index 45cfbb1be49..7650532fcf9 100644
--- a/connector/connect/server/src/main/scala/org/apache/spark/sql/connect/planner/SparkConnectPlanner.scala
+++ b/connector/connect/server/src/main/scala/org/apache/spark/sql/connect/planner/SparkConnectPlanner.scala
@@ -212,11 +212,11 @@ class SparkConnectPlanner(val session: SparkSession) {
}
private def transformSql(sql: proto.SQL): LogicalPlan = {
- val args = sql.getArgsMap.asScala.toMap
+ val args = sql.getArgsMap
val parser = session.sessionState.sqlParser
val parsedPlan = parser.parsePlan(sql.getQuery)
- if (args.nonEmpty) {
- ParameterizedQuery(parsedPlan, args.mapValues(parser.parseExpression).toMap)
+ if (!args.isEmpty) {
+ ParameterizedQuery(parsedPlan, args.asScala.mapValues(transformLiteral).toMap)
} else {
parsedPlan
}
@@ -1604,7 +1604,9 @@ class SparkConnectPlanner(val session: SparkSession) {
sessionId: String,
responseObserver: StreamObserver[ExecutePlanResponse]): Unit = {
// Eagerly execute commands of the provided SQL string.
- val df = session.sql(getSqlCommand.getSql, getSqlCommand.getArgsMap)
+ val df = session.sql(
+ getSqlCommand.getSql,
+ getSqlCommand.getArgsMap.asScala.mapValues(transformLiteral).toMap)
// Check if commands have been executed.
val isCommand = df.queryExecution.commandExecuted.isInstanceOf[CommandResult]
val rows = df.logicalPlan match {
diff --git a/python/pyspark/sql/connect/plan.py b/python/pyspark/sql/connect/plan.py
index 7988cc33009..97360a969b2 100644
--- a/python/pyspark/sql/connect/plan.py
+++ b/python/pyspark/sql/connect/plan.py
@@ -945,13 +945,12 @@ class SubqueryAlias(LogicalPlan):
class SQL(LogicalPlan):
- def __init__(self, query: str, args: Optional[Dict[str, str]] = None) -> None:
+ def __init__(self, query: str, args: Optional[Dict[str, Any]] = None) -> None:
super().__init__(None)
if args is not None:
for k, v in args.items():
assert isinstance(k, str)
- assert isinstance(v, str)
self._query = query
self._args = args
@@ -962,7 +961,7 @@ class SQL(LogicalPlan):
if self._args is not None and len(self._args) > 0:
for k, v in self._args.items():
- plan.sql.args[k] = v
+ plan.sql.args[k].CopyFrom(LiteralExpression._from_value(v).to_plan(session).literal)
return plan
@@ -971,7 +970,9 @@ class SQL(LogicalPlan):
cmd.sql_command.sql = self._query
if self._args is not None and len(self._args) > 0:
for k, v in self._args.items():
- cmd.sql_command.args[k] = v
+ cmd.sql_command.args[k].CopyFrom(
+ LiteralExpression._from_value(v).to_plan(session).literal
+ )
return cmd
diff --git a/python/pyspark/sql/connect/proto/commands_pb2.py b/python/pyspark/sql/connect/proto/commands_pb2.py
index b2b8934068c..69e083a0087 100644
--- a/python/pyspark/sql/connect/proto/commands_pb2.py
+++ b/python/pyspark/sql/connect/proto/commands_pb2.py
@@ -35,7 +35,7 @@ from pyspark.sql.connect.proto import relations_pb2 as spark_dot_connect_dot_rel
DESCRIPTOR = _descriptor_pool.Default().AddSerializedFile(
- b'\n\x1cspark/connect/commands.proto\x12\rspark.connect\x1a\x19google/protobuf/any.proto\x1a\x1fspark/connect/expressions.proto\x1a\x1dspark/connect/relations.proto"\xe9\x03\n\x07\x43ommand\x12]\n\x11register_function\x18\x01 \x01(\x0b\x32..spark.connect.CommonInlineUserDefinedFunctionH\x00R\x10registerFunction\x12H\n\x0fwrite_operation\x18\x02 \x01(\x0b\x32\x1d.spark.connect.WriteOperationH\x00R\x0ewriteOperation\x12_\n\x15\x63reate_dataframe_view\x18\x03 \x01(\x0b\x32).spark.connec [...]
+ b'\n\x1cspark/connect/commands.proto\x12\rspark.connect\x1a\x19google/protobuf/any.proto\x1a\x1fspark/connect/expressions.proto\x1a\x1dspark/connect/relations.proto"\xe9\x03\n\x07\x43ommand\x12]\n\x11register_function\x18\x01 \x01(\x0b\x32..spark.connect.CommonInlineUserDefinedFunctionH\x00R\x10registerFunction\x12H\n\x0fwrite_operation\x18\x02 \x01(\x0b\x32\x1d.spark.connect.WriteOperationH\x00R\x0ewriteOperation\x12_\n\x15\x63reate_dataframe_view\x18\x03 \x01(\x0b\x32).spark.connec [...]
)
@@ -187,29 +187,29 @@ if _descriptor._USE_C_DESCRIPTORS == False:
_COMMAND._serialized_start = 139
_COMMAND._serialized_end = 628
_SQLCOMMAND._serialized_start = 631
- _SQLCOMMAND._serialized_end = 775
+ _SQLCOMMAND._serialized_end = 810
_SQLCOMMAND_ARGSENTRY._serialized_start = 720
- _SQLCOMMAND_ARGSENTRY._serialized_end = 775
- _CREATEDATAFRAMEVIEWCOMMAND._serialized_start = 778
- _CREATEDATAFRAMEVIEWCOMMAND._serialized_end = 928
- _WRITEOPERATION._serialized_start = 931
- _WRITEOPERATION._serialized_end = 1982
- _WRITEOPERATION_OPTIONSENTRY._serialized_start = 1406
- _WRITEOPERATION_OPTIONSENTRY._serialized_end = 1464
- _WRITEOPERATION_SAVETABLE._serialized_start = 1467
- _WRITEOPERATION_SAVETABLE._serialized_end = 1725
- _WRITEOPERATION_SAVETABLE_TABLESAVEMETHOD._serialized_start = 1601
- _WRITEOPERATION_SAVETABLE_TABLESAVEMETHOD._serialized_end = 1725
- _WRITEOPERATION_BUCKETBY._serialized_start = 1727
- _WRITEOPERATION_BUCKETBY._serialized_end = 1818
- _WRITEOPERATION_SAVEMODE._serialized_start = 1821
- _WRITEOPERATION_SAVEMODE._serialized_end = 1958
- _WRITEOPERATIONV2._serialized_start = 1985
- _WRITEOPERATIONV2._serialized_end = 2798
- _WRITEOPERATIONV2_OPTIONSENTRY._serialized_start = 1406
- _WRITEOPERATIONV2_OPTIONSENTRY._serialized_end = 1464
- _WRITEOPERATIONV2_TABLEPROPERTIESENTRY._serialized_start = 2557
- _WRITEOPERATIONV2_TABLEPROPERTIESENTRY._serialized_end = 2623
- _WRITEOPERATIONV2_MODE._serialized_start = 2626
- _WRITEOPERATIONV2_MODE._serialized_end = 2785
+ _SQLCOMMAND_ARGSENTRY._serialized_end = 810
+ _CREATEDATAFRAMEVIEWCOMMAND._serialized_start = 813
+ _CREATEDATAFRAMEVIEWCOMMAND._serialized_end = 963
+ _WRITEOPERATION._serialized_start = 966
+ _WRITEOPERATION._serialized_end = 2017
+ _WRITEOPERATION_OPTIONSENTRY._serialized_start = 1441
+ _WRITEOPERATION_OPTIONSENTRY._serialized_end = 1499
+ _WRITEOPERATION_SAVETABLE._serialized_start = 1502
+ _WRITEOPERATION_SAVETABLE._serialized_end = 1760
+ _WRITEOPERATION_SAVETABLE_TABLESAVEMETHOD._serialized_start = 1636
+ _WRITEOPERATION_SAVETABLE_TABLESAVEMETHOD._serialized_end = 1760
+ _WRITEOPERATION_BUCKETBY._serialized_start = 1762
+ _WRITEOPERATION_BUCKETBY._serialized_end = 1853
+ _WRITEOPERATION_SAVEMODE._serialized_start = 1856
+ _WRITEOPERATION_SAVEMODE._serialized_end = 1993
+ _WRITEOPERATIONV2._serialized_start = 2020
+ _WRITEOPERATIONV2._serialized_end = 2833
+ _WRITEOPERATIONV2_OPTIONSENTRY._serialized_start = 1441
+ _WRITEOPERATIONV2_OPTIONSENTRY._serialized_end = 1499
+ _WRITEOPERATIONV2_TABLEPROPERTIESENTRY._serialized_start = 2592
+ _WRITEOPERATIONV2_TABLEPROPERTIESENTRY._serialized_end = 2658
+ _WRITEOPERATIONV2_MODE._serialized_start = 2661
+ _WRITEOPERATIONV2_MODE._serialized_end = 2820
# @@protoc_insertion_point(module_scope)
diff --git a/python/pyspark/sql/connect/proto/commands_pb2.pyi b/python/pyspark/sql/connect/proto/commands_pb2.pyi
index b01745e888f..7619b76434a 100644
--- a/python/pyspark/sql/connect/proto/commands_pb2.pyi
+++ b/python/pyspark/sql/connect/proto/commands_pb2.pyi
@@ -161,13 +161,17 @@ class SqlCommand(google.protobuf.message.Message):
KEY_FIELD_NUMBER: builtins.int
VALUE_FIELD_NUMBER: builtins.int
key: builtins.str
- value: builtins.str
+ @property
+ def value(self) -> pyspark.sql.connect.proto.expressions_pb2.Expression.Literal: ...
def __init__(
self,
*,
key: builtins.str = ...,
- value: builtins.str = ...,
+ value: pyspark.sql.connect.proto.expressions_pb2.Expression.Literal | None = ...,
) -> None: ...
+ def HasField(
+ self, field_name: typing_extensions.Literal["value", b"value"]
+ ) -> builtins.bool: ...
def ClearField(
self, field_name: typing_extensions.Literal["key", b"key", "value", b"value"]
) -> None: ...
@@ -177,17 +181,20 @@ class SqlCommand(google.protobuf.message.Message):
sql: builtins.str
"""(Required) SQL Query."""
@property
- def args(self) -> google.protobuf.internal.containers.ScalarMap[builtins.str, builtins.str]:
- """(Optional) A map of parameter names to string values that are parsed as
- SQL literal expressions. For example, map keys: "rank", "name", "birthdate";
- map values: "1", "'Steven'", "DATE'2023-03-21'". The fragments of string values
- belonged to SQL comments are skipped while parsing.
- """
+ def args(
+ self,
+ ) -> google.protobuf.internal.containers.MessageMap[
+ builtins.str, pyspark.sql.connect.proto.expressions_pb2.Expression.Literal
+ ]:
+ """(Optional) A map of parameter names to literal expressions."""
def __init__(
self,
*,
sql: builtins.str = ...,
- args: collections.abc.Mapping[builtins.str, builtins.str] | None = ...,
+ args: collections.abc.Mapping[
+ builtins.str, pyspark.sql.connect.proto.expressions_pb2.Expression.Literal
+ ]
+ | None = ...,
) -> None: ...
def ClearField(
self, field_name: typing_extensions.Literal["args", b"args", "sql", b"sql"]
diff --git a/python/pyspark/sql/connect/proto/relations_pb2.py b/python/pyspark/sql/connect/proto/relations_pb2.py
index 81a0499666b..a7d46534a57 100644
--- a/python/pyspark/sql/connect/proto/relations_pb2.py
+++ b/python/pyspark/sql/connect/proto/relations_pb2.py
@@ -36,7 +36,7 @@ from pyspark.sql.connect.proto import catalog_pb2 as spark_dot_connect_dot_catal
DESCRIPTOR = _descriptor_pool.Default().AddSerializedFile(
- b'\n\x1dspark/connect/relations.proto\x12\rspark.connect\x1a\x19google/protobuf/any.proto\x1a\x1fspark/connect/expressions.proto\x1a\x19spark/connect/types.proto\x1a\x1bspark/connect/catalog.proto"\xaf\x14\n\x08Relation\x12\x35\n\x06\x63ommon\x18\x01 \x01(\x0b\x32\x1d.spark.connect.RelationCommonR\x06\x63ommon\x12)\n\x04read\x18\x02 \x01(\x0b\x32\x13.spark.connect.ReadH\x00R\x04read\x12\x32\n\x07project\x18\x03 \x01(\x0b\x32\x16.spark.connect.ProjectH\x00R\x07project\x12/\n\x06\x66il [...]
+ b'\n\x1dspark/connect/relations.proto\x12\rspark.connect\x1a\x19google/protobuf/any.proto\x1a\x1fspark/connect/expressions.proto\x1a\x19spark/connect/types.proto\x1a\x1bspark/connect/catalog.proto"\xaf\x14\n\x08Relation\x12\x35\n\x06\x63ommon\x18\x01 \x01(\x0b\x32\x1d.spark.connect.RelationCommonR\x06\x63ommon\x12)\n\x04read\x18\x02 \x01(\x0b\x32\x13.spark.connect.ReadH\x00R\x04read\x12\x32\n\x07project\x18\x03 \x01(\x0b\x32\x16.spark.connect.ProjectH\x00R\x07project\x12/\n\x06\x66il [...]
)
@@ -728,117 +728,117 @@ if _descriptor._USE_C_DESCRIPTORS == False:
_RELATIONCOMMON._serialized_start = 2785
_RELATIONCOMMON._serialized_end = 2876
_SQL._serialized_start = 2879
- _SQL._serialized_end = 3013
+ _SQL._serialized_end = 3048
_SQL_ARGSENTRY._serialized_start = 2958
- _SQL_ARGSENTRY._serialized_end = 3013
- _READ._serialized_start = 3016
- _READ._serialized_end = 3644
- _READ_NAMEDTABLE._serialized_start = 3159
- _READ_NAMEDTABLE._serialized_end = 3351
- _READ_NAMEDTABLE_OPTIONSENTRY._serialized_start = 3293
- _READ_NAMEDTABLE_OPTIONSENTRY._serialized_end = 3351
- _READ_DATASOURCE._serialized_start = 3354
- _READ_DATASOURCE._serialized_end = 3631
- _READ_DATASOURCE_OPTIONSENTRY._serialized_start = 3293
- _READ_DATASOURCE_OPTIONSENTRY._serialized_end = 3351
- _PROJECT._serialized_start = 3646
- _PROJECT._serialized_end = 3763
- _FILTER._serialized_start = 3765
- _FILTER._serialized_end = 3877
- _JOIN._serialized_start = 3880
- _JOIN._serialized_end = 4351
- _JOIN_JOINTYPE._serialized_start = 4143
- _JOIN_JOINTYPE._serialized_end = 4351
- _SETOPERATION._serialized_start = 4354
- _SETOPERATION._serialized_end = 4833
- _SETOPERATION_SETOPTYPE._serialized_start = 4670
- _SETOPERATION_SETOPTYPE._serialized_end = 4784
- _LIMIT._serialized_start = 4835
- _LIMIT._serialized_end = 4911
- _OFFSET._serialized_start = 4913
- _OFFSET._serialized_end = 4992
- _TAIL._serialized_start = 4994
- _TAIL._serialized_end = 5069
- _AGGREGATE._serialized_start = 5072
- _AGGREGATE._serialized_end = 5654
- _AGGREGATE_PIVOT._serialized_start = 5411
- _AGGREGATE_PIVOT._serialized_end = 5522
- _AGGREGATE_GROUPTYPE._serialized_start = 5525
- _AGGREGATE_GROUPTYPE._serialized_end = 5654
- _SORT._serialized_start = 5657
- _SORT._serialized_end = 5817
- _DROP._serialized_start = 5820
- _DROP._serialized_end = 5961
- _DEDUPLICATE._serialized_start = 5964
- _DEDUPLICATE._serialized_end = 6135
- _LOCALRELATION._serialized_start = 6137
- _LOCALRELATION._serialized_end = 6226
- _SAMPLE._serialized_start = 6229
- _SAMPLE._serialized_end = 6502
- _RANGE._serialized_start = 6505
- _RANGE._serialized_end = 6650
- _SUBQUERYALIAS._serialized_start = 6652
- _SUBQUERYALIAS._serialized_end = 6766
- _REPARTITION._serialized_start = 6769
- _REPARTITION._serialized_end = 6911
- _SHOWSTRING._serialized_start = 6914
- _SHOWSTRING._serialized_end = 7056
- _STATSUMMARY._serialized_start = 7058
- _STATSUMMARY._serialized_end = 7150
- _STATDESCRIBE._serialized_start = 7152
- _STATDESCRIBE._serialized_end = 7233
- _STATCROSSTAB._serialized_start = 7235
- _STATCROSSTAB._serialized_end = 7336
- _STATCOV._serialized_start = 7338
- _STATCOV._serialized_end = 7434
- _STATCORR._serialized_start = 7437
- _STATCORR._serialized_end = 7574
- _STATAPPROXQUANTILE._serialized_start = 7577
- _STATAPPROXQUANTILE._serialized_end = 7741
- _STATFREQITEMS._serialized_start = 7743
- _STATFREQITEMS._serialized_end = 7868
- _STATSAMPLEBY._serialized_start = 7871
- _STATSAMPLEBY._serialized_end = 8180
- _STATSAMPLEBY_FRACTION._serialized_start = 8072
- _STATSAMPLEBY_FRACTION._serialized_end = 8171
- _NAFILL._serialized_start = 8183
- _NAFILL._serialized_end = 8317
- _NADROP._serialized_start = 8320
- _NADROP._serialized_end = 8454
- _NAREPLACE._serialized_start = 8457
- _NAREPLACE._serialized_end = 8753
- _NAREPLACE_REPLACEMENT._serialized_start = 8612
- _NAREPLACE_REPLACEMENT._serialized_end = 8753
- _TODF._serialized_start = 8755
- _TODF._serialized_end = 8843
- _WITHCOLUMNSRENAMED._serialized_start = 8846
- _WITHCOLUMNSRENAMED._serialized_end = 9085
- _WITHCOLUMNSRENAMED_RENAMECOLUMNSMAPENTRY._serialized_start = 9018
- _WITHCOLUMNSRENAMED_RENAMECOLUMNSMAPENTRY._serialized_end = 9085
- _WITHCOLUMNS._serialized_start = 9087
- _WITHCOLUMNS._serialized_end = 9206
- _HINT._serialized_start = 9209
- _HINT._serialized_end = 9341
- _UNPIVOT._serialized_start = 9344
- _UNPIVOT._serialized_end = 9671
- _UNPIVOT_VALUES._serialized_start = 9601
- _UNPIVOT_VALUES._serialized_end = 9660
- _TOSCHEMA._serialized_start = 9673
- _TOSCHEMA._serialized_end = 9779
- _REPARTITIONBYEXPRESSION._serialized_start = 9782
- _REPARTITIONBYEXPRESSION._serialized_end = 9985
- _MAPPARTITIONS._serialized_start = 9988
- _MAPPARTITIONS._serialized_end = 10118
- _GROUPMAP._serialized_start = 10121
- _GROUPMAP._serialized_end = 10324
- _COGROUPMAP._serialized_start = 10327
- _COGROUPMAP._serialized_end = 10679
- _COLLECTMETRICS._serialized_start = 10682
- _COLLECTMETRICS._serialized_end = 10818
- _PARSE._serialized_start = 10821
- _PARSE._serialized_end = 11209
- _PARSE_OPTIONSENTRY._serialized_start = 3293
- _PARSE_OPTIONSENTRY._serialized_end = 3351
- _PARSE_PARSEFORMAT._serialized_start = 11110
- _PARSE_PARSEFORMAT._serialized_end = 11198
+ _SQL_ARGSENTRY._serialized_end = 3048
+ _READ._serialized_start = 3051
+ _READ._serialized_end = 3679
+ _READ_NAMEDTABLE._serialized_start = 3194
+ _READ_NAMEDTABLE._serialized_end = 3386
+ _READ_NAMEDTABLE_OPTIONSENTRY._serialized_start = 3328
+ _READ_NAMEDTABLE_OPTIONSENTRY._serialized_end = 3386
+ _READ_DATASOURCE._serialized_start = 3389
+ _READ_DATASOURCE._serialized_end = 3666
+ _READ_DATASOURCE_OPTIONSENTRY._serialized_start = 3328
+ _READ_DATASOURCE_OPTIONSENTRY._serialized_end = 3386
+ _PROJECT._serialized_start = 3681
+ _PROJECT._serialized_end = 3798
+ _FILTER._serialized_start = 3800
+ _FILTER._serialized_end = 3912
+ _JOIN._serialized_start = 3915
+ _JOIN._serialized_end = 4386
+ _JOIN_JOINTYPE._serialized_start = 4178
+ _JOIN_JOINTYPE._serialized_end = 4386
+ _SETOPERATION._serialized_start = 4389
+ _SETOPERATION._serialized_end = 4868
+ _SETOPERATION_SETOPTYPE._serialized_start = 4705
+ _SETOPERATION_SETOPTYPE._serialized_end = 4819
+ _LIMIT._serialized_start = 4870
+ _LIMIT._serialized_end = 4946
+ _OFFSET._serialized_start = 4948
+ _OFFSET._serialized_end = 5027
+ _TAIL._serialized_start = 5029
+ _TAIL._serialized_end = 5104
+ _AGGREGATE._serialized_start = 5107
+ _AGGREGATE._serialized_end = 5689
+ _AGGREGATE_PIVOT._serialized_start = 5446
+ _AGGREGATE_PIVOT._serialized_end = 5557
+ _AGGREGATE_GROUPTYPE._serialized_start = 5560
+ _AGGREGATE_GROUPTYPE._serialized_end = 5689
+ _SORT._serialized_start = 5692
+ _SORT._serialized_end = 5852
+ _DROP._serialized_start = 5855
+ _DROP._serialized_end = 5996
+ _DEDUPLICATE._serialized_start = 5999
+ _DEDUPLICATE._serialized_end = 6170
+ _LOCALRELATION._serialized_start = 6172
+ _LOCALRELATION._serialized_end = 6261
+ _SAMPLE._serialized_start = 6264
+ _SAMPLE._serialized_end = 6537
+ _RANGE._serialized_start = 6540
+ _RANGE._serialized_end = 6685
+ _SUBQUERYALIAS._serialized_start = 6687
+ _SUBQUERYALIAS._serialized_end = 6801
+ _REPARTITION._serialized_start = 6804
+ _REPARTITION._serialized_end = 6946
+ _SHOWSTRING._serialized_start = 6949
+ _SHOWSTRING._serialized_end = 7091
+ _STATSUMMARY._serialized_start = 7093
+ _STATSUMMARY._serialized_end = 7185
+ _STATDESCRIBE._serialized_start = 7187
+ _STATDESCRIBE._serialized_end = 7268
+ _STATCROSSTAB._serialized_start = 7270
+ _STATCROSSTAB._serialized_end = 7371
+ _STATCOV._serialized_start = 7373
+ _STATCOV._serialized_end = 7469
+ _STATCORR._serialized_start = 7472
+ _STATCORR._serialized_end = 7609
+ _STATAPPROXQUANTILE._serialized_start = 7612
+ _STATAPPROXQUANTILE._serialized_end = 7776
+ _STATFREQITEMS._serialized_start = 7778
+ _STATFREQITEMS._serialized_end = 7903
+ _STATSAMPLEBY._serialized_start = 7906
+ _STATSAMPLEBY._serialized_end = 8215
+ _STATSAMPLEBY_FRACTION._serialized_start = 8107
+ _STATSAMPLEBY_FRACTION._serialized_end = 8206
+ _NAFILL._serialized_start = 8218
+ _NAFILL._serialized_end = 8352
+ _NADROP._serialized_start = 8355
+ _NADROP._serialized_end = 8489
+ _NAREPLACE._serialized_start = 8492
+ _NAREPLACE._serialized_end = 8788
+ _NAREPLACE_REPLACEMENT._serialized_start = 8647
+ _NAREPLACE_REPLACEMENT._serialized_end = 8788
+ _TODF._serialized_start = 8790
+ _TODF._serialized_end = 8878
+ _WITHCOLUMNSRENAMED._serialized_start = 8881
+ _WITHCOLUMNSRENAMED._serialized_end = 9120
+ _WITHCOLUMNSRENAMED_RENAMECOLUMNSMAPENTRY._serialized_start = 9053
+ _WITHCOLUMNSRENAMED_RENAMECOLUMNSMAPENTRY._serialized_end = 9120
+ _WITHCOLUMNS._serialized_start = 9122
+ _WITHCOLUMNS._serialized_end = 9241
+ _HINT._serialized_start = 9244
+ _HINT._serialized_end = 9376
+ _UNPIVOT._serialized_start = 9379
+ _UNPIVOT._serialized_end = 9706
+ _UNPIVOT_VALUES._serialized_start = 9636
+ _UNPIVOT_VALUES._serialized_end = 9695
+ _TOSCHEMA._serialized_start = 9708
+ _TOSCHEMA._serialized_end = 9814
+ _REPARTITIONBYEXPRESSION._serialized_start = 9817
+ _REPARTITIONBYEXPRESSION._serialized_end = 10020
+ _MAPPARTITIONS._serialized_start = 10023
+ _MAPPARTITIONS._serialized_end = 10153
+ _GROUPMAP._serialized_start = 10156
+ _GROUPMAP._serialized_end = 10359
+ _COGROUPMAP._serialized_start = 10362
+ _COGROUPMAP._serialized_end = 10714
+ _COLLECTMETRICS._serialized_start = 10717
+ _COLLECTMETRICS._serialized_end = 10853
+ _PARSE._serialized_start = 10856
+ _PARSE._serialized_end = 11244
+ _PARSE_OPTIONSENTRY._serialized_start = 3328
+ _PARSE_OPTIONSENTRY._serialized_end = 3386
+ _PARSE_PARSEFORMAT._serialized_start = 11145
+ _PARSE_PARSEFORMAT._serialized_end = 11233
# @@protoc_insertion_point(module_scope)
diff --git a/python/pyspark/sql/connect/proto/relations_pb2.pyi b/python/pyspark/sql/connect/proto/relations_pb2.pyi
index f287a740346..4dd1954f00f 100644
--- a/python/pyspark/sql/connect/proto/relations_pb2.pyi
+++ b/python/pyspark/sql/connect/proto/relations_pb2.pyi
@@ -560,13 +560,17 @@ class SQL(google.protobuf.message.Message):
KEY_FIELD_NUMBER: builtins.int
VALUE_FIELD_NUMBER: builtins.int
key: builtins.str
- value: builtins.str
+ @property
+ def value(self) -> pyspark.sql.connect.proto.expressions_pb2.Expression.Literal: ...
def __init__(
self,
*,
key: builtins.str = ...,
- value: builtins.str = ...,
+ value: pyspark.sql.connect.proto.expressions_pb2.Expression.Literal | None = ...,
) -> None: ...
+ def HasField(
+ self, field_name: typing_extensions.Literal["value", b"value"]
+ ) -> builtins.bool: ...
def ClearField(
self, field_name: typing_extensions.Literal["key", b"key", "value", b"value"]
) -> None: ...
@@ -576,17 +580,20 @@ class SQL(google.protobuf.message.Message):
query: builtins.str
"""(Required) The SQL query."""
@property
- def args(self) -> google.protobuf.internal.containers.ScalarMap[builtins.str, builtins.str]:
- """(Optional) A map of parameter names to string values that are parsed as
- SQL literal expressions. For example, map keys: "rank", "name", "birthdate";
- map values: "1", "'Steven'", "DATE'2023-03-21'". The fragments of string values
- belonged to SQL comments are skipped while parsing.
- """
+ def args(
+ self,
+ ) -> google.protobuf.internal.containers.MessageMap[
+ builtins.str, pyspark.sql.connect.proto.expressions_pb2.Expression.Literal
+ ]:
+ """(Optional) A map of parameter names to literal expressions."""
def __init__(
self,
*,
query: builtins.str = ...,
- args: collections.abc.Mapping[builtins.str, builtins.str] | None = ...,
+ args: collections.abc.Mapping[
+ builtins.str, pyspark.sql.connect.proto.expressions_pb2.Expression.Literal
+ ]
+ | None = ...,
) -> None: ...
def ClearField(
self, field_name: typing_extensions.Literal["args", b"args", "query", b"query"]
diff --git a/python/pyspark/sql/connect/session.py b/python/pyspark/sql/connect/session.py
index b49f6df969c..2fa32a0096e 100644
--- a/python/pyspark/sql/connect/session.py
+++ b/python/pyspark/sql/connect/session.py
@@ -382,7 +382,7 @@ class SparkSession:
createDataFrame.__doc__ = PySparkSession.createDataFrame.__doc__
- def sql(self, sqlQuery: str, args: Optional[Dict[str, str]] = None) -> "DataFrame":
+ def sql(self, sqlQuery: str, args: Optional[Dict[str, Any]] = None) -> "DataFrame":
cmd = SQL(sqlQuery, args)
data, properties = self.client.execute_command(cmd.command(self._client))
if "sql_command_result" in properties:
diff --git a/python/pyspark/sql/session.py b/python/pyspark/sql/session.py
index 8818092e5b5..17a85876e28 100644
--- a/python/pyspark/sql/session.py
+++ b/python/pyspark/sql/session.py
@@ -41,8 +41,10 @@ from py4j.java_gateway import JavaObject
from pyspark import SparkConf, SparkContext
from pyspark.rdd import RDD
+from pyspark.sql.column import _to_java_column
from pyspark.sql.conf import RuntimeConfig
from pyspark.sql.dataframe import DataFrame
+from pyspark.sql.functions import lit
from pyspark.sql.pandas.conversion import SparkConversionMixin
from pyspark.sql.readwriter import DataFrameReader
from pyspark.sql.sql_formatter import SQLStringFormatter
@@ -1321,7 +1323,7 @@ class SparkSession(SparkConversionMixin):
df._schema = struct
return df
- def sql(self, sqlQuery: str, args: Optional[Dict[str, str]] = None, **kwargs: Any) -> DataFrame:
+ def sql(self, sqlQuery: str, args: Optional[Dict[str, Any]] = None, **kwargs: Any) -> DataFrame:
"""Returns a :class:`DataFrame` representing the result of the given query.
When ``kwargs`` is specified, this method formats the given string by using the Python
standard formatter. The method binds named parameters to SQL literals from `args`.
@@ -1336,10 +1338,13 @@ class SparkSession(SparkConversionMixin):
sqlQuery : str
SQL query string.
args : dict
- A dictionary of parameter names to string values that are parsed as SQL literal
- expressions. For example, dict keys: "rank", "name", "birthdate"; dict values:
- "1", "'Steven'", "DATE'2023-03-21'". The fragments of string values belonged to
- SQL comments are skipped while parsing.
+ A dictionary of parameter names to Python objects that can be converted to
+ SQL literal expressions. See
+ <a href="https://spark.apache.org/docs/latest/sql-ref-datatypes.html">
+ Supported Data Types</a> for supported value types in Python.
+ For example, dictionary keys: "rank", "name", "birthdate";
+ dictionary values: 1, "Steven", datetime.date(2023, 4, 2).
+ Map value can be also a `Column` of literal expression, in that case it is taken as is.
.. versionadded:: 3.4.0
@@ -1419,7 +1424,7 @@ class SparkSession(SparkConversionMixin):
And substitude named parameters with the `:` prefix by SQL literals.
- >>> spark.sql("SELECT * FROM {df} WHERE {df[B]} > :minB", {"minB" : "5"}, df=mydf).show()
+ >>> spark.sql("SELECT * FROM {df} WHERE {df[B]} > :minB", {"minB" : 5}, df=mydf).show()
+---+---+
| A| B|
+---+---+
@@ -1431,7 +1436,8 @@ class SparkSession(SparkConversionMixin):
if len(kwargs) > 0:
sqlQuery = formatter.format(sqlQuery, **kwargs)
try:
- return DataFrame(self._jsparkSession.sql(sqlQuery, args or {}), self)
+ litArgs = {k: _to_java_column(lit(v)) for k, v in (args or {}).items()}
+ return DataFrame(self._jsparkSession.sql(sqlQuery, litArgs), self)
finally:
if len(kwargs) > 0:
formatter.clear()
diff --git a/python/pyspark/sql/tests/connect/test_connect_basic.py b/python/pyspark/sql/tests/connect/test_connect_basic.py
index 8e3a12dc678..008b95d6f14 100644
--- a/python/pyspark/sql/tests/connect/test_connect_basic.py
+++ b/python/pyspark/sql/tests/connect/test_connect_basic.py
@@ -1177,8 +1177,8 @@ class SparkConnectBasicTests(SparkConnectSQLTestCase):
self.assertEqual(1, len(pdf.index))
def test_sql_with_args(self):
- df = self.connect.sql("SELECT * FROM range(10) WHERE id > :minId", args={"minId": "7"})
- df2 = self.spark.sql("SELECT * FROM range(10) WHERE id > :minId", args={"minId": "7"})
+ df = self.connect.sql("SELECT * FROM range(10) WHERE id > :minId", args={"minId": 7})
+ df2 = self.spark.sql("SELECT * FROM range(10) WHERE id > :minId", args={"minId": 7})
self.assert_eq(df.toPandas(), df2.toPandas())
def test_head(self):
diff --git a/sql/core/src/main/scala/org/apache/spark/sql/SparkSession.scala b/sql/core/src/main/scala/org/apache/spark/sql/SparkSession.scala
index f9411a10fa6..c595b50950b 100644
--- a/sql/core/src/main/scala/org/apache/spark/sql/SparkSession.scala
+++ b/sql/core/src/main/scala/org/apache/spark/sql/SparkSession.scala
@@ -45,6 +45,7 @@ import org.apache.spark.sql.errors.QueryCompilationErrors
import org.apache.spark.sql.execution._
import org.apache.spark.sql.execution.command.ExternalCommandExecutor
import org.apache.spark.sql.execution.datasources.{DataSource, LogicalRelation}
+import org.apache.spark.sql.functions.lit
import org.apache.spark.sql.internal._
import org.apache.spark.sql.internal.StaticSQLConf.CATALOG_IMPLEMENTATION
import org.apache.spark.sql.sources.BaseRelation
@@ -614,21 +615,24 @@ class SparkSession private(
* This API eagerly runs DDL/DML commands, but not for SELECT queries.
*
* @param sqlText A SQL statement with named parameters to execute.
- * @param args A map of parameter names to string values that are parsed as
- * SQL literal expressions. For example, map keys: "rank", "name", "birthdate";
- * map values: "1", "'Steven'", "DATE'2023-03-21'". The fragments of string values
- * belonged to SQL comments are skipped while parsing.
+ * @param args A map of parameter names to Java/Scala objects that can be converted to
+ * SQL literal expressions. See
+ * <a href="https://spark.apache.org/docs/latest/sql-ref-datatypes.html">
+ * Supported Data Types</a> for supported value types in Scala/Java.
+ * For example, map keys: "rank", "name", "birthdate";
+ * map values: 1, "Steven", LocalDate.of(2023, 4, 2).
+ * Map value can be also a `Column` of literal expression, in that case
+ * it is taken as is.
*
* @since 3.4.0
*/
@Experimental
- def sql(sqlText: String, args: Map[String, String]): DataFrame = withActive {
+ def sql(sqlText: String, args: Map[String, Any]): DataFrame = withActive {
val tracker = new QueryPlanningTracker
val plan = tracker.measurePhase(QueryPlanningTracker.PARSING) {
- val parser = sessionState.sqlParser
- val parsedPlan = parser.parsePlan(sqlText)
+ val parsedPlan = sessionState.sqlParser.parsePlan(sqlText)
if (args.nonEmpty) {
- ParameterizedQuery(parsedPlan, args.mapValues(parser.parseExpression).toMap)
+ ParameterizedQuery(parsedPlan, args.mapValues(lit(_).expr).toMap)
} else {
parsedPlan
}
@@ -642,15 +646,19 @@ class SparkSession private(
* This API eagerly runs DDL/DML commands, but not for SELECT queries.
*
* @param sqlText A SQL statement with named parameters to execute.
- * @param args A map of parameter names to string values that are parsed as
- * SQL literal expressions. For example, map keys: "rank", "name", "birthdate";
- * map values: "1", "'Steven'", "DATE'2023-03-21'". The fragments of string values
- * belonged to SQL comments are skipped while parsing.
+ * @param args A map of parameter names to Java/Scala objects that can be converted to
+ * SQL literal expressions. See
+ * <a href="https://spark.apache.org/docs/latest/sql-ref-datatypes.html">
+ * Supported Data Types</a> for supported value types in Scala/Java.
+ * For example, map keys: "rank", "name", "birthdate";
+ * map values: 1, "Steven", LocalDate.of(2023, 4, 2).
+ * Map value can be also a `Column` of literal expression, in that case
+ * it is taken as is.
*
* @since 3.4.0
*/
@Experimental
- def sql(sqlText: String, args: java.util.Map[String, String]): DataFrame = {
+ def sql(sqlText: String, args: java.util.Map[String, Any]): DataFrame = {
sql(sqlText, args.asScala.toMap)
}
@@ -660,7 +668,7 @@ class SparkSession private(
*
* @since 2.0.0
*/
- def sql(sqlText: String): DataFrame = sql(sqlText, Map.empty[String, String])
+ def sql(sqlText: String): DataFrame = sql(sqlText, Map.empty[String, Any])
/**
* Execute an arbitrary string command inside an external execution engine rather than Spark.
diff --git a/sql/core/src/test/scala/org/apache/spark/sql/ParametersSuite.scala b/sql/core/src/test/scala/org/apache/spark/sql/ParametersSuite.scala
index e6e5eb9fac4..07463049239 100644
--- a/sql/core/src/test/scala/org/apache/spark/sql/ParametersSuite.scala
+++ b/sql/core/src/test/scala/org/apache/spark/sql/ParametersSuite.scala
@@ -17,6 +17,10 @@
package org.apache.spark.sql
+import java.time.{Instant, LocalDate, LocalDateTime, ZoneId}
+
+import org.apache.spark.sql.functions.lit
+import org.apache.spark.sql.internal.SQLConf
import org.apache.spark.sql.test.SharedSparkSession
class ParametersSuite extends QueryTest with SharedSparkSession {
@@ -28,25 +32,25 @@ class ParametersSuite extends QueryTest with SharedSparkSession {
|FROM VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9) AS t(id)
|WHERE id < :constA
|""".stripMargin
- val args = Map("div" -> "3", "constA" -> "4L")
+ val args = Map("div" -> 3, "constA" -> 4L)
checkAnswer(
spark.sql(sqlText, args),
Row(0, 0) :: Row(1, 1) :: Row(2, 2) :: Row(3, 0) :: Nil)
checkAnswer(
- spark.sql("""SELECT contains('Spark \'SQL\'', :subStr)""", Map("subStr" -> "'SQL'")),
+ spark.sql("""SELECT contains('Spark \'SQL\'', :subStr)""", Map("subStr" -> "SQL")),
Row(true))
}
test("parameter binding is case sensitive") {
checkAnswer(
- spark.sql("SELECT :p, :P", Map("p" -> "1", "P" -> "2")),
+ spark.sql("SELECT :p, :P", Map("p" -> 1, "P" -> 2)),
Row(1, 2)
)
checkError(
exception = intercept[AnalysisException] {
- spark.sql("select :P", Map("p" -> "1"))
+ spark.sql("select :P", Map("p" -> 1))
},
errorClass = "UNBOUND_SQL_PARAMETER",
parameters = Map("name" -> "P"),
@@ -62,7 +66,7 @@ class ParametersSuite extends QueryTest with SharedSparkSession {
|WITH w1 AS (SELECT :p1 AS p)
|SELECT p + :p2 FROM w1
|""".stripMargin
- val args = Map("p1" -> "1", "p2" -> "2")
+ val args = Map("p1" -> 1, "p2" -> 2)
checkAnswer(
spark.sql(sqlText, args),
Row(3))
@@ -75,7 +79,7 @@ class ParametersSuite extends QueryTest with SharedSparkSession {
| (WITH w2 AS (SELECT :p1 AS p) SELECT p + :p2 AS p2 FROM w2)
|SELECT p2 + :p3 FROM w1
|""".stripMargin
- val args = Map("p1" -> "1", "p2" -> "2", "p3" -> "3")
+ val args = Map("p1" -> 1, "p2" -> 2, "p3" -> 3)
checkAnswer(
spark.sql(sqlText, args),
Row(6))
@@ -83,7 +87,7 @@ class ParametersSuite extends QueryTest with SharedSparkSession {
test("parameters in subquery expression") {
val sqlText = "SELECT (SELECT max(id) + :p1 FROM range(10)) + :p2"
- val args = Map("p1" -> "1", "p2" -> "2")
+ val args = Map("p1" -> 1, "p2" -> 2)
checkAnswer(
spark.sql(sqlText, args),
Row(12))
@@ -91,7 +95,7 @@ class ParametersSuite extends QueryTest with SharedSparkSession {
test("parameters in nested subquery expression") {
val sqlText = "SELECT (SELECT (SELECT max(id) + :p1 FROM range(10)) + :p2) + :p3"
- val args = Map("p1" -> "1", "p2" -> "2", "p3" -> "3")
+ val args = Map("p1" -> 1, "p2" -> 2, "p3" -> 3)
checkAnswer(
spark.sql(sqlText, args),
Row(15))
@@ -103,7 +107,7 @@ class ParametersSuite extends QueryTest with SharedSparkSession {
|WITH w1 AS (SELECT (SELECT max(id) + :p1 FROM range(10)) + :p2 AS p)
|SELECT p + :p3 FROM w1
|""".stripMargin
- val args = Map("p1" -> "1", "p2" -> "2", "p3" -> "3")
+ val args = Map("p1" -> 1, "p2" -> 2, "p3" -> 3)
checkAnswer(
spark.sql(sqlText, args),
Row(15))
@@ -112,14 +116,14 @@ class ParametersSuite extends QueryTest with SharedSparkSession {
test("parameters in INSERT") {
withTable("t") {
sql("CREATE TABLE t (col INT) USING json")
- spark.sql("INSERT INTO t SELECT :p", Map("p" -> "1"))
+ spark.sql("INSERT INTO t SELECT :p", Map("p" -> 1))
checkAnswer(spark.table("t"), Row(1))
}
}
test("parameters not allowed in DDL commands") {
val sqlText = "CREATE VIEW v AS SELECT :p AS p"
- val args = Map("p" -> "1")
+ val args = Map("p" -> 1)
checkError(
exception = intercept[AnalysisException] {
spark.sql(sqlText, args)
@@ -135,7 +139,7 @@ class ParametersSuite extends QueryTest with SharedSparkSession {
test("non-substituted parameters") {
checkError(
exception = intercept[AnalysisException] {
- spark.sql("select :abc, :def", Map("abc" -> "1"))
+ spark.sql("select :abc, :def", Map("abc" -> 1))
},
errorClass = "UNBOUND_SQL_PARAMETER",
parameters = Map("name" -> "def"),
@@ -155,18 +159,42 @@ class ParametersSuite extends QueryTest with SharedSparkSession {
stop = 10))
}
- test("non-literal argument of `sql()`") {
- Seq("col1 + 1", "CAST('100' AS INT)", "map('a', 1, 'b', 2)", "array(1)").foreach { arg =>
- checkError(
- exception = intercept[AnalysisException] {
- spark.sql("SELECT :param1 FROM VALUES (1) AS t(col1)", Map("param1" -> arg))
- },
- errorClass = "INVALID_SQL_ARG",
- parameters = Map("name" -> "param1"),
- context = ExpectedContext(
- fragment = arg,
- start = 0,
- stop = arg.length - 1))
+ test("literal argument of `sql()`") {
+ val sqlText =
+ """SELECT s FROM VALUES ('Jeff /*__*/ Green'), ('E\'Twaun Moore'), ('Vander Blue') AS t(s)
+ |WHERE s = :player_name""".stripMargin
+ checkAnswer(
+ spark.sql(sqlText, args = Map("player_name" -> lit("E'Twaun Moore"))),
+ Row("E'Twaun Moore") :: Nil)
+ checkAnswer(
+ spark.sql(sqlText, args = Map("player_name" -> lit("Vander Blue--comment"))),
+ Nil)
+ checkAnswer(
+ spark.sql(sqlText, args = Map("player_name" -> lit("Jeff /*__*/ Green"))),
+ Row("Jeff /*__*/ Green") :: Nil)
+
+ withSQLConf(SQLConf.DATETIME_JAVA8API_ENABLED.key -> "true") {
+ checkAnswer(
+ spark.sql(
+ sqlText = """
+ |SELECT d
+ |FROM VALUES (DATE'1970-01-01'), (DATE'2023-12-31') AS t(d)
+ |WHERE d < :currDate
+ |""".stripMargin,
+ args = Map("currDate" -> lit(LocalDate.of(2023, 4, 1)))),
+ Row(LocalDate.of(1970, 1, 1)) :: Nil)
+ checkAnswer(
+ spark.sql(
+ sqlText = """
+ |SELECT d
+ |FROM VALUES (TIMESTAMP_LTZ'1970-01-01 01:02:03 Europe/Amsterdam'),
+ | (TIMESTAMP_LTZ'2023-12-31 04:05:06 America/Los_Angeles') AS t(d)
+ |WHERE d < :currDate
+ |""".stripMargin,
+ args = Map("currDate" -> lit(Instant.parse("2023-04-01T00:00:00Z")))),
+ Row(LocalDateTime.of(1970, 1, 1, 1, 2, 3)
+ .atZone(ZoneId.of("Europe/Amsterdam"))
+ .toInstant) :: Nil)
}
}
}
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@spark.apache.org
For additional commands, e-mail: commits-help@spark.apache.org