topic Re: Read temporary views in SQL Analytics in Data Engineering

Read temporary views in SQL Analytics

Marra — Tue, 07 Jun 2022 09:27:46 GMT

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

Re: Read temporary views in SQL Analytics

-werners- — Tue, 07 Jun 2022 09:44:08 GMT

@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.

Re: Read temporary views in SQL Analytics

Marra — Tue, 07 Jun 2022 09:55:14 GMT

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.

Re: Read temporary views in SQL Analytics

-werners- — Tue, 07 Jun 2022 09:58:20 GMT

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

Re: Read temporary views in SQL Analytics

Marra — Tue, 07 Jun 2022 14:54:29 GMT

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.

Re: Read temporary views in SQL Analytics

Marra — Tue, 07 Jun 2022 14:55:57 GMT

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

Re: Read temporary views in SQL Analytics

-werners- — Tue, 07 Jun 2022 14:59:15 GMT

@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?

Re: Read temporary views in SQL Analytics

Marra — Wed, 15 Jun 2022 10:11:38 GMT

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?