DataSource.Error: ADBC: UnknownError while running a query with left join

[CansuT]

I am getting this error after adding a data source that is a simple query that joins a view and a table from google bigquery. I tried adding each one of the sources (the view and the table) seperately as a datasource, and that goes without any problems. Only when adding a Google BigQuery source with a left join between these two tables in the SQL statement that I get this error. 

Full error

DataSource.Error: ADBC: UnknownError [] Cannot execute <ExecuteQueryInternalAsync>b__0 after 5 tries
Details:
DataSourceKind=GoogleBigQuery
DataSourcePath=GoogleBigQuery
AdbcErrors=[Table]

[v-kpoloju-msft]

Hi @CansuT,

Thank you for sharing the details. Also, thanks to @vojtechsima, for his inputs on this thread. From the error you have described, the issue is not with the individual table/view connections but specifically when running the LEFT JOIN through the new Google Big Query connector.

This happens because the connector uses Arrow Database Connectivity (ADBC), and complex SQL queries (like joins between views and tables) may fail if:
• The service account does not have all required roles,
• The billing project ID/region is not set properly, or
• The connector version does not support certain constructs.

To resolve this please go through the below mentioned steps those might help. Please make sure the service account you are using has the following roles in Big Query:
• Big Query Data Viewer
• Big Query Job User
• (If you are using Storage API) bigquery.readsessions.* permissions.

When connecting, set the Billing Project ID and verify that your datasets are in the same region (US/EU). Clear cached credentials and re-authenticate. If you are on an older build, upgrade to the latest Power BI Desktop (64-bit) and enable the new Google Big Query connector implementation (Implementation = 2.0).

Refer these links:
https://learn.microsoft.com/en-us/power-query/connectors/google-bigquery
https://cloud.google.com/bigquery/docs/access-control

If you still face the error after these steps, I recommend testing the query with a simple SELECT * FROM … LIMIT 10 first, and if it succeeds, try moving the JOIN logic into a Big Query view instead of passing it directly from Power BI. As a workaround, you can also use the Simba ODBC driver, which may handle the join query more reliably.

Let me know how it goes once you try the above.

Thank you.

[vojtechsima · Accepted solution]

Hello, @CansuT ,

You can try double-checking the selection to ensure that there are no duplicate column names, so use aliases for every column. Also, if possible, wrap your request inside Value.NativeQuery (with EnableFolding=true) function that should help with ensuring it folds back to source. Alternatively, if you have non-common data types for Power BI, you can try to normalise them in the select itself.

Native Query syntax:

Value.NativeQuery(GoogleBigQuery.Database(...), SqlAsText, null, [EnableFolding = false])

[danzrust]

I had the same error. My mistake that I had two columns with the same name.

However, the ADBC implementation has extremely poor messaging. Previous implementation would you show the errors from BQ API directly in PowerBI. The new ADBC just says "UnknownError...". Very unhelpful. 

I just edited the source to = GoogleBigQuery.Database([BillingProject="abc"]) and completely removed new Implemetnation=2.0. That way I was able to read the error from BQ API.

[Cedric_B]

So this is related to the Entra ID / Azure option for BigQuery connector.

Even if you don't select it when adding your source, it is overwritten with the preview feature "use new Google BigQuery connector implementation". 

[CansuT]

There weren't any duplicate/ambigious column names but using aliases everywhere solved this issue. 
It used to be okay to not use aliases when there the column only existed in one of the tables. Has there been a change on Google's side that led to this issue now?