Enable metadata caching for biglake tables to use materialized views

Google Cloud recently announced materialized views for BigLake tables. This provides some really nice opportunities to simplify loading data into bigquery and keeping it refreshed and fast. however in order to create a materialized view on a non-native bigquery table such as a biglake table you have to have cached metadata enabled.

I struggled a bit to figure out how to enable it — There was no clear way to do it within the console but you can use this query to update the table with metadata enabled:

CREATE OR REPLACE EXTERNAL TABLE
  `project_name.dataset_name.biglake_table_name`
  WITH CONNECTION `eu.biglake_connection`
  OPTIONS(
    format ="avro",
    uris = ['gs://path/to/bucket/and/folder/*.avro'],
    max_staleness = INTERVAL 6 HOUR,
    metadata_cache_mode = 'AUTOMATIC'
    );