1770986 - `org_mozilla_focus` and org_mozilla_klar` are missing `baseline_clients_last_seen`

Status: RESOLVED FIXED

(Data Platform and Tools :: General, task, P2)

Description

[Alessio Placitelli [:Dexter]]

While looking at bug 1770762 I found that both org_mozilla_focus and org_mozilla_klar(both Android) do not seem to have the autogeneratedbaseline_clients_last_seen`.

This seems to be related to 1770015, and it's currently blocking finalizing the implementation of 1770762 .

Why were this tables missing?
Is any other autogenerated table missing?
Can anyone help adding them?

Thanks!

Comment 1

[Arkadiusz Komarzewski [:akomar]]

As discussed in https://bugzilla.mozilla.org/show_bug.cgi?id=1770015 it seems like we neither currently have a process defined for creating derived glean usage tables, nor remember how this was done before. Long term solution to the problem is discussed in this bug.

I have run bqetl query generator with some modifications (in this case this check was not allowing init queries to be generated). It looks like for org_mozilla_focus and org_mozilla_klar following tables were missing:

org_mozilla_focus_beta_derived.baseline_clients_daily_v1
org_mozilla_focus_beta_derived.baseline_clients_first_seen_v1
org_mozilla_focus_beta_derived.baseline_clients_last_seen_v1
org_mozilla_focus_derived.baseline_clients_daily_v1
org_mozilla_focus_derived.baseline_clients_first_seen_v1
org_mozilla_focus_derived.baseline_clients_last_seen_v1
org_mozilla_focus_nightly_derived.baseline_clients_daily_v1
org_mozilla_focus_nightly_derived.baseline_clients_first_seen_v1
org_mozilla_focus_nightly_derived.baseline_clients_last_seen_v1
org_mozilla_klar_derived.baseline_clients_daily_v1
org_mozilla_klar_derived.baseline_clients_first_seen_v1
org_mozilla_klar_derived.baseline_clients_last_seen_v1

I have just created these tables.

Per https://bugzilla.mozilla.org/show_bug.cgi?id=1770015#c0 their views should get deployed after next run of probe_scraper.

These tables will start getting populated on the next run of copy_deduplicate DAG.

Comment 2

[Wesley Dawson [:whd]]

A mozdata views deploy failed with 404 Not found: Table moz-fx-data-shared-prod:focus_android_derived.clients_last_seen_joined_v1 was not found in location US, which is possibly related to this.

https://github.com/mozilla/bigquery-etl/commits/generated-sql/sql/moz-fx-data-shared-prod/focus_android_derived
https://github.com/mozilla/bigquery-etl/pull/2415

I suspect the creation of these tables has interacted with other deployment machinery to try to create other views into tables that don't exist.

Comment 3

[Arkadiusz Komarzewski [:akomar]]

Looks like these tables were missing:

focus_android_derived.clients_last_seen_joined_v1
klar_android_derived.clients_last_seen_joined_v1

I have just created them. I think this should unblock views deploy.

Points to revisit for long term solution:

• clients_last_seen_joined_v1 tables do not have init queries generated
• clients_last_seen_joined views generation seem to be missing a check for existence of referenced tables

Comment 4

[Alessio Placitelli [:Dexter]]

Arkadiusz, thank you for pushing on this. Looks like this process really needs some love :) After this bug gets fixed, would you consider chiming in on bug 1770015 commenting on what you had to do to enable the tables/views and how do you think the process should look like?

Thanks!

Comment 5

[Arkadiusz Komarzewski [:akomar]]

Yes, will do, let's first put this in order.

Comment 6

[Arkadiusz Komarzewski [:akomar]]

I have backfilled tables that I manually created here. I'll keep this bug open until we see successful mozdata views deploy.

Comment 7

[Wesley Dawson [:whd]]

I initiated a views deploy manually which succeeded.