How to integrate Apache Spark with MySQL for reading database tables as a spark dataframe? [closed]

Question

I want to run my existing application with Apache Spark and MySQL.

Answer 1

From pySpark, it work for me :

dataframe_mysql = mySqlContext.read.format("jdbc").options(     url="jdbc:mysql://localhost:3306/my_bd_name",     driver = "com.mysql.jdbc.Driver",     dbtable = "my_tablename",     user="root",     password="root").load() 

Answer 2

With spark 2.0.x,you can use DataFrameReader and DataFrameWriter. Use SparkSession.read to access DataFrameReader and use Dataset.write to access DataFrameWriter.

Suppose using spark-shell.

read example

val prop=new java.util.Properties() prop.put("user","username") prop.put("password","yourpassword") val url="jdbc:mysql://host:port/db_name"  val df=spark.read.jdbc(url,"table_name",prop)  df.show() 

read example 2

val jdbcDF = spark.read   .format("jdbc")   .option("url", "jdbc:mysql:dbserver")   .option("dbtable", "schema.tablename")   .option("user", "username")   .option("password", "password")   .load() 

from spark doc

read example3

If you want to read data from a query result rather than a table.

val sql="""select * from db.your_table where id>1""" val jdbcDF = spark.read   .format("jdbc")   .option("url", "jdbc:mysql:dbserver")   .option("dbtable",  s"( $sql ) t")   .option("user", "username")   .option("password", "password")   .load() 

write example

import org.apache.spark.sql.SaveMode  val prop=new java.util.Properties() prop.put("user","username") prop.put("password","yourpassword") val url="jdbc:mysql://host:port/db_name" //df is a dataframe contains the data which you want to write. df.write.mode(SaveMode.Append).jdbc(url,"table_name",prop) 

中文版戳我