Hi,

I have input files in S3 with below structure.

/mnt/<mount_name>/test/<company_id>/sales/file_1.json

/mnt/<mount_name>/test/<company_id>/sales/file_2.json

/mnt/<mount_name>/test/<company_id>/sales/file_<n>.json

Number of companies = 15

Number of files per company = 30

Total files = 450

Each file contains nearly 180000 records

My question is what the best way to read the file and insert records to Database table.

After reading the file i need to do below operations.

1. Typecast the column

2. Derive some columns from existing columns

3. Filter bad records

4. Join with Item dataframe

5. Filter records which are not matching with item data

6.  insert to DB table

7. Write error records to one file(error file) and Write completed one file(completed file)

My Approach:

1. I read all the files in multithreading and write to one location with parquet format (if i write with delta format it takes more time to write and also in multithreading it will fail to write because before writing delta table, table should be created) - this is taking nearly 30 minutes

2. Once all the file written data to one location with parquet format, i read and start processing records(one dataframe with nearly 81,000,000 records) - this is taking several hours to process the records.