Cloud Storage触发BigQuery MERGE查询的Cloud Function报错求助

问题场景 #

希望在Cloud Storage存储桶有文件上传时，触发Cloud Function执行BigQuery中的MERGE查询，但部署或运行时出现异常。

所用Cloud Function代码 #

from google.cloud import bigquery

def conn_to_bigquery(request) :

client = bigquery.Client()

# Perform a query.
QUERY = """
    MERGE INTO `vernal-isotope-370113.Pharmacie.docligne` as t  
USING `vernal-isotope-370113.Pharmacie.docligne_inserto` as s   
ON t.DO_Date  = s.DO_Date 
and t.Do_Type = s.DO_Type
and t.CT_Qualite = s.CT_Qualite
and t.Do_souche = s.DO_Souche
and t.Do_piece = s.DO_Piece
and t.AR_Design = s.AR_Design
and t.FA_CodeFamille = s.FA_CodeFamille
and t.FA_Intitule = s.FA_Intitule
and t.AR_Stat01 = s.AR_Stat01
and t.DL_Design = s.DL_Design
and t.FA_Central = s.FA_Central
and t.AR_Ref = s.AR_Ref
and t.AR_RefCompose = s.AR_RefCompose
and t.cbAR_Ref = s.cbAR_Ref
and t.cbAR_RefCompose = s.cbAR_RefCompose
and t.Pharmacie = s.Pharmacie
when matched then 
update set t.DL_MontantHT = s.DL_MontantHT, t.DL_MontantTTC = s.DL_MontantTTC
when not matched then 
INSERT (DO_Date,DO_Type,CT_Qualite,DO_Souche,  DO_Piece,AR_Design,FA_CodeFamille,FA_Intitule, AR_Stat01, DL_Design, FA_Central, AR_Ref, AR_RefCompose, cbAR_Ref, cbAR_RefCompose, DL_MontantHT, DL_MontantTTC, Pharmacie ) 
VALUES (s.DO_Date,s.DO_Type,s.CT_Qualite, s.DO_Souche, s.DO_Piece,s.AR_Design,s.FA_CodeFamille,s.FA_Intitule, s.AR_Stat01, s.DL_Design, s.FA_Central, s.AR_Ref, s.AR_RefCompose, s.cbAR_Ref, s.cbAR_RefCompose, s.DL_MontantHT, s.DL_MontantTTC, s.Pharmacie )
"""
query_job = client.query(QUERY)  # API request

return f"The query run successfully"

错误日志 #

Cloud FunctionsUpdateFunctionus-central1:docligne_merge_sqlkevin.rabemananjara93@gmail.com {@type: type.googleapis.com/google.cloud.audit.AuditLog, authenticationInfo: {…}, methodName: google.cloud.functions.v1.CloudFunctionsService.UpdateFunction, resourceName: projects/vernal-isotope-370113/locations/us-central1/functions/docligne_merge_sql, serviceName: cloudfunctions.googleapis.com, s…

错误成因 #

1. 代码语法错误：Python是缩进敏感语言，函数conn_to_bigquery定义后的所有代码（client = bigquery.Client()及以下）没有缩进，不属于函数体范围，会直接导致部署失败或运行时抛出语法异常。
2. 权限配置缺失：Cloud Function的默认服务账号可能未被授予足够的BigQuery权限（如执行MERGE查询所需的BigQuery Data Editor和BigQuery Job User角色），或没有Cloud Storage触发器的监听权限。
3. 触发器绑定问题：若为部署阶段触发的错误，可能存在Cloud Storage触发器与目标存储桶的绑定配置错误，比如事件类型选择不当，或存储桶未开放权限给Cloud Function服务账号。

修复方案 #

1. 修正代码缩进与错误处理 #

将函数内代码统一缩进4个空格，同时添加异常捕获逻辑便于调试，确保查询执行完成后再返回：

from google.cloud import bigquery

def conn_to_bigquery(request):
    try:
        client = bigquery.Client()

        QUERY = """
            MERGE INTO `vernal-isotope-370113.Pharmacie.docligne` as t  
            USING `vernal-isotope-370113.Pharmacie.docligne_inserto` as s   
            ON t.DO_Date  = s.DO_Date 
            and t.Do_Type = s.DO_Type
            and t.CT_Qualite = s.CT_Qualite
            and t.Do_souche = s.DO_Souche
            and t.Do_piece = s.DO_Piece
            and t.AR_Design = s.AR_Design
            and t.FA_CodeFamille = s.FA_CodeFamille
            and t.FA_Intitule = s.FA_Intitule
            and t.AR_Stat01 = s.AR_Stat01
            and t.DL_Design = s.DL_Design
            and t.FA_Central = s.FA_Central
            and t.AR_Ref = s.AR_Ref
            and t.AR_RefCompose = s.AR_RefCompose
            and t.cbAR_Ref = s.cbAR_Ref
            and t.cbAR_RefCompose = s.cbAR_RefCompose
            and t.Pharmacie = s.Pharmacie
            when matched then 
            update set t.DL_MontantHT = s.DL_MontantHT, t.DL_MontantTTC = s.DL_MontantTTC
            when not matched then 
            INSERT (DO_Date,DO_Type,CT_Qualite,DO_Souche,  DO_Piece,AR_Design,FA_CodeFamille,FA_Intitule, AR_Stat01, DL_Design, FA_Central, AR_Ref, AR_RefCompose, cbAR_Ref, cbAR_RefCompose, DL_MontantHT, DL_MontantTTC, Pharmacie ) 
            VALUES (s.DO_Date,s.DO_Type,s.CT_Qualite, s.DO_Souche, s.DO_Piece,s.AR_Design,s.FA_CodeFamille,s.FA_Intitule, s.AR_Stat01, s.DL_Design, s.FA_Central, s.AR_Ref, s.AR_RefCompose, s.cbAR_Ref, s.cbAR_RefCompose, s.DL_MontantHT, s.DL_MontantTTC, s.Pharmacie )
        """
        query_job = client.query(QUERY)
        query_job.result()  # 等待查询执行完成，避免函数提前返回导致查询中断
        return "The query ran successfully"
    except Exception as e:
        return f"Error executing query: {str(e)}"

2. 配置正确权限 #

• 进入Cloud Function详情页，找到默认服务账号（格式为PROJECT_ID@appspot.gserviceaccount.com）。
• 为该账号添加以下IAM角色：
  • BigQuery Data Editor：允许编辑BigQuery数据集和表。
  • BigQuery Job User：允许提交BigQuery查询作业。
  • Cloud Storage Object Viewer：允许监听存储桶事件。

3. 验证触发器配置 #

• 确认Cloud Function的触发器类型为Cloud Storage，事件类型选择对象创建（最终）。
• 检查绑定的存储桶是否正确，且存储桶权限已开放给Cloud Function服务账号。

内容的提问来源于stack exchange，提问作者Kevin Rabemananjara