如何在GCP Cloud Functions中添加BigQuery数据CSV附件至每日邮件

要实现将BigQuery查询结果作为CSV附件发送，你需要完成以下关键修改：

1. 导入必要模块：添加CSV处理、内存文件操作以及SendGrid附件相关的类
2. 将BigQuery结果转换为CSV格式：在内存中生成CSV内容，无需写入本地文件
3. 创建并添加邮件附件：使用SendGrid的Attachment类配置附件信息，添加到邮件对象中

修改后的完整代码 #

from google.oauth2 import service_account
from google.cloud import bigquery
from sendgrid import SendGridAPIClient
from sendgrid.helpers.mail import Mail, Email, Attachment, FileContent, FileName, FileType, Disposition
import csv
import io
from datetime import datetime
from requests.exceptions import HTTPError

def send_email_attachment(request):
    project_id = '[MY-PROJECT-ID]'
    credentials = service_account.Credentials.from_service_account_file('[SERVICE-ACCOUNT-KEY]')

    client = bigquery.Client(project=project_id, credentials=credentials)

    query = '''
        SELECT *
        FROM `PROJECT_ID.DATASET.TABLE`
    '''

    results = client.query(query).result()

    # 将BigQuery结果转换为CSV格式
    headers = [field.name for field in results.schema]
    output = io.StringIO()
    writer = csv.writer(output)
    writer.writerow(headers)
    for row in results:
        writer.writerow(row.values())
    csv_content = output.getvalue().encode('utf-8')
    output.close()

    # 补充邮件HTML内容（原代码未定义该变量）
    html_content = "<p>您好，附件为每日BigQuery数据提取结果，请查收。</p>"
    message = Mail(
        to_emails="person@email.com",
        from_email=Email('steve-h@example.com', "Steve H."),
        subject="This is the data extract - attached",
        html_content=html_content
    )
    message.add_bcc("steve-h@example.com")

    # 创建并添加CSV附件
    attachment = Attachment()
    attachment.file_content = FileContent(csv_content.hex())
    attachment.file_name = FileName(f"bigquery_data_{datetime.now().strftime('%Y%m%d')}.csv")
    attachment.file_type = FileType("text/csv")
    attachment.disposition = Disposition("attachment")
    message.attachment = attachment

    try:
        sg = SendGridAPIClient('[API-KEY]')
        response = sg.send(message)
        return f"Email sent at {datetime.now()} with status code {response.status_code}"
    except HTTPError as e:
        return e.message

关键说明 #

• 内存生成CSV：用io.StringIO在内存中生成CSV，适配Cloud Functions无服务器环境，避免本地文件操作
• 动态文件名：通过日期格式化生成唯一文件名，方便区分每日数据
• 附件编码：将CSV内容转为base64格式（hex()实现），符合SendGrid的附件要求，同时指定正确的文件类型和附件属性

内容的提问来源于stack exchange，提问作者Steve Hartland Johnson