Forum Discussion

homanz's avatar
homanz
Icon for Advocate I rankAdvocate I
4 months ago
Solved

Temp views don't work when selecting tables with a schema name using Spark SQL

I created a new temporary view using Spark SQL. Code is below   CREATE OR REPLACE TEMPORARY VIEW tmpvw1 AS SELECT * FROM IM.test   In next step, I tried to select the temp view tmpvw1 using...
data engineering
notebook
  • v-veshwara-msft's avatar
    v-veshwara-msft
    4 months ago

    Hi homanz ,


    Thanks for reaching out to Microsoft Fabric Community.

    At the moment, this appears to be the current behavior of Spark SQL in schema enabled Lakehouses rather than an issue that can be resolved through an immediate hotfix. The PySpark approach you identified is currently the recommended workaround.

     

    Microsoft has also indicated that schema enabled Lakehouses in Spark still have some limitations, with additional support and improvements expected to roll out gradually over the coming months.

    Lakehouse Schemas (Generally Available) - Microsoft Fabric Community

     

    For more details: Spark views in lakehouses - Microsoft Fabric | Microsoft Learn

     

    You may also consider raising this as an enhancement request through the Fabric Ideas - Microsoft Fabric Community so the product team can review and prioritize support for this scenario in future updates.

     

    Hope this helps. Please reach out for further assistance.
    Thank you.

  • homanz's avatar
    homanz
    Icon for Advocate I rankAdvocate I
    4 months ago

    Hi ati_puri  and deborshi_nag, thanks both for your sugguestion.

     

    Using the schema name works only for joining tables within a single schema, but we have tables from different schemas. So, this solution won’t work for us.

     

    Using the full four-part table name also works, but we have to parameterise the workspace name in all our SQL code for CI/CD deployment, which makes the code very messy and hard to read.

     

    One solution I found is to wrap the SQL code in the PySpark createOrReplaceTempView function. It’s surprising that PySpark can somehow resolve all the schema names from different tables, but SparkSQL cannot.

     

    %%pyspark
    spark.sql("""
    --insert SQL code below
    SELECT
        *
    from
        IM.test t1
        left join
        ref.testref t2
        on t1.key= t2.key

    """).createOrReplaceTempView("tmp_python_view")

     

    Do you know if this issue will be fixed by MS soon?

    • v-veshwara-msft's avatar
      v-veshwara-msft
      Icon for Community Support rankCommunity Support
      4 months ago

      Hi homanz ,


      Thanks for reaching out to Microsoft Fabric Community.

      At the moment, this appears to be the current behavior of Spark SQL in schema enabled Lakehouses rather than an issue that can be resolved through an immediate hotfix. The PySpark approach you identified is currently the recommended workaround.

       

      Microsoft has also indicated that schema enabled Lakehouses in Spark still have some limitations, with additional support and improvements expected to roll out gradually over the coming months.

      Lakehouse Schemas (Generally Available) - Microsoft Fabric Community

       

      For more details: Spark views in lakehouses - Microsoft Fabric | Microsoft Learn

       

      You may also consider raising this as an enhancement request through the Fabric Ideas - Microsoft Fabric Community so the product team can review and prioritize support for this scenario in future updates.

       

      Hope this helps. Please reach out for further assistance.
      Thank you.

      • v-veshwara-msft's avatar
        v-veshwara-msft
        Icon for Community Support rankCommunity Support
        4 months ago

        Hi homanz ,


        Just wanted to check if the response provided was helpful. If further assistance is needed, please reach out.

        If you have submitted this as an idea in the Ideas Portal, please feel free to share the link here so others in the community can upvote it. With enough support, Microsoft may consider it for future implementation.

         

        Thank you.