BigQuery - Simba JDBC Driver - DDL statements are not supported in a transaction

Question

I was using Simba JDBC driver for BigQuery version 1.2 to drop a table in BQ if not exist and create it again.

But since driver 1.3, the session/transaction thing is introduced, causing it fails to drop the table with this error:

Transaction control statements are supported only in scripts or sessions

After adding EnableSession=1 into the connection string, that error is gone but still BQ won't allow to drop/create permanent assets like table inside transaction I guess. It fails with this error:

{
  "code": 400,
  "errors": [
    {
      "domain": "global",
      "location": "q",
      "locationType": "parameter",
      "message": "DDL statements are not supported in a transaction, except for those creating or droping temporary tables or temporary functions.",
      "reason": "invalidQuery"
    }
  ],
  "message": "DDL statements are not supported in a transaction, except for those creating or droping temporary tables or temporary functions.",
  "status": "INVALID_ARGUMENT"
}

Even though my statements do not have transaction command, how could I achieve the same goal with this new driver?

Answer 1

This is a limitation of BigQuery. Only temporary tables can be created/deleted transactionally.

Maybe using "create or replace table" ddl outside of a transaction could work for your use-case