Spark JDBC: Incorrect syntax in spark.read-CodePudding

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

CodePudding user response：

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

CodePudding user response：

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)