Is it possible to execute SQL against a Postgres database in AWS Glue?

user_9908952 0
Viewed 1950

I am aggregating data from S3 and writing it to Postgres using Glue. My issue is that I need to truncate the table I write to before writing it. I have found the connection_options: {"preactions":"truncate table <table_name>"} functionality but that only seems to work for Redshift. Is there any easy way, using a glue connection, to just run a simple truncate query? I have found answers that suggest using a custom jar or writing a custom java function but I was really hoping for something similar. Here are the relevant lines of code:

dfFinal = df4.drop_duplicates()
datasource2 = DynamicFrame.fromDF(dfFinal, glueContext, "scans")

output = glueContext.write_dynamic_frame.from_jdbc_conf(frame = datasource2, catalog_connection = "MPtest", connection_options = {"preactions":"truncate table scans_staging;","database" : "app", "dbtable" : "scans_staging"})
aws-glue pyspark amazon-web-services postgresql python
2 Answers

You already have a pyspark dataframe, then create a pyspark jdbc connection and use mode overwrite

conn = glueContext.extract_jdbc_conf(connection_name)
HOST_NAME = conn['host']
USERNAME = conn['user']
PASSWORD = conn['password']
PORT = conn['port']
DATABASE = “<Database name>”
URL = conn['url']+"/"+DATABASE
DRIVER = "org.postgresql.Driver"

finaldf.write.jdbc(url=URL, dbtable=table, user= USERNAME, password=PASSWORD, driver=DRIVER, mode='overwrite')
user_53527480

This is FYI:
BUT, the syntax is out of date in this article:

https://aws.amazon.com/premiumsupport/knowledge-center/sql-commands-redshift-glue-job/

So try this:

replace the syntax from

glueContext.write_dynamic_frame.from_jdbc_conf()
to
glueContext.write_dynamic_frame_from_jdbc_conf(),
and it will work.

Here is the docs for the function: https://docs.aws.amazon.com/glue/latest/dg/aws-glue-api-crawler-pyspark-extensions-glue-context.html

At least this helped me out in my case (AWS Glue job just insert data into Redshift without executing Truncate table actions.)

user_149038570
Related