Re: Using spark.read.json with a {} literal in my ...

johngabbradley · ‎01-22-2025

I am pulling data from an S3 bucket using spark.read.json like this

s3_uri = "s3://snowflake-genesys/v2.outbound.campaigns.{id}/2025-01-22/00/"

df = spark.read.json(s3_uri)

My s3 url has the {id} in the file path. I have used r"s3://snowflake-genesys/v2.outbound.campaigns.{id}/2025-01-22/00/" and f"s3://snowflake-genesys/v2.outbound.campaigns.{{id}}/2025-01-22/00/".

I can get an

dbutils.fs.ls(f"s3://snowflake-genesys/v2.outbound.campaigns.{{id}}/2025-01-22/00/") to return the files for me.

I can get it to work with a wild card but that's not optimal because I have other large folders between campaigns and /2025-01-22/. This returns way to much data:

"s3://snowflake-genesys/v2.outbound.campaigns.*/2025-01-22/00/". I have other folders like this v2.outbound.campaigns.{id}.progress/2025-01-22/

How can I get spark to appropriately read the file. What am I doing wrong?

Alberto_Umana · ‎01-22-2025

Hi @johngabbradley,

Would below approach work for you?

s3_uri = "s3://snowflake-genesys/v2.outbound.campaigns.{id}/2025-01-22/00/"
files = dbutils.fs.ls(s3_uri)
file_paths = [file.path for file in files]
df = spark.read.json(file_paths)

johngabbradley · ‎01-22-2025

Thanks so much for responding. It is still bombing out:

Path does not exist: s3://snowflake-genesys/v2.outbound.campaigns.{id}/2025-01-22/00/002054-134158ad-1647-f75a-7cd9-b36910365e09.json. SQLSTATE: 42K03

Using spark.read.json with a {} literal in my path