Connect a Bigquery "external data source"

Hello,
I am trying to perform queries that join between a bigquery dataset and a mysql table, by way of bigquery external connections: https://cloud.google.com/bigquery/external-data-sources .

I managed to set this up in bigquery, and the queries - with joins - work fine.

However, I am having difficulties setting this up in metabase.
I have created a service account, and have successfully added other bigquery datasets to metabase before.
However, I cannot find what to enter in the "Dataset ID" field in the metabase "add database" form for a bq "external datasource".

How could I go about accomplishing this?

Thank you in advance!

image

Hi @fk-s
I have never heard of this before, but they keep on adding functionality, so perhaps it's new.
If you run a query against this and look in your query logs, how does the query look?
As far as I know, there's always a dataset and table, and sometimes a project id, example:
select * from project.dataset.table

Hey @flamber,
thank you for the prompt reply!

I'm not sure what you mean by query log, but here's a few screenshots

Slightly more detail

@fk-s Oh, so it's federated queries? How would you connect using something like DBeaver.io?
From what I know, you need to allow access to a project, where you can then run the same queries (no matter if you only have an empty dataset). You won't have the tables available in the GUI. For that, you would need to create Views in your dataset, which does the external_query.
Kinda similar to a public project: https://github.com/metabase/metabase/issues/2273

I tried with dbeaver, and was not able to access the external data source directly from there either.
Creating scheduled views however works great, thank you very much for the suggestion!

With that I would mark this issue as solved :heavy_check_mark: