Airflow: Best Practice Sending E-Mail with (small) Database Tables

user_18310435 0
Viewed 168

Context

At the moment my company sends out E-Mails via the SQL Server Agent on the SQL Server. The SQL queries used are generating a long String that can be interpreted as HTML code in the end. However I am about to change this setup by putting this task in Airflow, where I'll be able to seperate the pure SQL query from the HTML site of things and make things cleaner.

I know that this involves a little bit of computing power for Airflow, when it converts the SQL table data into HTML but since the tables are fairly small I think that this is okay - but i am also happy to discuss a better practice here.

The Setup

However I thought I'd build a little helper script outside the DAG (helper.py) that takes an SQL file (query.sql) and converts the results into a pandas dataframe and ultimately in an HTML string. This is possible via the MsSqlHook.get_records() method and the pandas .to_html() method. Afterwards this HTML string is passed to the EmailOperator() of the DAG. Sketch:

Folder Structure

dags

dags/mydag.py (with EmailOperator(func: my_html_function.py, str: path_to_query))

dags/modules/helper.py (with my_html_function(str: path_to_query))

dags/scripts/query.sql

So the path to query.sql is put into the EmailOperator() as a string, where it's passed to my_html_function().

The Problem

However the problem is that I'm not able to find the query.sql file from my_html_function.py it seems like the "standard" python rules of finding a file do not apply here. Since I am passing the path to the query in mydag.py I'd think that the path is "scripts/query.sql" but this does not work. I also tested putting the query.sql in the dags-folder and in the modules-folder and just use "query.sql" as a path and both did not work. So how can I find the correct path?

Your answers are also very welcome if you have a better approach to tackle this whole email problem. Thank you in advance!

Edit: To seperate the two questions (best practice for emails and path problems) I created this new post about the path problem: Airflow + Docker: Path behaviour (+Repo). Use this thread for the email discussion please.

airflow path python
1 Answers

To find your file, you could get your current directory, then navigate around there to find the scripts, something like:

import os
current_dir = os.path.dirname(os.path.realpath(__file__))

The way we structure our code is that each DAG's code is self-contained in a folder with all accompanying scripts, so we use the above then just use os.path.join(current_dir, 'my_script.sql') or something similar to reference the local file.

In terms of your data into HTML method, outputting raw HTML from a SQL query is pretty clunky, you might want to look at using jinja2 as a library to create yourself an HTML template, then just use Python to fill in the gaps.

user_182997500
Related