Read temporary views in SQL Analytics

[Marra]

I'm having issues trying to read temporary views in SQL Analytics module. Ive managed to create temporary views based on a query but I don't know how to read from them? Just using the name of the view returns "Table or view not found".

[-werners-]

@Daniel Alteborg​ , Temp views are visible only to the session created and are dropped when the session ends.

I guess that is your issue here.

[Marra]

No, I'm actually having issues reading from the view in the same session that created it. Using the same view name I get a table or view not found.

[-werners-]

Strange,

did you create the view as follows:

CREATE TEMPORARY VIEW IF NOT EXISTS view

AS ...

(If you are on community edition this feature might not be available, can't tell.)

[Marra]

So this is interesting, I tried to run the CREATE query per your example above:

CREATE TEMPORARY VIEW IF NOT EXISTS view_test AS

select * from ....

But this gave me a exception:

"It is not allowed to define a TEMPORARY view with IF NOT EXISTS"

However, I used a similar query before to create my view:

CREATE OR REPLACE TEMPORARY VIEW view_test AS

select * from ....

This works fine, the query returns: "was successfully executed" but I cant use the view becouse it's not found.

[Marra]

I should be able to read the view with "select * from view_test"?

[-werners-]

@Daniel Alteborg​, hm ok,

https://docs.microsoft.com/en-us/azure/databricks/sql/language-manual/sql-ref-syntax-ddl-create-view

so it should work.

Maybe you do not have the necessary permissions?

[Kaniz]

Hi @Daniel Alteborg​ ​, We haven’t heard from you on the last response from @Werner Stinckens​ , and I was checking back to see if you have a resolution yet. If you have any solution, please share it with the community as it can be helpful to others. Otherwise, we will respond with more details and try to help.

[Marra]

I havn't been able to resolve the issue. I'm using a admin account in databricks so it should't be a permission issue?

Is there any sample dataset that I can use in sql analytics so that I can try again and share my specific code here?