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 <guoliqiang2...@126.com> 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)<https://urldefense.com/v3/__https:/github.com/microsoft/mssql-jdbc/issues/2395__;!!I2XIyG2ANlwasLbx!U_I9TWKKWI32dEJ7Kb5VA62QS1ZaqdVnWoC09pRUuq3exaScVM1g4VqYO3GvRE49OWWAidJocrUdTUdWBWBLBjAKTQ$> 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=xxxx") .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).
