🐛 [firestore-export-bigquery] Missing Materialized View

[christina-abridge]

Missing materialized view/Incorrect documentation regarding materialized view name

• Extension name: firestore-export-bigquery
• Extension version: 0.2.5

Describe the problem

I've encountered a discrepancy between the Firebase documentation for an extension and its actual behavior. I expect the extension to create the materialized views that ends in _latest, as previous versions.

The documentation suggests that the new materialized view created by the extension is named identically to the underlying table. However, upon installation, I verified that the changelog is a BigQuery table, not a view, and the old "latest" view no longer exists.

This contradicts my understanding that you cannot have two objects with the same name in BigQuery. https://github.com/firebase/extensions/blob/next/firestore-bigquery-export/README.md

Please see the documentation for reference.

[google-oss-bot]

This issue does not seem to follow the issue template. Make sure you provide all the required information.

[cabljac]

Hey! Thanks for opening this, we'll investigate and provide updates as we get them

[CorieW]

Hi @christina-abridge

Could you provide your extension configuration, please?

[christina-abridge]

Hi @CorieW, here are my configuration parameters. VIEW_TYPE is "materialized_non_incremental"

params = {
  BIGQUERY_PROJECT_ID           = data.google_project.current.project_id
  DATASET_LOCATION              = local.bigquery_dataset_location
  DATABASE_REGION               = local.firestore_database_location
  COLLECTION_PATH               = "provider-conversations/{id}/medication-orders"
  WILDCARD_IDS                  = false
  DATASET_ID                    = local.bigquery_dataset_id
  TABLE_ID                      = "provider_conversations_medication_orders"
  TABLE_PARTITIONING            = "DAY"
  TIME_PARTITIONING_FIELD       = "timestamp"
  CLUSTERING                    = "document_name,operation"
  BACKUP_COLLECTION             = "provider-conversations-medication-orders-dlq"
  USE_NEW_SNAPSHOT_QUERY_SYNTAX = "yes"
  EXCLUDE_OLD_DATA              = "yes"
  USE_COLLECTION_GROUP_QUERY    = "no"
  MAX_DISPATCHES_PER_SECOND     = "100"
  TIME_PARTITIONING_FIELD_TYPE  = "omit"
  LOG_FAILED_EXPORTS            = "yes"
  VIEW_TYPE                     = "materialized_non_incremental"
  ENABLE_REFRESH                = "yes"
  REFRESH_INTERVAL_MINUTES      = "60"
  MAX_STALENESS                 = "8:0:0"
}

[cabljac]

Hi @christina-abridge

Thanks for your patience on this!

It sounds like there are two separate issues here, just rephrasing to check I understand:

1. The docs are incorrect and miss the "latest" suffix on the table
2. The old latest view doesn't persist if the materialised view is enabled.

I think 1. is a simple docs update, thanks for raising this - We'll open a PR to resolve this.

I think 2. is by design, at the moment the extension only supports one type of view, so if materialised view is selected then the old view won't exist any more. For now I think we can make this clearer in the documentation as well.

We could also open a feature request to support both both views being available at once, if that's something that would be useful?

[christina-abridge]

Hi @cabljac, are you suggesting if we want to change the latest view from standard view to a materialized view, we need to drop the old view first? It seems that during the extension upgrade, the old view was deleted, but a new materialized view wasn't created. This does not sound like expected behavior. Wonder if there are other (i.e. permission) errors not surfaced during the extension installation. How can we debug this?

[cabljac]

Ah I misunderstood, this doesn't sound like expected behaviour. I didn't realise you meant the materialised view wasn't created. I'll reopen this for fresh investigation!

[cabljac]

Hi @christina-abridge

I think this could be misconfiguration of the extension -

, MAX_STALENESS param needs to be a full string like:

"INTERVAL "8:0:0" HOUR TO SECOND"

This is because 8:0:0 alone is ambiguous, it could be hour to second, or it could be e.g month to day.

references in the code:

https://github.com/firebase/extensions/blob/next/firestore-bigquery-export/extension.yaml#L535

I'll double check we reproduce the

A feature request could be to accept 8:0:0 format and have a reasonable interpretation like hours to seconds, ideally it doesn't need INTERVAL from the user I think.