Send custom emails from databricks notebook without using third party SMTP server. Would like to utilize databricks existing smtp or databricks api.

[969091]

We want to use existing databricks smtp server or if databricks api can used to send custom emails. Databricks Workflows sends email notifications on success, failure, etc. of jobs but cannot send custom emails. So we want to send custom emails to differentiate databricks environment whether email notifications are received from Dev, QA, Stage or Prod environment.

What are other possible ways to send emails? Please advise.

I have used python smtplib package to send message but got error ConnectionRefusedError: [Errno 111] Connection refused at smtpObj = smtplib.SMTP('localhost')

import smtplib

sender = 'abc@gcd.com'

receivers = ['myname@company.com']

message = """From: From Person <abc@gcd.com>

To: To Person <myname@company.com>

Subject: SMTP e-mail test

This is a test e-mail message.

"""

try:

  smtpObj = smtplib.SMTP('localhost')

  smtpObj.sendmail(sender, receivers, message)     

  print ("Successfully sent email")

except SMTPException:

  print ("Error: unable to send email")

[LandanG]

@Krishna Prasad​ Depending on the emails and how you want them to be sent, you could check out Databricks Alerts where you can send alerts depending on custom logic with a Delta table. You could use Jobs to combine the notebooks and SQL alerts to raise alerts if a notebook failed or something.

[Anonymous]

Hi @Krishna Prasad​ 

Thank you for your question! To assist you better, please take a moment to review the answer and let me know if it best fits your needs.

Please help us select the best solution by clicking on "Select As Best" if it does.

Your feedback will help us ensure that we are providing the best possible service to you.

Thank you!

[htv]

Currently, the Alert service only allow to send a fix email list. can we send dynamic based on the a column in the dataset ? e.g. i have a dataset which includes a email column, can i send email to each row based on columns ? 

[pavlos_skev]

Hello. Did you figure this out? We have a similar problem and this approach could potentially be a nice solution.

[mido1978]

So you could do this via the api and combine with alerts. The challenge is obtaining the query_ids needed to create the notification and the notification destinations themselves -  it would go something like this -

- Save your queries as actual "queries" in Databricks
 - Create a table storing your metadata, so the query display name, email recipients, a column for the trigger column and a column for the query_id

- Use the notification_destination api, iterate through your table and create a destination per email in your table
- Use the queries api, iterate through the queries you own using 

queries.list() - update your metadata table query_id by joining on display_name and updating the query_id. Note, you can combine this step with the next by doing the queries_create() api at the same time if your queries themselves are stored in a table. This is quite intensive so it might be better to create the queries via the api and retrieve the query_id at the same time
- Now you've got your query_ids and notification defined, you can then iterate through your table to create your actual alerts, passing through your destinations and queries that you've now defined, there's an example here

Might sound a bit clunky - if you're happy to wait i'm building a POC for a customer as we speak doing this very thing, if you're happy to wait a few days i'll share the final code here 🙂