1770015 - New glean app generated tables deployment pipeline requires a manual derived table deploy

Status: RESOLVED FIXED

(Data Platform and Tools :: General, defect, P1)

Description

[Wesley Dawson [:whd]]

https://mozilla.slack.com/archives/GE83ZMSAW/p1652873171608879

I started to write up some background on this before finding https://github.com/mozilla/bigquery-etl/issues/2413, which is effectively the same issue.

I believe what happened here is as follows:

1. Probe scraper repo is updated to add a new glean app: https://github.com/mozilla/probe-scraper/commit/b2d20ed42aba7dfa4d5d843ff5cac42a1617d7c9
2. The probe-scraper DAG runs, producing https://github.com/mozilla-services/mozilla-pipeline-schemas/commit/da26f512f0f322e06a297ffa05f2cc51d3eabc72
3. A schemas deployment is triggered

• This deploys pine* datasets and associated ingestion tables
• This also deploys views generated via generate_all_views, which I believe is just an alias for generate-sql now
• Notably, this pipeline does not create any derived tables, and Jenkins doesn't push the results of its generated-sql computation back to bigquery-etl generated branches

At this point I believe we enter a somewhat known state where the next run of bigquery-etl CI generate-sql should create via glean_usage the associated pine_derived.metrics_clients_daily_v1 SQL metadata. However, we presently don't have a way of ensuring all generated derived tables new views might reference are created before initiating a views deploy or CI check.

4. It's not clear to me yet how metrics_clients_daily_v1 is supposed to be created. If it is currently created manually, as might be inferred from the aforementioned bqetl issue, then that means that every new glean app that has a metrics_clients_daily_v1 table will cause the deployment pipeline to break (particularly views deploys and bqetl CI failures) until that manual step is taken, and we should consider how we can improve this process.

Comment 1

[Anna Scholtz [:ascholtz]]

Our current setup does not support automatically deploying derived tables. I believe in the past we've had someone manually run bqetl generate glean_usage and then deploy newly created tables via bqetl query schema deploy new_app_derived.*.
Certainly not ideal. One way to prevent things from breaking is having some kind of allowlist in bigquery-etl listing the Glean apps glean_usage is generating view and datasets for. Whenever a new app is added, someone also needs to add it there and deploy the derived tables.
Ideally, we'd want to find a way to completely automate this at some point.

Comment 2

[Wesley Dawson [:whd]]

Our current setup does not support automatically deploying derived tables. I believe in the past we've had someone manually run bqetl generate glean_usage and then deploy newly created tables via bqetl query schema deploy new_app_derived.*.
Certainly not ideal. One way to prevent things from breaking is having some kind of allowlist in bigquery-etl listing the Glean apps glean_usage is generating view and datasets for. Whenever a new app is added, someone also needs to add it there and deploy the derived tables.

This is the kind of manual DE workflow that is the subject of https://mozilla-hub.atlassian.net/browse/DSRE-782?focusedCommentId=547186. Now that automation pipelines / CI are blocked on creation of derived tables in some cases, I think we should prioritize a workaround like the one proposed here.

Ideally, we'd want to find a way to completely automate this at some point.

I filed https://mozilla-hub.atlassian.net/browse/DSRE-808 for my take on how we might eventually automate this (tl;dr metadata+terraform), but it's unlikely to happen any time soon.

Comment 3

[Wesley Dawson [:whd]]

It appears :relud created the specific table earlier today, so I believe CI and views deploys are now working. The next new glean app that has these auto-generated derived tables will presumably trigger the same issue. I'm not sure if the time between introduction of new glean apps with this property should affect the priority of introducing a more general workaround, but the last one before this week appears to have been October 2021.

Comment 4

[Jeff Klukas [:klukas] (UTC-4)]

At the time the generated glean_usage ETL was first implemented, it was definitely the intention that new apps would automatically have the baseline derived tables automatically created. The bqetl backfill command at one point was able to run init.sql in case the table didn't exist. I do believe it took two runs to fully create everything.

But I am guessing this is a regression as we have done a few refactors of the logic for this.

Comment 5

[Alessio Placitelli [:Dexter]]

