Spark JDBC: Incorrect syntax in spark.read

Question

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'.

Answer 1

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()