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?