Cloud Composer BigQueryOperator及定时查询间歇性触发jobInternalError问题排查求助

Problem Description #

We're facing random intermittent failures in multiple Cloud Composer BigQueryOperator tasks and some scheduled BigQuery queries. Retrying the failed task/query resolves the issue each time.

When querying INFORMATION_SCHEMA.JOBS_BY_PROJECT, we observe:

• error_result.reason = jobInternalError
• error_result.message = 任务执行期间遇到内部错误，无法成功完成。

Key details:

• Failed jobs still generate billed bytes, and child jobs show as successful.
• This issue started yesterday, with no changes made to related tasks/queries.

We're seeking answers to:

1. If other users have encountered similar issues recently
2. How to further debug this problem
3. How to get additional technical assistance

Example Error Details #

Cloud Composer Task Failure

Exception: BigQuery job failed. Final error was: {'reason': 'jobInternalError', 'message': '任务执行期间遇到内部错误，无法成功完成.'}

Full task details:

{'kind': 'bigquery#job', 'etag': 'cFex61A/InyX/L1+vy8GHw==', 'id': '#################:US.job_FIr-PFixKsdVvOJddG9zuIUeSj1i', 'selfLink': 'https://bigquery.googleapis.com/bigquery/v2/projects/#################/jobs/job_FIr-PFixKsdVvOJddG9zuIUeSj1i?location=US', 'user_email': '###########@developer.gserviceaccount.com', 'configuration': {'query': {'query': '############################################;
 ', 'priority': 'INTERACTIVE', 'useLegacySql': False}, 'jobType': 'QUERY'}, 'jobReference': {'projectId': '#################', 'jobId': 'job_FIr-PFixKsdVvOJddG9zuIUeSj1i', 'location': 'US'}, 'statistics': {'creationTime': '1629364585265', 'startTime': '1629364585352', 'endTime': '1629365366433', 'totalBytesProcessed': '135043690', 'query': {'totalBytesProcessed': '135043690', 'totalBytesBilled': '492830720', 'totalSlotMs': '192771', 'schema': {'fields': [{'name': 'total_rows', 'type': 'NUMERIC', 'mode': 'NULLABLE'}]}, 'statementType': 'SCRIPT'}, 'totalSlotMs': '192771', 'numChildJobs': '69'}, 'status': {'errorResult': {'reason': 'jobInternalError', 'message': '任务执行期间遇到内部错误，无法成功完成.'}, 'state': 'DONE'}

• Worker host: airflow-worker-##############
• Log file path: /home/airflow/gcs/logs/#########################/bq_build_reconciliation/2021-08-18T09:00:00+00:00.log

Scheduled Query Failure

2021-08-19T03:06:13.536907177Z Job scheduled_query_6148e950-0000-2b6a-89c9-94eb2c09dfdc (table ) failed with error INTERNAL: 任务执行期间遇到内部错误，无法成功完成.; JobID: ##########:scheduled_query_6148e950-0000-2b6a-89c9-94eb2c09dfdc

Troubleshooting & Next Steps #

Let’s break down solutions to your questions:

1. Have other users faced similar issues? #

Yes, intermittent jobInternalError is a well-documented transient issue in BigQuery, typically caused by temporary backend infrastructure hiccups. Start by checking the GCP Status Dashboard (found in your GCP Console's status section) for any ongoing or recent incidents in the BigQuery service, specifically the US region referenced in your job details. If there’s an active incident, GCP’s engineering team is already working to resolve it.

2. Further Debugging Steps #

Here are actionable steps to narrow down the root cause:

• Enable verbose logging: For Cloud Composer tasks, update your BigQueryOperator to explicitly set the location parameter to US and set the logging level to DEBUG to capture more granular details. For scheduled queries, enable enhanced logging in the BigQuery console under the query’s settings.
• Analyze job history & metrics: Use the BigQuery Job History page in the GCP Console to compare failed and successful runs. Look for patterns like consistent failure times, specific segments of your script, or spikes in resource usage (slot utilization, bytes processed). Since your job is a script (statementType: SCRIPT), dive into child job details to see if a specific sub-query is triggering the error.
• Isolate and test queries: Run the problematic script directly in the BigQuery console multiple times to see if failures reproduce. If they do, split the script into smaller sub-queries to pinpoint exactly which part is causing the issue.
• Check resource contention: Verify if your project is hitting slot limits or has high concurrent job volume during failure times. Use the BigQuery Resource Management page in the GCP Console to monitor slot usage.

3. Getting Technical Assistance #

If debugging doesn’t resolve the issue, here’s how to get more help:

• File a GCP Support Case: Navigate to your GCP Console > Support > Create Case. Include all the details you’ve shared (job IDs, full error messages, task/job JSON data, timestamps). Be sure to highlight that failed jobs are generating billed bytes despite showing errors—this is a critical detail that helps the support team prioritize and investigate.
• Engage with the GCP Community: The Google Cloud Community has a dedicated BigQuery section where users and experts share experiences and workarounds. Post your issue there to see if others have found solutions to similar problems.
• Review BigQuery Release Notes: Check the BigQuery Release Notes (available in the GCP Documentation section) for any recent updates or known issues that align with when your problem started.

Additional Mitigation #

Since retrying fixes the issue, you can automate this in Cloud Composer by adding retries with exponential backoff to your BigQueryOperator tasks. For example:

BigQueryOperator(
    task_id='bq_build_reconciliation',
    sql='your_query_script.sql',
    use_legacy_sql=False,
    location='US',
    retries=3,
    retry_delay=timedelta(seconds=30),
    # other parameters
)

This will automatically retry failed tasks and reduce manual intervention for transient errors.

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