I've been dealing with moving data between S3 and Aurora for a while. Right now I don't have much trouble truncating the table and importing all the dataset from fresh. But, I want to improve the scalability of my pipelines, so looking into the examples from aws to "Export and import data from Amazon S3 to Amazon Aurora PostgreSQL" I haven't identify any alternative that enables me to update my tables without truncating them or using staging tables.
I want to leave the staging tables as a last alternative 'cause I'm updating more than 10 tables each time, many times a day. And the burden on the DB would be the same or even more than just truncating and moving all the data from zero. Thus, I was wondering if there is a way to replicate the COPY command from RedShift just using pgsql, or any other similar option.
Right now my PROCEDURE is more or less like this:
TRUNCATE table_name;
SELECT aws_s3.table_import_from_s3('table_name', '', '(format csv)', '(bucket,folder/file.csv,s3-region)')
And on RedShift just a mere COPY is enough to update and insert like this:
COPY table_name FROM 's3://bucket/file.csv' iam_role 'arn:aws:iam:identifier:role/role_name' csv IGNOREHEADER 1;
I've already consider using another language and let the job be done by a lambda function, but I want to ask if there's another native way that I'm not taking into account.