send_mail¶

send_mail(to, subject, content, attachment_filename, attachment_content)

Description¶

Sends an email to to email with subject, content and possible attachment (defined by attachment_filename and attachment_content).

Param	Possible values
`to`	One or multiple comma separated emails. For instance `contact@unytics.io` or `contact@unytics.io, paul.marcombes@unytics.io`
`subject`	Email subject
`content`	Can be plain text, html or markdown
`attachment_filename`	`null` or filename with extension such as `report.xlsx`
`attachment_content`	`null` or can be plain text or base64 encoded content (useful to send excel files, pdf or images)

This function uses SendGrid to send the emails and Lee Munroe HTML template for styling emails.

Usage¶

Call or Deploy send_mail ?

Call send_mail directly

The easiest way to use bigfunctions

send_mail function is deployed in 39 public datasets for all of the 39 BigQuery regions.
It can be called by anyone. Just copy / paste examples below in your BigQuery console. It just works!
(You need to use the dataset in the same region as your datasets otherwise you may have a function not found error)

Public BigFunctions Datasets

Region	Dataset
`eu`	`bigfunctions.eu`
`us`	`bigfunctions.us`
`europe-west1`	`bigfunctions.europe_west1`
`asia-east1`	`bigfunctions.asia_east1`
...	...

Deploy send_mail in your project

Why deploy?

You may prefer to deploy send_mail in your own project to build and manage your own catalog of functions.
This is particularly useful if you want to create private functions (for example calling your internal APIs).
Get started by reading the framework page

Deployment

send_mail function can be deployed with:

pip install bigfunctions
bigfun get send_mail
bigfun deploy send_mail

Requirements

send_mail uses the following secrets. Get them by reading the documentation link and store them in Google Secret Manager in the project where you deploy the function (and give Accessor role to the service account of the function):

name	description	documentation to get the secret
`sendgrid_api_key`	Sendgrid API Key	doc

Examples¶

1. Send email without file attached

EUUSeurope-west1

select bigfunctions.eu.send_mail("contact@unytics.io", "I love BigFunctions", "Hey Paul, could you deploy more BigFunctions \ud83d\ude4f?", null, null)

select bigfunctions.us.send_mail("contact@unytics.io", "I love BigFunctions", "Hey Paul, could you deploy more BigFunctions \ud83d\ude4f?", null, null)

select bigfunctions.europe_west1.send_mail("contact@unytics.io", "I love BigFunctions", "Hey Paul, could you deploy more BigFunctions \ud83d\ude4f?", null, null)

+---------+
| success |
+---------+
| true    |
+---------+

2. Send email with plain text file attached

EUUSeurope-west1

select bigfunctions.eu.send_mail("contact@unytics.io", "I love BigFunctions", "Hey Paul, could you deploy more BigFunctions \ud83d\ude4f?", "report.csv", "col1,col2\\nval1,val2\\nval3,val4")

select bigfunctions.us.send_mail("contact@unytics.io", "I love BigFunctions", "Hey Paul, could you deploy more BigFunctions \ud83d\ude4f?", "report.csv", "col1,col2\\nval1,val2\\nval3,val4")

select bigfunctions.europe_west1.send_mail("contact@unytics.io", "I love BigFunctions", "Hey Paul, could you deploy more BigFunctions \ud83d\ude4f?", "report.csv", "col1,col2\\nval1,val2\\nval3,val4")

+---------+
| success |
+---------+
| true    |
+---------+

3. Send email with excel file attached

EUUSeurope-west1

select bigfunctions.eu.send_mail("contact@unytics.io", "I love BigFunctions", "Hey Paul, could you deploy more BigFunctions \ud83d\ude4f?", "report.xlsx", "(select bigfunctions.eu.json2excel(\u0027[{\"col1\": \"val1\", \"col2\": \"val2\"}, {\"col1\": \"val3\", \"col2\": \"val4\"}]\u0027))")

select bigfunctions.us.send_mail("contact@unytics.io", "I love BigFunctions", "Hey Paul, could you deploy more BigFunctions \ud83d\ude4f?", "report.xlsx", "(select bigfunctions.us.json2excel(\u0027[{\"col1\": \"val1\", \"col2\": \"val2\"}, {\"col1\": \"val3\", \"col2\": \"val4\"}]\u0027))")

