Error in SQL statement: AnalysisException: Table or view not found

Learn how to resolve the AnalysisException SQL error "Table or view not found".

Written by Adam Pavlacka

Last published at: May 23rd, 2022

Problem

When you try to query a table or view, you get this error:

AnalysisException:Table or view not found when trying to query a global temp view

Cause

You typically create global temp views so they can be accessed from different sessions and kept alive until the application ends. You can create a global temp view with the following statement:

%scala

df.createOrReplaceGlobalTempView("<global-view-name>")

Here, df is the DataFrame. Another way to create the view is with:

%sql

CREATE GLOBAL TEMP VIEW <global-view-name>

All global temporary views are tied to a system temporary database named global_temp. If you query the global table or view without explicitly mentioning the global_temp database, then the error occurs.

Solution

Always use the qualified table name with the global_temp database, so that you can query the global view data successfully.

For example:

%sql

select * from global_temp.<global-view-name>;