How to override the default folder structure created in Amazon S3 when using AWS DMS to export data as CSV

To address your queries regarding customizing the folder structure and file naming when using AWS Database Migration Service (DMS) to export data to Amazon S3 in CSV format:

1. Avoiding the subfolder created based on the schema name:

Unfortunately, AWS DMS does not provide a direct way to completely remove the schema name from the folder structure. However, you can minimize its impact by using the BucketFolder setting. By specifying a BucketFolder, you can control the prefix of the path where your files are stored.

For example, if you set BucketFolder to "Inbound/Appcard/data", your files would be stored at: s3://TestBucket/Inbound/Appcard/data/public/Employee/LOAD00000001.csv

While this doesn't remove the "public" folder entirely, it does allow you to have more control over the overall structure.

2. Renaming the default file name:

AWS DMS doesn't provide a built-in option to directly rename the output files from the default LOAD00000001.csv format to a custom name like employee_details.csv. The file naming convention is part of DMS's internal processes to ensure unique and ordered file creation.

However, you have a few options to achieve a similar result:

a. Post-processing: After the DMS task completes, you could use an AWS Lambda function triggered by S3 events to rename the files.

b. Use AWS Glue: You could set up an AWS Glue job to process the data after it's written by DMS. This would allow you to not only rename the files but also perform additional transformations if needed.

c. Custom scripting: Develop a custom script that runs after the DMS task to rename the files according to your requirements.

While these solutions require additional steps beyond the initial DMS task, they provide the flexibility to achieve your desired naming convention.

Remember to thoroughly test any changes to your data migration process in a non-production environment before implementing them in your production workflow.
Sources
add column name as bucket folder in aws dms full-load task | source MySQL | target S3 | AWS re:Post
Using Amazon S3 as a source for AWS DMS - AWS Database Migration Service
S3Settings - AWS Database Migration Service