Automating BigQuery exports to an email

CodePudding user response：

This might be challenging to accomplish. Here is what I would do.

1. Set up BigQuery export to a Cloud Storage bucket. More details here.
2. Set up a trigger when finalizing objects on the Cloud Storage bucket to run a Cloud Function which will send the mail.

You can either send the entire file by mail but it incurs high costs for networking and all if the file size is big. What you can do is generate a signed url and send that URL over the email. If you want to have granular access to the objects, you can give permission to the email using ACL and then send the link to the object in the mail.

CodePudding user response：

Bigquery Remote Functions are great for this use case.

I created an open-source project, BigFunctions, to simplify the deployment of these functions.

I specifically created one here to send the result of a query in an excel file attached in an email (via gmail). It can be called by:

call bigfunctions.eu.export_to_gmail('bigfunctions.samples.natality', '[email protected]', 'Finance Figures for Today', 'Hey Paul, you fill find the figures in the attached excel file. Enjoy