python - 使用python将BigQuery查询结果写入表

我想将存储在txt文件中的BigQuery查询结果写入BigQuery表。
我将查询文本作为变量传递到以下函数中，但出现以下错误：

error_info = method +''+ url）google.cloud.exceptions.BadRequest：400缺少必需的参数（POST https://www.googleapis.com/bigquery/v2/projects/myproject/jobs）

我想念什么？

功能：

from google.cloud import bigquery
import uuid

def async_query(query, dataset_id, dest_table, project_Id):


    # configuration json
    query_data = {
        "configuration": {
            "query": {
                "query": query,
                "defaultDataset": dataset_id,
                "allowLargeResults": True,
                "destinationTable": {
                    "projectId": project_Id,
                    "datasetId": dataset_id,
                    "tableId": dest_table
                    },
                "createDisposition": 'CREATE_IF_NEEDED',
                "writeDisposition": 'WRITE_TRUNCATE'
          }
        }
    }

    client = bigquery.Client()
    query_job = client.run_async_query(str(uuid.uuid4()), query_data)
    query_job.use_legacy_sql = False
    query_job.begin()
    wait_for_job(query_job)

    # Drain the query results by requesting a page at a time.
    query_results = query_job.results()
    page_token = None

    while True:
        rows, total_rows, page_token = query_results.fetch_data(
            max_results=10,
            page_token=page_token)

        for row in rows:
            print(row)

        if not page_token:
            break
def wait_for_job(job):
    while True:
        job.reload()  # Refreshes the state via a GET request.
        if job.state == 'DONE':
            if job.error_result:
                raise RuntimeError(job.errors)
            return
        time.sleep(1)

最佳答案

您可以按以下方式在配置中修复defaultDataset

# configuration json
query_data = {
    "configuration": {
        "query": {
            "query": query,
            "defaultDataset": {
                "projectId": project_Id,
                "datasetId": dataset_id
                },
            "allowLargeResults": True,
            "destinationTable": {
                "projectId": project_Id,
                "datasetId": dataset_id,
                "tableId": dest_table
                },
            "createDisposition": 'CREATE_IF_NEEDED',
            "writeDisposition": 'WRITE_TRUNCATE'
      }
    }
}

注意：“ projectId”：project_Id在defaultDataset中是可选的


整个defaultDataset也是可选的，在您的情况下，您可以像下面这样忽略它

# configuration json
query_data = {
    "configuration": {
        "query": {
            "query": query,
            "allowLargeResults": True,
            "destinationTable": {
                "projectId": project_Id,
                "datasetId": dataset_id,
                "tableId": dest_table
                },
            "createDisposition": 'CREATE_IF_NEEDED',
            "writeDisposition": 'WRITE_TRUNCATE'
      }
    }
}

在configuration.query.defaultDataset中查看更多