topic Why Spark Streaming from S3 is returning thousands of files when there are only 9? in Data Engineering

Why Spark Streaming from S3 is returning thousands of files when there are only 9?

CarterM — Thu, 29 Sep 2022 23:46:56 GMT

I am attempting to stream JSON endpoint responses from an s3 bucket into a spark DLT. I have been very successful in this practice previously, but the difference this time is that I am storing the responses from multiple endpoints in the same s3 bucket, whereas before it has been a single endpoint response in a single bucket.

Setting The Scene

I am making GET requests to 9 different Season Endpoints, each for a different Sports League. The endpoints return a list of Seasons for the given Sports League. I decided to store them all in the same s3 bucket because the endpoint responses are structured almost identically.

8/9 of the endpoints return a response structured like so:

Giving a League and list of Seasons

The 9th endpoint is for Soccer Seasons, which is structured slightly differently but it also returns a list of Seasons.

Key endpoint differences:

8/9 endpoints give a League object, Season.Type, and Season.Status
The 9th (Soccer) endpoint does NOT have a League object, Season.Type, nor Season.Status
- It additionally provides a Season.Competitor_Id
It is also important to note, not all 8/9 endpoint responses are exactly the same and there is some variation in the provided Season fields.

The Issue:

When Spark Streaming from the s3 bucket, it says there are thousands of JSON files when there are only 9.

Here you can see all 9 in the bucket. Notice Soccer is the largest file as well.

However, when displaying the streaming spark data frame of the s3 bucket it shows there are thousands of null soccer JSON files while also correctly displaying the other 8 sports as having only 1 JSON file.

I suspect that the soccer endpoint response file is being auto-exploded into thousands of files. This would explain why the Seasons column for soccer is null because it has already exploded and now contains other fields.

Any guidance would be appreciated and if I can be any more clear please let me know.

Thanks in advance!

Re: Why Spark Streaming from S3 is returning thousands of files when there are only 9?

CarterM — Sat, 01 Oct 2022 00:23:05 GMT

The Soccer Seasons Endpoint Response was the only one with backslashes `\` which signify a new line character. When using AutoLoader you need to specify the `multiLine` Option to indicate the JSON spans multiple lines.

.option("multiline", "true")

This caused the s3 stream to interpret each section between backslashes as a separate JSON file, resulting in Thousands of null Soccer Files and no Seasons column.

Re: Why Spark Streaming from S3 is returning thousands of files when there are only 9?

Anonymous — Mon, 03 Oct 2022 22:04:39 GMT

@Carter Mooring Thank you SO MUCH for coming back to provide a solution to your thread! Happy you were able to figure this out so quickly. And I am sure that this will help someone in the future with the same issue. 😊

Re: Why Spark Streaming from S3 is returning thousands of files when there are only 9?

szymon_dybczak — Sat, 24 Aug 2024 11:54:38 GMT

Hi @williamyoung ,

I must admit, that's creative way to sneak in spam 😄 Anyway, I marked it as an inappropriate content.