Search code examples
pythonsqlsql-serverpysparkazure-databricks

Insert into SQL table using jdbc connection from databricks

I wanted to insert data into sql table from databricks. I have already set-up jdbc sql connection in databricks. I am using below query but its giving me parse exception

%sql

query=insert into table company(name,location) values('abc','xyz')
df=spark.write.jdbc(url=url,table=query,properties=properties)

display(df)

Note-url and properties are already set-up during SQL JDBC connection in databricks

Solution

  • Please follow the below code:

    Create a data frame with sample values and insert the values into the SQL server with connection properties.

    Code

    Server_name = "<server_name>.database.windows.net"
Database = "<database>"
Port = "1433"
user_name = "<your_user_name>"
Password = "<Your_password>"
jdbc_Url = "jdbc:sqlserver://{0}:{1};database={2}".format(Server_name, Port,Database)
conProp = {
  "user" : user_name,
  "password" : Password,
  "driver" : "com.microsoft.sqlserver.jdbc.SQLServerDriver"
}

#Write
df=spark.createDataFrame([(1, "gopal","sol"),(2,"sam","chennai")],["PersonID", "LastName","City"])
df.write.jdbc(url=jdbc_Url,table="Persons",mode="overwrite",properties=conProp)

    enter image description here

    Output:

    enter image description here

    For more information refer this MS Document.