generateChangeLog with a regional dataset and a view causes "Dataset X:X was not found in location US"

[gabrieldeal]

Community Note

• Please vote on this issue by adding a 👍 reaction
  to the original issue to help the community and maintainers prioritize this request
• Please do not leave "+1" or "me too" comments, they generate extra noise for issue followers and do
  not help prioritize the request
• If you are interested in working on this issue or have submitted a pull request, please leave a comment.

Debug Output

https://gist.github.com/gabrielmdeal/f82a75154fefbade72061d95c6d6fd2d

The gist also contains the Repro_Table.json and Repro_View.sql files that are referenced below.

Panic Output

N/A

Steps to Reproduce

TLDR: liquibase generateChangeLog fails if my dataset is regional and contains a view. It works for a multi-region dataset.

1. Install:
   • The liquibase/liquibase:4.22 image from hub.docker.com
   • https://storage.googleapis.com/simba-bq-release/jdbc/SimbaBigQueryJDBC42-1.3.2.1003.zip
   • https://github.com/liquibase/liquibase-bigquery/releases/download/v4.22.0/liquibase-bigquery-4.22.0.jar
2. Create a Big Query dataset named Gabriel_Liquibase in the us-central1 region. Not multi-region.
3. Create a liquibase service account with the BigQuery Admin, BigQuery Data Owner and BigQuery Metadata Viewer roles. (I know this is overkill, but I knee-jerk added more roles when I ran into this error.)
4. gcloud iam service-accounts keys create "liquibase-service-account-credentials.json" --iam-account "liquibase@redacted-project-id.iam.gserviceaccount.com"
5. Use the JSON and SQL from the above gist to create a table and a view:

   bq mk Gabriel_Liquibase.Repro_Table Repro_Table.json
   bq mk --use_legacy_sql=false --view "$(cat Repro_View.sql)" Gabriel_Liquibase.Repro_View
   

6. Try to generate a change log via liquibase generateChangeLog --url 'jdbc:bigquery://https://www.googleapis.com/bigquery/v2:443;ProjectId=redacted-project-id;DefaultDataset=Gabriel_Liquibase;OAuthType=0;OAuthServiceAcctEmail=liquibase@redacted-project-id.iam.gserviceaccount.com;OAuthPvtKeyPath=liquibase-service-account-credentials.json' --logLevel DEBUG --changeLogFile changelog.json --driver com.simba.googlebigquery.jdbc.Driver

Expected Behavior

A changelog is created with my table and view.

Actual Behavior

The changelog is not created. liquibase generateChangeLog fails with the below error (see gist for full output). If I use console.cloud.google.com to run the SQL from the error, I do get the expected view definition.

The liquibase generateChangeLog command succeeds if I delete my view.

[2023-05-16 21:39:35] SEVERE [liquibase.integration] Error executing SQL SELECT view_definition FROM Gabriel_Liquibase.INFORMATION_SCHEMA.VIEWS WHERE table_name='Repro_View' AND table_schema='Gabriel_Liquibase' AND table_catalog='redacted-project-id';: [Simba][BigQueryJDBCDriver](100032) Error executing query job. Message: Not found: Dataset redacted-project-id:Gabriel_Liquibase was not found in location US
  - Caused by: 404 Not Found
POST https://bigquery.googleapis.com/bigquery/v2/projects/redacted-project-id/queries
{
  "code": 404,
  "errors": [
    {
      "domain": "global",
      "message": "Not found: Dataset redacted-project-id:Gabriel_Liquibase was not found in location US",
      "reason": "notFound"
    }
  ],
  "message": "Not found: Dataset redacted-project-id:Gabriel_Liquibase was not found in location US",
  "status": "NOT_FOUND"
}

[vincbon]

liquibase-bigquery sets the driver parameter Location with the default value of US if not present, as shown here. I'm not sure if it is really necessary, as the driver docs seems to indicate otherwise.

You can override this parameter in the driver url like --url 'jdbc:bigquery://https://www.googleapis.com/bigquery/v2:443;ProjectId=redacted-project-id;DefaultDataset=Gabriel_Liquibase;Location=us-central1'. Note that in this case you will only be able to query datasets in the given region.

[filipelautert]

Fixed by #301 , to be included in next release. Thanks for the issue report!