This tutorial will explain how to write data from Spark dataframe into various types of databases(such as Mysql, SingleStore, Teradata) using JDBC Connection.
df.write.format("jdbc").options(driver="com.mysql.cj.jdbc.Driver",
user="tutorial_user",
password="user_password",
url="jdbc:mysql://Mysql.dbmstutorials.com:3306?serverTimezone=UTC&useSSL=false",
dbtable="tutorial_db.spark_department"
).save()
df.write.format("jdbc") \
.option("driver","com.mysql.cj.jdbc.Driver") \
.option("user","tutorial_user") \
.option("password","user_password") \
.option("url","jdbc:mysql://Mysql.dbmstutorials.com:3306?serverTimezone=UTC&useSSL=false") \
.option("dbtable","tutorial_db.spark_department") \
.save()
df.write.mode("append").format("jdbc").options(driver="com.mysql.cj.jdbc.Driver",
user="tutorial_user",
password="user_password",
url="jdbc:mysql://Mysql.dbmstutorials.com:3306?serverTimezone=UTC&useSSL=false",
dbtable="tutorial_db.spark_department"
).save()
df.write.mode("append").format("jdbc") \
.option("driver","com.mysql.cj.jdbc.Driver") \
.option("user","tutorial_user") \
.option("password","user_password") \
.option("url","jdbc:mysql://Mysql.dbmstutorials.com:3306?serverTimezone=UTC&useSSL=false") \
.option("dbtable","tutorial_db.spark_department") \
.save()
df.write.mode("overwrite").format("jdbc").options(driver="com.mysql.cj.jdbc.Driver",
user="tutorial_user",
password="user_password",
url="jdbc:mysql://Mysql.dbmstutorials.com:3306?serverTimezone=UTC&useSSL=false",
dbtable="tutorial_db.spark_department"
).save()
df.write.mode("overwrite").format("jdbc") \
.option("driver","com.mysql.cj.jdbc.Driver") \
.option("user","tutorial_user") \
.option("password","user_password") \
.option("url","jdbc:mysql://Mysql.dbmstutorials.com:3306?serverTimezone=UTC&useSSL=false") \
.option("dbtable","tutorial_db.spark_department") \
.save()
df.write.mode("ignore").format("jdbc") \
.option("driver","com.mysql.cj.jdbc.Driver") \
.option("user","tutorial_user") \
.option("password","user_password") \
.option("url","jdbc:mysql://Mysql.dbmstutorials.com:3306?serverTimezone=UTC&useSSL=false") \
.option("dbtable","tutorial_db.spark_department") \
.save()
df.write.format("jdbc") \
.option("driver","org.mariadb.jdbc.Driver") \
.option("user","tutorial_user") \
.option("password","user_password") \
.option("url","jdbc:mysql://singlestore.dbmstutorials.com:3306/tutorial_db") \
.option("dbtable","spark_department") \
.save()
df.write.mode("append").format("jdbc") \
.option("driver","org.mariadb.jdbc.Driver") \
.option("user","tutorial_user") \
.option("password","user_password") \
.option("url","jdbc:mysql://singlestore.dbmstutorials.com:3306/tutorial_db") \
.option("dbtable","spark_department") \
.save()
df.write.mode("overwrite").format("jdbc") \
.option("driver","org.mariadb.jdbc.Driver") \
.option("user","tutorial_user") \
.option("password","user_password") \
.option("url","jdbc:mysql://singlestore.dbmstutorials.com:3306/tutorial_db") \
.option("dbtable","tutorial_db.spark_department") \
.save()
df.write.mode("ignore").format("jdbc") \
.option("driver","org.mariadb.jdbc.Driver") \
.option("user","tutorial_user") \
.option("password","user_password") \
.option("url","jdbc:mysql://singlestore.dbmstutorials.com:3306/tutorial_db") \
.option("dbtable","tutorial_db.spark_department") \
.save()
df.write.format("jdbc") \
.option("driver", "com.teradata.jdbc.TeraDriver") \
.option("user", "tutorial_user") \
.option("password", "user_password") \
.option("url", "jdbc:teradata://Teradata.dbmstutorials.com/COLUMN_NAME=ON") \
.option("dbtable", "tutorial_db.spark_department") \
.save()
df.write.mode("append").format("jdbc") \
.option("driver", "com.teradata.jdbc.TeraDriver") \
.option("user", "tutorial_user") \
.option("password", "user_password") \
.option("url", "jdbc:teradata://Teradata.dbmstutorials.com/COLUMN_NAME=ON") \
.option("dbtable", "tutorial_db.spark_department") \
.save()
df.write.mode("overwrite").format("jdbc") \
.option("driver", "com.teradata.jdbc.TeraDriver") \
.option("user", "tutorial_user") \
.option("password", "user_password") \
.option("url", "jdbc:teradata://Teradata.dbmstutorials.com/COLUMN_NAME=ON") \
.option("dbtable", "tutorial_db.spark_department") \
.save()
df.write.mode("ignore").format("jdbc") \
.option("driver", "com.teradata.jdbc.TeraDriver") \
.option("user", "tutorial_user") \
.option("password", "user_password") \
.option("url", "jdbc:teradata://Teradata.dbmstutorials.com/COLUMN_NAME=ON") \
.option("dbtable", "tutorial_db.spark_department") \
.save()
df.write.saveAsTable("retail.spark_department")
Example 1:
df.write.insertInto("retail.spark_department")
Example 2:
df.write.mode("append").saveAsTable("retail.spark_department")
df.write.mode("overwrite").saveAsTable("retail.spark_department")
df.write.mode("ignore").saveAsTable("retail.spark_department")