I have a S3 folder with 40k++ json file where each of the file has the following format:

[{"AAA": "XXXX", "BBB": "XXXX", "CCC": "XXXX"}]

Tried to read each json files through boto3 with something like 'obj.get()['Body'].read()' (in python), however, the iteration over all the files took me few hours to run.

My purpose is to read these json files (in one S3 folder), combine them into one structured table perhaps to perform some transformation of the data and then load them into a MySQL table. And this process will probably be needed to be run on weekly basis.

Any quicker way for doing ETL on this kind of data source? Would appreciate if you have any feasible recommendation. Thanks a lot!

  • Is Python a requirement? Or is another tool OK if it is faster? – Nicolas Raoul Jul 23 at 10:28
  • You could do it like this guy stackoverflow.com/questions/46407770/…, importing into SQLite, but then exporting from there into mysql. – knb Jul 23 at 11:02
  • @NicolasRaoul Thanks for your reply! Not necessarily to be Python, I'm open with other options as well. Let me know if you have any on mind. Appreciate it! – ahboy Jul 23 at 12:31
  • @knb Thanks for the idea. Will try first! – ahboy Jul 23 at 12:46
  • @knb: My feeling is that the bottleneck is file reading rather than database insertion, though. Also, inserting into SQLite is generally slower than inserting into MySQL. – Nicolas Raoul Jul 23 at 15:20

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.