SNOW-666918: session.write_pandas with create_temp_table fails

[krish-adi]

Please answer these questions before submitting your issue. Thanks!

1. What did you do?

session.write_pandas(input_table_df, "output_table", auto_create_table=True, create_temp_table=True)

2. What did you expect to see?

A temporary table created.

3. Logs in the snowpark console when running the stored procedure.

snowflake.connector.errors.ProgrammingError: 090236 (42601): Stored procedure execution error: Unsupported statement type 'temporary TABLE'.
 in function MAKE_RATIO with handler make_ratio   

[sfc-gh-sfan]

@krish-adi: ~Creating temp object~ Creating named temp object is not allowed inside a stored procedure is not supported today. The auto generated temp object by snowpark itself is allowed.

[krish-adi]

Ah, okay! Then there needs to be an update to the documentation. This is what it shows currently:

write_pandas(df: DataFrame, table_name: str, *, database: Optional[str] = None, schema: Optional[str] = None, chunk_size: Optional[int] = None, compression: str = 'gzip', on_error: str = 'abort_statement', parallel: int = 4, quote_identifiers: bool = True, auto_create_table: bool = False, create_temp_table: bool = False, overwrite: bool = False)

https://docs.snowflake.com/en/developer-guide/snowpark/reference/python/_autosummary/snowflake.snowpark.html#snowflake.snowpark.Session

[sfc-gh-sfan]

Hey thanks for the suggestions. You are right the docs should be improved, but probably not on the API side. On the API side we provided a link to stored proc for more information: https://github.com/snowflakedb/snowpark-python/blob/67113c1e9b7747db9b2479b8faf9e228febff189/src/snowflake/snowpark/stored_procedure.py#L78

We'll add this limitation to the docs for stored proc instead: https://docs.snowflake.com/en/sql-reference/stored-procedures-python.html#limitations.

Also one thing I want to clarify is that creating named temp object is not allowed. The auto generated temp object by snowpark itself is allowed. I'll edit my previous comment as well.

[krish-adi]

Could you elaborate on auto generated temp object? What does this imply and how do I generate this?

Could I use this approach to return a pandas DataFrame from a stored procedure?

[sfc-gh-sfan]

Those are the objects that is generated by snowpark under the hood. cache_result() is one of those examples.

No you cannot. Those objects are supposed to live within the scope of a stored procedure. It is still suggested to use table or stage files if you want to persist result from a stored procedure.