Returning data from a UDF to Snowflake in a Snowflake Native App

32 Views Asked by At

I have a python udf that performs a http request and stores the output in a list and returns this output to snowflake. The http request returns a name for every value sent by it. Here is the code snippet of this function test:

# val = '["abc","etc", "dif", "tef"]' what is coming to this function from snowflake
values=json.loads(val)
op=[]
for value in values:
    body = {
            "parameters": [{ "token": value }]
        }
        
    try:
        session = requests.Session()
        response = session.post(url, json=body, headers=headers)
        response.raise_for_status()
            
        response_as_json = json.loads(response.text)
        op.append(response_as_json["records"][0]["value"])
    except Exception as e:
        print(f"Error processing value {value}: {e}")

# print(op) Gives me output as ['John', 'Tom', 'Jimmy', 'Harry']
    
return op

I have done this for this function:

CREATE OR REPLACE FUNCTION code_schema.test(val string)
  RETURNS variant
  LANGUAGE python
  runtime_version = '3.8'
  packages = ('snowflake-snowpark-python', 'requests', 'simplejson')
  imports = ('/src/udf.py')
  handler = 'udf.test';

GRANT USAGE ON FUNCTION code_schema.test(string) TO APPLICATION ROLE app_public;

In Snowflake -

create table shopper (first_name string);
insert into shopper (first_name) values ('abc');
select * from shopper;

set tokens =  (SELECT to_json(array_agg(first_name)) from shopper);
select $tokens;

which gives tokens as:

["abc","etc", "dif", "tef"]

And I call this snowflake native app udf like

SELECT app.code_schema.test($tokens) AS name;

This gives me the output only as

['John']

when the output I want is of the format

['John']
['Tom']
['Jimmy']
['Harry']

If I do a json.dumps on the output like return json.dumps(op) by returning string rather than variant for this function I get this

["John", "Tom", "Jimmy", "Harry"]

However, the format is wrong then. Why am I getting just the first name of it when returning variant and how do I resolve it to get it in the format I want if I return string?

0

There are 0 best solutions below