Enhancement Request: Retry Mechanism for BigQuery Connector Streaming Inserts (InsertAll API)

a { text-decoration: none; color: #464feb; } tr th, tr td { border: 1px solid #e6e6e6; } tr th { background-color: #f5f5f5; }

Title

Enhancement Request – Implement Automatic Retry Mechanism in DataStage BigQuery Connector for InsertAll API Failures

Background

We use an IBM DataStage job to load millions of records from source systems into Google BigQuery using the BigQuery Connector in Insert (InsertAll API / Streaming Insert) mode.

Currently, if even a single connection interruption, network glitch, timeout, or transient error occurs during the load process, the entire DataStage job fails. This results in incomplete data processing and requires manual intervention to restart or rerun the load.

Current Behavior

• DataStage uses BigQuery Connector with InsertAll API.
• Large volumes of records are inserted into BigQuery.
• Intermittent network or connectivity issues can occur during long-running loads.
• The connector does not internally retry failed requests.
• A single connection failure causes the entire job to fail.
• Manual restart/reprocessing is required.

Discussion with Google and IBM Teams

We conducted multiple discussions with both Google Engineering and IBM Engineering teams.

Google's recommendation was that the DataStage BigQuery Connector should support a configurable retry mechanism for transient failures, similar to the retry capabilities available in many cloud-native ingestion tools.

Examples of transient failures include:

• Temporary network interruption
• Connection reset
• API timeout
• Service unavailable (HTTP 503)
• Rate limiting (HTTP 429)
• Temporary BigQuery service disruptions

These failures are typically recoverable through retry without requiring job termination.

Enhancement Requested

Introduce a built-in retry framework within the DataStage BigQuery Connector when using the InsertAll API.

Suggested capabilities:

Retry Configuration

• Retry Count (user configurable)
• Initial Retry Interval
• Exponential Backoff Support
• Maximum Retry Duration

Operational Benefits

• Improved reliability for large-volume loads
• Reduced production failures
• Reduced operational support effort
• Better alignment with Google Cloud best practices
• Enhanced resilience during temporary connectivity issues

Business Impact

Our environment processes tables containing millions of records daily.

Currently:

• A single transient connectivity issue causes job failure.
• Manual intervention is required. Even after multiple manual retry some jobs wont complete.
• Production SLA is impacted.
• High-volume BigQuery loads are difficult to maintain reliably.

The proposed enhancement would significantly improve stability and reduce operational overhead for customers using BigQuery as a target platform.

Expected Outcome

The DataStage BigQuery Connector should automatically retry transient InsertAll API failures and continue processing successfully whenever the underlying issue is temporary, thereby avoiding unnecessary job failures and improving overall reliability for large-scale BigQuery data loads.