CODEWITHSUNDEEP
pythonsnowflake-cloud-data-platform
Maran
Maran

Reputation: 171

snowflake python udf for mail alert

I was trying to make use of Python within snowflake UI and create an UDF to send an mail using smtplib. Can someone help if this works with Python UDF's withing snowflake UI else is there any way we can write a Python UDF/Procedure to send an mail alert when the procedure call fails. I don't want to try AWS SNS service or external functions.

Below is the sample Python UDF I was trying with snowflake. Anaconda python packages were enabled in my snowflake account to make use of Python.

CREATE OR REPLACE FUNCTION sendMail(env_name string, object_name string, tolist string, cclist string, status string, message string)
RETURNS STRING
LANGUAGE PYTHON
RUNTIME_VERSION = '3.8'
PACKAGES = ('snowflake-snowpark-python')
HANDLER = 'send_mail'
AS
$$
import smtplib
from email import mime
from email.mime.multipart import MIMEMultipart
from email.mime.text import MIMEText
from email.mime.base import MIMEBase
from email import encoders

def send_mail(env_name, object_name, tolist, cclist, status, message):
    try:
        #Connect mail Server
        server = smtplib.SMTP('smtp.myserver.net', 25)
        fromaddr = '[email protected]'
        toaddr = '[email protected]'+','+ tolist
        msg = MIMEMultipart()
        msg['From'] = fromaddr
        msg['To'] = toaddr
        msg['Cc'] = cclist
        msg['Subject'] = env_name + ": " + object_name + " - " + status
        body = message

        rcpt = msg['To'].split(',') + msg['Cc'].split(',')
        msg.attach(MIMEText(body, 'plain'))
        server.ehlo()
        server.starttls()
        server.ehlo()
        text = msg.as_string()
        server.sendmail(fromaddr, rcpt, text)
        server.quit()
        return "Your mail sent successfully"
    except Exception as e:
        #raise e
        return e + "Message Sending FAILED"
$$;

Tested above function with simple select but that was giving error.

select sendmail('Test','Test OBJ','','','Test mail','This is an test mail');

Error

    Python Interpreter Error: Traceback (most recent call last): File "_udf_code.py", line 43, in send_mail File "_udf_code.py", line 15, 
in send_mail File "/usr/lib/python_udf/XXXXXXXXXXXXXXXXXXXXXXXXXXXXXX/lib/python3.8/smtplib.py", line 255, in __init__ (code, msg) = 
self.connect(host, port) File "/usr/lib/python_udf/XXXXXXXXXXXXXXXXXXXXXXXXXXXXXX/lib/python3.8/smtplib.py", line 339, in connect 
self.sock = self._get_socket(host, port, self.timeout) File "/usr/lib/python_udf/XXXXXXXXXXXXXXXXXXXXXXXXXXXXXX/lib/python3.8/smtplib.py", 
line 310, in _get_socket return socket.create_connection((host, port), timeout, File "/usr/lib/python_udf/XXXXXXXXXXXXXXXXXXXXXXXXXXXXXX/lib/python3.8/socket.py", 
line 787, in create_connection for res in getaddrinfo(host, port, 0, SOCK_STREAM): File "/usr/lib/python_udf/XXXXXXXXXXXXXXXXXXXXXXXXXXXXXX/lib/python3.8/socket.py", 
line 918, in getaddrinfo for res in _socket.getaddrinfo(host, port, family, type, proto, flags): OSError: [Errno 16] Device or resource busy in 
function SENDMAIL with handler send_mail

Upvotes: 2

Views: 1881

Answers (2)

Dave Welden
Dave Welden

Reputation: 1918

Check out Sending Email Notifications, currently in Public Preview.

Upvotes: 2

Sergiu
Sergiu

Reputation: 4578

You can't do that since Python currently runs in Snowflake in a sandbox and doesn't have access to network.

This is mentioned on the official docs:

Because your code cannot access the network directly or indirectly, you cannot use the code in the Snowflake Python Connector to access the database. Your UDF cannot itself act as a client of Snowflake.

You can read more here.

Upvotes: 3

Related Questions