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

[tranquangdu]

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?

[ms4446]

To drop and create a table in BigQuery with the Simba JDBC driver 1.3, you should avoid using transactions for DDL operations on permanent tables due to BigQuery's limitations. Instead, execute the DROP and CREATE statements separately without wrapping them in a transaction.

Here's an example in Java:

import java.sql.*;

public class DropAndCreateTable {

    public static void main(String[] args) throws Exception {

        // Create a new connection to BigQuery.
        String connectionString = "jdbc:bigquery://https://www.googleapis.com/bigquery/v2:443;ProjectId=YourProjectId;OAuthType=0;";
        Connection connection = DriverManager.getConnection(connectionString);

        // Drop the table (if it exists).
        Statement statement = connection.createStatement();
        statement.execute("DROP TABLE IF EXISTS my_dataset.my_table");

        // Create the table.
        statement.execute("CREATE TABLE my_dataset.my_table (id INT64, name STRING)");

        // Close the connection.
        connection.close();
    }
}

If you need to use transactions, consider using temporary tables. To create a temporary table in BigQuery, use:

CREATE TEMPORARY TABLE temp_table_name (id INT64, name STRING);

Temporary tables are session-scoped and will be automatically dropped at the end of the session.