apache-sparkjdbcpysparkdatabricks

Spark JDBC: Incorrect syntax in spark.read

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

Solution

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