how to load data from csv to mysql database in Spark?

Question

I would like to load data from csv to mySql as a batch. But I could see the tutorials/logic to insert the data from csv to hive database. Could anyone kindly help me to achieve the above integration in spark using scala?

What problems have you had doing this? Are you able to make a JDBC connection to mysql? Then you can write("jdbc") on a dataset... — OneCricketeer
– OneCricketeer, Commented Oct 27, 2017 at 5:27
And you can find much documentation... docs.databricks.com/spark/latest/data-sources/… — OneCricketeer
– OneCricketeer, Commented Oct 27, 2017 at 5:30
@cricket_007 Now I am able to have entire data from csv as dataFrame. But I am bit confused to load the same dataFrame to insert it into a mysql database. — Ashok Bala
– Ashok Bala, Commented Oct 27, 2017 at 5:42
As shown twice. You df.write into a new source. jdbc is the format method. Give it your database options — OneCricketeer
– OneCricketeer, Commented Oct 27, 2017 at 5:43

Sergey Kovalev · Accepted Answer · 2017-10-27 07:21:45Z

6

There is a reason why those tutorials don't exist. This task is very straightforward. Here is minimal working example:

val dbStr = "jdbc:mysql://[host1][:port1][,[host2][:port2]]...[/[database]]"

spark
  .read
    .format("csv")
    .option("header", "true")
    .load("some/path/to/file.csv")
  .write
    .mode("overwrite")
    .jdbc(dbStr, tablename, props)

answered Oct 27, 2017 at 7:21

Sergey Kovalev

9,4712 gold badges30 silver badges32 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

maxmithun · Accepted Answer · 2017-10-27 08:11:02Z

1

Create the dataframe reading CSV using spark session and write using the method jdbc with mysql Connection properties

val url = "jdbc:mysql://[host][:port][/[database]]"
val table = "mytable"
val property = new Properties()

spark
  .read
    .csv("some/path/to/file.csv")
  .write
    .jdbc(url, table, property)

answered Oct 27, 2017 at 8:11

maxmithun

1,14710 silver badges19 bronze badges

Collectives™ on Stack Overflow

how to load data from csv to mysql database in Spark?

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related