Forum Discussion
T-SQL magic cell with parameters in for loop Fabric Python Notebook
Hi austworks19,
Thanks for reaching out to the Microsoft fabric community forum. You're absolutely right Fabric notebooks using spark.sql() are scoped to the default lakehouse of the Spark session, and unfortunately, you can't dynamically switch workspaces or lakehouses mid-session using Spark commands.
Your workaround of constructing the full abfss path and replacing table references with backtick-enclosed paths is spot on and probably the most flexible approach for your use case.
As for using the %%sql magic function from the blog you linked, you're also correct that it requires separate cells for each query, which doesn’t play well with loops or dynamic execution.
If you're looking to keep everything inside a loop, here’s a suggestion that continue using spark.sql() but dynamically rewrite each query to use the full abfss path. You can store the rewritten queries in a list and iterate through them, executing each with spark.sql().
This way, you avoid the cell-per-query limitation and still get access to the correct lakehouse data.
I would also take a moment to thank tackytechtom , for actively participating in the community forum and for the solutions you’ve been sharing in the community forum. Your contributions make a real difference.
If I misunderstand your needs or you still have problems on it, please feel free to let us know.
Best Regards,
Hammad.
Hi austworks19 ,
That's a cool use case!
Couldn't you do something like this? (note, this is pseudo code 🙂 )
for workspace in workspaces:
for schema in schemas:
for table in tables:
table_name = f"{workspace.name}.{schema.name}.{table.name}"
spark.sql(f"SELECT * FROM {table_name}")
Essentially, just use the full identifier of the table? If this does not work, you might wanna try out to add some spark.sql(f"USE [catalog}.{schema}").
Let me know if this helps 🙂
/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/
Hi Tackytechtom, for some reason Spark cannot access tables inside lakehouses that are not the Spark session default lakehouse. However, I did find a workaround that works for my use case: dynamically creating the abfss paths from the workspace and lakehouse ids and replacing table references with their full abfss paths in backticks in the t-sql modified for spark.sql.
