Problems with updating - BigQuery related

Get Help
1

Hello.
We have been running Metabase for a few years now and we had no problems.
We are running version 0.41.1.
We tried upgrading to 0.42.0 but something went wrong. Then (after rolling back to our working 0.41.1 version) we tried going to 0.41.6 and the same thing happened - we then tried moving to 0.41.2 with the same results.
In all cases, everything works except for all BigQuery databases.

Here are the troubleshooting logs:

...

"--> driver.bigquery_cloud_sdk$execute_bigquery$fn__78279.invoke(bigquery_cloud_sdk.clj:161)"]}

{:status :failed,
:class java.util.concurrent.ExecutionException,
:error "com.google.cloud.bigquery.BigQueryException: Unrecognized name: core_kpis.total_ads_stats at [11:5]",
:stacktrace
["java.base/java.util.concurrent.FutureTask.report(FutureTask.java:122)"
"java.base/java.util.concurrent.FutureTask.get(FutureTask.java:191)"
"clojure.core$deref_future.invokeStatic(core.clj:2304)"
"clojure.core$future_call$reify__8477.deref(core.clj:6976)"
"clojure.core$deref.invokeStatic(core.clj:2324)"
"clojure.core$deref.invoke(core.clj:2310)"
"--> driver.bigquery_cloud_sdk$execute_bigquery.invokeStatic(bigquery_cloud_sdk.clj:149)"

...

"com.google.api.client.googleapis.services.AbstractGoogleClientRequest.execute(AbstractGoogleClientRequest.java:565)"
"com.google.cloud.bigquery.spi.v2.HttpBigQueryRpc.queryRpc(HttpBigQueryRpc.java:635)"
"com.google.cloud.bigquery.BigQueryImpl$34.call(BigQueryImpl.java:1255)"
"com.google.cloud.bigquery.BigQueryImpl$34.call(BigQueryImpl.java:1252)"
"com.google.api.gax.retrying.DirectRetryingExecutor.submit(DirectRetryingExecutor.java:105)"
"com.google.cloud.RetryHelper.run(RetryHelper.java:76)"
"com.google.cloud.RetryHelper.runWithRetries(RetryHelper.java:50)"
"com.google.cloud.bigquery.BigQueryImpl.queryRpc(BigQueryImpl.java:1251)"
"com.google.cloud.bigquery.BigQueryImpl.query(BigQueryImpl.java:1239)"
"--> driver.bigquery_cloud_sdk$execute_bigquery$fn__78279.invoke(bigquery_cloud_sdk.clj:161)"],
:card_id 420,
:context :question,
:error
"400 Bad Request\nPOST https://www.googleapis.com/bigquery/v2/projects/sheets-and-ds-project/queries\n{\n "code" : 400,\n "errors" : [ {\n "domain" : "global",\n "location" : "q",\n "locationType" : "parameter",\n "message" : "Unrecognized name: core_kpis.total_ads_stats at [11:5]",\n "reason" : "invalidQuery"\n } ],\n "message" : "Unrecognized name: core_kpis.total_ads_stats at [11:5]",\n "status" : "INVALID_ARGUMENT"\n}",
:row_count 0,
:running_time 0,
:data {:rows [], :cols []}}

Any ideas?

2

Hi @lialios
You should at a minimum upgrade to 0.41.6, but 0.42.1 would work as well.
Sounds like you are seeing this issue, fixed in 0.41.2 - check the "Project ID override", see the comments here: https://github.com/metabase/metabase/issues/18725

3

Hello and thank you for your answer.

Upgrades to 0.41.2, 0.41.6 and 0.42.0 all fail the same way. Between each upgrade we rollback to 0.41.1 which works ok - we have no problems with big query when running on 0.41.1.

I can't see the way the fix you mention relates to our problem. Can you please elaborate?

Thanks.

4

@lialios I don't know which "Project ID" you are using in your Service Account JSON, nor if you have defined a "Project ID Override", but the problem relates to that.

Your FROM-clauses includes the project.dataset.table, but should not include the project.

5

Hello again @flamber and thank you for your answer.

I read the comments in the fix again and, although I can understand what is being said, I am not sure about what's actually happening.

So, in order to test this, we created a new table with only one record and two fields, a Date and a string. Then we connected the database as such:

03
032098×1598 320 KB

The question we tried out is this one:
select * from sheets-and-ds-project.test_mb_versions.mytable where 1=1 [[AND {{mdate}}]]

22
222098×1598 298 KB

We tried the following scenarios, both under 0.41.1 and 0.41.2. We tried having the project ID in both the query and the database (in admin - as shown above), in neither, in the query and not the database, and not in the query but in the database. 8 cases in total.

Results

0.41.1

Things worked only when Project ID existed in both the query and the database OR in neither. Otherwise (when the project ID exists in one of the two places) then the query doesn't work.

0.41.2

No matter what happens with the Project ID field in the database (admin), things only work when the project ID is missing from the query, i.e.

54
542098×1598 312 KB

So, if we go and update all our custom questions in order to remove the project id, then things will be ok. The bad thing is that we have SO.MANY.QUESTIONS. We could go into metabase's database and try changing things there but that's plan Z.

Is there something we can do so that we don't have to change all our questions by hand?

Note that the Project ID in the admin section is the same as the one in the service account JSON.

Thank you in advance.

6

@lialios You had "Project ID (override)" filled out, but it's actually the same value as the Service Account JSON, then you should never had filled out the "Project ID (override)" originally.
The change in 0.41.2 was made to fix a problem, when people switched from the old to the new driver.

You should make a backup of the application database and do a search-replace on the application database table report_card.dataset_query

7

Hello and thanks again @flamber

So, just to confirm: We should end up having removed the Project ID from everywhere.
That is, the database definition in admin should have the Project ID field empty and the project ID shouldn't appear in the queries.
Is this correct?

8

Hello again @flamber

You suggestion worked, all is ok now.
Thank you!