Forum Discussion

Element115's avatar
Element115
Icon for Memorable Member rankMemorable Member
1 year ago

QUESTION::NOTEBOOK::PYSPARK::CROSS WORKSPACE::LAKEHOUSE DETAIL ACCESS

What is the elegant, fast, and easy way with PySpark to iterate over all exisiting workspaces, list all lakehouses, and finally get the metadata of all lakehouse tables such as size with the aim to d...
Data Integration
Anonymous's avatar
Anonymous
Not applicable
1 year ago

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!

Element115's avatar
Element115
Icon for Memorable Member rankMemorable Member
1 year ago

Another way to get all your lakehouse IDs across all your workspaces is to use mssparkutils like so:

 

from pyspark.sql import SparkSession
from notebookutils import mssparkutils

# Initialize Spark Session
spark = SparkSession.builder.appName("MultiLakehouseAccess").getOrCreate()

# List of lakehouses in other workspaces to access
workspaces = ['ECOCOUNT', 'IVDS', 'RWS']
workspace_IDs = {
    'ECOCOUNT':'47dc360e-87b5-46de-9415-c5c54e81687a',
    'IVDS':'7e3d0ca3-a8f6-4483-afe9-45978ffa3c6d',
    'RWS':'142e0ce2-c040-436d-8eb8-58aea822ed45'
}

for key in workspace_IDs:
    display(mssparkutils.lakehouse.list(workspace_IDs[key]))

 

But the problem then remains, how do you get DeltaTable.detail() to work?

 

The problem is that 

    deltaTable = DeltaTable.forName(spark, tableName)
    # Get the information of the table as a DataFrame
    table_size_df = deltaTable.detail()

the spark param is a SparkSession object that is only aware of the current worksapce context, meaning it only sees all the artifacts inside the workspace in which the Notebook is saved. So even though you can list all your lakehouse IDs with the previous script, it doesn't help you because you can't get the SparkSession object to see the metadata of these tables with the DeltaTable.detail() method.