How to write spark DataFrames to Postgres DB

Question

I use Spark 1.3.0 Let's say I have a dataframe in Spark and I need to store this to Postgres DB (postgresql-9.2.18-1-linux-x64) on a 64bit ubuntu machine. I also use postgresql9.2jdbc41.jar as a driver to connect to postgres

I was able to read data from postgres DB using the below commands

import org.postgresql.Driver val url="jdbc:postgresql://localhost/postgres?user=user&password=pwd" val driver = "org.postgresql.Driver" val users = { sqlContext.load("jdbc", Map( "url" -> url, "driver" -> driver, "dbtable" -> "cdimemployee", "partitionColumn" -> "intempdimkey", "lowerBound" -> "0", "upperBound" -> "500", "numPartitions" -> "50" )) } val get_all_emp = users.select("*") val empDF = get_all_emp.toDF get_all_emp.foreach(println)

I want to write this DF back to postgres after some processing. Is this below code right?

empDF.write.jdbc("jdbc:postgresql://localhost/postgres", "test", Map("user" -> "user", "password" -> "pwd"))

Any pointers(scala) would be helpful.

Have you at least tested your code to ask if is it right or not ? — eliasah
– eliasah, Commented Sep 1, 2016 at 18:39
Hi @eliasah The error I'm getting is below error: value write is not a member of org.apache.spark.sql.DataFrame — Vishnu Jayanand
– Vishnu Jayanand, Commented Sep 2, 2016 at 5:33

giaosudau · Accepted Answer · 2016-09-01 15:08:40Z

3

You should follow the code below.

val database = jobConfig.getString("database") val url: String = s"jdbc:postgresql://localhost/$database" val tableName: String = jobConfig.getString("tableName") val user: String = jobConfig.getString("user") val password: String = jobConfig.getString("password") val sql = jobConfig.getString("sql") val df = sc.sql(sql) val properties = new Properties() properties.setProperty("user", user) properties.setProperty("password", password) properties.put("driver", "org.postgresql.Driver") df.write.mode(SaveMode.Overwrite).jdbc(url, tableName, properties)

answered Sep 1, 2016 at 15:08

giaosudau

2,2616 gold badges34 silver badges65 bronze badges

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

5 Comments

eliasah Over a year ago

Maybe this is not the best explained answer ever but at least for the one who has down-voted, some comments on why it's been down voted is also good.

eliasah Over a year ago

@VishnuJayanand I didn't say that you down-voted it. I'm just asking the one who did to give a reason.

giaosudau Over a year ago

@eliasah Sorry for no explanation but this code is what I did before I don't remember correctly but I sure that using Map instead of Properties doesn't work with Scala. So that why I give the code what I already did and succeed.

Vishnu Jayanand Over a year ago

I am getting this error when I execute the first line of code error: not found: value jobConfig @giaosudau

giaosudau Over a year ago

@VishnuJayanand jobConfig just a instance of Config you can replace it by variable you use for db, password, user ...

Collectives™ on Stack Overflow

How to write spark DataFrames to Postgres DB

1 Answer 1

5 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Related