I have a large (5-10 GB) binary file on AWS S3 that will require custom parsing, probably in python. It is essentially a sequential set of millions of dataframes, all having the same structure. What is the best way for me to get this data into a severless/hosted AWS Aurora PostgreSQL instance? So far I have thought of: 1. I could write to a CSV file and use COPY, but the size would be astronomical 2. I could send it over the wire in batches of rows 3. use AWS Glue, though I'm still learning about that.
Not something I would recommend as a general solution, but I wrote a similar thing that would convert data on the fly and write them out using the wire format (e.g. the same format that
COPY uses). It was in Java and used the internal
PGWriter class, so you'd need to find a way to do the same thing in Python.
It's incredibly fast though, over a magnitude faster than inserting with batches. Although I'm not sure whether rewritebatchedinserts would have made normal batch insertion fast enough.