cancel
Showing results for 
Search instead for 
Did you mean: 
Data Engineering
Join discussions on data engineering best practices, architectures, and optimization strategies within the Databricks Community. Exchange insights and solutions with fellow data engineers.
cancel
Showing results for 
Search instead for 
Did you mean: 

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

johngabbradley
New Contributor II

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?
2 REPLIES 2

Alberto_Umana
Databricks Employee
Databricks Employee

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)

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

Connect with Databricks Users in Your Area

Join a Regional User Group to connect with local Databricks users. Events will be happening in your city, and you won’t want to miss the chance to attend and share knowledge.

If there isn’t a group near you, start one and help create a community that brings people together.

Request a New Group