Enable Spark SQL to create temporary views using tables with schema

[homanz]

Hi MS Support team,

Currently, when you use Spark SQL to create a temp view with tables that have schema name, the temp view can't be used in later steps.

For example,

Firstly, create a temp view using below code

CREATE OR REPLACE TEMPORARY VIEW tmpvw1 AS

SELECT

    *

FROM

    IM.test t1

    LEFT JOIN  ref.testref t2

    ON t1.key= t2.key

In next step, select the temp view tmpvw1 using the following code:

SELECT * FROM tmpvw1

You will see a error message similar to below 

"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"

In PySpark, it works fine. The current workaround is to encapsulate the SQL code inside a PySpark code. 

%%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")

Could you please enable Spark SQL to create temporary views using tables with schema, just as PySpark does?

Thanks,

Homan