Skip to main content

BigQuery Writer runtime considerations

Handling replayed DDL during recovery when Support Transaction is enabled

BigQuery does not natively support the execution of DDL statements within multi-statement transactions. Consequently, BigQuery Writer persists the E1P checkpoint for DDL events as a separate operation after the DDL statement has been successfully executed. As a result, if a failure occurs after the DDL is applied but before the corresponding checkpoint is persisted, the DDL event may be replayed during application recovery. Since the DDL has already been applied to the target, replaying an ALTER TABLE ... ADD COLUMN statement can lead to errors or inconsistent behavior unless you do the following.

DDL

Behavior during replay

Action required

ALTER TABLE ... ADD COLUMN

BigQuery Writer tries to apply the DDL in the target again. If the column already exists, it fails with the following error:

{
    "code": 400,
    "errors": [
        {
            "domain": "global",
            "message": "Field <fieldName> already exists in schema",
            "reason": "invalid"
        }
    ],
    "message": "Field <fieldName> already exists in schema",
    "status": "INVALID_ARGUMENT"
}

Configure FIELD_ALREADY_EXISTS as an ignorable exception code in BigQuery Writer. This ensures that column-addition DDLs replayed during application recovery do not interrupt processing if the target column already exists. The exception is logged to the exception store, and the application continues processing subsequent events.

BigQuery Writer known limitations

  • If you configure BigQuery Writer to exceed Google's quotas and limits, your application may halt. You can minimize the chances of this happening by configuring BigQuery Writer to use the Storage Write API. Quotas and limits are subject to change by Google without notice; numbers in this document are up to date as of July 2023. For the latest information, see BigQuery > Documentation > Reference > Quotas and limits.

  • When using the Storage Write API, if a single row contains approximately 5MB of data or more, the BigQuery Writer application may terminate. This is due to Storage Write API's AppendRows request size limit of 10MB (metadata may increase the request size to be twice that of the original data).

  • Arrays cannot be used as KeyColumns.

  • Due to limitations in Google BigQuery, when BigQuery Writer's input stream is the output of a Database Reader, Database Reader's RestartBehaviourOnILInterruption property is set to truncateTargetTableData or replaceTargetTableData, and BigQuery Writer's Streaming Upload property is True, initial load recovery may terminate or halt with an "entity not found" exception or "Could not perform a streaming insert" error. In this case, restarting the application after five to 30 minutes will typically resolve the problem.