0

I am currently struggling with this piece of code using spark:

spark.read.format("jdbc").option("url", connectionUrl).option("driver", "com.microsoft.sqlserver.jdbc.SQLServerDriver").option("query", "SELECT Customer ID - Source FROM some_table").option("user", localusername).option("password", localpassword).load()

I tried to wrap Customer ID - Source in `s and in []. None of these works and code keeps returning the error

com.microsoft.sqlserver.jdbc.SQLServerException: Incorrect syntax near '`'

Any suggestions for using columns with spaces in the query? Renaming would be problematic as I plan to ingest xlsx into this table and there are a lot of columns. The excel files are provided by users.

Thom A
  • 88,727
  • 11
  • 45
  • 75
sergeusprecious
  • 29
  • 4
  • *"Any suggestions for using columns with spaces in the query?"* Don't use spaces in your object names, or (even better) characters that force delimit identification. – Thom A Mar 02 '23 at 11:28
  • Also see the Delimited identifiers section in the [documentation](https://learn.microsoft.com/en-us/sql/relational-databases/databases/database-identifiers?view=sql-server-ver16#classes-of-identifiers). – Thom A Mar 02 '23 at 11:30
  • @Larnu Hi! Yes, I know about that and it works perfectly in MSSQL, for example. I am struggling with query in spark.read. Square brackets lead to the same "Incorrect Syntax" error – sergeusprecious Mar 02 '23 at 12:06
  • Using brackets (`[]`) leads the error *"Incorrect syntax near '\`'"*? Then you have a backtick (`\``) somewhere in your code. The query you've provided uses neither brackets or backticks, so that error isn't coming from the SQL query you've shown. – Thom A Mar 02 '23 at 12:07
  • @Larnu "I tried to wrap Customer ID - Source in `s and in []. None of these works and code keeps returning the error" Yes. I did mention that in my question. I tried backticks but it does not help. I guess they only work with Scala? – sergeusprecious Mar 02 '23 at 12:27
  • SQL Server doesn't use backticks for delimit identifying; as the linked documentation states. It's brackets (`[]`) or double quotes (`"`). If you are still getting an error, then demonstrate your attempt(s) in the question and the error you are getting by using the [edit] feature. – Thom A Mar 02 '23 at 12:31

0 Answers0