使用Legacy SQL在BigQuery配置查询目标表遇错，求正确配置方法

Let's break down what's causing your error and how to fix it quickly.

The Root Cause #

Your mistake comes from incorrectly constructing the destination table reference. When you use dest_dataset_ref.table('datasetId:mydestTable'), you're passing a fully qualified table name (dataset:table) to the table() method—but this method only expects the raw table name.

Since dest_dataset_ref already points to your datasetId dataset, combining it with datasetId:mydestTable creates an invalid dataset ID like datasetId.datasetId:mydestTable, which violates BigQuery's naming rules (hence the 400 error). The printed TableReference you shared confirms this: it shows duplicate project/dataset prefixes, which shouldn't be there.

Corrected Code Implementation #

Here's how to properly set up your destination table:

bigquery_client = bigquery.Client.from_service_account_json(config.ZF_FILE)
job_config = bigquery.QueryJobConfig()
job_config.use_legacy_sql = True
job_config.allow_large_results = True

# 1. Correctly reference your target dataset
# If your client is already linked to the right project, just pass the dataset ID
dest_dataset_ref = bigquery_client.dataset('datasetId')
# 2. Pass ONLY the table name to the table() method (no dataset prefix)
dest_table_ref = dest_dataset_ref.table('mydestTable')

job_config.destination = dest_table_ref

# Ensure your Legacy SQL query uses the correct source table format
# Replace with your full source table path if needed (project:dataset.table)
query =""" SELECT abc FROM [{0}] LIMIT 10 """.format(mySourcetable_name)

query_job = bigquery_client.query(query, job_config=job_config)
# Wait for the job to complete (optional but recommended for large jobs)
query_job.result()

Additional Notes #

• If you need to explicitly specify the project for the dataset (e.g., if your client defaults to a different project), modify the dataset reference like this:

  dest_dataset_ref = bigquery_client.dataset('datasetId', project='your_project_name')
  

• For your Legacy SQL query, make sure mySourcetable_name is in the format projectId:datasetId.tableName (or just datasetId.tableName if using the default project). The [{0}] syntax is correct for Legacy SQL table references.

内容的提问来源于stack exchange，提问作者Shivkumar kondi