Cannot Refresh Materialized View in EU multi-region

[Gneofy]

I have a very simple base table that belongs in a dataset with location to the EU multi region.
I have created a materialized view (also set in the EU multi-region) out of that base table and I am trying to manually refresh the view using the system procedure:

CALL BQ.REFRESH_MATERIALIZED_VIEW('PROJECT.DATASET.MATERIALIZED_VIEW');

Unfortunately this fails and I get the error message that the Dataset does not exist in the US

When I  recreate the same base table and materialized view in a dataset set to the US  the system procedure works fine and I can manually refresh just fine.

Not sure why this is happening and how to overcome this.

Thank you in advance,

A

[ms4446 · Accepted solution]

BigQuery currently does not offer a built-in mechanism for routing the refresh of materialized views. As a workaround, you can manually refresh your materialized views by specifying a location in the BigQuery UI or by using the --location flag when working with the command line.

To manually refresh a materialized view, you can invoke the BQ.REFRESH_MATERIALIZED_VIEW system procedure. When executed, BigQuery identifies and applies the changes from the base tables to the materialized view. It's important to note that you should avoid initiating multiple concurrent refreshes for the same materialized view, as only the first completed refresh will be successful.

For more comprehensive details and best practices on managing materialized views in BigQuery, you might want to refer to the official documentation.

[Gneofy]

Just in case someone stubbles upon this problem the solution is to  set the Appropriate Location Type inside the Query Settings before Executing the query.

[jsar]

This worked, thank you so much 😊