BigQuery table to csv and email

[ayushmaheshwari]

Hi,

I have done some transformations in Dataform and have BQ tables consisting of the transformed data. I have a data pipeline running through Airflow and all is ok

Now, I want another task in airflow to do the following:

1) Convert each table to csv

2) Attach in an email

3) Send email to xyz@soemthing.com (some email id)

I know about Airflow sendgrid but how do I do 1 and 2 and email as part of the pipeline run?

Can someone please help? Thank you so much

CC: @ms4446 

[kolban]

I'd break down the puzzle into a series of piece parts.  Take each one in turn.  From a high level, it sounds like you want to email one or more people with an attachment of CSVs from one or more tables.  Breaking this down, it feels like you want logic that may be:

For Each table {
   export the table to Google Cloud Storage as a CSV
}

And then an email processor which emails each of your users passing the externalized CSVs as attachments.  You will likely then want to clean up / remove the attachments files stored on GCS.  You can export to CSV in GCS using EXPORT DATA.   It looks like you can use Airflow BigQuery operators to execute BQ SQL to export the CSV (ref) and you can use SendGrid to send an email from Airflow.

I notice that there is a "new" option in town called "Application Integration" (ref) that seems to be an alternative option ... however ... I just noticed that there isn't an obvious "email attachment" feature in the Send Email task (ref).

[ayushmaheshwari]

Thanks @kolban , I know that. The attachment is the main problem. IS there anyway I could achieve that?

[kolban · Accepted solution]

It looks like this Stack Overflow Q&A will help us:

How to attach a file using email operator in Airflow

[ayushmaheshwari]

Thanks, I haven't yet implemented it but it should work. I will update the post once I have implemented it, thanks @kolban