CODEWITHSUNDEEP
databricksazure-databricks
Dumbledore__
Dumbledore__

Reputation: 107

Databricks API Download Query Output Excel

Databricks SQL Supports downloading the result set of a SQL query to a local document (csv, excel, etc.)

enter image description here

I'd like to implement a feature allowing users to run scheduled queries, then plug the result set into a predefined excel templates (containing a bunch of macros) to be sent to users by email.

Unfortunately, I haven't been able to find an API that would allow me to write the custom logic to do something like this. I feel like there might be another implementation using live tables or a custom notebook, however I haven't been able to put together the pieces.

What implementation could i use to produce this feature?

Upvotes: 0

Views: 737

Answers (1)

Karthikeyan Rasipalay Durairaj
Karthikeyan Rasipalay Durairaj

Reputation: 2344

am giving answer here as workaround solution as I don't see direct solution from databricks notebook .

Step-01 : Writing your content into any DBFS location . ref : link

df_spark.write.format("com.crealytics.spark.excel")\
  .option("header", "true")\
  .mode("overwrite")\
  .save(anydbfspath)

Step-02 : Reading respective location and send as email through python

# Import smtplib for the actual sending function
import smtplib

# Import the email modules we'll need
from email.message import EmailMessage

# Open the plain text file whose name is in textfile for reading.
with open(textfile) as fp:
    # Create a text/plain message
    msg = EmailMessage()
    msg.set_content(fp.read())

# me == the sender's email address
# you == the recipient's email address
msg['Subject'] = f'The contents of {textfile}'
msg['From'] = me
msg['To'] = you

# Send the message via our own SMTP server.
s = smtplib.SMTP('localhost')
s.send_message(msg)
s.quit()

Step-03 : Make this notebook in job cluster with schedule .

Upvotes: 1

Related Questions