topic Re: Using spark.read.json with a {} literal in my path in Data Engineering

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

johngabbradley — Wed, 22 Jan 2025 18:42:05 GMT

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?

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

Alberto_Umana — Wed, 22 Jan 2025 20:00:45 GMT

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)

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

johngabbradley — Wed, 22 Jan 2025 20:06:52 GMT

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