Upsert operation on PostGres or Aurora from S3 CSV file using aws_s3.table_import_from_s3
Importing tens of thousands of rows into a PostGres table from S3 is awesome and incredibly fast, but is there any way to perform Upserts using the same process without creating staging tables?
Ideally it would be nice to have the equivalent of the conflict keyword, and perform an update if the PK is already present, but is there anything simpler than a staging+merge operation? At the moment it looks like it needs:
- Create temporary table
- aws_s3.table_import_from_s3 into temporary table
- MERGE operation into main table
- Clear temporary table
and it needs this for all 20 tables we're merging... Open to any suggestions
Your current architecture sounds standard (and good!).
Part of the reason that CSV import is so fast is that it's only handling a limited set of functions. I'm pretty sure there's no ability to handle the on conflict idea there.
Thanks for the feedback.
What would be a nice simplification of the solution would be if there was a way of parsing the JSON structure in the S3 bucket directly as part of the import, as I currently have to split the JSON into individual files for each 'table' for the import process, then write a custom stored procedure to do the merge from each bucket.
First world problems...
Relevant questions
Fetch the number of rows returned/affected by a SQL query
asked 2 days agoNo Options to migrate from RDS Postgres to Aurora
asked 2 months agoHow to insert S3 data into Aurora table via glue transform?
asked 3 years agoDMS from Aurora to Redshift
Accepted Answerasked 5 years agoRDS Postgres Logical replication access to essential table denied
Accepted Answerasked 3 years ago60K updates to RDS Postgres - performance challenge
Accepted Answerasked 2 years agoCan we create a Table in Aurora during the infra setup using terraform IaaC code ?
Accepted Answerasked a month agounable to use table_import_from_s3 with any path
asked 2 years agoUpsert operation on PostGres or Aurora from S3 CSV file using aws_s3.table_import_from_s3
asked 2 months agoPhantom Update on Aurora Postgres from AWS DMS Replication from SQL Server Source
asked 3 months ago