select bigfunctions.europe_west1.send_mail("contact@unytics.io", "I love BigFunctions", "Hey Paul, could you deploy more BigFunctions \ud83d\ude4f?", "report.xlsx", "(select bigfunctions.europe_west1.json2excel(\u0027[{\"col1\": \"val1\", \"col2\": \"val2\"}, {\"col1\": \"val3\", \"col2\": \"val4\"}]\u0027))")

+---------+
| success |
+---------+
| true    |
+---------+

Use cases¶

This send_mail function has several practical use cases within BigQuery:

1. Data-Driven Alerting:

Imagine you have a BigQuery script that monitors website traffic. You could use send_mail to send an alert if traffic drops below a certain threshold.

DECLARE low_traffic_threshold INT64 DEFAULT 1000;
DECLARE current_traffic INT64;

SET current_traffic = (SELECT COUNT(*) FROM `your_project.your_dataset.website_traffic` WHERE _PARTITIONTIME = CURRENT_DATE());

IF current_traffic < low_traffic_threshold THEN
  SELECT bigfunctions.us.send_mail(
    'admin@yourcompany.com',
    'Low Website Traffic Alert',
    FORMAT('Website traffic dropped to %d today, below the threshold of %d', current_traffic, low_traffic_threshold),
    null,
    null
  );
END IF;

2. Report Generation and Distribution:

You can generate reports within BigQuery and then email them directly using this function. The example in the documentation shows converting JSON to Excel and attaching it. You could adapt this for CSV reports as well:

SELECT bigfunctions.us.send_mail(
    'marketing@yourcompany.com',
    'Weekly Sales Report',
    'Please find attached the weekly sales report.',
    'weekly_sales.csv',
    (SELECT STRING_AGG(FORMAT('%t,%t', product_name, sales), '\n') FROM `your_project.your_dataset.sales_data` WHERE _PARTITIONTIME BETWEEN DATE_SUB(CURRENT_DATE(), INTERVAL 7 DAY) AND CURRENT_DATE())
);

3. Scheduled Notifications:

Combine send_mail with BigQuery's scheduled queries to automate regular email updates. For example, send a daily summary of key metrics:

-- Scheduled Query Configuration (set in the BigQuery UI)
-- Destination Table: None
-- Schedule: Daily at 8:00 AM

SELECT bigfunctions.us.send_mail(
    'team@yourcompany.com',
    'Daily Metrics Summary',
    FORMAT("""
        Total users: %d
        Total revenue: %f
        """,
        (SELECT COUNT(DISTINCT user_id) FROM `your_project.your_dataset.user_activity` WHERE _PARTITIONTIME = CURRENT_DATE()),
        (SELECT SUM(revenue) FROM `your_project.your_dataset.transactions` WHERE _PARTITIONTIME = CURRENT_DATE())
    ),
    null,
    null
);

4. User-Specific Notifications (within a script):

You could iterate through a result set and send customized emails to different recipients based on data in the table. For example, sending personalized product recommendations:

DECLARE done BOOLEAN DEFAULT FALSE;
DECLARE current_user STRUCT<email STRING, recommended_product STRING>;
DECLARE cur CURSOR FOR
  SELECT user_email, recommended_product
  FROM `your_project.your_dataset.product_recommendations`;

BEGIN
  OPEN cur;
  LOOP
    FETCH cur INTO current_user;
    IF done THEN
      LEAVE;
    END IF;
    SELECT bigfunctions.us.send_mail(
      current_user.email,
      'Personalized Product Recommendation',
      FORMAT('We recommend you check out: %s', current_user.recommended_product),
      null,
      null
    );
  END LOOP;
  CLOSE cur;
END;

These are just a few examples. The flexibility of send_mail allows it to be integrated into various data processing workflows within BigQuery, enhancing communication and automation. Remember to choose the correct regional dataset for the bigfunctions project based on your BigQuery data location.

Need help or Found a bug?

Get help using send_mail

The community can help! Engage the conversation on Slack

We also provide professional suppport.

Report a bug about send_mail

If the function does not work as expected, please

report a bug so that it can be improved.
or open the discussion with the community on Slack.

We also provide professional suppport.

Show your by adding a on