PySpark with MySQL Connection - Example

Download mysql connector and put it here : D\Spark\mysql folder

in windows:

D:\\Spark\\mysql\\mysql-connector-java-8.0.20.jar

Start pyspark with --jars option

pyspark --jars D:\\Spark\\mysql\\mysql-connector-java-8.0.20.jar

from pyspark import SparkConf, SparkContext, sql

from pyspark.sql import SparkSession

dataframe_mysql = sqlContext.read.format("jdbc").\

option("url", "jdbc:mysql://localhost:3306/school").\

option("driver", "com.mysql.jdbc.Driver").\

option("dbtable", "student").\

option("user", "root").

option("password", "Studi0Plus").load()

dataframe_mysql.show()

+----------+---------+---------+------------+

|rollnumber|firstname| lastname|        city|

+----------+---------+---------+------------+

|         1|  Sankara|Narayanan|   Pallathur|

|         3|   Rajini|    Kanth|   Bangalore|

|         4|    Vijay|   Balaji|   Bangalore|

|         5|     Anbu|    Sudha|Melasivapuri|

|         6|     Arun|    Vijay|     Chennai|

+----------+---------+---------+------------+

df.filter("city='Bangalore' and firstname='Rajini'").show(5)

+----------+---------+--------+---------+
|rollnumber|firstname|lastname|     city|
+----------+---------+--------+---------+
|         3|   Rajini|   Kanth|Bangalore|
+----------+---------+--------+---------+

in Ubuntu:

Start Pyspark with jars option

pyspark --jars /home/hadoop/apache-hive-3.1.2-bin/lib/mysql-connector-java-8.0.20.jar

from pyspark.sql import SparkSession
sparkdriver = SparkSession.builder.master("local").appName("demoApp").\
			  config("spark.jars.packages","mysql:mysql-connector-java-8.0.20").\
			  getOrCreate()
			  
df = sparkdriver.read.format("jdbc").\
	   option("url","jdbc:mysql://localhost:3306").\
	   option("driver","com.mysql.jdbc.Driver").\
	   option("user","hadoop").\
	   option("password","p@ssw0rd").\
	   option("query","select * from school.student").\
	   load()
	   
df.show()

+---+-----+---------+
| id| name|     city|
+---+-----+---------+
|100| Raja|Pallathur|
|101|Silva|Singapore|
+---+-----+---------+