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"})
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')
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.)