How to get LakeHouse id and Lakehouse Name in a pipeline activity?

[Nashrin]

I'm configuring the default lakehouse using %%configure magic commands as below in a notebook,

%%configure

{    

    "defaultLakehouse": {  // This overwrites the default lakehouse for current session

        "name":

            {

                "parameterName": "LH_Name",

                "defaultValue": "XYZ"

            },

        "id":

            {

                "parameterName": "LakeHouse_ID",

                "defaultValue": "xxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxx"

            },

        "workspaceId": // Add workspace ID if it's from another workspace

            {

                "parameterName": "WorkSpace_ID",

                "defaultValue": "xxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxx"

            }

    }

}

But I need to set/generate the default value to those parameters dynamically through a pipeline activity without any manual interventions.

Currently I doing it by defining parameters in the pipeline and manually assinging the values to it but I felt it's not generic when I move the pipeline from dev to stage, I need to reassign the value manually according to its current workspace. 

Any Suggestions or ideas......

[DataBard]

Are you trying to get the workspace and lakehouse IDs into a pipeline or a notebook?

• If a pipeline, the WorkspaceID for the workspace where the pipeline resides is a system variable you can populate. For a lakehouse, I haven't seen a variable, and would likely use a notebook to find it.
• If a notebook, one alternative approach from those already mentioned would be use of sempy (see code below).

The following code snippet shows how to get various info from sempy, including the Workspace and Lakehouse IDs.

import sempy
import sempy.fabric as fabric
import json
from pyspark.sql.functions import col,lit
import pandas as pd

#Can I get the lakehouse ID for a lakehouse by finding its name?
lakehouseName = 'FirstLakeHouse'

#Get basic details from the fabric library
workspaceID = fabric.get_workspace_id()
workspaceName = fabric.resolve_workspace_name(workspaceID)

#Call the Fabric REST API, specifically looking at the lakehouse API
client = fabric.FabricRestClient()
response = client.get(f"/v1/workspaces/{workspaceID}/lakehouses")
responseJson = response.json()
items= pd.json_normalize(responseJson['value'], sep='_')

#Create a dataframe and start looking for our values
df = spark.createDataFrame(items)
display(df)

#This is for context, showing some of the various properties we can get about lakehouses
result_df = df.select(lit(workspaceName).alias("WorkSpace"),
                      col("id").alias("LakehouseId"),
                      col("displayName").alias("Name"),
                      col("type").alias("Type"),
                      col("description").alias("Description"),
                      col("properties_sqlEndpointProperties_connectionString").alias("ConnectionString"),
                      col("properties_oneLakeTablesPath").alias("OneLakeTablePath"),
                      col("properties_oneLakeFilesPath").alias("OneLakeFilePath"))

#Filter to the record we want
result_df = result_df.filter(result_df["Name"] == lakehouseName)
display(result_df)

#Return Lakehouse ID of the desired lakehouse
result_lakehouseid = result_df.select(col("LakehouseId"))
display(result_lakehouseid)

Once you've found a suitable answer, be sure to select it so others in the community can learn from it!

[okana]

Get details in a notebook and return them to data pipeline

lakehouse  = mssparkutils.lakehouse.get(LakehouseName)
mssparkutils.notebook.exit(lakehouse)​

set parameters from notebook output

@Json(activity('Nb_LakehouseDetails').output.result.exitvalue).id

Call the main notebook with parameters that works with Lakehouse. The first cell must be %%configure

%%configure -f
{
  "defaultLakehouse": {
    "name": 
     { 
        "parameterName": "lakehouseName", 
        "defaultValue": "NexusSourcingLakehouse" 
    },
    "id":{ 
        "parameterName": "lakehouseId", 
        "defaultValue": "" 
    },
    "workspaceId":
    { 
        "parameterName": "workspaceId", 
        "defaultValue": ""
    }, 
  }
}

[T-Rez]

I have this same question, is there a good way to use some kind of .utils function to retrieve the lakehouse name or guid?  (notebook or pipeline is fine for me)

[Anonymous]

Is there already an answer to this question? 

[v-cboorla-msft]

Hi @Nashrin 

Thanks for using Microsoft Fabric Community.

At this time, we are reaching out to the internal team to get some help on this.
We will update you once we hear back from them.
Appreciate your patience.

Thanks.

[okana]

Hello,
I had the same issue and fixed it with a workaround, hope it helps. 

1. Read workspace details with a notebook and exit the notebook with details

LakehouseName="NexusSourcingLakehouse"
lakehouse  = mssparkutils.lakehouse.get(LakehouseName)
mssparkutils.notebook.exit(lakehouse)

2. Read exit value (lakehouse details) of the notebook and set variables

@Json(activity('Nb_LakehouseDetails').output.result.exitvalue).id

@json(activity('Nb_LakehouseDetails').output.result.exitvalue).workspaceId

3. Pass variables to the other notebook as parameters

4. Use parameters to %%configure workspace in the second notebook. This must be the first cell in your notebook

%%configure -f
{
  "defaultLakehouse": {
    "name": 
     { 
        "parameterName": "lakehouseName", 
        "defaultValue": "" 
    },
    "id":{ 
        "parameterName": "lakehouseId", 
        "defaultValue": "" 
    },
    "workspaceId":
    { 
        "parameterName": "workspaceId", 
        "defaultValue": ""
    }, 
  }
}

[v-cboorla-msft]

Hi @Nashrin 

Apologies for the inconvenience and the delay in the response from my end.

Please reach out to our support team to gain deeper insights and explore potential solutions. It's highly recommended that you reach out to our support team. Their expertise will be invaluable in suggesting the most appropriate approach.

Please go ahead and raise a support ticket to reach our support team:

https://support.fabric.microsoft.com/support

After creating a Support ticket please provide the ticket number as it would help us to track for more information.

Thank you.

[Nashrin]

Ticket Number: 2405070050000197

[v-cboorla-msft]

Hi @Nashrin 

Thank you for sharing the details.