BigQuery : Python Client not capturing errors found in audit logs when running multiple statements in a single query

huangapple go评论64阅读模式
英文:

BigQuery : Python Client not capturing errors found in audit logs when running multiple statements in a single query

问题

BigQuery:Python客户端在运行多个语句的单个查询时(例如在创建表语句之前声明变量语句),未捕获审核日志中发现的错误。

查询中存在变量声明的语法问题,Python客户端无法捕获错误并将状态标记为完成。然而,审计日志中的检查显示了错误。

BQ客户端代码片段:

query_job = self.client.query(query)
print(dir(query_job))
# print(query_job.exception())
with suppress(TypeError):
    ##https://github.com/googleapis/python-bigquery/issues/1459
    exc = query_job.exception()
    if exc:
        raise exc
while (query_job.state != 'DONE'):
    print("Job {} is currently in state {}".format(query_job.job_id, query_job.state))
    if query_job.errors is not None:
        raise Exception("Bigquery job failed with error {}".format(query_job.errors))
    query_job = self.client.get_job(query_job.job_id)
    print(query_job.total_bytes_processed)
    print(query_job.errors)
    time.sleep(wait)

日志显示以下错误:

jobStatus: {
error: {
code: 11
message: "Query error: Unrecognized name: varname at [4:56]"
}
state: "DONE"
}
serviceName: "bigquery.googleapis.com"

有没有一种方法来捕获这种错误/异常?

英文:

BigQuery : Python Client not capturing errors found in audit logs , when running multiple statements in a single query (for eg a declare variable statement before a create table statement ).

There was a syntax issue in variable declaration in the query , The python client is not able to catch the error and it deems the status as done . However inspection of logs in the auditlog trail shows the error

BQ Client Code Snippet:

            query_job = self.client.query(query) 
            print(dir(query_job))
            # print(query_job.exception())
            with suppress(TypeError):
                ##https://github.com/googleapis/python-bigquery/issues/1459
                exc = query_job.exception()
                if exc:
                    raise exc
            while (query_job.state != 'DONE'):
                
                print("Job {} is currently in state {}".format(query_job.job_id, query_job.state))

                if query_job.errors is not None:
                    raise Exception("Bigquery job failed with error {}".format(query_job.errors)) 

                query_job = self.client.get_job(query_job.job_id)
                print(query_job.total_bytes_processed)
                print(query_job.errors)
                time.sleep(wait)

The log trail shows below error :

jobStatus: {
error: {
code: 11
message: "Query error: Unrecognized name: varname at [4:56]"
}
state: "DONE"
}}}}
serviceName: "bigquery.googleapis.com"

Is there a way to capture such errors / exceptions ?

答案1

得分: 1

你可以通过使用 .errors() 函数的 message 字段来获取详细错误信息。

try:
    job = client.query(query)
    job.result()
except:
    for error in job.errors:
        print('在执行查询时发生以下错误: {}'.format(error['message']))
英文:

You can get the detailed error by using the message field of .errors() function return.

try:
    job = client.query(query)
    job.result()
except:
    for error in job.errors:
        print('Following error occurred while executing the query: {}'.format(error['message']))

答案2

得分: 0

添加到 @Sakshi Gatyan 的回答中,通过将参数 (create_session=True) 添加到查询作业配置中,有助于识别包含多个语句的整个查询作为单个会话,并等待 query_job.result()。

query_job = self.client.query(query, job_config=bq.QueryJobConfig(create_session=True))
query_job = self.client.get_job(query_job.job_id)

query_job.result()
英文:

To add to @Sakshi Gatyan's answer , adding parameter (create_session=True) to query job config helped in identifying the entire query containing multiple statements as a single session along with waiting for query_job.result()

query_job = self.client.query(query,job_config=bq.QueryJobConfig(create_session=True))
query_job = self.client.get_job(query_job.job_id)

query_job.result()

huangapple
  • 本文由 发表于 2023年2月16日 15:06:32
  • 转载请务必保留本文链接:https://go.coder-hub.com/75468851.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定