I have a Partitioned base table my_project.my_dataset.base_table with a usage_start_time column ( used to partition this base table). And I also have a materialized view which references this base table. In my use case, I am periodically (every hour) refreshing my base table with a script like this :
CREATE OR REPLACE TABLE
`my_project.my_dataset.base_table` PARTITION BY DATE(
usage_start_time
) AS
SELECT *
FROM
`different_project.different_dataset.base_table_view`
Everytime the above script runs, I get an error like this while querying the materialized view:
The Google BigQuery service was unable to process this request.
Materialized view my_project:my_dataset.my_view references table my_project.my_dataset.base_table which was deleted and recreated. The view must be deleted and recreated as well.
Unable to connect to the Google BigQuery server "". Check that the server is running and that you have access privileges to the requested database.
If I DROP and CREATE this Materialized view again, I don't get this error. Can someone please help me understand why this behaviour is happening ?
Also, I tried to do the same thing with an un-partitioned base table and I did not get this issue then.
I used the bq cli command bq query --destination_table my_project.my_dataset.base_table --use_legacy_sql=false --replace=true 'select * from `different_project.different_dataset.base_table_view`' and it does not invalidate the MV.
I searched through the GCP Docs and it says that if the base table is partitioned then consider partitioning the Materialized view. I haven't partitioned my Materialized view, so could this be a reason ?
Thank you so much if you read till here. I would really appreciate an involved discussion on this.