Forum Discussion

smpa01's avatar
smpa01
Icon for Community Champion rankCommunity Champion
2 years ago

Mounting NB on- the-fly

I am running some codes in a standalone notebook that is currently giving me the following error Spark SQL queries are only possible in the context of a lakehouse. Please attach a lakehouse to pro...
lakehouse
frithjof_v's avatar
frithjof_v
Icon for Community Champion rankCommunity Champion
2 years ago

This worked for me in a Notebook which has no default lakehouse. The default lakehouse gets attached programmatically in the %%configure code cell.
I hardcoded the values for lakehouseName, lakehouseID and workspaceID (as mentioned in previous comment).

 

%%configure -f
{
    "defaultLakehouse": {  
        "name": "<lakehouseName>",
        "id": "<lakehouseID>",
        "workspaceId": "<workspaceID>"
    }
}

 

 

 

%%sql
CREATE OR REPLACE TEMPORARY VIEW Dim_Product_temp_vw
AS 
SELECT *, CURRENT_TIMESTAMP AS load_timestamp 
FROM Dim_Product

 

 

 

df = spark.sql("SELECT * FROM Dim_Product_temp_vw")

df.write.mode("append").saveAsTable("Dim_Product_2")

 

 

However, if you want to switch to another default Lakehouse on-the-fly, then the temporary view will not be available after you switch to another default Lakehouse.

Because the variables get lost when running the %%configure -f code cell.

 

So I guess you would need to solve that using a workaround.

frithjof_v's avatar
frithjof_v
Icon for Community Champion rankCommunity Champion
2 years ago

smpa01 
Could you explain more in detail what you want to do with the data?
Moving data from one workspace to another?

There could be easier ways to accomplish this instead of attaching Lakehouse programmatically to Notebook.

  • frithjof_v's avatar
    frithjof_v
    Icon for Community Champion rankCommunity Champion
    2 years ago

    Here is a solution which doesn't involve mounting or attaching Lakehouses to the Notebook:

     

    Perhaps you can use temporary views to be able to work with Spark SQL without having a default Lakehouse for your Notebook.

     

    In this example I have a Notebook without any default Lakehouse and without any mounted/attached lakehouses.

     

    I create a dataframe to connect to data from a lakehouse using the abfss path, and create a temporary view based on that dataframe. 

     

     

    df = spark.read.load("abfss://<workspaceID>@onelake.dfs.fabric.microsoft.com/<lakehouseID>/Tables/<tableName>")

df.createOrReplaceTempView("MyView_Read")

     

     

    I use Spark SQL to do some modifications to the temporary view, and save it as another temporary view.

     

    %%sql
CREATE OR REPLACE TEMPORARY VIEW MyView_Write AS SELECT *, Current_Timestamp as LoadTime FROM MyView_Read;

     

     

    I create a new dataframe from the new temporary new.

    I then write this new dataframe to another lakehouse table in another workspace.

     

    df_write = spark.sql("SELECT * FROM MyView_Write")

df_write.write.mode("append").save("<anotherWorkspaceID>@onelake.dfs.fabric.microsoft.com/<anotherLakehouseName>/Tables/<anotherTableName>")​

     

     

    This seems to work fine for me.