While I agree it's ideal to have derived tables automatically generated whenever a new glean product is added to the pipeline (users request that via following step 4 of this checklist and filling a template bug), I believe there's an opportunity here to make some of the implicit steps more explicit while we get there.

For instance, I wasn't aware that Glean products got baseline_clients_daily (and other tables) automatically generated, nor I was (and am) familiar with the process of triggering the creation of that. With that said, I have a few questions for you all :-)

1. Is there a list or current document of the derived tables created for Glean products?
2. Who owns the creation of these tables? DE? SRE?
3. When does creation happen, ideally? When things get added to the probe-scraper?
   3a. How would this change when we'll move towards a more "push model"?

Comment 6

[Alessio Placitelli [:Dexter]]

Hey Arkadiusz!

Thank you for taking bug 1770986 and fixing it. Would you kindly document here what is it that you needed to do ?

Comment 7

[Arkadiusz Komarzewski [:akomar]]

(In reply to Alessio Placitelli [:Dexter] from comment #6)

Thank you for taking bug 1770986 and fixing it. Would you kindly document here what is it that you needed to do ?

In Bug 1770986 I created the following tables:

baseline_clients_daily_v1
baseline_clients_first_seen_v1
baseline_clients_last_seen_v1
clients_last_seen_joined_v1

I needed to do some code changes in bigquery-etl in order to generate init queries because query generation is coupled to view generation in the bqetl cli.

(In reply to Jeff Klukas [:klukas] (UTC-4) from comment #4)

At the time the generated glean_usage ETL was first implemented, it was definitely the intention that new apps would automatically have the baseline derived tables automatically created. The bqetl backfill command at one point was able to run init.sql in case the table didn't exist. I do believe it took two runs to fully create everything.

init.sql is not generated and run because of the referenced_table_exists check I linked to above.
At first glance I think if we decouple query/init generation from view generation, we'll have these tables created automatically each time bqetl backfill is run during copy_deduplicate.

Some notes from Bug 1770986 that are relevant if we go with automatic table creation:

• there is no init script generated for clients_last_seen_joined_v1 which we would like to have due to partitioning/clustering config
• clients_last_seen_joined view generation might be missing a check for existence of referenced tables (see comment)

Comment 8

[Wesley Dawson [:whd]]

Looks like we landed a new glean app today (org-mozilla-firefox-vpn) which may be affected by this: https://github.com/mozilla-services/mozilla-pipeline-schemas/commit/ddca384908750560a5d3a880de3e96e689355ed5. I saw a views deploy failure 404 Not found: Dataset moz-fx-data-shared-prod:mozilla_vpn_android was not found in location US but that could be a standard propagation issue (instead of one that requires manual DE intervention).

Comment 9

[Daniel Thorn [:relud]]

datasets were automatically deployed, but I'm manually initializing moz-fx-data-shared-prod.mozilla_vpn_android_derived.metrics_clients_daily_v1 because the daily command run by airflow doesn't automatically include partitioning and clustering configuration, even though they are in the metadata.yaml, and those are required on first run.

Comment 10

[GitHub Bugzilla PR Linker]

Attachment: Link to GitHub pull-request: https://github.com/mozilla/bigquery-etl/pull/3056

Comment 11

[Wesley Dawson [:whd]]

Not entirely sure why but views deploys failed today with google.api_core.exceptions.NotFound: 404 Not found: Table moz-fx-data-shared-prod:mozilla_vpn_derived.clients_last_seen_joined_v1 was not found in location US (mozdata deploy) and google.api_core.exceptions.NotFound: 404 Not found: Table moz-fx-data-shared-prod:mozillavpn.baseline_clients_daily was not found in location US (shared-prod views deploy). I re-ran a mozdata views deploy and got Table moz-fx-data-shared-prod:glean_dictionary_derived.clients_last_seen_joined_v1 was not found in location US instead. It's not clear to me if this is the same issue or something different, as I don't see a recent bqetl code change that would account for this.

If it continues to occur I will file a bqetl issue with more details.

Comment 12

[Daniel Thorn [:relud]]

this is essentially fixed by :ascholtz in https://github.com/mozilla/bigquery-etl/pull/3412 but there is still an allowlist that needs to be removed.

Comment 13

[Daniel Thorn [:relud]]

fixed in https://github.com/mozilla/bigquery-etl/pull/3423