Open Synohara opened 1 month ago
hi and thanks for raising this with us, and especially for providing all the details in advance! Took a quick look and reproduced the issue the following way:
LANGUAGE SQL
) and installed as Native App as described in tutorial https://docs.snowflake.com/en/developer-guide/native-apps/tutorials/getting-started-tutorial#add-application-logic-and-install-your-first-application
CREATE OR REPLACE PROCEDURE addtwo_tabular(input NUMBER)
RETURNS TABLE(result NUMBER)
LANGUAGE PYTHON
RUNTIME_VERSION = '3.8'
PACKAGES = ('snowflake-snowpark-python')
HANDLER = 'run'
AS
$$
from snowflake.snowpark import Row
def run(session, input): df = session.create_dataframe([Row(result=input + 2)]) return df $$;
and verified it works, outside of the Native App when created as standalone, and also when installed from Native App.
4. create new task which calls this procedure. Now the execution fails from the Task - per Query History:
call HELLO_SNOWFLAKE_APP_t.core.addtwo_tabular(2) Python Interpreter Error: snowflake.snowpark.exceptions.SnowparkSQLException: 1304): 01b..9ba: 000709 (02000): Statement 01b..9b6 not found in function ADDTWO_TABULAR with handler run
Will investigate further.
turns out this is a known limitation (internally tracked on SNOW-1050018
) and there are efforts to address this by e.g. changing the security model to allow result_scan
in additional use-cases to happen. Don't have any timeline unfortunately however, but will update this thread once any new info is known.
As a potential workaround i found suggestions creating procedure with EXECUTE AS CALLER
but i'm really not sure if that addresses this specific use-case.
What version of Python are you using?
Python 3.11
What operating system and processor architecture are you using?
The environment of the Stored Procedure implemented within the Snowflake Native Apps
What are the component versions in the environment (
pip freeze
)?The Stored Procedure is executed within a Snowflake Native App environment. The specific versions of the installed packages are not directly accessible within the Native App context.
What did you do?
I am attempting to execute a Stored Procedure implemented within a Snowflake Native App via a Snowpark Python Task. The Stored Procedure is designed to return a table, but it fails to execute when called from the Task.
Steps to Reproduce:
What did you expect to see?
I expected the Task to successfully call the Stored Procedure and retrieve the resulting table.
What happened instead?
The Task fails with an error indicating that the Stored Procedure could not be executed. Additionally, attempting to retrieve the results using
result_scan
fails, suggesting that the SYSTEM user (used by Tasks) does not have permission to access the results within the Native App context.It seems that the execution of the procedure is failing when trying to run the following code:
https://github.com/snowflakedb/snowpark-python/blob/d640a36e781295188a543b2f9fdbf991cb0dc362/src/snowflake/snowpark/_internal/server_connection.py#L452
Additional Information:
Request:
Please investigate and resolve this issue to enable the successful execution of Stored Procedures within Snowflake Native Apps when called from Snowpark Tasks, especially when those procedures return tables. Additionally, please address the permissions issue related to result retrieval to ensure seamless integration between these components.