bigfunctions > send_mail
send_mailΒΆ
Call or Deploy send_mail
?
β
You can call this send_mail
bigfunction directly from your Google Cloud Project (no install required).
- This
send_mail
function is deployed inbigfunctions
GCP project in 39 datasets for all of the 39 BigQuery regions. You need to use the dataset in the same region as your datasets (otherwise you may have a function not found error). - Function is public, so it can be called by anyone. Just copy / paste examples below in your BigQuery console. It just works!
- You may prefer to deploy the BigFunction in your own project if you want 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). Discover the framework
Public BigFunctions Datasets:
Region | Dataset |
---|---|
eu |
bigfunctions.eu |
us |
bigfunctions.us |
europe-west1 |
bigfunctions.europe_west1 |
asia-east1 |
bigfunctions.asia_east1 |
... | ... |
DescriptionΒΆ
Signature
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.
ExamplesΒΆ
1. Send email without file attached
select bigfunctions.eu.send_mail('contact@unytics.io', 'I love BigFunctions', 'Hey Paul, could you deploy more BigFunctions π?', null, null)
select bigfunctions.us.send_mail('contact@unytics.io', 'I love BigFunctions', 'Hey Paul, could you deploy more BigFunctions π?', null, null)
select bigfunctions.europe_west1.send_mail('contact@unytics.io', 'I love BigFunctions', 'Hey Paul, could you deploy more BigFunctions π?', null, null)
+---------+
| success |
+---------+
| true |
+---------+
2. Send email with plain text file attached
select bigfunctions.eu.send_mail('contact@unytics.io', 'I love BigFunctions', 'Hey Paul, could you deploy more BigFunctions π?', '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 π?', '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 π?', 'report.csv', 'col1,col2\nval1,val2\nval3,val4')
+---------+
| success |
+---------+
| true |
+---------+
3. Send email with excel file attached
select bigfunctions.eu.send_mail('contact@unytics.io', 'I love BigFunctions', 'Hey Paul, could you deploy more BigFunctions π?', 'report.xlsx', (select bigfunctions.eu.json2excel('[{"col1": "val1", "col2": "val2"}, {"col1": "val3", "col2": "val4"}]')))
select bigfunctions.us.send_mail('contact@unytics.io', 'I love BigFunctions', 'Hey Paul, could you deploy more BigFunctions π?', 'report.xlsx', (select bigfunctions.us.json2excel('[{"col1": "val1", "col2": "val2"}, {"col1": "val3", "col2": "val4"}]')))
select bigfunctions.europe_west1.send_mail('contact@unytics.io', 'I love BigFunctions', 'Hey Paul, could you deploy more BigFunctions π?', 'report.xlsx', (select bigfunctions.europe_west1.json2excel('[{"col1": "val1", "col2": "val2"}, {"col1": "val3", "col2": "val4"}]')))
+---------+
| success |
+---------+
| true |
+---------+
Need help using send_mail
?
The community can help! Engage the conversation on Slack
For professional suppport, don't hesitate to chat with us.
Found a bug using 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.
For professional suppport, don't hesitate to chat with us.
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.
Spread the wordΒΆ
BigFunctions is fully open-source. Help make it a success by spreading the word!