Create and write to a database JDBC PySpark

user_14416346 0
Viewed 2435

I have a dataframe that I wish to write to a database table, however with the command:

df.select("id", "scale", "mentions")\
        .write.format("jdbc") \
        .option("url", "jdbc:postgresql://ec2xxxxamazonaws.com:xxxx/xxxx") \
        .option("dbtable", 'table) \
        .option("user", "xxxx") \
        .option("password", "xxxx") \
        .option("driver", "org.postgresql.Driver").mode('append').save()

I am not able to write to the database because the table already exists since I created it via psql on DB EC2 instance.

My question is, is there a way to create a table, insert queries in the spark python program itself?

pyspark jdbc postgresql
1 Answers

As far as I know, you can simply use the save mode of ‘append’, in order to insert a data frame into a pre-existing table on PostgreSQL.

Try the below:

df.write.format('jdbc').options(
  url='jdbc:postgresql://ec2xxxxamazonaws.com:xxxx/xxxx',
  driver='org.postgresql.Driver',
  dbtable='table',
  user='xxxx',
  password='xxxx').mode('append').save()

However, keep in mind this only works if the table has no constraints (I.e. primary key columns or indexes). So generally there are better implementation options for when your table and insert operation contains more complexity. Try this article for a starter: https://medium.com/@radek.strnad/tips-for-using-jdbc-in-apache-spark-sql-396ea7b2e3d3

user_92847050
Related