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?
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);