Spark Temp View with two Lakehouses

[tkiwi]

Hey there, I`ve got the following situation

Notebook with SparkSQL, the following SELECT is running

SELECT * FROM LH1.dbo.table1 t1 
JOIN LH2.dbo.table2 t2 
ON t1.columnID = t2.columnID

The following statement is also running

CREATE OR REPLACE TEMP VIEW tmp_select AS
SELECT * FROM LH1.dbo.table1 t1 
JOIN LH2.dbo.table2 t2
ON t1.columnID = t2.columnID  

But if I want to select this generated temporary View I get the following error:

[REQUIRES_SINGLE_PART_NAMESPACE] spark_catalog requires a single-part namespace, but got \LH1`.`dbo`.`

What I am doing wrong here?

P.S. Both Lakehouses are connected with my notebook and have schema enabled.

[smpa01]

@tkiwi  temporary views in spark are session-scoped; meaning they are short lived and don't exists beyond the scope of the query. If you are expecting to create a view with the hope that it shows up in table section of lakehouse tables, that is not the intended use of the TEMPORRY VIEW

fabric notebook has full support for them; try the following

spark.sql("create or replace temporary view temp_view_1 as select 1 as colA")
df = spark.sql(f"select * from temp_view_1")
df.show()

[tkiwi · Accepted solution]

Found a reason by another post on reddit...Spark views are currently not supported:

https://learn.microsoft.com/en-us/fabric/data-engineering/lakehouse-schemas#public-preview-limitatio...