In Spark, when reading from SQL Server, how do you add binary parameters?

TheDuck 0
Viewed 62

I want to use Spark to read data from a table in Azure SQL. However I don't want to entire table, so I have used the "query" option so I can filter down what is needed. However, I can't find a way to pass a binary (SQL's RowVersion) parameter into the query. How can this be done?

df = spark.read.format("jdbc") \
    .option("url", "jdbc:sqlserver://serverName.database.windows.net;databaseName=databaseName") \
    .option("query", "SELECT * FROM dbo.tableName WHERE RowVersion > ?") \
    .option("accesstoken", access_token) \
    .option("driver", "com.microsoft.sqlserver.jdbc.SQLServerDriver") \
    .load()
databricks pyspark apache-spark apache-spark-sql azure
1 Answers

I reproduce the same thing in my environment and created a sample table state with row version name RV in Azure SQL.

Now I can read the row version data in Azure Databricks .please follow the below code :

Hostname = "<server_name>.database.windows.net"
Database = "<data_base_name"
Port = "1433"
username = "username"
password = "pass"
Url = "jdbc:sqlserver://{0}:{1};database={2}".format(Hostname,Port,Database)
connProp = {
  "user" : username,
  "password" : password,
  "driver" : "com.microsoft.sqlserver.jdbc.SQLServerDriver"
}
query = "(Select rv from states ) states"
df = spark.read.jdbc(url=Url, table=query, properties=connProp)
display(df)

Ref1

BhanunagasaiVamsi0
Related