Spark JDBC: Incorrect syntax in spark.read

user_14934181 0
Viewed 842

I am trying to read maximum id value in my table by using

  _select_sql = f"(SELECT MAX(id) FROM {tablename})"
  highest_id = spark.read.jdbc(url, table=_select_sql, properties=properties)

After executing this I am getting :

com.microsoft.sqlserver.jdbc.SQLServerException: Incorrect syntax near the keyword 'WHERE'

When I try to read all data using highest_id = spark.read.jdbc(url, table=tablename, properties=properties) evrything is fine.

Do you know where could be mistake?

Edit:

After changing to

_select_sql = f"(SELECT MAX(id) FROM {tablename}"

I am getting:

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

pyspark databricks apache-spark jdbc
3 Answers

You're using query as a table name - this will not work. You need to use the query option instead (see docs):

highest_id = spark.read.format("jdbc") \
  .option("url", jdbcUrl) \
  .option("query", f"SELECT MAX(id) FROM {tablename}") \
  .load()
Alex_Ott_186270

Spark internally creates a SQL statement like

SELECT * FROM <table>

So, if you want a statement as table parameter it has to look like this (just adding a name to the inline view):

_select_sql = f"(SELECT MAX(id) FROM {tablename}) tab"

and sparks cretated SQL will look like this and do the job:

SELECT * FROM (SELECT MAX(id) FROM mytable) tab
user_67253530

I solved this by using

_select_sql = f"(SELECT TOP(1) id FROM {tablename} ORDER BY id DESC)"

Thanks anyway to Alex Ott, It works only in way that he gave. Don t really know why because of some sources tells this should work in similar way:

df = spark.read.jdbc(url = url, table = query, properties = properties)
user_149341810
Related