Odtnhj

I have S3 where all files in different formats and from different clients are stored and new files arrive.

Files from different clients are stored under the CLIENT_ID subfolder. Inside of these subfolders files has the same format. But from folder to folder the file format may differ. For example, in folder CLIENT_1 we have CSV files separated by "," in CLIENT_2 we have CSV files separated by "|", in CLIENT_N we have JSON files and so on.

I can have thousands of such folders and I need to monitor/ETL all of them (process existing files and continuous process newly arrived files in these folders). After the ETL of these files, I want to have the normalized information in my common format and store somewhere in the database in common table.

Please advise how to properly implement this architecture with AWS and Apache Spark.

I guess I can try to implement it with Spark Streaming and the Databricks S3-SQS connector https://docs.databricks.com/spark/latest/structured-streaming/sqs.html but I don't understand where the transformation logic should be placed when using the Databricks S3-SQS connector.

Also, it is not clear or can I monitor the different S3 folders with the Databricks S3-SQS connector and provide the different spark.readStream configurations in order to be able to load the files with different schemas and file formats.

Also, is it a good idea to have thousands of different spark.readStream instances that will monitor thousands AWS S3 folders independently, like:

spark.readStream 
 .format("s3-sqs") 
 .option("fileFormat", "json") 
 .option("queueUrl", ...) 
 .schema(...) 
 .load()

Please advise. I'll highly appreciate any help on this. Thanks!