Unable to load & create the table in the silver lakehouse

[amty63]

Hello Experts,
I am trying to read the table and renaming of few columns through SQL and trying to load & create the table in the silver lakehouse.
Below is the code using through notebooks in the Fabric
I am trying to fetch the data from bronze layer (staging schema) and need to create & load the table with same data but changing of names. Please suggest.

# Import lib

from pyspark.sql import SparkSession

spark = SparkSession.builder \

    .appName("DataFrame Write") \

    .getOrCreate()

df = spark.sql("""

SELECT

    ORDER_ID,

    TOOLPURCHASE_ID,

    SOC_ID AS CORP_ID,

    STATUS_ID,

    CURRENCY_ID,

    COLSID_CREATION AS EMPID_CREATION,

    ETS_ID AS EST_ID,

    COLSID_REQUESTER AS EMPID_REQUESTER,

    ORDER_IDEXTERNAL,

    ORDER_DATE,

    ORDER_AMOUNT,

    ORDER_MODIFICATIONDATE AS ORDER_MODIFICATION_DATE,

    ORDER_DESCRIPTION AS ORDER_DESCR,

    ORDER_DELIVERYDATEASKED

FROM

    ODS_PURCHASE_ORDER

""")

df.show()

# Saving to the Silver Lakehouse

encoded_url = "https%3A%2F%2Fonelake.dfs.fabric.microsoft.com%2FWKS%20POC%20FABRIC%20ORBITALSHIFT%20UAT%2FLKH_PROCUREMENT_UAT_Silver.Lakehouse%2FTables%2FNotebook_DWH_PROCUREMENT_PURCHASE_ORDER%2F"

df.write.mode("overwrite").format("delta").save(encoded_url)


Error >>>
Py4JJavaError Traceback (most recent call last) Cell In[41], line 32 30 # Saving to the Silver Lakehouse 31 encoded_url = "https%3A%2F%2Fonelake.dfs.fabric.microsoft.com%2FWKS%20POC%20FABRIC%20ORBITALSHIFT%20UAT%2FLKH_PROCUREMENT_UAT_Silver.Lakehouse%2FTables%2FNotebook_DWH_PROCUREMENT_PURCHASE_ORDER%2F" ---> 32 df.write.mode("overwrite").format("delta").save(encoded_url)

[amty63]

@v-nikhilan-msft 
thanks for the reply,
I know the workaround and could do through Dataflow Gen2 & Data Pipelines but requirement is to use & achieve it through notebooks.
is there any possibility through code & create the table in the silver lakehouse ?

[v-nikhilan-msft]

Hi @amty63 

Currently achieving this through notebooks is not possible, because we can have only one default lakehouse for a notebook. 

Hope this helps.

[v-nikhilan-msft]

Hi @amty63 
We haven’t heard from you on the last response and was just checking back to see if your query got resolved. Otherwise, will respond back with the more details and we will try to help.
Thanks

[v-nikhilan-msft]

Hi @amty63 
We haven’t heard from you on the last response and was just checking back to see if your query got resolved. Otherwise, will respond back with the more details and we will try to help.
Thanks

[v-nikhilan-msft]

Hi @amty63 
Thanks for using Fabric Community.
Reading data from one lakehouse and creating a table using the same data in another lakehouse is not possible in Notebooks. The reason is there can be only one default lakehouse for a notebook in the current spark session. Instead you can use a dataflow and create a new table in another lakehouse. I have created a repro for you and attached the screenshots below:

I have a table named Covid_table in the Source Lakehouse. The schema is as below:

2) Create a Dataflow Gen2 and select the source as Lakehouse. Select the required lakehouse and the table.

After the data is successfully imported, you can do the required changes. You can remove the columns.

You can rename the columns:

After doing the changes, select the destination as the silver lakehouse.

Give the table a new name:

Save the settings and click on Publish. A new table will be created in the Lakehouse. 

Hope this helps. Please let me know if you have any further questions.