Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Connection closed when try to use om.microsoft.sqlserver.jdbc.spark connector #247

Open
Rozenceto opened this issue Dec 8, 2023 · 4 comments

Comments

@Rozenceto
Copy link

Hi, I try to write a row from Azure Databricks (11.3 LTS (includes Apache Spark 3.3.0, Scala 2.12) ) to Azure SQL DB using com.microsoft.azure:spark-mssql-connector_2.12:1.3.0-BETA library but unfortunately receive the following error:

23/12/08 17:29:56 ERROR Executor: Exception in task 0.3 in stage 198.0 (TID ...) com.microsoft.sqlserver.jdbc.SQLServerException: The connection is closed.

Below is the part from the code where I invoke Dataframe writing. The service principle has a db_owner database role.
if "access_token" in token:
access_token = token["access_token"]
result = df.write
.format("com.microsoft.sqlserver.jdbc.spark")
.option("url", the_sql_url)
.option("dbtable", f"staging.test_table")
.option("accessToken", access_token)
.mode("overwrite")
.save()
else:
raise ValueError("Failed to obtain an access token.")

At the same time, I do not have such a problem when I use the JDBC connector.
Would anyone be able to help me or at the current moment I can only use JDBC connector?

Thank you,
Roza

@StevenM11
Copy link

StevenM11 commented Dec 14, 2023

I'm getting the same error using:
13.3 LTS (includes Apache Spark 3.4.1, Scala 2.12)
spark_mssql_connector_2_12_1_4_0_BETA.jar

I can see the table itself is created before the error with columns in it

Standard JDBC is working but processed only 2M rows in 2 hours

@RozalinaZaharieva
Copy link

RozalinaZaharieva commented Dec 14, 2023

I found the problem on my side, so please check it on yours.
The problem was between differences in data types in Databricks and SQL server and more precisely between TIMESTAMP columns in the table in Databricks (source table) and DATETIME data type for the column in SQL Server (target table). In the SQL Server, I changed the datatype to DATETIM2 and CAST() the column in Databricks ETL before writing the data to the SQL server and everything started working.
Unfortunately, the message is quite unuseful. I hope MS/Databricks to consider this and return more useful error messages in these cases.

Best Regards,
Roza

@StevenM11
Copy link

Interesting, I am going to play around at bit with the datatypes. The weird thing is that I was creating a new table without specifying any datatypes. Probably should because with the standard JDBC all strings become nvarchar(max)

Just creating a new table using this:

df.write
.format("com.microsoft.sqlserver.jdbc.spark")
.mode("overwrite")
.option("url", url)
.option("dbtable", table_name)
.option("user", username)
.option("password", password)
.save()

@franko14
Copy link

franko14 commented Feb 22, 2024

For anyone interested, we faced similar issue recently and the solution is to recast the datatypes on PySpark DataFrame. It looks like that for some reason, the write to SQL works more reliable with strong casting. We added this step before every write in ETL pipelines.

for column in df.schema:
    df = df.withColumn(column.name, col(column.name).cast(column.dataType))

If you use .mode(“overwrite”), you will probably not see any issue because this will drop and recreate the table in SQL with datatypes inferred from the DataFrame. However, if you want to use the table with predefined datatypes without actually dropping the table before write (with .mode(“overwrite”) alongside with .option(“truncate”, True)), I strongly suggest recasting with the code snippet above.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
None yet
Projects
None yet
Development

No branches or pull requests

4 participants