How can I call a Python UDF from another Python UDF in Snowflake?

Question

I have a UDF which returns my Gchat Webhook URL including a stored secret token, called 'RETRIEVE_GCHAT_WEBHOOK_URL__BI_NOTIFICATION'. Now I want to use that UDF in another UDF to send out notifications, but I'm struggling to get it to work.

This is what I have:

USE WAREHOUSE BI_ELT_WH;
USE DATABASE BI_ELT;
USE SCHEMA INTEGRATIONS;

-- SELECT RETRIEVE_GCHAT_WEBHOOK_URL__BI_NOTIFICATION(); -- this works

CREATE OR REPLACE FUNCTION send_gchat_message(message string)
RETURNS STRING
LANGUAGE PYTHON
RUNTIME_VERSION = '3.8'
PACKAGES = ('requests')
HANDLER = 'send_gchat_message'
AS
$$
import requests

def send_gchat_message(message):
    target_url = RETRIEVE_GCHAT_WEBHOOK_URL__BI_NOTIFICATION()
    requests.post(target_url, json={'text': message})
    return 'Done.'
  
$$;

SELECT send_gchat_message('This is a test message!');

When I run this I get

NameError: name 'RETRIEVE_GCHAT_WEBHOOK_URL__BI_NOTIFICATION' is not defined

How can I point to this UDF?

In a task, this works:

from snowflake.snowpark.context import get_active_session
session = get_active_session()

gchat_webhook_url__bi_notification  = session.sql('SELECT BI_ELT.INTEGRATIONS.retrieve_gchat_webhook_url__bi_notification()').collect()[0][0]

This however does not work in a UDF, I get:

ModuleNotFoundError: No module named 'snowflake'

Any ideas?

Answer 1

I believe a Python UDFs cannot execute another Python UDF (at least don't know how). However, SQL statements can. Therefore I can retrieve the token with SQL outside of the Python function and pass it in as a parameter. So the function is defined like this:

CREATE OR REPLACE FUNCTION send_gchat_message(message string, webhook_url_with_token string)
RETURNS STRING
LANGUAGE PYTHON
RUNTIME_VERSION = '3.8'
PACKAGES = ('requests')
HANDLER = 'send_gchat_message'
EXTERNAL_ACCESS_INTEGRATIONS = (GCHAT_WEBHOOK_INTEGRATION)
AS
$$
import requests

def send_gchat_message(message, webhook_url_with_token):
    requests.post(webhook_url_with_token, json={'text': message})
    return 'Done.'
$$;

And then I can call this function using SQL, like this:

SET GCHAT_WEBHOOK_URL__BI_NOTIFICATION=(SELECT BI_ELT.INTEGRATIONS.RETRIEVE_GCHAT_WEBHOOK_URL__BI_NOTIFICATION());

SELECT SEND_GCHAT_MESSAGE('This is a test message!', $GCHAT_WEBHOOK_URL__BI_NOTIFICATION);