0

I am looking to create a temporary view on Snowflake using the Snowpark API (version 1.6.0). I have created a DataFrame and was attempting to use the createOrReplaceTempView() function as follows:

  // code to create dataframe here
  finalDF.createOrReplaceTempView(outputTableName)

When I run this code, I see the following error:

net.snowflake.client.jdbc.SnowflakeSQLException: Stored procedure execution error: Unsupported statement type 'temporary VIEW'.

On a worksheet within my Snowflake account, I am able to create the same table successfully through raw SQL.

Any ideas why I'm prevented from doing the same through Snowpark?

Rohan Aletty
  • 2,432
  • 1
  • 14
  • 20
  • Probably because there is no such thing as a temporary view in Snowflake – NickW Sep 30 '22 at 19:49
  • 1
    @NickW "Probably because there is no such thing as a temporary view in Snowflake" - `CREATE OR REPLACE TEMPORARY VIEW vw AS SELECT 1 c; -- View VW successfully created.` It is possible to create TEMPORARY view in Snowflake. – Lukasz Szozda Oct 01 '22 at 07:57
  • Looks like Snowflake have a documentation problem, then, as this option is not covered here https://docs.snowflake.com/en/sql-reference/sql/create-view.html – NickW Oct 01 '22 at 09:25
  • 1
    @NickW This is documented at [Snowpark](https://docs.snowflake.com/en/developer-guide/snowpark/scala/working-with-dataframes.html#creating-a-view-from-a-dataframe) part and it's available also on Snowpark API docs. – Sergiu Oct 03 '22 at 07:23
  • Rohan, what is the value of variable **outputTableName**? I just did a quick test from my side using Snowpark 1.6.0 and createOrReplaceTempView works fine for me, I can even see the SQL on the Query History. – Sergiu Oct 03 '22 at 07:24
  • Hey @Sergiu, the `outputTableName` is a `String` structured as the following: `"database"."schema"."table"`. The quotes are included within the string. – Rohan Aletty Oct 03 '22 at 20:36
  • Can you maybe share how **finalDF** is created? – Sergiu Oct 04 '22 at 09:10
  • 1
    FYI a case has been raised with Snowflake to get the documentation for the CREATE VIEW statement updated to include the "TEMPORARY" keyword – NickW Oct 12 '22 at 09:41

1 Answers1

0

From the error message, I understood that you are running a stored procedure. By default, if not specified, stored procedure runs on the owner's rights which blocks the creation of temporary objects. So, use the caller's rights by adding EXECUTE AS CALLER statement in the stored procedure.

eg:

CREATE or replace PROCEDURE sample_proc(
)
RETURNS VARCHAR
...
...
EXECUTE AS CALLER
AS
PooMac
  • 41
  • 2