Scenario:
We have a source SQL database table which gets updated every 24 hours. I am designing an automated process which would export that table to CSV file to an EC2 instance after the update of the source DB happens.
Problem: I am trying to figure out what would be the best way to load a CSV file containing DB records from a table exported with bcp command-line utility to Aurora Serverless PostgreSQL database.
My current plan is to generate a bunch of insert statements from that CSV file using a script
Then use the AWS CLI on the EC2 Linux instance to talk to the Aurora DB and execute the following:
// empty the table
AWS rds-data execute-statement --transaction-id $ID --database users --sql "delete from mytable"
Use the Data API feature of Aurora Serverless to run a transaction such as:
$ $ID=`aws rds-data begin-transaction --database users --output json | jq .transactionId`
// populate the table with latest data
$ aws rds-data execute-statement --transaction-id $ID --database users --sql "insert into mytable values (value1,value2)"
$ aws rds-data execute-statement --transaction-id $ID --database users --sql "insert into mytable values (value1,value2)"
$ ...
$ aws rds-data commit-transaction $ID
Is there a better way to load that CSV file to the Aurora DB? Or I should stick with the above solution.
Note:
I found that article on AWS docs - "Loading data into an Amazon Aurora MySQL DB cluster from text files in an Amazon S3 bucket" but it explicitly states that This feature currently isn't available for Aurora Serverless clusters.