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 belowSELECT
*
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
No CommentsBe the first to comment
