Unable to select other Google BigQuery projects or run cross project queries

[mkrigba]

Description

I believe that these issues are related but if not, you may want to separate them into two separate issues.

Connection Settings

All projects avaliable to the OAuth token are listed in "Additional project(s)" field

Unable to select other Google BigQuery projects

Only the project that is listed in the "Project" field of the Connection Settings can be selected in the "Choose catalog/schema" dialog.

1. Click on Catalog (currently populated with default project from Connnection Settings
2. Double click on a different project in the Choose catalog/schema dialog
3. Selected project doesn't change:

Unable to run cross project query

Even though I'm able to browse the schema details of other projects in the Database Navigator window, I'm not able to run queries against other projects with fully qualified project.dataset.tablename references in the query

Returns the following error:

Details

eclipse.buildId=unknown java.version=17.0.6 java.vendor=Eclipse Adoptium BootLoader constants: OS=win32, ARCH=x86_64, WS=win32, NL=en Framework arguments: -eclipse.keyring C:\Users\mkrigbaum\AppData\Roaming\DBeaverData\secure\secure_storage Command-line arguments: -os win32 -ws win32 -arch x86_64 org.jkiss.dbeaver.model Error Wed Jan 03 09:10:37 MST 2024 SQL Error [100032] [HY000]: [Simba][BigQueryJDBCDriver](100032) Error executing query job. Message: BIGQUERY_API_ERR org.jkiss.dbeaver.model.sql.DBSQLException: SQL Error [100032] [HY000]: [Simba][BigQueryJDBCDriver](100032) Error executing query job. Message: BIGQUERY_API_ERR at org.jkiss.dbeaver.model.impl.jdbc.exec.JDBCStatementImpl.executeStatement(JDBCStatementImpl.java:133) at org.jkiss.dbeaver.ui.editors.sql.execute.SQLQueryJob.executeStatement(SQLQueryJob.java:607) at org.jkiss.dbeaver.ui.editors.sql.execute.SQLQueryJob.lambda$1(SQLQueryJob.java:510) at org.jkiss.dbeaver.model.exec.DBExecUtils.tryExecuteRecover(DBExecUtils.java:190) at org.jkiss.dbeaver.ui.editors.sql.execute.SQLQueryJob.executeSingleQuery(SQLQueryJob.java:517) at org.jkiss.dbeaver.ui.editors.sql.execute.SQLQueryJob.extractData(SQLQueryJob.java:971) at org.jkiss.dbeaver.ui.editors.sql.SQLEditor$QueryResultsContainer.readData(SQLEditor.java:4135) at org.jkiss.dbeaver.ui.controls.resultset.ResultSetJobDataRead.lambda$0(ResultSetJobDataRead.java:123) at org.jkiss.dbeaver.model.exec.DBExecUtils.tryExecuteRecover(DBExecUtils.java:190) at org.jkiss.dbeaver.ui.controls.resultset.ResultSetJobDataRead.run(ResultSetJobDataRead.java:121) at org.jkiss.dbeaver.ui.controls.resultset.ResultSetViewer$ResultSetDataPumpJob.run(ResultSetViewer.java:5150) at org.jkiss.dbeaver.model.runtime.AbstractJob.run(AbstractJob.java:105) at org.eclipse.core.internal.jobs.Worker.run(Worker.java:63) Caused by: java.sql.SQLException: [Simba][BigQueryJDBCDriver](100032) Error executing query job. Message: BIGQUERY_API_ERR at com.simba.googlebigquery.googlebigquery.client.requests.jobs.JobsQueryRequest.throwException(Unknown Source) at com.simba.googlebigquery.googlebigquery.client.requests.AbstractRequestWithRetry.executeWithRetry(Unknown Source) at com.simba.googlebigquery.googlebigquery.client.queryclient.JobsQueryClient.executeQuery(Unknown Source) at com.simba.googlebigquery.googlebigquery.client.BQClient.executeQuery(Unknown Source) at com.simba.googlebigquery.googlebigquery.dataengine.BQAbstractExecutor.execute(Unknown Source) at com.simba.googlebigquery.googlebigquery.dataengine.BQSQLExecutor.execute(Unknown Source) at com.simba.googlebigquery.jdbc.common.SStatement.executeNoParams(Unknown Source) at com.simba.googlebigquery.jdbc.common.BaseStatement.execute(Unknown Source) at org.jkiss.dbeaver.model.impl.jdbc.exec.JDBCStatementImpl.execute(JDBCStatementImpl.java:330) at org.jkiss.dbeaver.model.impl.jdbc.exec.JDBCStatementImpl.executeStatement(JDBCStatementImpl.java:131) ... 12 more Caused by: com.simba.googlebigquery.googlebigquery.client.exceptions.JobExecutionErrorException: [Simba][BigQueryJDBCDriver](100032) Error executing query job. Message: BIGQUERY_API_ERR ... 22 more Caused by: com.simba.googlebigquery.support.exceptions.GeneralException: BIGQUERY_API_ERR at com.simba.googlebigquery.googlebigquery.client.requests.AbstractRequestWithRetry.shouldRetry(Unknown Source) at com.simba.googlebigquery.googlebigquery.client.requests.jobs.JobsQueryRequest.shouldRetry(Unknown Source) at com.simba.googlebigquery.googlebigquery.client.requests.AbstractRequestWithRetry.executeWithRetry(Unknown Source) at com.simba.googlebigquery.googlebigquery.client.queryclient.JobsQueryClient.executeQuery(Unknown Source) at com.simba.googlebigquery.googlebigquery.client.BQClient.executeQuery(Unknown Source) at com.simba.googlebigquery.googlebigquery.dataengine.BQAbstractExecutor.execute(Unknown Source) at com.simba.googlebigquery.googlebigquery.dataengine.BQSQLExecutor.execute(Unknown Source) at com.simba.googlebigquery.jdbc.common.SStatement.executeNoParams(Unknown Source) at com.simba.googlebigquery.jdbc.common.BaseStatement.execute(Unknown Source) at org.jkiss.dbeaver.model.impl.jdbc.exec.JDBCStatementImpl.execute(JDBCStatementImpl.java:330) at org.jkiss.dbeaver.model.impl.jdbc.exec.JDBCStatementImpl.executeStatement(JDBCStatementImpl.java:131) at org.jkiss.dbeaver.ui.editors.sql.execute.SQLQueryJob.executeStatement(SQLQueryJob.java:607) at org.jkiss.dbeaver.ui.editors.sql.execute.SQLQueryJob.lambda$1(SQLQueryJob.java:510) at org.jkiss.dbeaver.model.exec.DBExecUtils.tryExecuteRecover(DBExecUtils.java:190) at org.jkiss.dbeaver.ui.editors.sql.execute.SQLQueryJob.executeSingleQuery(SQLQueryJob.java:517) at org.jkiss.dbeaver.ui.editors.sql.execute.SQLQueryJob.extractData(SQLQueryJob.java:971) at org.jkiss.dbeaver.ui.editors.sql.SQLEditor$QueryResultsContainer.readData(SQLEditor.java:4135) at org.jkiss.dbeaver.ui.controls.resultset.ResultSetJobDataRead.lambda$0(ResultSetJobDataRead.java:123) at org.jkiss.dbeaver.model.exec.DBExecUtils.tryExecuteRecover(DBExecUtils.java:190) at org.jkiss.dbeaver.ui.controls.resultset.ResultSetJobDataRead.run(ResultSetJobDataRead.java:121) at org.jkiss.dbeaver.ui.controls.resultset.ResultSetViewer$ResultSetDataPumpJob.run(ResultSetViewer.java:5150) at org.jkiss.dbeaver.model.runtime.AbstractJob.run(AbstractJob.java:105)

If I switch the "Project" in the Connection Settings to the Project containing the table, I can run the same query successfully:

DBeaver Version

Community Edition 23.3.0.202312122044 (I also have Lite license that has the same problem)

Operating System

Windows 10 Education 21H2

Database and driver

Database: Google BigQuery Driver: Simba's JDBC Driver for Google BigQuery com.simba.googlebigquery.jdbc42.Driver

Steps to reproduce

No response

Additional context

No response

[LonwoLonwo]

Hello @mkrigba

We added limitations for catalog changing for BigQuery. Here is the reason: https://github.com/dbeaver/dbeaver/issues/13916#issuecomment-1020948777

You can use fully qualified names (https://github.com/dbeaver/dbeaver/issues/13916#issuecomment-1020286818) to execute your query for another project from your current project.

The best option for now is to create separate connections for different objects.

[mkrigba]

@LonwoLonwo Thank you so much for your speedy response.

As for my first issue of not being able to select other projects, I had seen #13916 but I guess didn't interpret that as reason for limiting changing catalogs. If this is a known limitation, the suggestion to create separate connections seems to be a reasonable solution.

My second issue still remains though in that I'm unable to run this query unless the selected catalog/project is prj-dev-ebi-data-raw

select tblstg.table_schema, tblstg.table_name, tblstg.total_rows, tblstg.storage_last_modified_time
from prj-dev-ebi-data-raw.`region-us-west3`.INFORMATION_SCHEMA.TABLE_STORAGE tblstg
where tblstg.total_rows > 0;

When run from a connection with the project set to prj-sb-ebi-products-healthera, the above query returns the following error:

SQL Error [100032] [HY000]: [Simba][BigQueryJDBCDriver](100032) Error executing query job. Message: BIGQUERY_API_ERR

Details

org.jkiss.dbeaver.model.sql.DBSQLException: SQL Error [100032] [HY000]: [Simba][BigQueryJDBCDriver](100032) Error executing query job. Message: BIGQUERY_API_ERR at org.jkiss.dbeaver.model.impl.jdbc.exec.JDBCStatementImpl.executeStatement(JDBCStatementImpl.java:133) at org.jkiss.dbeaver.ui.editors.sql.execute.SQLQueryJob.executeStatement(SQLQueryJob.java:607) at org.jkiss.dbeaver.ui.editors.sql.execute.SQLQueryJob.lambda$1(SQLQueryJob.java:510) at org.jkiss.dbeaver.model.exec.DBExecUtils.tryExecuteRecover(DBExecUtils.java:190) at org.jkiss.dbeaver.ui.editors.sql.execute.SQLQueryJob.executeSingleQuery(SQLQueryJob.java:517) at org.jkiss.dbeaver.ui.editors.sql.execute.SQLQueryJob.extractData(SQLQueryJob.java:971) at org.jkiss.dbeaver.ui.editors.sql.SQLEditor$QueryResultsContainer.readData(SQLEditor.java:4135) at org.jkiss.dbeaver.ui.controls.resultset.ResultSetJobDataRead.lambda$0(ResultSetJobDataRead.java:123) at org.jkiss.dbeaver.model.exec.DBExecUtils.tryExecuteRecover(DBExecUtils.java:190) at org.jkiss.dbeaver.ui.controls.resultset.ResultSetJobDataRead.run(ResultSetJobDataRead.java:121) at org.jkiss.dbeaver.ui.controls.resultset.ResultSetViewer$ResultSetDataPumpJob.run(ResultSetViewer.java:5150) at org.jkiss.dbeaver.model.runtime.AbstractJob.run(AbstractJob.java:105) at org.eclipse.core.internal.jobs.Worker.run(Worker.java:63) Caused by: java.sql.SQLException: [Simba][BigQueryJDBCDriver](100032) Error executing query job. Message: BIGQUERY_API_ERR at com.simba.googlebigquery.googlebigquery.client.requests.jobs.JobsQueryRequest.throwException(Unknown Source) at com.simba.googlebigquery.googlebigquery.client.requests.AbstractRequestWithRetry.executeWithRetry(Unknown Source) at com.simba.googlebigquery.googlebigquery.client.queryclient.JobsQueryClient.executeQuery(Unknown Source) at com.simba.googlebigquery.googlebigquery.client.BQClient.executeQuery(Unknown Source) at com.simba.googlebigquery.googlebigquery.dataengine.BQAbstractExecutor.execute(Unknown Source) at com.simba.googlebigquery.googlebigquery.dataengine.BQSQLExecutor.execute(Unknown Source) at com.simba.googlebigquery.jdbc.common.SStatement.executeNoParams(Unknown Source) at com.simba.googlebigquery.jdbc.common.BaseStatement.execute(Unknown Source) at org.jkiss.dbeaver.model.impl.jdbc.exec.JDBCStatementImpl.execute(JDBCStatementImpl.java:330) at org.jkiss.dbeaver.model.impl.jdbc.exec.JDBCStatementImpl.executeStatement(JDBCStatementImpl.java:131) ... 12 more Caused by: com.simba.googlebigquery.googlebigquery.client.exceptions.JobExecutionErrorException: [Simba][BigQueryJDBCDriver](100032) Error executing query job. Message: BIGQUERY_API_ERR ... 22 more Caused by: com.simba.googlebigquery.support.exceptions.GeneralException: BIGQUERY_API_ERR at com.simba.googlebigquery.googlebigquery.client.requests.AbstractRequestWithRetry.shouldRetry(Unknown Source) at com.simba.googlebigquery.googlebigquery.client.requests.jobs.JobsQueryRequest.shouldRetry(Unknown Source) at com.simba.googlebigquery.googlebigquery.client.requests.AbstractRequestWithRetry.executeWithRetry(Unknown Source) at com.simba.googlebigquery.googlebigquery.client.queryclient.JobsQueryClient.executeQuery(Unknown Source) at com.simba.googlebigquery.googlebigquery.client.BQClient.executeQuery(Unknown Source) at com.simba.googlebigquery.googlebigquery.dataengine.BQAbstractExecutor.execute(Unknown Source) at com.simba.googlebigquery.googlebigquery.dataengine.BQSQLExecutor.execute(Unknown Source) at com.simba.googlebigquery.jdbc.common.SStatement.executeNoParams(Unknown Source) at com.simba.googlebigquery.jdbc.common.BaseStatement.execute(Unknown Source) at org.jkiss.dbeaver.model.impl.jdbc.exec.JDBCStatementImpl.execute(JDBCStatementImpl.java:330) at org.jkiss.dbeaver.model.impl.jdbc.exec.JDBCStatementImpl.executeStatement(JDBCStatementImpl.java:131) at org.jkiss.dbeaver.ui.editors.sql.execute.SQLQueryJob.executeStatement(SQLQueryJob.java:607) at org.jkiss.dbeaver.ui.editors.sql.execute.SQLQueryJob.lambda$1(SQLQueryJob.java:510) at org.jkiss.dbeaver.model.exec.DBExecUtils.tryExecuteRecover(DBExecUtils.java:190) at org.jkiss.dbeaver.ui.editors.sql.execute.SQLQueryJob.executeSingleQuery(SQLQueryJob.java:517) at org.jkiss.dbeaver.ui.editors.sql.execute.SQLQueryJob.extractData(SQLQueryJob.java:971) at org.jkiss.dbeaver.ui.editors.sql.SQLEditor$QueryResultsContainer.readData(SQLEditor.java:4135) at org.jkiss.dbeaver.ui.controls.resultset.ResultSetJobDataRead.lambda$0(ResultSetJobDataRead.java:123) at org.jkiss.dbeaver.model.exec.DBExecUtils.tryExecuteRecover(DBExecUtils.java:190) at org.jkiss.dbeaver.ui.controls.resultset.ResultSetJobDataRead.run(ResultSetJobDataRead.java:121) at org.jkiss.dbeaver.ui.controls.resultset.ResultSetViewer$ResultSetDataPumpJob.run(ResultSetViewer.java:5150) at org.jkiss.dbeaver.model.runtime.AbstractJob.run(AbstractJob.java:105)

BTW, I've also tried the following variations and get the same error:

select tblstg.table_schema, tblstg.table_name, tblstg.total_rows, tblstg.storage_last_modified_time
from **`**prj-dev-ebi-data-raw**`**.`region-us-west3`.INFORMATION_SCHEMA.TABLE_STORAGE tblstg
where tblstg.total_rows > 0;

select tblstg.table_schema, tblstg.table_name, tblstg.total_rows, tblstg.storage_last_modified_time
from prj-dev-ebi-data-raw.INFORMATION_SCHEMA.TABLE_STORAGE tblstg
where tblstg.total_rows > 0;

select tblstg.table_schema, tblstg.table_name, tblstg.total_rows, tblstg.storage_last_modified_time
from `prj-dev-ebi-data-raw`.INFORMATION_SCHEMA.TABLE_STORAGE tblstg
where tblstg.total_rows > 0;

[mkrigba]

Also, since I'm creating separate connections for each project, is there a way to suppress display of all of the other projects in the Database Navigator pane?

[LonwoLonwo]

About the Navigator - did you try not to add values in the Additional Projects field in your connection settings? You can provide just the Project name.

[mkrigba]

Yes, here's a screenshot. On the left is the Database Navigator window for the prj-dev-ebi-data-raw connection. On the right is the connection settings window for that same connection. You can see that there are no additional projects in the connection settings window but the additional projects show up in the navigator window.

[mkrigba]

The extraneous projects showing up in the navigator window are just an annoyance and therefore lower priority for me.

The inability to run cross project queries with fully qualified FROM clauses is a higher priority for me.

[mkrigba]

Should I be able to run a cross project query? Please see https://github.com/dbeaver/dbeaver/issues/22319#issuecomment-1877225003 for details including the error I receive.

[LonwoLonwo]

I can't say at the moment if it is available or not. This may be a server limitation. Maybe - the driver limitation.

[Destrolaric]

@mkrigba Hi, I didn't find any info on how the driver should behave for cross-platform queries. Unfortunately, we don't have an environment to test cross-project behavior queries, for now, so it may take some time.

[LonwoLonwo]

The extraneous projects showing up in the navigator window

Could you please create a separate issue for this case?

[serjiokov]

Thanks for your requests and feedback!

So, several my attempts to execute queries with or without region specification lead to the same error from the driver side. Some queries can be executed successfully related to DB structure, but others related to billed details are failed with driver Exception.

org.jkiss.dbeaver.model.sql.DBSQLException: SQL Error [100032] [HY000]: [Simba][BigQueryJDBCDriver](100032) Error executing query job. Message: BIGQUERY_API_ERR
    at org.jkiss.dbeaver.model.impl.jdbc.exec.JDBCStatementImpl.executeStatement(JDBCStatementImpl.java:133)
    at org.jkiss.dbeaver.ui.editors.sql.execute.SQLQueryJob.executeStatement(SQLQueryJob.java:614) 

I think we have to return to the question of usage API. seems Simba JDBC implementation has a lot of restrictions.

[serjiokov]

So, my guess, the query for multiple projects is not supported on the driver level.