Thank you.
My main purpose is pass "MaxDop 1" to MSSQL to control the CPU usage. From the
offical doc, I guess the problem of my codes is spark wrap the query to
select * from (SELECT TOP 10 * FROM dbo.Demo with (nolock) WHERE Id = 1 option
(maxdop 1)) spark_gen_alias
Apparently, this valilate MSSQL syntax, because "option (maxdop 1)" is not
placed at the end.
May I know, how spark wrap the query if I use prepareQuery?
I do not have spark 3.4+ env now, so did not got a chance to try this option.
At 2024-04-24 20:51:45, "Appel, Kevin" wrote:
You might be able to leverage the prepareQuery option, that is at
https://spark.apache.org/docs/3.5.1/sql-data-sources-jdbc.html#data-source-option
… this was introduced in Spark 3.4.0 to handle temp table query and CTE query
against MSSQL server since what you send in is not actually what gets sent,
there is some items that get wrapped.
There is more of the technical info in
https://issues.apache.org/jira/browse/SPARK-37259 with the PR’s linked that had
the fix done for this
From: Elite
Sent: Tuesday, April 23, 2024 10:28 PM
To: user@spark.apache.org
Subject: How to add MaxDOP option in spark mssql JDBC
[QUESTION] How to pass MAXDOP option · Issue #2395 · microsoft/mssql-jdbc
(github.com)
Hi team,
I am suggested to require help form spark community.
We suspect spark rewerite the query before pass to ms sql, and it lead to
syntax error.
Is there any work around to let make my codes work?
spark.read()
.format("jdbc")
.option("driver","com.microsoft.sqlserver.jdbc.SQLServerDriver")
.option("url", "jdbc:sqlserver://xxx.database.windows.net;databaseName=")
.option("query", "SELECT TOP 10 * FROM dbo.Demo with (nolock) WHERE Id = 1
option (maxdop 1)")
.load()
.show();
com.microsoft.sqlserver.jdbc.SQLServerException: Incorrect syntax near the
keyword 'option'.
at
com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:270)
at
com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(SQLServerStatement.java:1778)
at
com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.doExecutePreparedStatement(SQLServerPreparedStatement.java:697)
at
com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement$PrepStmtExecCmd.doExecute(SQLServerPreparedStatement.java:616)
at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:7775)
at
com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:4397)
at
com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(SQLServerStatement.java:293)
at
com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(SQLServerStatement.java:263)
at
com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.executeQuery(SQLServerPreparedStatement.java:531)
at
org.apache.spark.sql.execution.datasources.jdbc.JDBCRDD$.resolveTable(JDBCRDD.scala:61)
at
org.apache.spark.sql.execution.datasources.jdbc.JDBCRelation$.getSchema(JDBCRelation.scala:226)
at
org.apache.spark.sql.execution.datasources.jdbc.JdbcRelationProvider.createRelation(JdbcRelationProvider.scala:35)
at
org.apache.spark.sql.execution.datasources.DataSource.resolveRelation(DataSource.scala:344)
at org.apache.spark.sql.DataFrameReader.loadV1Source(DataFrameReader.scala:297)
at
org.apache.spark.sql.DataFrameReader.$anonfun$load$2(DataFrameReader.scala:286)
at scala.Option.getOrElse(Option.scala:189)
at org.apache.spark.sql.DataFrameReader.load(DataFrameReader.scala:286)
at org.apache.spark.sql.DataFrameReader.load(DataFrameReader.scala:221)
This message, and any attachment(s), is for the intended recipient(s) only, may
contain information that is privileged, confidential and/or proprietary and
subject to important terms and conditions available at
http://www.bankofamerica.com/electronic-disclaimer. If you are not the intended
recipient, please delete this message. For more information about how Bank of
America protects your privacy, including specific rights that may apply, please
visit the following pages:
https://business.bofa.com/en-us/content/global-privacy-notices.html (which
includes global privacy notices) and
https://www.bankofamerica.com/security-center/privacy-overview/ (which includes
US State specific privacy notices such as the
http://www.bankofamerica.com/ccpa-notice).