我有以下代码,在第一次删除目标中的数据后,我将使用这些代码从一个bigquery表中选择数据并插入到另一个big查询表中:
#delete
sql = """
delete from `b`
where _date_ = '2019-10-24'
"""
query_job = client.query(sql)
results = query_job.result()
#insert
sql = """
insert into `b` (col1,col2)
select col1, col2 from a
where a._date_ = '2019-10-24'
"""
query_job = client.query(sql)
results = query_job.result()
它运行成功。然而,我想参数化我跑步的日期,所以我遵循了https://cloud.google.com/bigquery/docs/parameterized-queries并将其更改为:
query_params = [
bigquery.ScalarQueryParameter("_date_", "STRING", "2019-10-24")
]
job_config = bigquery.QueryJobConfig()
job_config.query_parameters = query_params
#delete
sql = """
delete from `b`
where _date_ = @_date_
"""
query_job = client.query(sql, job_config=job_config)
results = query_job.result()
#insert
sql = """
insert into `b` (col1,col2)
select col1, col2 from a
where a._date_ = @_date_
"""
query_job = client.query(sql, job_config=job_config)
results = query_job.result()
现在由于插入错误而失败:
无法在具有DML语句的作业中设置目标表
删除操作正常!
我在谷歌上搜索了一下,发现了在job_config
中指定目标表时发生此错误的示例(例如,无法在BigQuery中使用DML语句的作业中设置目标表(,但我没有这样做,所以我有点困惑。
为什么我提供查询参数会导致代码失败并出现此错误?
问题是运行DML语句会更新传递给client.query
的job_config
中的目标表。你需要像这样在两者之间重置它:
query_params = [
bigquery.ScalarQueryParameter("_date_", "STRING", "2019-10-24")
]
job_config = bigquery.QueryJobConfig()
job_config.query_parameters = query_params
#delete
sql = """
delete from `b`
where _date_ = @_date_
"""
query_job = client.query(sql, job_config=job_config)
results = query_job.result()
# reset config
job_config = bigquery.QueryJobConfig()
job_config.query_parameters = query_params
#insert
sql = """
insert into `b` (col1,col2)
select col1, col2 from a
where a._date_ = @_date_
"""
query_job = client.query(sql, job_config=job_config)
results = query_job.result()