I am trying to write data from S3 to BigQuery using Glue, this is working perfectly fine -** have used the Glue BQ connector from Marketplace**. I am able to write the data to the table at BQ but when trying to use prequery/postquery to run a delete SQL query on BQ table, it does not run. How can I perform the deletion that I need to run on the table at BQ, is there any way for this, or am I doing any mistake while adding the prequery?
Below is the code for writing the dynamic_frame to BQ where I am adding the prequery as option:
prequery = f"""DELETE FROM fynd-1088.fynd_warehouse_pre.rds_subscriber_config_dms WHERE id in ({list_id})"""
preact = False if deletedf_count == 0 else True
writedf = (
glueContext.write_dynamic_frame.from_options(
frame=target_df,
connection_type="marketplace.spark",
connection_options={
"parentProject": "parentProject",
"table": "parentProject.dataset_name.table_name",
"temporaryGcsBucket": "bucket_name",
"connectionName": "new_bq",
"prequery": prequery,
"addPreAction": preact,
},
transformation_ctx="writedf",
)
)
Could you please share where you have read about the prequery option? I could not find any mentions in the documentation. Thank you
Here is the link to it, but seems like it is supported for Redshift as target only, https://aws.amazon.com/premiumsupport/knowledge-center/sql-commands-redshift-glue-job/ Please check this and let me know.