Forum Discussion

tkiwi's avatar
tkiwi
Helper I
1 year ago
Solved

Spark Temp View with two Lakehouses

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.

data engineering
lakehouse
notebook

2 Replies

  • smpa01's avatar
    smpa01
    Community Champion
    1 year ago

    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()