python - 错误 : "Required Parameter is Missing" - While Getting Anonymous Table in BigQuery

Question

我正在尝试查询由于在 BigQuery 中查询表数据集而创建的匿名表。我正在尝试使用 Jobs.get() 通过 Google BigQuery Analytics 中的示例查找匿名表名但我遇到了一个错误。

Google BigQuery Analytics 示例(第 209 页):

查询 1:

class QueryHandler(webapp2.RequestHandler):
credentials = GoogleCredentials.get_application_default()
service = discovery.build('bigquery', 'v2', credentials=credentials)
def query1(self):
    myquery = {'configuration': {
        'query': {
            'query': 'SELECT DISTINCT user_id FROM `app.mydataset.mytable`',
            'destinationTable': {
            'projectId': projectId,
            'datasetId': datasetId,
            'tableId': 'tableId'},
            'useLegacySql': False
                 }
            }
    }

    response = service.jobs().query(projectId=projectId, body=myquery).execute()
    job = service.jobs().get(**response['jobReference']).execute()
    # both versions of this variable (destination_table) produce the same error message
    # destination_table = job['configuration']['query']['destinationTable']
    destination_table = job['destinationTable']

    table = service.jobs().get(projectId=destination_table['projectId'],
                              datasetId=destination_table['datasetId'],
                              tableId=destination_table['tableId']).execute()
    return table

错误:

Internal Server Error

The server has either erred or is incapable of performing the requested operation.

....

HttpError: https://www.googleapis.com/bigquery/v2/projects/app_id/queries?alt=json returned "Required parameter is missing">

我的问题:

1. 为什么会出现此错误？ (我是按照例子来的，看不出我错过了什么)
2. 如何使用 Python 将第一个查询中的匿名表名传递到第二个查询中？例如:

查询 2:

def query2(self):
....
query: SELECT * FROM [anonymous table from query 1]

Answer 1

1. Why am I getting this error? (I followed the example and I can't see what I missed)

您的请求正文格式不正确 jobs.query API call .您不需要包装现有内容的“配置”或“查询”对象。

尝试:

myquery = {
    'query': 'SELECT DISTINCT user_id FROM `app.mydataset.mytable`',
    'useLegacySql': False
}

response = service.jobs().query(projectId=projectId, body=myquery).execute()

作为元评论，我们(BigQuery 团队)知道“缺少必需参数”错误消息过于模糊，无法调试，并会导致像这样令人困惑的情况。同样，无法识别的参数(如“配置”对象)会被简单地忽略，因此如果您在请求中错误地命名了一个参数，您很容易得到“缺少必需参数”错误。我们希望在未来的 API 更新中解决这个问题。

---

2. How can I pass an anonymous table name from the first query in the second query using Python?

您应该能够从 jobs.get response 中检索目标表，假设您传入预期的 jobReference。

但是，请注意，在另一个查询中使用此匿名表是一个 unsupported operation on anonymous results tables ，没有保证:

The query results from this method are saved to a temporary table that is deleted approximately 24 hours after the query is run. You can read this results table by calling either bigquery.tabledata.list(table_reference) or bigquery.jobs.getQueryResults(job_reference). The table and dataset name are non-standard, and cannot be used in any other APIs, as the behavior may be unpredictable.

相反，您最好传入一个明确的目标表，这只能通过 jobs.insert 来完成而不是 jobs.query。查找参数 configuration.query.destinationTable。

您可以将这些目标表放在 sets up an expiration time for contained tables 的数据集中在一定时间(一个小时、一天或...)后，如果您担心将它们保留一段时间。