Forum Discussion
Memorable MemberQUESTION::NOTEBOOK::PYSPARK::CROSS WORKSPACE::LAKEHOUSE DETAIL ACCESS
Hi Element115
I don't find an out-of-box solution to get the desired outcome you want. Usually we would tend to use Power BI REST APIs or Fabric REST APIs to get the data. As you want to use PySpark, you can use some python libraries to call these REST APIs to get the data. Here are some of my ideas:
1. List all capacities and get their capacity IDs. Filter the result according to capacity type (sku) to remain only capacities that may have Fabric items. Capacities - List Capacities - REST API (Core) | Microsoft Learn
2. Iterate all above capacities to get the list of all workspaces in those capacities. Workspaces - List Workspaces - REST API (Admin) | Microsoft Learn
3. Iterate all of above workspaces to get the list of all lakehouses in these workspaces. Items - List Lakehouses - REST API (Lakehouse) | Microsoft Learn
4. Iterate all lakehouses to get the tables in each of them. Tables - List Tables - REST API (Lakehouse) | Microsoft Learn
5. To get the size of a delta table, currently there is no such API. You might refer to the solution in this thread QUESTION::LAKEHOUSE::SQL ENDPOINT::SYSTEM VIEWS - Microsoft Fabric Community
Hope this would be helpful.
Best Regards,
Jing
If this post helps, please Accept it as Solution to help other members find it. Appreciate your Kudos!
Memorable MemberRegarding all points but #5, I'd prefer not to use a REST API. I find it clunky and not as elegant as using API calls directly from PySpark or DeltaTable. Besides, the Notebook lives inside Fabric as do the various lakehouses. So to have to go through REST https calls to get this information seems not optimal as opposed to staying within Fabric compute environment (Jupyter, PySpark, abfss, etc).
In any case, one solution inspired by point #5 I'll end up posting in that thread is to duplicate a Notebook in every workspace that has a lakehouse, run the Notebook in each workspace, populate a size monitoring lakehouse table, and use that as a data source to a new Power BI report to have a page to monitor the size of all tables across all workspaces as they grow over time.
I have tried multiple different angles to do this with PySpark and DeltaTable and every single time there's no way around the fact that the SparkSession object can only be aware of the current workspace and what's in it EVEN THOUGH I can do this:
dtable = DeltaTable.forPath(spark, "abfss://[email protected]/lakehouse_id/Tables/DimDate")
dtable_meta = dtable.detail()
dtable_col = dtable_meta.select("sizeInBytes").collect() # list
display(dtable_col[0].sizeInBytes)
So the Notebook running this code is in, say, workspace ws_0, and the abfss URI is able to access the table data from a lakehouse in another workspace, say, ws_1. The problem with this approach is when you have so many tables, you don't want to hard code all their names. As soon as a table name changes or is deleted, you have to go in your Notebook and change it accordingly, which is a brittle solution.
What I can't fathom is why we can get access to the data across workspaces using this method, but we can't get to the table metadata with the same method. Instinctively, you would think that a wildcard approach for the URI would do the trick, like so:
abfss://[email protected]/lakehouse_id/Tables/*but this does not work. So you are now forced to duplicate Notebooks.