_udf_code error when using filter within a Python Stored Procedure

Been trying to get started with using Python procedures in Snowflake. I have another basic procedure that works fine, but I can't get this part working. I am hoping to filter a dataframe, but getting this weird error.

[P0000][100357] Python Interpreter Error: Traceback (most recent call last): File "_udf_code.py", line 6, in run File "/usr/lib/python_udf/de--0d/lib/python3.8/site-packages/snowflake/snowpark/_internal/telemetry.py", line 133, in wrap result = func(*args, **kwar ...

Here is the stored procedure, its fairly simple

CREATE OR REPLACE PROCEDURE utility.procedure.RECREATE_STALE_STREAM_PYTHON()
  RETURNS STRING
  LANGUAGE PYTHON
  RUNTIME_VERSION = '3.8'
  PACKAGES = ('snowflake-snowpark-python')
  HANDLER = 'run'
AS
$$
from snowflake.snowpark.functions import col
def run(session):
    show_streams = "show streams in account;"
    streams = session.sql(show_streams)
    stale_streams = streams.filter(col('stale') == 'true').collect();
    return stale_streams
$$;

Thanks in advance

I have tried everything I can think of, nothing seems to work

Solution

The stored procedure called from Python code fails with the error:

invalid identifier 'STALE'

In this case it happens because Python generated the actual SQL statement using the column name without double-quotes while the actual column name is written in small letters.

If there are no double-quotes in a SQL command the object names are always capitalized. (which is fine in 99% of the cases).

However, if the object identifier is written not in all capitals, then the double-quotes must be used to preserve the case. In Python it would look as adding a pair of double quotes around the object identifier:

(col('"stale"') == 'true')

More details about quoted and unquoted identifiers can be found in the doc: Identifier Requirements

You are right that the full stack trace of the error can be found in the UI console. Also, you can find the actual query that was generated by Python (History in Classic UI, or Activity in Snowsight) and check it correctness, e.g. by running outside of the stored procedure call and debug using the SQL commands.