How to create a dataframe from relational database table using JDBC?

https://bigdataelearning.com/course/apache-spark-2-with-scala/
https://bigdataelearning.com/courses
https://bigdataelearning.com
Dataframe from a relational database table can be created using spark.read.jdbc API.

It takes 3 parameters.
i) Jdbcurl,
ii) dbTableName, and
iii) connectionProperties

jdbcUrl contains relational database server name. Here it is sqlserver. Then jdbcHostName & jdbcPortNumber. It is the hostname and port number of the sql server. And then semi colon database equals the actual database name. Here we need to specify the database name from where we are reading the data from.

Create the JDBC URL without passing in the user and password parameters.

Now that we have created the first parameter jdbcUrl, let’s create the connectionProperties. Connection properties is nothing but a java properties object. To create this, we need to import java.util.properties. Then create a properties object. Put the user name and password using the put method on the properties object. The key “user” contains the actual username and the key password contains the actual password.

On the properties object, using the setProperty method, let’s set the key “driver” with the actual driver class. If it is sQL server, then it should be SQL server driver. For e.g. “com.microsoft.sqlserver.jdbc.SQLServerDriver” should be the value for the key “driver”.

Now let’s use the spark.read.jdbc API to create a dataframe. For the jdbcUrl, let’s specify the jdbcUrl variable that we created. For the database table name, we can specify the actual database table name within double quotes. For the connection properties, we can specify the properties object that we created.

This will create a dataframe that will have the contents of the relational database table name.

Now that we have created dataframe from a relational database table, let’s write the contents of the dataframe back to a database table. Here I am applying df.write.jdbc API. For the table, we can specify the dataframe name.

Then for the jdbc, we should again specify the same set of parameters. The jdbcUrl and connection properties are pretty much the same. dbTableName is the name of the relational database table where we want to save the dataframe contents.

Based on the dataframe name specified, it writes the contents of the dataframe to a relational database table with the dbTableName. If the table doesn’t exist, then it writes a new table with that name.

If the table in RDBMS already exists, we can either ask it to append to the existing data or to overwrite the existing data. This can be done using “mode” parameter.