Below is the code that I have as part of databricks job running against snowflake table. It generates an error email in case the SQL code fails, not email otherwise.
My goal is: To return result of the query run in the body of the email(The query is pulling the Clustering ratio of a Snowflake table, therefore I need the actual value of the clustering ratio like 12, 13 etc to be send in the email..)
Present code:
My goal is: To return result of the query run in the body of the email(The query is pulling the Clustering ratio of a Snowflake table, therefore I need the actual value of the clustering ratio like 12, 13 etc to be send in the email..)
Present code:
def sf_connect(usr, pwd, host):
try:
ctx = snowflake.connector.connect(
user=usr,
password=pwd,
account=host
)
return ctx
except IOError as e:
print ('I/O error !')
fileOpen = open("/path/script_for_pulling_clustering_ratio.sql","r")
sql_script =fileOpen.read()
conn = sf_connect(usr = username, pwd = password_sf, host = 'URL')
cs = conn.cursor()
cs.execute('USE WAREHOUSE warehouse_name;')
cs.execute('USE DATABASE db;')
for sub_query in sql_script.split(';'):
print(sub_query)
logging.info(sub_query)
try:
db=cs.execute(sub_query)
print (db.sqlstate)
logging.info(db.sqlstate)
except Exception as e:
if sub_query in ['\n', '\r\n']:
print('Executed')
pass
else:
recipients = ["people's email ids"]
addr_from = 'sender_id'
msg = MIMEMultipart('alternative')
msg['To'] = ", ".join(recipients)
msg.attach(MIMEText('Debug the failed DB job at the link:corresponding to the failed run'))
msg['From'] = addr_from
msg['Subject'] = "clustering ratio pull-DB job-SQL FAILURE!"
msg['X-Priority'] = '1'
# Send the message via an SMTP server
s = smtplib.SMTP('ponyex.capitalone.com')
s.sendmail(addr_from, recipients, msg.as_string())
s.quit()
print("###################################")
print(e.args)
status= 'Fail'
print(status)
raise
