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

[homanz]

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 the following code:

SELECT * FROM tmpvw1

Then, I got this error:

"Error

[TABLE_OR_VIEW_NOT_FOUND] The table or view `IM`.`test` cannot be found. Verify the spelling and correctness of the schema and catalog. If you did not qualify the name with a schema, verify the current_schema() output, or qualify the name with the correct schema and catalog. To tolerate the error on drop use DROP VIEW IF EXISTS or DROP TABLE IF EXISTS. SQLSTATE: 42P01; line 1 pos 14; 'Project [upcast(getviewcolumnbynameandordinal(`tmpvw1`, new_column, 0, 1), StringType) AS new_column#690, upcast(getviewcolumnbynameandordinal(`tmpvw1`, TestCol, 0, 1), StringType) AS TestCol#691] +- 'Project [*] +- 'UnresolvedRelation [IM, test], [], false"

Temp views seem to work fine when I only use tables in the dbo schema and don't specify the schema name. For example, SELECT * from tableA. Temp views start to fail when I add a schema name to tables. I often need to join tables in different schemas. Is it a bug in temp view? how to fix it? Thanks!

[ati_puri]

HI @homanz ,

These are known issues and solution have bene provided in this Fabric Community before for the same issue. Pls find the link here: Solved: Re: Issue with SparkSQL Schema Behavior in Fabric - Microsoft Fabric Community

For more details on information around Lakehouse Schema behaviour, pls find the link below.

Lakehouse schemas - Microsoft Fabric | Microsoft Learn

Thanks

Ati Puri

[homanz]

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 · Accepted solution]

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]

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.

[homanz]

Thanks @v-veshwara-msft , I raised an idea. The link is https://community.fabric.microsoft.com/t5/Fabric-Ideas/Enable-Spark-SQL-to-create-temporary-views-us...

If anyone has experienced the same issue, please help upvote this idea. Hopefully, Microsoft will implement a solution for this.

[deborshi_nag]

Hello @homanz 

Fabric relies on an internal session database for Spark session and lakehouse binding. If you run SELECT current_database() as current_db, you'll notice a temporary database name appears.

To resolve this, always use a four-part name with backticks—`<Your Workspace Name>`.Lakehouse.Schema.Table—when referencing tables. You can find your schema's namespace by running the SQL below and checking the "namespace" column.

SHOW TABLES in IM;