dataops-ci-bot
commented
2 weeks ago Integration report for "Generate event monitoring view for all apps with events ping"
sql.diff
Click to expand!
```diff Only in /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/accounts_cirrus_derived: event_monitoring_live_v1 Only in /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/burnham_derived: event_monitoring_live_v1 Only in /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/firefox_desktop_background_defaultagent_derived: event_monitoring_live_v1 Only in /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/firefox_desktop_background_update_derived: event_monitoring_live_v1 Only in /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/glean_dictionary_derived: event_monitoring_live_v1 Only in /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/gleanjs_docs_derived: event_monitoring_live_v1 Only in /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/monitor_cirrus_derived: event_monitoring_live_v1 Only in /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/mozilla_lockbox_derived: event_monitoring_live_v1 Only in /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/mozilla_mach_derived: event_monitoring_live_v1 Only in /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/mozillavpn_backend_cirrus_derived: event_monitoring_live_v1 Only in /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/mozphab_derived: event_monitoring_live_v1 Only in /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/org_mozilla_connect_firefox_derived: event_monitoring_live_v1 Only in /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/org_mozilla_firefoxreality_derived: event_monitoring_live_v1 Only in /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/org_mozilla_ios_lockbox_derived: event_monitoring_live_v1 Only in /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/org_mozilla_mozregression_derived: event_monitoring_live_v1 Only in /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/org_mozilla_tv_firefox_derived: event_monitoring_live_v1 Only in /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/treeherder_derived: event_monitoring_live_v1 diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/accounts_cirrus_derived/event_monitoring_live_v1/materialized_view.sql /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/accounts_cirrus_derived/event_monitoring_live_v1/materialized_view.sql --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/accounts_cirrus_derived/event_monitoring_live_v1/materialized_view.sql 1970-01-01 00:00:00.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/accounts_cirrus_derived/event_monitoring_live_v1/materialized_view.sql 2024-06-18 14:18:50.000000000 +0000 @@ -0,0 +1,76 @@ +CREATE MATERIALIZED VIEW +IF + NOT EXISTS `moz-fx-data-shared-prod.accounts_cirrus_derived.event_monitoring_live_v1` + OPTIONS + (enable_refresh = TRUE, refresh_interval_minutes = 60) + AS + SELECT + DATE(submission_timestamp) AS submission_date, + TIMESTAMP_ADD( + TIMESTAMP_TRUNC(submission_timestamp, HOUR), + -- Aggregates event counts over 60-minute intervals + INTERVAL(DIV(EXTRACT(MINUTE FROM submission_timestamp), 60) * 60) MINUTE + ) AS window_start, + TIMESTAMP_ADD( + TIMESTAMP_TRUNC(submission_timestamp, HOUR), + INTERVAL((DIV(EXTRACT(MINUTE FROM submission_timestamp), 60) + 1) * 60) MINUTE + ) AS window_end, + event.category AS event_category, + event.name AS event_name, + event_extra.key AS event_extra_key, + normalized_country_code AS country, + 'Mozilla Accounts (Cirrus)' AS normalized_app_name, + channel, + version, + -- Access experiment information. + -- Additional iteration is necessary to aggregate total event count across experiments + -- which is denoted with "*". + -- Some clients are enrolled in multiple experiments, so simply summing up the totals + -- across all the experiments would double count events. + CASE + experiment_index + WHEN ARRAY_LENGTH(ping_info.experiments) + THEN "*" + ELSE ping_info.experiments[SAFE_OFFSET(experiment_index)].key + END AS experiment, + CASE + experiment_index + WHEN ARRAY_LENGTH(ping_info.experiments) + THEN "*" + ELSE ping_info.experiments[SAFE_OFFSET(experiment_index)].value.branch + END AS experiment_branch, + COUNT(*) AS total_events + FROM + ( + SELECT + submission_timestamp, + events, + normalized_country_code, + client_info.app_channel AS channel, + client_info.app_display_version AS version, + ping_info + FROM + `moz-fx-data-shared-prod.accounts_cirrus_live.events_v1` + ) + CROSS JOIN + UNNEST(events) AS event, + -- Iterator for accessing experiments. + -- Add one more for aggregating events across all experiments + UNNEST(GENERATE_ARRAY(0, ARRAY_LENGTH(ping_info.experiments))) AS experiment_index + LEFT JOIN + UNNEST(event.extra) AS event_extra + WHERE + DATE(submission_timestamp) >= "2024-06-18" + GROUP BY + submission_date, + window_start, + window_end, + event_category, + event_name, + event_extra_key, + country, + normalized_app_name, + channel, + version, + experiment, + experiment_branch diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/accounts_cirrus_derived/event_monitoring_live_v1/metadata.yaml /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/accounts_cirrus_derived/event_monitoring_live_v1/metadata.yaml --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/accounts_cirrus_derived/event_monitoring_live_v1/metadata.yaml 1970-01-01 00:00:00.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/accounts_cirrus_derived/event_monitoring_live_v1/metadata.yaml 2024-06-18 14:33:16.000000000 +0000 @@ -0,0 +1,19 @@ +friendly_name: Event Monitoring Live +description: |- + Materialized view of experimentation related events + coming from . +owners: +- ascholtz@mozilla.com +- akomar@mozilla.com +labels: + materialized_view: true + owner1: ascholtz + owner2: akomar +bigquery: null +workgroup_access: +- role: roles/bigquery.dataViewer + members: + - workgroup:mozilla-confidential +references: + materialized_view.sql: + - moz-fx-data-shared-prod.accounts_cirrus_live.events_v1 diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/bedrock_derived/event_monitoring_live_v1/materialized_view.sql /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/bedrock_derived/event_monitoring_live_v1/materialized_view.sql --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/bedrock_derived/event_monitoring_live_v1/materialized_view.sql 2024-06-18 14:16:00.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/bedrock_derived/event_monitoring_live_v1/materialized_view.sql 2024-06-18 14:18:51.000000000 +0000 @@ -50,7 +50,7 @@ client_info.app_display_version AS version, ping_info FROM - `moz-fx-data-shared-prod.bedrock_live.events_v1` + `moz-fx-data-shared-prod.bedrock_live.non_interaction_v1` UNION ALL SELECT submission_timestamp, @@ -60,7 +60,7 @@ client_info.app_display_version AS version, ping_info FROM - `moz-fx-data-shared-prod.bedrock_live.non_interaction_v1` + `moz-fx-data-shared-prod.bedrock_live.interaction_v1` UNION ALL SELECT submission_timestamp, @@ -70,7 +70,7 @@ client_info.app_display_version AS version, ping_info FROM - `moz-fx-data-shared-prod.bedrock_live.interaction_v1` + `moz-fx-data-shared-prod.bedrock_live.events_v1` ) CROSS JOIN UNNEST(events) AS event, diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/burnham_derived/event_monitoring_live_v1/materialized_view.sql /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/burnham_derived/event_monitoring_live_v1/materialized_view.sql --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/burnham_derived/event_monitoring_live_v1/materialized_view.sql 1970-01-01 00:00:00.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/burnham_derived/event_monitoring_live_v1/materialized_view.sql 2024-06-18 14:18:51.000000000 +0000 @@ -0,0 +1,76 @@ +CREATE MATERIALIZED VIEW +IF + NOT EXISTS `moz-fx-data-shared-prod.burnham_derived.event_monitoring_live_v1` + OPTIONS + (enable_refresh = TRUE, refresh_interval_minutes = 60) + AS + SELECT + DATE(submission_timestamp) AS submission_date, + TIMESTAMP_ADD( + TIMESTAMP_TRUNC(submission_timestamp, HOUR), + -- Aggregates event counts over 60-minute intervals + INTERVAL(DIV(EXTRACT(MINUTE FROM submission_timestamp), 60) * 60) MINUTE + ) AS window_start, + TIMESTAMP_ADD( + TIMESTAMP_TRUNC(submission_timestamp, HOUR), + INTERVAL((DIV(EXTRACT(MINUTE FROM submission_timestamp), 60) + 1) * 60) MINUTE + ) AS window_end, + event.category AS event_category, + event.name AS event_name, + event_extra.key AS event_extra_key, + normalized_country_code AS country, + 'Burnham' AS normalized_app_name, + channel, + version, + -- Access experiment information. + -- Additional iteration is necessary to aggregate total event count across experiments + -- which is denoted with "*". + -- Some clients are enrolled in multiple experiments, so simply summing up the totals + -- across all the experiments would double count events. + CASE + experiment_index + WHEN ARRAY_LENGTH(ping_info.experiments) + THEN "*" + ELSE ping_info.experiments[SAFE_OFFSET(experiment_index)].key + END AS experiment, + CASE + experiment_index + WHEN ARRAY_LENGTH(ping_info.experiments) + THEN "*" + ELSE ping_info.experiments[SAFE_OFFSET(experiment_index)].value.branch + END AS experiment_branch, + COUNT(*) AS total_events + FROM + ( + SELECT + submission_timestamp, + events, + normalized_country_code, + client_info.app_channel AS channel, + client_info.app_display_version AS version, + ping_info + FROM + `moz-fx-data-shared-prod.burnham_live.events_v1` + ) + CROSS JOIN + UNNEST(events) AS event, + -- Iterator for accessing experiments. + -- Add one more for aggregating events across all experiments + UNNEST(GENERATE_ARRAY(0, ARRAY_LENGTH(ping_info.experiments))) AS experiment_index + LEFT JOIN + UNNEST(event.extra) AS event_extra + WHERE + DATE(submission_timestamp) >= "2024-06-18" + GROUP BY + submission_date, + window_start, + window_end, + event_category, + event_name, + event_extra_key, + country, + normalized_app_name, + channel, + version, + experiment, + experiment_branch diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/burnham_derived/event_monitoring_live_v1/metadata.yaml /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/burnham_derived/event_monitoring_live_v1/metadata.yaml --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/burnham_derived/event_monitoring_live_v1/metadata.yaml 1970-01-01 00:00:00.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/burnham_derived/event_monitoring_live_v1/metadata.yaml 2024-06-18 14:33:12.000000000 +0000 @@ -0,0 +1,19 @@ +friendly_name: Event Monitoring Live +description: |- + Materialized view of experimentation related events + coming from . +owners: +- ascholtz@mozilla.com +- akomar@mozilla.com +labels: + materialized_view: true + owner1: ascholtz + owner2: akomar +bigquery: null +workgroup_access: +- role: roles/bigquery.dataViewer + members: + - workgroup:mozilla-confidential +references: + materialized_view.sql: + - moz-fx-data-shared-prod.burnham_live.events_v1 diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/contextual_services/event_aggregates/schema.yaml /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/contextual_services/event_aggregates/schema.yaml --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/contextual_services/event_aggregates/schema.yaml 2024-06-18 14:15:22.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/contextual_services/event_aggregates/schema.yaml 2024-06-18 14:23:45.000000000 +0000 @@ -1,49 +1,49 @@ fields: -- mode: NULLABLE - name: submission_date +- name: submission_date type: DATE -- mode: NULLABLE - name: source + mode: NULLABLE +- name: source type: STRING -- mode: NULLABLE - name: event_type + mode: NULLABLE +- name: event_type type: STRING -- mode: NULLABLE - name: form_factor + mode: NULLABLE +- name: form_factor type: STRING -- mode: NULLABLE - name: country + mode: NULLABLE +- name: country type: STRING -- mode: NULLABLE - name: subdivision1 + mode: NULLABLE +- name: subdivision1 type: STRING -- mode: NULLABLE - name: advertiser + mode: NULLABLE +- name: advertiser type: STRING -- mode: NULLABLE - name: release_channel + mode: NULLABLE +- name: release_channel type: STRING -- mode: NULLABLE - name: position + mode: NULLABLE +- name: position type: INTEGER -- mode: NULLABLE - name: provider + mode: NULLABLE +- name: provider type: STRING -- mode: NULLABLE - name: match_type + mode: NULLABLE +- name: match_type type: STRING -- mode: NULLABLE - name: normalized_os + mode: NULLABLE +- name: normalized_os type: STRING -- mode: NULLABLE - name: suggest_data_sharing_enabled + mode: NULLABLE +- name: suggest_data_sharing_enabled type: BOOLEAN -- mode: NULLABLE - name: event_count + mode: NULLABLE +- name: event_count type: INTEGER -- mode: NULLABLE - name: user_count + mode: NULLABLE +- name: user_count type: INTEGER -- mode: NULLABLE - name: query_type + mode: NULLABLE +- name: query_type type: STRING + mode: NULLABLE diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/contextual_services/event_aggregates_suggest/schema.yaml /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/contextual_services/event_aggregates_suggest/schema.yaml --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/contextual_services/event_aggregates_suggest/schema.yaml 2024-06-18 14:15:22.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/contextual_services/event_aggregates_suggest/schema.yaml 2024-06-18 14:23:45.000000000 +0000 @@ -1,40 +1,40 @@ fields: -- mode: NULLABLE - name: submission_date +- name: submission_date type: DATE -- mode: NULLABLE - name: form_factor + mode: NULLABLE +- name: form_factor type: STRING -- mode: NULLABLE - name: country + mode: NULLABLE +- name: country type: STRING -- mode: NULLABLE - name: advertiser + mode: NULLABLE +- name: advertiser type: STRING -- mode: NULLABLE - name: normalized_os + mode: NULLABLE +- name: normalized_os type: STRING -- mode: NULLABLE - name: release_channel + mode: NULLABLE +- name: release_channel type: STRING -- mode: NULLABLE - name: position + mode: NULLABLE +- name: position type: INTEGER -- mode: NULLABLE - name: provider + mode: NULLABLE +- name: provider type: STRING -- mode: NULLABLE - name: match_type + mode: NULLABLE +- name: match_type type: STRING -- mode: NULLABLE - name: suggest_data_sharing_enabled + mode: NULLABLE +- name: suggest_data_sharing_enabled type: BOOLEAN -- mode: NULLABLE - name: impression_count + mode: NULLABLE +- name: impression_count type: INTEGER -- mode: NULLABLE - name: click_count + mode: NULLABLE +- name: click_count type: INTEGER -- mode: NULLABLE - name: query_type + mode: NULLABLE +- name: query_type type: STRING + mode: NULLABLE diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/fenix/funnel_retention_clients/schema.yaml /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/fenix/funnel_retention_clients/schema.yaml --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/fenix/funnel_retention_clients/schema.yaml 2024-06-18 14:15:22.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/fenix/funnel_retention_clients/schema.yaml 2024-06-18 14:25:06.000000000 +0000 @@ -26,6 +26,9 @@ - name: adjust_network type: STRING mode: NULLABLE +- name: install_source + type: STRING + mode: NULLABLE - name: retained_week_2 type: BOOLEAN mode: NULLABLE diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/fenix/funnel_retention_week_4/schema.yaml /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/fenix/funnel_retention_week_4/schema.yaml --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/fenix/funnel_retention_week_4/schema.yaml 2024-06-18 14:15:22.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/fenix/funnel_retention_week_4/schema.yaml 2024-06-18 14:25:07.000000000 +0000 @@ -48,6 +48,10 @@ description: 'The type of source of a client installation. ' +- name: install_source + type: STRING + mode: NULLABLE + description: null - name: new_profiles type: INTEGER mode: NULLABLE diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/firefox_desktop_background_defaultagent_derived/event_monitoring_live_v1/materialized_view.sql /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/firefox_desktop_background_defaultagent_derived/event_monitoring_live_v1/materialized_view.sql --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/firefox_desktop_background_defaultagent_derived/event_monitoring_live_v1/materialized_view.sql 1970-01-01 00:00:00.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/firefox_desktop_background_defaultagent_derived/event_monitoring_live_v1/materialized_view.sql 2024-06-18 14:18:51.000000000 +0000 @@ -0,0 +1,76 @@ +CREATE MATERIALIZED VIEW +IF + NOT EXISTS `moz-fx-data-shared-prod.firefox_desktop_background_defaultagent_derived.event_monitoring_live_v1` + OPTIONS + (enable_refresh = TRUE, refresh_interval_minutes = 60) + AS + SELECT + DATE(submission_timestamp) AS submission_date, + TIMESTAMP_ADD( + TIMESTAMP_TRUNC(submission_timestamp, HOUR), + -- Aggregates event counts over 60-minute intervals + INTERVAL(DIV(EXTRACT(MINUTE FROM submission_timestamp), 60) * 60) MINUTE + ) AS window_start, + TIMESTAMP_ADD( + TIMESTAMP_TRUNC(submission_timestamp, HOUR), + INTERVAL((DIV(EXTRACT(MINUTE FROM submission_timestamp), 60) + 1) * 60) MINUTE + ) AS window_end, + event.category AS event_category, + event.name AS event_name, + event_extra.key AS event_extra_key, + normalized_country_code AS country, + 'Firefox Desktop Default Agent Task' AS normalized_app_name, + channel, + version, + -- Access experiment information. + -- Additional iteration is necessary to aggregate total event count across experiments + -- which is denoted with "*". + -- Some clients are enrolled in multiple experiments, so simply summing up the totals + -- across all the experiments would double count events. + CASE + experiment_index + WHEN ARRAY_LENGTH(ping_info.experiments) + THEN "*" + ELSE ping_info.experiments[SAFE_OFFSET(experiment_index)].key + END AS experiment, + CASE + experiment_index + WHEN ARRAY_LENGTH(ping_info.experiments) + THEN "*" + ELSE ping_info.experiments[SAFE_OFFSET(experiment_index)].value.branch + END AS experiment_branch, + COUNT(*) AS total_events + FROM + ( + SELECT + submission_timestamp, + events, + normalized_country_code, + client_info.app_channel AS channel, + client_info.app_display_version AS version, + ping_info + FROM + `moz-fx-data-shared-prod.firefox_desktop_background_defaultagent_live.events_v1` + ) + CROSS JOIN + UNNEST(events) AS event, + -- Iterator for accessing experiments. + -- Add one more for aggregating events across all experiments + UNNEST(GENERATE_ARRAY(0, ARRAY_LENGTH(ping_info.experiments))) AS experiment_index + LEFT JOIN + UNNEST(event.extra) AS event_extra + WHERE + DATE(submission_timestamp) >= "2024-06-18" + GROUP BY + submission_date, + window_start, + window_end, + event_category, + event_name, + event_extra_key, + country, + normalized_app_name, + channel, + version, + experiment, + experiment_branch diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/firefox_desktop_background_defaultagent_derived/event_monitoring_live_v1/metadata.yaml /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/firefox_desktop_background_defaultagent_derived/event_monitoring_live_v1/metadata.yaml --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/firefox_desktop_background_defaultagent_derived/event_monitoring_live_v1/metadata.yaml 1970-01-01 00:00:00.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/firefox_desktop_background_defaultagent_derived/event_monitoring_live_v1/metadata.yaml 2024-06-18 14:33:16.000000000 +0000 @@ -0,0 +1,19 @@ +friendly_name: Event Monitoring Live +description: |- + Materialized view of experimentation related events + coming from . +owners: +- ascholtz@mozilla.com +- akomar@mozilla.com +labels: + materialized_view: true + owner1: ascholtz + owner2: akomar +bigquery: null +workgroup_access: +- role: roles/bigquery.dataViewer + members: + - workgroup:mozilla-confidential +references: + materialized_view.sql: + - moz-fx-data-shared-prod.firefox_desktop_background_defaultagent_live.events_v1 diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/firefox_desktop_background_update_derived/event_monitoring_live_v1/materialized_view.sql /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/firefox_desktop_background_update_derived/event_monitoring_live_v1/materialized_view.sql --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/firefox_desktop_background_update_derived/event_monitoring_live_v1/materialized_view.sql 1970-01-01 00:00:00.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/firefox_desktop_background_update_derived/event_monitoring_live_v1/materialized_view.sql 2024-06-18 14:18:51.000000000 +0000 @@ -0,0 +1,76 @@ +CREATE MATERIALIZED VIEW +IF + NOT EXISTS `moz-fx-data-shared-prod.firefox_desktop_background_update_derived.event_monitoring_live_v1` + OPTIONS + (enable_refresh = TRUE, refresh_interval_minutes = 60) + AS + SELECT + DATE(submission_timestamp) AS submission_date, + TIMESTAMP_ADD( + TIMESTAMP_TRUNC(submission_timestamp, HOUR), + -- Aggregates event counts over 60-minute intervals + INTERVAL(DIV(EXTRACT(MINUTE FROM submission_timestamp), 60) * 60) MINUTE + ) AS window_start, + TIMESTAMP_ADD( + TIMESTAMP_TRUNC(submission_timestamp, HOUR), + INTERVAL((DIV(EXTRACT(MINUTE FROM submission_timestamp), 60) + 1) * 60) MINUTE + ) AS window_end, + event.category AS event_category, + event.name AS event_name, + event_extra.key AS event_extra_key, + normalized_country_code AS country, + 'Firefox for Desktop Background Update Task' AS normalized_app_name, + channel, + version, + -- Access experiment information. + -- Additional iteration is necessary to aggregate total event count across experiments + -- which is denoted with "*". + -- Some clients are enrolled in multiple experiments, so simply summing up the totals + -- across all the experiments would double count events. + CASE + experiment_index + WHEN ARRAY_LENGTH(ping_info.experiments) + THEN "*" + ELSE ping_info.experiments[SAFE_OFFSET(experiment_index)].key + END AS experiment, + CASE + experiment_index + WHEN ARRAY_LENGTH(ping_info.experiments) + THEN "*" + ELSE ping_info.experiments[SAFE_OFFSET(experiment_index)].value.branch + END AS experiment_branch, + COUNT(*) AS total_events + FROM + ( + SELECT + submission_timestamp, + events, + normalized_country_code, + client_info.app_channel AS channel, + client_info.app_display_version AS version, + ping_info + FROM + `moz-fx-data-shared-prod.firefox_desktop_background_update_live.events_v1` + ) + CROSS JOIN + UNNEST(events) AS event, + -- Iterator for accessing experiments. + -- Add one more for aggregating events across all experiments + UNNEST(GENERATE_ARRAY(0, ARRAY_LENGTH(ping_info.experiments))) AS experiment_index + LEFT JOIN + UNNEST(event.extra) AS event_extra + WHERE + DATE(submission_timestamp) >= "2024-06-18" + GROUP BY + submission_date, + window_start, + window_end, + event_category, + event_name, + event_extra_key, + country, + normalized_app_name, + channel, + version, + experiment, + experiment_branch diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/firefox_desktop_background_update_derived/event_monitoring_live_v1/metadata.yaml /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/firefox_desktop_background_update_derived/event_monitoring_live_v1/metadata.yaml --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/firefox_desktop_background_update_derived/event_monitoring_live_v1/metadata.yaml 1970-01-01 00:00:00.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/firefox_desktop_background_update_derived/event_monitoring_live_v1/metadata.yaml 2024-06-18 14:33:16.000000000 +0000 @@ -0,0 +1,19 @@ +friendly_name: Event Monitoring Live +description: |- + Materialized view of experimentation related events + coming from . +owners: +- ascholtz@mozilla.com +- akomar@mozilla.com +labels: + materialized_view: true + owner1: ascholtz + owner2: akomar +bigquery: null +workgroup_access: +- role: roles/bigquery.dataViewer + members: + - workgroup:mozilla-confidential +references: + materialized_view.sql: + - moz-fx-data-shared-prod.firefox_desktop_background_update_live.events_v1 diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/firefox_desktop_derived/event_monitoring_live_v1/materialized_view.sql /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/firefox_desktop_derived/event_monitoring_live_v1/materialized_view.sql --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/firefox_desktop_derived/event_monitoring_live_v1/materialized_view.sql 2024-06-18 14:16:00.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/firefox_desktop_derived/event_monitoring_live_v1/materialized_view.sql 2024-06-18 14:18:51.000000000 +0000 @@ -50,7 +50,7 @@ client_info.app_display_version AS version, ping_info FROM - `moz-fx-data-shared-prod.firefox_desktop_live.prototype_no_code_events_v1` + `moz-fx-data-shared-prod.firefox_desktop_live.urlbar_potential_exposure_v1` UNION ALL SELECT submission_timestamp, @@ -60,7 +60,7 @@ client_info.app_display_version AS version, ping_info FROM - `moz-fx-data-shared-prod.firefox_desktop_live.urlbar_potential_exposure_v1` + `moz-fx-data-shared-prod.firefox_desktop_live.events_v1` UNION ALL SELECT submission_timestamp, @@ -70,7 +70,7 @@ client_info.app_display_version AS version, ping_info FROM - `moz-fx-data-shared-prod.firefox_desktop_live.newtab_v1` + `moz-fx-data-shared-prod.firefox_desktop_live.prototype_no_code_events_v1` UNION ALL SELECT submission_timestamp, @@ -80,7 +80,7 @@ client_info.app_display_version AS version, ping_info FROM - `moz-fx-data-shared-prod.firefox_desktop_live.events_v1` + `moz-fx-data-shared-prod.firefox_desktop_live.newtab_v1` ) CROSS JOIN UNNEST(events) AS event, diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/firefox_translations_derived/event_monitoring_live_v1/materialized_view.sql /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/firefox_translations_derived/event_monitoring_live_v1/materialized_view.sql --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/firefox_translations_derived/event_monitoring_live_v1/materialized_view.sql 2024-06-18 14:16:00.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/firefox_translations_derived/event_monitoring_live_v1/materialized_view.sql 2024-06-18 14:18:51.000000000 +0000 @@ -51,6 +51,16 @@ ping_info FROM `moz-fx-data-shared-prod.firefox_translations_live.custom_v1` + UNION ALL + SELECT + submission_timestamp, + events, + normalized_country_code, + client_info.app_channel AS channel, + client_info.app_display_version AS version, + ping_info + FROM + `moz-fx-data-shared-prod.firefox_translations_live.events_v1` ) CROSS JOIN UNNEST(events) AS event, diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/firefox_translations_derived/event_monitoring_live_v1/metadata.yaml /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/firefox_translations_derived/event_monitoring_live_v1/metadata.yaml --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/firefox_translations_derived/event_monitoring_live_v1/metadata.yaml 2024-06-18 14:16:00.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/firefox_translations_derived/event_monitoring_live_v1/metadata.yaml 2024-06-18 14:33:16.000000000 +0000 @@ -17,3 +17,4 @@ references: materialized_view.sql: - moz-fx-data-shared-prod.firefox_translations_live.custom_v1 + - moz-fx-data-shared-prod.firefox_translations_live.events_v1 diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/focus_android/use_counters/schema.yaml /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/focus_android/use_counters/schema.yaml --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/focus_android/use_counters/schema.yaml 2024-06-18 14:16:00.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/focus_android/use_counters/schema.yaml 2024-06-18 14:31:59.000000000 +0000 @@ -1,11 +1,11 @@ fields: - name: normalized_app_id - type: STRING mode: NULLABLE + type: STRING description: App ID of the channel data was received from - name: normalized_channel - type: STRING mode: NULLABLE + type: STRING description: Normalized channel name - name: additional_properties type: STRING diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/glean_dictionary_derived/event_monitoring_live_v1/materialized_view.sql /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/glean_dictionary_derived/event_monitoring_live_v1/materialized_view.sql --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/glean_dictionary_derived/event_monitoring_live_v1/materialized_view.sql 1970-01-01 00:00:00.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/glean_dictionary_derived/event_monitoring_live_v1/materialized_view.sql 2024-06-18 14:18:51.000000000 +0000 @@ -0,0 +1,76 @@ +CREATE MATERIALIZED VIEW +IF + NOT EXISTS `moz-fx-data-shared-prod.glean_dictionary_derived.event_monitoring_live_v1` + OPTIONS + (enable_refresh = TRUE, refresh_interval_minutes = 60) + AS + SELECT + DATE(submission_timestamp) AS submission_date, + TIMESTAMP_ADD( + TIMESTAMP_TRUNC(submission_timestamp, HOUR), + -- Aggregates event counts over 60-minute intervals + INTERVAL(DIV(EXTRACT(MINUTE FROM submission_timestamp), 60) * 60) MINUTE + ) AS window_start, + TIMESTAMP_ADD( + TIMESTAMP_TRUNC(submission_timestamp, HOUR), + INTERVAL((DIV(EXTRACT(MINUTE FROM submission_timestamp), 60) + 1) * 60) MINUTE + ) AS window_end, + event.category AS event_category, + event.name AS event_name, + event_extra.key AS event_extra_key, + normalized_country_code AS country, + 'Glean Dictionary' AS normalized_app_name, + channel, + version, + -- Access experiment information. + -- Additional iteration is necessary to aggregate total event count across experiments + -- which is denoted with "*". + -- Some clients are enrolled in multiple experiments, so simply summing up the totals + -- across all the experiments would double count events. + CASE + experiment_index + WHEN ARRAY_LENGTH(ping_info.experiments) + THEN "*" + ELSE ping_info.experiments[SAFE_OFFSET(experiment_index)].key + END AS experiment, + CASE + experiment_index + WHEN ARRAY_LENGTH(ping_info.experiments) + THEN "*" + ELSE ping_info.experiments[SAFE_OFFSET(experiment_index)].value.branch + END AS experiment_branch, + COUNT(*) AS total_events + FROM + ( + SELECT + submission_timestamp, + events, + normalized_country_code, + client_info.app_channel AS channel, + client_info.app_display_version AS version, + ping_info + FROM + `moz-fx-data-shared-prod.glean_dictionary_live.events_v1` + ) + CROSS JOIN + UNNEST(events) AS event, + -- Iterator for accessing experiments. + -- Add one more for aggregating events across all experiments + UNNEST(GENERATE_ARRAY(0, ARRAY_LENGTH(ping_info.experiments))) AS experiment_index + LEFT JOIN + UNNEST(event.extra) AS event_extra + WHERE + DATE(submission_timestamp) >= "2024-06-18" + GROUP BY + submission_date, + window_start, + window_end, + event_category, + event_name, + event_extra_key, + country, + normalized_app_name, + channel, + version, + experiment, + experiment_branch diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/glean_dictionary_derived/event_monitoring_live_v1/metadata.yaml /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/glean_dictionary_derived/event_monitoring_live_v1/metadata.yaml --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/glean_dictionary_derived/event_monitoring_live_v1/metadata.yaml 1970-01-01 00:00:00.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/glean_dictionary_derived/event_monitoring_live_v1/metadata.yaml 2024-06-18 14:33:16.000000000 +0000 @@ -0,0 +1,19 @@ +friendly_name: Event Monitoring Live +description: |- + Materialized view of experimentation related events + coming from . +owners: +- ascholtz@mozilla.com +- akomar@mozilla.com +labels: + materialized_view: true + owner1: ascholtz + owner2: akomar +bigquery: null +workgroup_access: +- role: roles/bigquery.dataViewer + members: + - workgroup:mozilla-confidential +references: + materialized_view.sql: + - moz-fx-data-shared-prod.glean_dictionary_live.events_v1 diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/gleanjs_docs_derived/event_monitoring_live_v1/materialized_view.sql /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/gleanjs_docs_derived/event_monitoring_live_v1/materialized_view.sql --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/gleanjs_docs_derived/event_monitoring_live_v1/materialized_view.sql 1970-01-01 00:00:00.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/gleanjs_docs_derived/event_monitoring_live_v1/materialized_view.sql 2024-06-18 14:18:52.000000000 +0000 @@ -0,0 +1,76 @@ +CREATE MATERIALIZED VIEW +IF + NOT EXISTS `moz-fx-data-shared-prod.gleanjs_docs_derived.event_monitoring_live_v1` + OPTIONS + (enable_refresh = TRUE, refresh_interval_minutes = 60) + AS + SELECT + DATE(submission_timestamp) AS submission_date, + TIMESTAMP_ADD( + TIMESTAMP_TRUNC(submission_timestamp, HOUR), + -- Aggregates event counts over 60-minute intervals + INTERVAL(DIV(EXTRACT(MINUTE FROM submission_timestamp), 60) * 60) MINUTE + ) AS window_start, + TIMESTAMP_ADD( + TIMESTAMP_TRUNC(submission_timestamp, HOUR), + INTERVAL((DIV(EXTRACT(MINUTE FROM submission_timestamp), 60) + 1) * 60) MINUTE + ) AS window_end, + event.category AS event_category, + event.name AS event_name, + event_extra.key AS event_extra_key, + normalized_country_code AS country, + 'Glean.js Documentation' AS normalized_app_name, + channel, + version, + -- Access experiment information. + -- Additional iteration is necessary to aggregate total event count across experiments + -- which is denoted with "*". + -- Some clients are enrolled in multiple experiments, so simply summing up the totals + -- across all the experiments would double count events. + CASE + experiment_index + WHEN ARRAY_LENGTH(ping_info.experiments) + THEN "*" + ELSE ping_info.experiments[SAFE_OFFSET(experiment_index)].key + END AS experiment, + CASE + experiment_index + WHEN ARRAY_LENGTH(ping_info.experiments) + THEN "*" + ELSE ping_info.experiments[SAFE_OFFSET(experiment_index)].value.branch + END AS experiment_branch, + COUNT(*) AS total_events + FROM + ( + SELECT + submission_timestamp, + events, + normalized_country_code, + client_info.app_channel AS channel, + client_info.app_display_version AS version, + ping_info + FROM + `moz-fx-data-shared-prod.gleanjs_docs_live.events_v1` + ) + CROSS JOIN + UNNEST(events) AS event, + -- Iterator for accessing experiments. + -- Add one more for aggregating events across all experiments + UNNEST(GENERATE_ARRAY(0, ARRAY_LENGTH(ping_info.experiments))) AS experiment_index + LEFT JOIN + UNNEST(event.extra) AS event_extra + WHERE + DATE(submission_timestamp) >= "2024-06-18" + GROUP BY + submission_date, + window_start, + window_end, + event_category, + event_name, + event_extra_key, + country, + normalized_app_name, + channel, + version, + experiment, + experiment_branch diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/gleanjs_docs_derived/event_monitoring_live_v1/metadata.yaml /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/gleanjs_docs_derived/event_monitoring_live_v1/metadata.yaml --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/gleanjs_docs_derived/event_monitoring_live_v1/metadata.yaml 1970-01-01 00:00:00.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/gleanjs_docs_derived/event_monitoring_live_v1/metadata.yaml 2024-06-18 14:33:16.000000000 +0000 @@ -0,0 +1,19 @@ +friendly_name: Event Monitoring Live +description: |- + Materialized view of experimentation related events + coming from . +owners: +- ascholtz@mozilla.com +- akomar@mozilla.com +labels: + materialized_view: true + owner1: ascholtz + owner2: akomar +bigquery: null +workgroup_access: +- role: roles/bigquery.dataViewer + members: + - workgroup:mozilla-confidential +references: + materialized_view.sql: + - moz-fx-data-shared-prod.gleanjs_docs_live.events_v1 diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/mdn_yari_derived/event_monitoring_live_v1/materialized_view.sql /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/mdn_yari_derived/event_monitoring_live_v1/materialized_view.sql --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/mdn_yari_derived/event_monitoring_live_v1/materialized_view.sql 2024-06-18 14:16:00.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/mdn_yari_derived/event_monitoring_live_v1/materialized_view.sql 2024-06-18 14:18:52.000000000 +0000 @@ -51,6 +51,16 @@ ping_info FROM `moz-fx-data-shared-prod.mdn_yari_live.action_v1` + UNION ALL + SELECT + submission_timestamp, + events, + normalized_country_code, + client_info.app_channel AS channel, + client_info.app_display_version AS version, + ping_info + FROM + `moz-fx-data-shared-prod.mdn_yari_live.events_v1` ) CROSS JOIN UNNEST(events) AS event, diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/mdn_yari_derived/event_monitoring_live_v1/metadata.yaml /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/mdn_yari_derived/event_monitoring_live_v1/metadata.yaml --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/mdn_yari_derived/event_monitoring_live_v1/metadata.yaml 2024-06-18 14:16:00.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/mdn_yari_derived/event_monitoring_live_v1/metadata.yaml 2024-06-18 14:33:07.000000000 +0000 @@ -17,3 +17,4 @@ references: materialized_view.sql: - moz-fx-data-shared-prod.mdn_yari_live.action_v1 + - moz-fx-data-shared-prod.mdn_yari_live.events_v1 diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/monitor_cirrus_derived/event_monitoring_live_v1/materialized_view.sql /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/monitor_cirrus_derived/event_monitoring_live_v1/materialized_view.sql --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/monitor_cirrus_derived/event_monitoring_live_v1/materialized_view.sql 1970-01-01 00:00:00.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/monitor_cirrus_derived/event_monitoring_live_v1/materialized_view.sql 2024-06-18 14:18:52.000000000 +0000 @@ -0,0 +1,76 @@ +CREATE MATERIALIZED VIEW +IF + NOT EXISTS `moz-fx-data-shared-prod.monitor_cirrus_derived.event_monitoring_live_v1` + OPTIONS + (enable_refresh = TRUE, refresh_interval_minutes = 60) + AS + SELECT + DATE(submission_timestamp) AS submission_date, + TIMESTAMP_ADD( + TIMESTAMP_TRUNC(submission_timestamp, HOUR), + -- Aggregates event counts over 60-minute intervals + INTERVAL(DIV(EXTRACT(MINUTE FROM submission_timestamp), 60) * 60) MINUTE + ) AS window_start, + TIMESTAMP_ADD( + TIMESTAMP_TRUNC(submission_timestamp, HOUR), + INTERVAL((DIV(EXTRACT(MINUTE FROM submission_timestamp), 60) + 1) * 60) MINUTE + ) AS window_end, + event.category AS event_category, + event.name AS event_name, + event_extra.key AS event_extra_key, + normalized_country_code AS country, + 'Mozilla Monitor (Cirrus)' AS normalized_app_name, + channel, + version, + -- Access experiment information. + -- Additional iteration is necessary to aggregate total event count across experiments + -- which is denoted with "*". + -- Some clients are enrolled in multiple experiments, so simply summing up the totals + -- across all the experiments would double count events. + CASE + experiment_index + WHEN ARRAY_LENGTH(ping_info.experiments) + THEN "*" + ELSE ping_info.experiments[SAFE_OFFSET(experiment_index)].key + END AS experiment, + CASE + experiment_index + WHEN ARRAY_LENGTH(ping_info.experiments) + THEN "*" + ELSE ping_info.experiments[SAFE_OFFSET(experiment_index)].value.branch + END AS experiment_branch, + COUNT(*) AS total_events + FROM + ( + SELECT + submission_timestamp, + events, + normalized_country_code, + client_info.app_channel AS channel, + client_info.app_display_version AS version, + ping_info + FROM + `moz-fx-data-shared-prod.monitor_cirrus_live.events_v1` + ) + CROSS JOIN + UNNEST(events) AS event, + -- Iterator for accessing experiments. + -- Add one more for aggregating events across all experiments + UNNEST(GENERATE_ARRAY(0, ARRAY_LENGTH(ping_info.experiments))) AS experiment_index + LEFT JOIN + UNNEST(event.extra) AS event_extra + WHERE + DATE(submission_timestamp) >= "2024-06-18" + GROUP BY + submission_date, + window_start, + window_end, + event_category, + event_name, + event_extra_key, + country, + normalized_app_name, + channel, + version, + experiment, + experiment_branch diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/monitor_cirrus_derived/event_monitoring_live_v1/metadata.yaml /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/monitor_cirrus_derived/event_monitoring_live_v1/metadata.yaml --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/monitor_cirrus_derived/event_monitoring_live_v1/metadata.yaml 1970-01-01 00:00:00.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/monitor_cirrus_derived/event_monitoring_live_v1/metadata.yaml 2024-06-18 14:33:17.000000000 +0000 @@ -0,0 +1,19 @@ +friendly_name: Event Monitoring Live +description: |- + Materialized view of experimentation related events + coming from . +owners: +- ascholtz@mozilla.com +- akomar@mozilla.com +labels: + materialized_view: true + owner1: ascholtz + owner2: akomar +bigquery: null +workgroup_access: +- role: roles/bigquery.dataViewer + members: + - workgroup:mozilla-confidential +references: + materialized_view.sql: + - moz-fx-data-shared-prod.monitor_cirrus_live.events_v1 diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/monitoring_derived/event_monitoring_aggregates_v1/metadata.yaml /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/monitoring_derived/event_monitoring_aggregates_v1/metadata.yaml --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/monitoring_derived/event_monitoring_aggregates_v1/metadata.yaml 2024-06-18 14:16:00.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/monitoring_derived/event_monitoring_aggregates_v1/metadata.yaml 2024-06-18 14:33:08.000000000 +0000 @@ -31,32 +31,50 @@ references: query.sql: - moz-fx-data-shared-prod.accounts_backend_stable.accounts_events_v1 + - moz-fx-data-shared-prod.accounts_cirrus_stable.events_v1 - moz-fx-data-shared-prod.accounts_frontend_stable.accounts_events_v1 - moz-fx-data-shared-prod.bedrock_stable.events_v1 - moz-fx-data-shared-prod.bedrock_stable.interaction_v1 - moz-fx-data-shared-prod.bedrock_stable.non_interaction_v1 + - moz-fx-data-shared-prod.burnham_stable.events_v1 - moz-fx-data-shared-prod.debug_ping_view_stable.events_v1 + - moz-fx-data-shared-prod.firefox_desktop_background_defaultagent_stable.events_v1 - moz-fx-data-shared-prod.firefox_desktop_background_tasks_stable.background_tasks_v1 - moz-fx-data-shared-prod.firefox_desktop_background_tasks_stable.events_v1 + - moz-fx-data-shared-prod.firefox_desktop_background_update_stable.events_v1 - moz-fx-data-shared-prod.firefox_desktop_stable.events_v1 - moz-fx-data-shared-prod.firefox_desktop_stable.newtab_v1 - moz-fx-data-shared-prod.firefox_desktop_stable.prototype_no_code_events_v1 - moz-fx-data-shared-prod.firefox_desktop_stable.urlbar_potential_exposure_v1 - moz-fx-data-shared-prod.firefox_translations_stable.custom_v1 + - moz-fx-data-shared-prod.firefox_translations_stable.events_v1 + - moz-fx-data-shared-prod.glean_dictionary_stable.events_v1 + - moz-fx-data-shared-prod.gleanjs_docs_stable.events_v1 - moz-fx-data-shared-prod.mdn_yari_stable.action_v1 + - moz-fx-data-shared-prod.mdn_yari_stable.events_v1 + - moz-fx-data-shared-prod.monitor_cirrus_stable.events_v1 - moz-fx-data-shared-prod.monitor_frontend_stable.events_v1 + - moz-fx-data-shared-prod.mozilla_lockbox_stable.events_v1 + - moz-fx-data-shared-prod.mozilla_mach_stable.events_v1 + - moz-fx-data-shared-prod.mozillavpn_backend_cirrus_stable.events_v1 - moz-fx-data-shared-prod.mozillavpn_stable.daemonsession_v1 + - moz-fx-data-shared-prod.mozillavpn_stable.events_v1 - moz-fx-data-shared-prod.mozillavpn_stable.main_v1 - moz-fx-data-shared-prod.mozillavpn_stable.vpnsession_v1 + - moz-fx-data-shared-prod.mozphab_stable.events_v1 - moz-fx-data-shared-prod.org_mozilla_bergamot_stable.custom_v1 + - moz-fx-data-shared-prod.org_mozilla_bergamot_stable.events_v1 + - moz-fx-data-shared-prod.org_mozilla_connect_firefox_stable.events_v1 - moz-fx-data-shared-prod.org_mozilla_fenix_nightly_stable.events_v1 - moz-fx-data-shared-prod.org_mozilla_fenix_stable.events_v1 - moz-fx-data-shared-prod.org_mozilla_fennec_aurora_stable.events_v1 - moz-fx-data-shared-prod.org_mozilla_firefox_beta_stable.events_v1 - moz-fx-data-shared-prod.org_mozilla_firefox_stable.events_v1 - moz-fx-data-shared-prod.org_mozilla_firefox_vpn_stable.daemonsession_v1 + - moz-fx-data-shared-prod.org_mozilla_firefox_vpn_stable.events_v1 - moz-fx-data-shared-prod.org_mozilla_firefox_vpn_stable.main_v1 - moz-fx-data-shared-prod.org_mozilla_firefox_vpn_stable.vpnsession_v1 + - moz-fx-data-shared-prod.org_mozilla_firefoxreality_stable.events_v1 - moz-fx-data-shared-prod.org_mozilla_focus_beta_stable.events_v1 - moz-fx-data-shared-prod.org_mozilla_focus_nightly_stable.events_v1 - moz-fx-data-shared-prod.org_mozilla_focus_stable.events_v1 @@ -70,16 +88,23 @@ - moz-fx-data-shared-prod.org_mozilla_ios_firefoxbeta_stable.first_session_v1 - moz-fx-data-shared-prod.org_mozilla_ios_firefoxbeta_stable.metrics_v1 - moz-fx-data-shared-prod.org_mozilla_ios_firefoxvpn_network_extension_stable.daemonsession_v1 + - moz-fx-data-shared-prod.org_mozilla_ios_firefoxvpn_network_extension_stable.events_v1 - moz-fx-data-shared-prod.org_mozilla_ios_firefoxvpn_network_extension_stable.main_v1 - moz-fx-data-shared-prod.org_mozilla_ios_firefoxvpn_network_extension_stable.vpnsession_v1 - moz-fx-data-shared-prod.org_mozilla_ios_firefoxvpn_stable.daemonsession_v1 + - moz-fx-data-shared-prod.org_mozilla_ios_firefoxvpn_stable.events_v1 - moz-fx-data-shared-prod.org_mozilla_ios_firefoxvpn_stable.main_v1 - moz-fx-data-shared-prod.org_mozilla_ios_firefoxvpn_stable.vpnsession_v1 - moz-fx-data-shared-prod.org_mozilla_ios_focus_stable.events_v1 - moz-fx-data-shared-prod.org_mozilla_ios_klar_stable.events_v1 + - moz-fx-data-shared-prod.org_mozilla_ios_lockbox_stable.events_v1 - moz-fx-data-shared-prod.org_mozilla_klar_stable.events_v1 + - moz-fx-data-shared-prod.org_mozilla_mozregression_stable.events_v1 - moz-fx-data-shared-prod.org_mozilla_reference_browser_stable.events_v1 + - moz-fx-data-shared-prod.org_mozilla_tv_firefox_stable.events_v1 - moz-fx-data-shared-prod.org_mozilla_vrbrowser_stable.events_v1 - moz-fx-data-shared-prod.pine_stable.events_v1 + - moz-fx-data-shared-prod.treeherder_stable.events_v1 + - moz-fx-data-shared-prod.viu_politica_stable.events_v1 - moz-fx-data-shared-prod.viu_politica_stable.main_events_v1 - moz-fx-data-shared-prod.viu_politica_stable.video_index_v1 diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/monitoring_derived/event_monitoring_aggregates_v1/query.sql /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/monitoring_derived/event_monitoring_aggregates_v1/query.sql --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/monitoring_derived/event_monitoring_aggregates_v1/query.sql 2024-06-18 14:16:00.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/monitoring_derived/event_monitoring_aggregates_v1/query.sql 2024-06-18 14:20:12.000000000 +0000 @@ -45,7 +45,7 @@ client_info.app_display_version AS version, ping_info FROM - `moz-fx-data-shared-prod.firefox_desktop_stable.prototype_no_code_events_v1` + `moz-fx-data-shared-prod.firefox_desktop_stable.urlbar_potential_exposure_v1` UNION ALL SELECT submission_timestamp, @@ -55,7 +55,7 @@ client_info.app_display_version AS version, ping_info FROM - `moz-fx-data-shared-prod.firefox_desktop_stable.urlbar_potential_exposure_v1` + `moz-fx-data-shared-prod.firefox_desktop_stable.events_v1` UNION ALL SELECT submission_timestamp, @@ -65,7 +65,7 @@ client_info.app_display_version AS version, ping_info FROM - `moz-fx-data-shared-prod.firefox_desktop_stable.newtab_v1` + `moz-fx-data-shared-prod.firefox_desktop_stable.prototype_no_code_events_v1` UNION ALL SELECT submission_timestamp, @@ -75,7 +75,149 @@ client_info.app_display_version AS version, ping_info FROM - `moz-fx-data-shared-prod.firefox_desktop_stable.events_v1` + `moz-fx-data-shared-prod.firefox_desktop_stable.newtab_v1` + ) +CROSS JOIN + UNNEST(events) AS event, + -- Iterator for accessing experiments. + -- Add one more for aggregating events across all experiments + UNNEST(GENERATE_ARRAY(0, ARRAY_LENGTH(ping_info.experiments))) AS experiment_index +LEFT JOIN + UNNEST(event.extra) AS event_extra +WHERE + DATE(submission_timestamp) = @submission_date +GROUP BY + submission_date, + window_start, + window_end, + event_category, + event_name, + event_extra_key, + country, + normalized_app_name, + channel, + version, + experiment, + experiment_branch +UNION ALL +SELECT + @submission_date AS submission_date, + TIMESTAMP_ADD( + TIMESTAMP_TRUNC(submission_timestamp, HOUR), + -- Aggregates event counts over 60-minute intervals + INTERVAL(DIV(EXTRACT(MINUTE FROM submission_timestamp), 60) * 60) MINUTE + ) AS window_start, + TIMESTAMP_ADD( + TIMESTAMP_TRUNC(submission_timestamp, HOUR), + INTERVAL((DIV(EXTRACT(MINUTE FROM submission_timestamp), 60) + 1) * 60) MINUTE + ) AS window_end, + event.category AS event_category, + event.name AS event_name, + event_extra.key AS event_extra_key, + normalized_country_code AS country, + "Firefox for Desktop Background Update Task" AS normalized_app_name, + channel, + version, + -- Access experiment information. + -- Additional iteration is necessary to aggregate total event count across experiments + -- which is denoted with "*". + -- Some clients are enrolled in multiple experiments, so simply summing up the totals + -- across all the experiments would double count events. + CASE + experiment_index + WHEN ARRAY_LENGTH(ping_info.experiments) + THEN "*" + ELSE ping_info.experiments[SAFE_OFFSET(experiment_index)].key + END AS experiment, + CASE + experiment_index + WHEN ARRAY_LENGTH(ping_info.experiments) + THEN "*" + ELSE ping_info.experiments[SAFE_OFFSET(experiment_index)].value.branch + END AS experiment_branch, + COUNT(*) AS total_events +FROM + ( + SELECT + submission_timestamp, + events, + normalized_country_code, + client_info.app_channel AS channel, + client_info.app_display_version AS version, + ping_info + FROM + `moz-fx-data-shared-prod.firefox_desktop_background_update_stable.events_v1` + ) +CROSS JOIN + UNNEST(events) AS event, + -- Iterator for accessing experiments. + -- Add one more for aggregating events across all experiments + UNNEST(GENERATE_ARRAY(0, ARRAY_LENGTH(ping_info.experiments))) AS experiment_index +LEFT JOIN + UNNEST(event.extra) AS event_extra +WHERE + DATE(submission_timestamp) = @submission_date +GROUP BY + submission_date, + window_start, + window_end, + event_category, + event_name, + event_extra_key, + country, + normalized_app_name, + channel, + version, + experiment, + experiment_branch +UNION ALL +SELECT + @submission_date AS submission_date, + TIMESTAMP_ADD( + TIMESTAMP_TRUNC(submission_timestamp, HOUR), + -- Aggregates event counts over 60-minute intervals + INTERVAL(DIV(EXTRACT(MINUTE FROM submission_timestamp), 60) * 60) MINUTE + ) AS window_start, + TIMESTAMP_ADD( + TIMESTAMP_TRUNC(submission_timestamp, HOUR), + INTERVAL((DIV(EXTRACT(MINUTE FROM submission_timestamp), 60) + 1) * 60) MINUTE + ) AS window_end, + event.category AS event_category, + event.name AS event_name, + event_extra.key AS event_extra_key, + normalized_country_code AS country, + "Firefox Desktop Default Agent Task" AS normalized_app_name, + channel, + version, + -- Access experiment information. + -- Additional iteration is necessary to aggregate total event count across experiments + -- which is denoted with "*". + -- Some clients are enrolled in multiple experiments, so simply summing up the totals + -- across all the experiments would double count events. + CASE + experiment_index + WHEN ARRAY_LENGTH(ping_info.experiments) + THEN "*" + ELSE ping_info.experiments[SAFE_OFFSET(experiment_index)].key + END AS experiment, + CASE + experiment_index + WHEN ARRAY_LENGTH(ping_info.experiments) + THEN "*" + ELSE ping_info.experiments[SAFE_OFFSET(experiment_index)].value.branch + END AS experiment_branch, + COUNT(*) AS total_events +FROM + ( + SELECT + submission_timestamp, + events, + normalized_country_code, + client_info.app_channel AS channel, + client_info.app_display_version AS version, + ping_info + FROM + `moz-fx-data-shared-prod.firefox_desktop_background_defaultagent_stable.events_v1` ) CROSS JOIN UNNEST(events) AS event, @@ -572,7 +714,7 @@ client_info.app_display_version AS version, ping_info FROM - `moz-fx-data-shared-prod.org_mozilla_ios_firefox_stable.events_v1` + `moz-fx-data-shared-prod.org_mozilla_ios_firefox_stable.metrics_v1` UNION ALL SELECT submission_timestamp, @@ -592,7 +734,7 @@ client_info.app_display_version AS version, ping_info FROM - `moz-fx-data-shared-prod.org_mozilla_ios_firefox_stable.metrics_v1` + `moz-fx-data-shared-prod.org_mozilla_ios_firefox_stable.events_v1` ) CROSS JOIN UNNEST(events) AS event, @@ -663,7 +805,7 @@ client_info.app_display_version AS version, ping_info FROM - `moz-fx-data-shared-prod.org_mozilla_ios_firefoxbeta_stable.events_v1` + `moz-fx-data-shared-prod.org_mozilla_ios_firefoxbeta_stable.metrics_v1` UNION ALL SELECT submission_timestamp, @@ -683,7 +825,7 @@ client_info.app_display_version AS version, ping_info FROM - `moz-fx-data-shared-prod.org_mozilla_ios_firefoxbeta_stable.metrics_v1` + `moz-fx-data-shared-prod.org_mozilla_ios_firefoxbeta_stable.events_v1` ) CROSS JOIN UNNEST(events) AS event, @@ -754,7 +896,7 @@ client_info.app_display_version AS version, ping_info FROM - `moz-fx-data-shared-prod.org_mozilla_ios_fennec_stable.events_v1` + `moz-fx-data-shared-prod.org_mozilla_ios_fennec_stable.metrics_v1` UNION ALL SELECT submission_timestamp, @@ -774,7 +916,7 @@ client_info.app_display_version AS version, ping_info FROM - `moz-fx-data-shared-prod.org_mozilla_ios_fennec_stable.metrics_v1` + `moz-fx-data-shared-prod.org_mozilla_ios_fennec_stable.events_v1` ) CROSS JOIN UNNEST(events) AS event, @@ -885,6 +1027,77 @@ event.name AS event_name, event_extra.key AS event_extra_key, normalized_country_code AS country, + "Firefox for Fire TV" AS normalized_app_name, + channel, + version, + -- Access experiment information. + -- Additional iteration is necessary to aggregate total event count across experiments + -- which is denoted with "*". + -- Some clients are enrolled in multiple experiments, so simply summing up the totals + -- across all the experiments would double count events. + CASE + experiment_index + WHEN ARRAY_LENGTH(ping_info.experiments) + THEN "*" + ELSE ping_info.experiments[SAFE_OFFSET(experiment_index)].key + END AS experiment, + CASE + experiment_index + WHEN ARRAY_LENGTH(ping_info.experiments) + THEN "*" + ELSE ping_info.experiments[SAFE_OFFSET(experiment_index)].value.branch + END AS experiment_branch, + COUNT(*) AS total_events +FROM + ( + SELECT + submission_timestamp, + events, + normalized_country_code, + client_info.app_channel AS channel, + client_info.app_display_version AS version, + ping_info + FROM + `moz-fx-data-shared-prod.org_mozilla_tv_firefox_stable.events_v1` + ) +CROSS JOIN + UNNEST(events) AS event, + -- Iterator for accessing experiments. + -- Add one more for aggregating events across all experiments + UNNEST(GENERATE_ARRAY(0, ARRAY_LENGTH(ping_info.experiments))) AS experiment_index +LEFT JOIN + UNNEST(event.extra) AS event_extra +WHERE + DATE(submission_timestamp) = @submission_date +GROUP BY + submission_date, + window_start, + window_end, + event_category, + event_name, + event_extra_key, + country, + normalized_app_name, + channel, + version, + experiment, + experiment_branch +UNION ALL +SELECT + @submission_date AS submission_date, + TIMESTAMP_ADD( + TIMESTAMP_TRUNC(submission_timestamp, HOUR), + -- Aggregates event counts over 60-minute intervals + INTERVAL(DIV(EXTRACT(MINUTE FROM submission_timestamp), 60) * 60) MINUTE + ) AS window_start, + TIMESTAMP_ADD( + TIMESTAMP_TRUNC(submission_timestamp, HOUR), + INTERVAL((DIV(EXTRACT(MINUTE FROM submission_timestamp), 60) + 1) * 60) MINUTE + ) AS window_end, + event.category AS event_category, + event.name AS event_name, + event_extra.key AS event_extra_key, + normalized_country_code AS country, "Firefox Reality" AS normalized_app_name, channel, version, @@ -956,6 +1169,574 @@ event.name AS event_name, event_extra.key AS event_extra_key, normalized_country_code AS country, + "Lockwise for Android" AS normalized_app_name, + channel, + version, + -- Access experiment information. + -- Additional iteration is necessary to aggregate total event count across experiments + -- which is denoted with "*". + -- Some clients are enrolled in multiple experiments, so simply summing up the totals + -- across all the experiments would double count events. + CASE + experiment_index + WHEN ARRAY_LENGTH(ping_info.experiments) + THEN "*" + ELSE ping_info.experiments[SAFE_OFFSET(experiment_index)].key + END AS experiment, + CASE + experiment_index + WHEN ARRAY_LENGTH(ping_info.experiments) + THEN "*" + ELSE ping_info.experiments[SAFE_OFFSET(experiment_index)].value.branch + END AS experiment_branch, + COUNT(*) AS total_events +FROM + ( + SELECT + submission_timestamp, + events, + normalized_country_code, + client_info.app_channel AS channel, + client_info.app_display_version AS version, + ping_info + FROM + `moz-fx-data-shared-prod.mozilla_lockbox_stable.events_v1` + ) +CROSS JOIN + UNNEST(events) AS event, + -- Iterator for accessing experiments. + -- Add one more for aggregating events across all experiments + UNNEST(GENERATE_ARRAY(0, ARRAY_LENGTH(ping_info.experiments))) AS experiment_index +LEFT JOIN + UNNEST(event.extra) AS event_extra +WHERE + DATE(submission_timestamp) = @submission_date +GROUP BY + submission_date, + window_start, + window_end, + event_category, + event_name, + event_extra_key, + country, + normalized_app_name, + channel, + version, + experiment, + experiment_branch +UNION ALL +SELECT + @submission_date AS submission_date, + TIMESTAMP_ADD( + TIMESTAMP_TRUNC(submission_timestamp, HOUR), + -- Aggregates event counts over 60-minute intervals + INTERVAL(DIV(EXTRACT(MINUTE FROM submission_timestamp), 60) * 60) MINUTE + ) AS window_start, + TIMESTAMP_ADD( + TIMESTAMP_TRUNC(submission_timestamp, HOUR), + INTERVAL((DIV(EXTRACT(MINUTE FROM submission_timestamp), 60) + 1) * 60) MINUTE + ) AS window_end, + event.category AS event_category, + event.name AS event_name, + event_extra.key AS event_extra_key, + normalized_country_code AS country, + "Lockwise for iOS" AS normalized_app_name, + channel, + version, + -- Access experiment information. + -- Additional iteration is necessary to aggregate total event count across experiments + -- which is denoted with "*". + -- Some clients are enrolled in multiple experiments, so simply summing up t ```⚠️ Only part of the diff is displayed.
BenWu
fixes #5797
This recreates the materialized views that were removed from the generated sql in https://github.com/mozilla/bigquery-etl/pull/5576. Looking at the generated sql diff,
monitoring/event_monitoring_live/view.sqlwasn't changed so I think that means the nothing new gets added or removed as expected (other thangleanjs_docs_derived.event_monitoring_live_v1).@scholtzan One thing I'm not sure about is that this adds
events_v1to the union in some of the materialized views. Did we intentionally want to exclude those from the views for apps not listed in the bqetl config?Checklist for reviewer:
<username>:<branch>of the fork as parameter. The parameter will also show up in the logs of themanual-trigger-required-for-forkCI task together with more detailed instructions.For modifications to schemas in restricted namespaces (see
CODEOWNERS):┆Issue is synchronized with this Jira Task