Add views for seeing outerbounds job compute costs

chelseatroy commented 2 months ago

These are views for a dashboard that will allow decision-makers to track our compute costs on remotely run machine learning training jobs. The referenced tables have now been created in the bigquery console; after this merges, the next step is for me to expose these views for inclusion in Looker dashboards.

Checklist for reviewer:

[ ] Commits should reference a bug or github issue, if relevant (if a bug is referenced, the pull request should include the bug number in the title).
[ ] If the PR comes from a fork, trigger integration CI tests by running the Push to upstream workflow and provide the <username>:<branch> of the fork as parameter. The parameter will also show up in the logs of the manual-trigger-required-for-fork CI task together with more detailed instructions.
[ ] If adding a new field to a query, ensure that the schema and dependent downstream schemas have been updated.
[ ] When adding a new derived dataset, ensure that data is not available already (fully or partially) and recommend extending an existing dataset in favor of creating new ones. Data can be available in the bigquery-etl repository, looker-hub or in looker-spoke-default.

For modifications to schemas in restricted namespaces (see CODEOWNERS):

[ ] Follow the change control procedure

┆Issue is synchronized with this Jira Task

dataops-ci-bot commented 2 months ago

Integration report for "Add views for seeing outerbounds job compute costs"

`sql.diff`

Click to expand!

```diff Only in /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod: mlops diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/mlops/outerbounds_cost_per_flow/metadata.yaml /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/mlops/outerbounds_cost_per_flow/metadata.yaml --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/mlops/outerbounds_cost_per_flow/metadata.yaml 1970-01-01 00:00:00.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/mlops/outerbounds_cost_per_flow/metadata.yaml 2024-07-22 20:45:41.000000000 +0000 @@ -0,0 +1,5 @@ + +# Generated by bigquery_etl.dependency +references: + view.sql: + - moz-fx-data-shared-prod.billing_syndicate.gcp_billing_export_resource_v1_01E7D5_97288E_E2EBA0 diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/mlops/outerbounds_cost_per_flow/view.sql /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/mlops/outerbounds_cost_per_flow/view.sql --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/mlops/outerbounds_cost_per_flow/view.sql 1970-01-01 00:00:00.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/mlops/outerbounds_cost_per_flow/view.sql 2024-07-22 20:48:11.000000000 +0000 @@ -0,0 +1,34 @@ +CREATE OR REPLACE VIEW + `moz-fx-data-shared-prod.mlops.outerbounds_cost_per_flow` +AS +WITH cost_by_run AS ( + SELECT + DATE_TRUNC(usage_start_time, DAY) AS invoice_day, + labels.value AS run_id, + labels.value, + IFNULL(SAFE.LEFT(labels.value, INSTR(labels.value, '-', -1) - 1), labels.value) AS flow_name, + SUM(cost) AS cost_usd + FROM + moz - fx - data - shared - prod.billing_syndicate.gcp_billing_export_resource_v1_01E7D5_97288E_E2EBA0 + JOIN + UNNEST(labels) AS labels + ON labels.key = "k8s-label/workflows.argoproj.io/workflow" + WHERE + project.id = "moz-fx-mfouterbounds-prod-f98d" + AND usage_start_time >= '2024-07-10' + GROUP BY + 1, + 2 + ORDER BY + 3 DESC +) +SELECT + flow_name, + SUM(cost_usd) AS total_cost_usd, + COUNT(flow_name) AS num_runs +FROM + cost_by_run +GROUP BY + 1 +ORDER BY + 2 DESC diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/mlops/outerbounds_cost_per_flow_run/metadata.yaml /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/mlops/outerbounds_cost_per_flow_run/metadata.yaml --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/mlops/outerbounds_cost_per_flow_run/metadata.yaml 1970-01-01 00:00:00.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/mlops/outerbounds_cost_per_flow_run/metadata.yaml 2024-07-22 20:45:41.000000000 +0000 @@ -0,0 +1,5 @@ + +# Generated by bigquery_etl.dependency +references: + view.sql: + - moz-fx-data-shared-prod.billing_syndicate.gcp_billing_export_resource_v1_01E7D5_97288E_E2EBA0 diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/mlops/outerbounds_cost_per_flow_run/view.sql /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/mlops/outerbounds_cost_per_flow_run/view.sql --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/mlops/outerbounds_cost_per_flow_run/view.sql 1970-01-01 00:00:00.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/mlops/outerbounds_cost_per_flow_run/view.sql 2024-07-22 20:48:11.000000000 +0000 @@ -0,0 +1,22 @@ +CREATE OR REPLACE VIEW + `moz-fx-data-shared-prod.mlops.outerbounds_cost_per_flow_run` +AS +SELECT + DATE_TRUNC(usage_start_time, DAY) AS invoice_day, + labels.value AS run_id, + labels.value, + IFNULL(SAFE.LEFT(labels.value, INSTR(labels.value, '-', -1) - 1), labels.value) AS flow_name, + SUM(cost) AS cost +FROM + moz - fx - data - shared - prod.billing_syndicate.gcp_billing_export_resource_v1_01E7D5_97288E_E2EBA0 +JOIN + UNNEST(labels) AS labels + ON labels.key = "k8s-label/workflows.argoproj.io/workflow" +WHERE + project.id = "moz-fx-mfouterbounds-prod-f98d" + AND usage_start_time >= '2024-07-10' +GROUP BY + 1, + 2 +ORDER BY + 3 DESC ```

Link to full diff

dataops-ci-bot commented 2 months ago

Integration report for "Move views to a more fitting location"

`sql.diff`

Click to expand!

```diff Only in /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/monitoring_derived: outerbounds_cost_per_flow_run_v1 Only in /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/monitoring_derived: outerbounds_cost_per_flow_v1 diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/monitoring_derived/outerbounds_cost_per_flow_run_v1/metadata.yaml /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/monitoring_derived/outerbounds_cost_per_flow_run_v1/metadata.yaml --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/monitoring_derived/outerbounds_cost_per_flow_run_v1/metadata.yaml 1970-01-01 00:00:00.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/monitoring_derived/outerbounds_cost_per_flow_run_v1/metadata.yaml 2024-07-22 21:32:33.000000000 +0000 @@ -0,0 +1,13 @@ +friendly_name: Outerbounds Cost Per Flow Run V1 +description: |- + Please provide a description for the query +owners: [] +labels: {} +bigquery: null +workgroup_access: +- role: roles/bigquery.dataViewer + members: + - workgroup:mozilla-confidential +references: + view.sql: + - moz-fx-data-shared-prod.billing_syndicate.gcp_billing_export_resource_v1_01E7D5_97288E_E2EBA0 diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/monitoring_derived/outerbounds_cost_per_flow_run_v1/view.sql /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/monitoring_derived/outerbounds_cost_per_flow_run_v1/view.sql --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/monitoring_derived/outerbounds_cost_per_flow_run_v1/view.sql 1970-01-01 00:00:00.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/monitoring_derived/outerbounds_cost_per_flow_run_v1/view.sql 2024-07-22 21:30:38.000000000 +0000 @@ -0,0 +1,22 @@ +CREATE OR REPLACE VIEW + `moz-fx-data-shared-prod.monitoring_derived.outerbounds_cost_per_flow_run_v1` +AS +SELECT + DATE_TRUNC(usage_start_time, DAY) AS invoice_day, + labels.value AS run_id, + labels.value, + IFNULL(SAFE.LEFT(labels.value, INSTR(labels.value, '-', -1) - 1), labels.value) AS flow_name, + SUM(cost) AS cost +FROM + moz - fx - data - shared - prod.billing_syndicate.gcp_billing_export_resource_v1_01E7D5_97288E_E2EBA0 +JOIN + UNNEST(labels) AS labels + ON labels.key = "k8s-label/workflows.argoproj.io/workflow" +WHERE + project.id = "moz-fx-mfouterbounds-prod-f98d" + AND usage_start_time >= '2024-07-10' +GROUP BY + 1, + 2 +ORDER BY + 3 DESC diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/monitoring_derived/outerbounds_cost_per_flow_v1/metadata.yaml /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/monitoring_derived/outerbounds_cost_per_flow_v1/metadata.yaml --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/monitoring_derived/outerbounds_cost_per_flow_v1/metadata.yaml 1970-01-01 00:00:00.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/monitoring_derived/outerbounds_cost_per_flow_v1/metadata.yaml 2024-07-22 21:32:33.000000000 +0000 @@ -0,0 +1,13 @@ +friendly_name: Outerbounds Cost Per Flow V1 +description: |- + Please provide a description for the query +owners: [] +labels: {} +bigquery: null +workgroup_access: +- role: roles/bigquery.dataViewer + members: + - workgroup:mozilla-confidential +references: + view.sql: + - moz-fx-data-shared-prod.billing_syndicate.gcp_billing_export_resource_v1_01E7D5_97288E_E2EBA0 diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/monitoring_derived/outerbounds_cost_per_flow_v1/view.sql /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/monitoring_derived/outerbounds_cost_per_flow_v1/view.sql --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/monitoring_derived/outerbounds_cost_per_flow_v1/view.sql 1970-01-01 00:00:00.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/monitoring_derived/outerbounds_cost_per_flow_v1/view.sql 2024-07-22 21:30:38.000000000 +0000 @@ -0,0 +1,34 @@ +CREATE OR REPLACE VIEW + `moz-fx-data-shared-prod.monitoring_derived.outerbounds_cost_per_flow_v1` +AS +WITH cost_by_run AS ( + SELECT + DATE_TRUNC(usage_start_time, DAY) AS invoice_day, + labels.value AS run_id, + labels.value, + IFNULL(SAFE.LEFT(labels.value, INSTR(labels.value, '-', -1) - 1), labels.value) AS flow_name, + SUM(cost) AS cost_usd + FROM + moz - fx - data - shared - prod.billing_syndicate.gcp_billing_export_resource_v1_01E7D5_97288E_E2EBA0 + JOIN + UNNEST(labels) AS labels + ON labels.key = "k8s-label/workflows.argoproj.io/workflow" + WHERE + project.id = "moz-fx-mfouterbounds-prod-f98d" + AND usage_start_time >= '2024-07-10' + GROUP BY + 1, + 2 + ORDER BY + 3 DESC +) +SELECT + flow_name, + SUM(cost_usd) AS total_cost_usd, + COUNT(flow_name) AS num_runs +FROM + cost_by_run +GROUP BY + 1 +ORDER BY + 2 DESC ```

Link to full diff

dataops-ci-bot commented 2 months ago

Integration report for "These table names go in backticks"

`sql.diff`

Click to expand!

```diff Only in /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/monitoring_derived: outerbounds_cost_per_flow_run_v1 Only in /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/monitoring_derived: outerbounds_cost_per_flow_v1 diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/monitoring_derived/outerbounds_cost_per_flow_run_v1/metadata.yaml /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/monitoring_derived/outerbounds_cost_per_flow_run_v1/metadata.yaml --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/monitoring_derived/outerbounds_cost_per_flow_run_v1/metadata.yaml 1970-01-01 00:00:00.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/monitoring_derived/outerbounds_cost_per_flow_run_v1/metadata.yaml 2024-07-22 22:17:13.000000000 +0000 @@ -0,0 +1,13 @@ +friendly_name: Outerbounds Cost Per Flow Run V1 +description: |- + Please provide a description for the query +owners: [] +labels: {} +bigquery: null +workgroup_access: +- role: roles/bigquery.dataViewer + members: + - workgroup:mozilla-confidential +references: + view.sql: + - moz-fx-data-shared-prod.billing_syndicate.gcp_billing_export_resource_v1_01E7D5_97288E_E2EBA0 diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/monitoring_derived/outerbounds_cost_per_flow_run_v1/view.sql /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/monitoring_derived/outerbounds_cost_per_flow_run_v1/view.sql --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/monitoring_derived/outerbounds_cost_per_flow_run_v1/view.sql 1970-01-01 00:00:00.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/monitoring_derived/outerbounds_cost_per_flow_run_v1/view.sql 2024-07-22 22:15:03.000000000 +0000 @@ -0,0 +1,21 @@ +CREATE OR REPLACE VIEW + `moz-fx-data-shared-prod.monitoring_derived.outerbounds_cost_per_flow_run_v1` +AS +SELECT + DATE_TRUNC(usage_start_time, DAY) AS invoice_day, + labels.value AS run_id, + IFNULL(SAFE.LEFT(labels.value, INSTR(labels.value, '-', -1) - 1), labels.value) AS flow_name, + SUM(cost) AS cost_usd +FROM + `moz-fx-data-shared-prod.billing_syndicate.gcp_billing_export_resource_v1_01E7D5_97288E_E2EBA0` +JOIN + UNNEST(labels) AS labels + ON labels.key = "k8s-label/workflows.argoproj.io/workflow" +WHERE + project.id = "moz-fx-mfouterbounds-prod-f98d" + AND usage_start_time >= '2024-07-10' +GROUP BY + 1, + 2 +ORDER BY + 3 DESC diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/monitoring_derived/outerbounds_cost_per_flow_v1/metadata.yaml /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/monitoring_derived/outerbounds_cost_per_flow_v1/metadata.yaml --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/monitoring_derived/outerbounds_cost_per_flow_v1/metadata.yaml 1970-01-01 00:00:00.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/monitoring_derived/outerbounds_cost_per_flow_v1/metadata.yaml 2024-07-22 22:17:13.000000000 +0000 @@ -0,0 +1,13 @@ +friendly_name: Outerbounds Cost Per Flow V1 +description: |- + Please provide a description for the query +owners: [] +labels: {} +bigquery: null +workgroup_access: +- role: roles/bigquery.dataViewer + members: + - workgroup:mozilla-confidential +references: + view.sql: + - moz-fx-data-shared-prod.billing_syndicate.gcp_billing_export_resource_v1_01E7D5_97288E_E2EBA0 diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/monitoring_derived/outerbounds_cost_per_flow_v1/view.sql /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/monitoring_derived/outerbounds_cost_per_flow_v1/view.sql --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/monitoring_derived/outerbounds_cost_per_flow_v1/view.sql 1970-01-01 00:00:00.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/monitoring_derived/outerbounds_cost_per_flow_v1/view.sql 2024-07-22 22:15:03.000000000 +0000 @@ -0,0 +1,33 @@ +CREATE OR REPLACE VIEW + `moz-fx-data-shared-prod.monitoring_derived.outerbounds_cost_per_flow_v1` +AS +WITH cost_by_run AS ( + SELECT + DATE_TRUNC(usage_start_time, DAY) AS invoice_day, + labels.value AS run_id, + IFNULL(SAFE.LEFT(labels.value, INSTR(labels.value, '-', -1) - 1), labels.value) AS flow_name, + SUM(cost) AS cost_usd + FROM + `moz-fx-data-shared-prod.billing_syndicate.gcp_billing_export_resource_v1_01E7D5_97288E_E2EBA0` + JOIN + UNNEST(labels) AS labels + ON labels.key = "k8s-label/workflows.argoproj.io/workflow" + WHERE + project.id = "moz-fx-mfouterbounds-prod-f98d" + AND usage_start_time >= '2024-07-10' + GROUP BY + 1, + 2 + ORDER BY + 3 DESC +) +SELECT + flow_name, + SUM(cost_usd) AS total_cost_usd, + COUNT(flow_name) AS num_runs +FROM + cost_by_run +GROUP BY + 1 +ORDER BY + 2 DESC ```

Link to full diff

dataops-ci-bot commented 2 months ago

Integration report for "Skip dry run for these tables"

`sql.diff`

Click to expand!

```diff diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/dags/bqetl_generated_funnels.py /tmp/workspace/generated-sql/dags/bqetl_generated_funnels.py --- /tmp/workspace/main-generated-sql/dags/bqetl_generated_funnels.py 2024-07-23 17:04:23.000000000 +0000 +++ /tmp/workspace/generated-sql/dags/bqetl_generated_funnels.py 2024-07-23 17:22:38.000000000 +0000 @@ -63,18 +63,6 @@ pool="DATA_ENG_EXTERNALTASKSENSOR", ) - wait_for_accounts_frontend_derived__events_stream__v1 = ExternalTaskSensor( - task_id="wait_for_accounts_frontend_derived__events_stream__v1", - external_dag_id="bqetl_glean_usage", - external_task_id="accounts_frontend.accounts_frontend_derived__events_stream__v1", - execution_delta=datetime.timedelta(seconds=10800), - check_existence=True, - mode="reschedule", - allowed_states=ALLOWED_STATES, - failed_states=FAILED_STATES, - pool="DATA_ENG_EXTERNALTASKSENSOR", - ) - wait_for_checks__fail_fenix_derived__firefox_android_clients__v1 = ( ExternalTaskSensor( task_id="wait_for_checks__fail_fenix_derived__firefox_android_clients__v1", @@ -214,21 +202,6 @@ depends_on_past=False, ) - accounts_frontend_derived__reg_engagement_funnel__v1 = bigquery_etl_query( - task_id="accounts_frontend_derived__reg_engagement_funnel__v1", - destination_table="reg_engagement_funnel_v1", - dataset_id="accounts_frontend_derived", - project_id="moz-fx-data-shared-prod", - owner="ksiegler@mozilla.org", - email=[ - "ascholtz@mozilla.com", - "ksiegler@mozilla.org", - "telemetry-alerts@mozilla.com", - ], - date_partition_parameter="submission_date", - depends_on_past=False, - ) - accounts_frontend_derived__registration_funnels_by_service__v1 = bigquery_etl_query( task_id="accounts_frontend_derived__registration_funnels_by_service__v1", destination_table="registration_funnels_by_service_v1", @@ -276,6 +249,21 @@ ) ) + monitor_frontend_derived__monitor_dashboard_user_journey_funnels__v1 = bigquery_etl_query( + task_id="monitor_frontend_derived__monitor_dashboard_user_journey_funnels__v1", + destination_table="monitor_dashboard_user_journey_funnels_v1", + dataset_id="monitor_frontend_derived", + project_id="moz-fx-data-shared-prod", + owner="ksiegler@mozilla.org", + email=[ + "ascholtz@mozilla.com", + "ksiegler@mozilla.org", + "telemetry-alerts@mozilla.com", + ], + date_partition_parameter="submission_date", + depends_on_past=False, + ) + accounts_frontend_derived__email_first_reg_login_funnels__v1.set_upstream( wait_for_copy_deduplicate_all ) @@ -296,10 +284,6 @@ wait_for_copy_deduplicate_all ) - accounts_frontend_derived__reg_engagement_funnel__v1.set_upstream( - wait_for_accounts_frontend_derived__events_stream__v1 - ) - accounts_frontend_derived__registration_funnels_by_service__v1.set_upstream( wait_for_copy_deduplicate_all ) @@ -325,3 +309,7 @@ firefox_accounts_derived__registration_funnels_legacy_events__v1.set_upstream( wait_for_firefox_accounts_derived__fxa_stdout_events__v1 ) + + monitor_frontend_derived__monitor_dashboard_user_journey_funnels__v1.set_upstream( + wait_for_copy_deduplicate_all + ) Only in /tmp/workspace/main-generated-sql/sql/mozfun/glam: histogram_cast_struct Only in /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/accounts_frontend_derived: reg_engagement_funnel_v1 Only in /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/monitoring_derived: outerbounds_cost_per_flow_run_v1 Only in /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/monitoring_derived: outerbounds_cost_per_flow_v1 diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/mozfun/glam/histogram_cast_struct/metadata.yaml /tmp/workspace/generated-sql/sql/mozfun/glam/histogram_cast_struct/metadata.yaml --- /tmp/workspace/main-generated-sql/sql/mozfun/glam/histogram_cast_struct/metadata.yaml 2024-07-23 16:58:34.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/mozfun/glam/histogram_cast_struct/metadata.yaml 1970-01-01 00:00:00.000000000 +0000 @@ -1,3 +0,0 @@ -description: | - Cast a String-based JSON histogram to an Array of Structs -friendly_name: Histogram cast Struct diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/mozfun/glam/histogram_cast_struct/udf.sql /tmp/workspace/generated-sql/sql/mozfun/glam/histogram_cast_struct/udf.sql --- /tmp/workspace/main-generated-sql/sql/mozfun/glam/histogram_cast_struct/udf.sql 2024-07-23 16:58:34.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/mozfun/glam/histogram_cast_struct/udf.sql 1970-01-01 00:00:00.000000000 +0000 @@ -1,35 +0,0 @@ -/* -Casts a JSON String histogram into a ARRAY>. -I found no SQL native way to do this. If you have, please open change or open a ticket. -*/ -CREATE OR REPLACE FUNCTION glam.histogram_cast_struct(json_str STRING) -RETURNS ARRAY> -LANGUAGE js -AS - """ - if (!json_str) { - return null - } - const json_dict = JSON.parse(json_str); - const entries = Object.entries(json_dict).map( - (r)=>Object.fromEntries( - [["KEY", parseFloat(r[0])],["value", parseFloat(r[1])]] - ) - ); - return entries; -"""; - -SELECT - assert.array_equals( - ARRAY>[("0", 0.1111), ("1", 0.6667), ("2", 0)], - glam.histogram_cast_struct('{"0":0.1111,"1":0.6667,"2":0}') - ), - assert.array_equals( - ARRAY>[("0", 0.1111), ("1", 0.6667), ("2", 0), ("10", 100)], - glam.histogram_cast_struct('{"0":0.1111,"1":0.6667,"2":0,"10":100}') - ), - assert.array_empty(glam.histogram_cast_struct('{}')), - assert.array_equals( - ARRAY>[("always", 0.5), ("never", 0.5)], - glam.histogram_cast_struct('{"always":0.5,"never":0.5}') - ) diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-glam-prod-fca7/glam_etl/glam_desktop_beta_aggregates/view.sql /tmp/workspace/generated-sql/sql/moz-fx-data-glam-prod-fca7/glam_etl/glam_desktop_beta_aggregates/view.sql --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-glam-prod-fca7/glam_etl/glam_desktop_beta_aggregates/view.sql 2024-07-23 16:58:34.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-glam-prod-fca7/glam_etl/glam_desktop_beta_aggregates/view.sql 2024-07-23 16:59:16.000000000 +0000 @@ -1,24 +1,11 @@ CREATE OR REPLACE VIEW - `moz-fx-data-glam-prod-fca7.glam_etl.glam_desktop_beta_aggregates` AS ( - WITH base AS ( - SELECT - * EXCEPT (percentiles, non_norm_percentiles), - IF( - percentiles IS NOT NULL, - NULL, - udfs.glam_histogram_to_struct(histogram) - ) AS struct_histogram, - IF( - metric_type IN ("scalar", "keyed-scalar") - OR non_norm_percentiles IS NOT NULL, - NULL, - udfs.glam_histogram_to_struct(non_norm_histogram) - ) AS struct_non_norm_histogram, - percentiles AS existing_percentiles, - non_norm_percentiles AS existing_non_norm_percentiles - FROM + `moz-fx-data-glam-prod-fca7.glam_etl.glam_desktop_beta_aggregates` +AS +SELECT + * +FROM `moz-fx-data-glam-prod-fca7.glam_etl.glam_desktop_beta_aggregates_v1` - WHERE +WHERE -- filter based on https://github.com/mozilla/python_mozaggregator/blob/6c0119bfd0b535346c37cb3f707d998039d3e24b/mozaggregator/service.py#L51 ( metric NOT LIKE r"%search\_counts%" @@ -28,66 +15,3 @@ AND metric NOT LIKE r"%manager\_message\_size%" AND metric NOT LIKE r"%dropped\_frames\_proportion%" ) - ), - calculated_percentiles AS ( - SELECT - * EXCEPT (struct_histogram, struct_non_norm_histogram), - IF( - struct_histogram IS NOT NULL, - mozfun.glam.histogram_cast_json( - ARRAY>[ - ('0.1', mozfun.glam.percentile(0.1, struct_histogram, metric_type)), - ('1', mozfun.glam.percentile(1, struct_histogram, metric_type)), - ('5', mozfun.glam.percentile(5, struct_histogram, metric_type)), - ('25', mozfun.glam.percentile(25, struct_histogram, metric_type)), - ('50', mozfun.glam.percentile(50, struct_histogram, metric_type)), - ('75', mozfun.glam.percentile(75, struct_histogram, metric_type)), - ('95', mozfun.glam.percentile(95, struct_histogram, metric_type)), - ('99', mozfun.glam.percentile(99, struct_histogram, metric_type)), - ('99.9', mozfun.glam.percentile(99.9, struct_histogram, metric_type)) - ] - ), - existing_percentiles - ) AS percentiles, - IF( - struct_non_norm_histogram IS NOT NULL, - mozfun.glam.histogram_cast_json( - ARRAY>[ - ('0.1', mozfun.glam.percentile(0.1, struct_non_norm_histogram, metric_type)), - ('1', mozfun.glam.percentile(1, struct_non_norm_histogram, metric_type)), - ('5', mozfun.glam.percentile(5, struct_non_norm_histogram, metric_type)), - ('25', mozfun.glam.percentile(25, struct_non_norm_histogram, metric_type)), - ('50', mozfun.glam.percentile(50, struct_non_norm_histogram, metric_type)), - ('75', mozfun.glam.percentile(75, struct_non_norm_histogram, metric_type)), - ('95', mozfun.glam.percentile(95, struct_non_norm_histogram, metric_type)), - ('99', mozfun.glam.percentile(99, struct_non_norm_histogram, metric_type)), - ('99.9', mozfun.glam.percentile(99.9, struct_non_norm_histogram, metric_type)) - ] - ), - existing_non_norm_percentiles - ) AS non_norm_percentiles - FROM - base - ) - SELECT - version, - os, - build_id, - process, - metric, - metric_key, - client_agg_type, - metric_type, - total_users, - histogram, - percentiles, - total_sample, - non_norm_histogram, - IF( - metric_type IN ("scalar", "keyed-scalar"), - percentiles, - non_norm_percentiles - ) AS non_norm_percentiles - FROM - calculated_percentiles - ) diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-glam-prod-fca7/glam_etl/glam_desktop_nightly_aggregates/view.sql /tmp/workspace/generated-sql/sql/moz-fx-data-glam-prod-fca7/glam_etl/glam_desktop_nightly_aggregates/view.sql --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-glam-prod-fca7/glam_etl/glam_desktop_nightly_aggregates/view.sql 2024-07-23 16:58:34.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-glam-prod-fca7/glam_etl/glam_desktop_nightly_aggregates/view.sql 2024-07-23 16:59:16.000000000 +0000 @@ -1,24 +1,11 @@ CREATE OR REPLACE VIEW - `moz-fx-data-glam-prod-fca7.glam_etl.glam_desktop_nightly_aggregates` AS ( - WITH base AS ( - SELECT - * EXCEPT (percentiles, non_norm_percentiles), - IF( - percentiles IS NOT NULL, - NULL, - udfs.glam_histogram_to_struct(histogram) - ) AS struct_histogram, - IF( - metric_type IN ("scalar", "keyed-scalar") - OR non_norm_percentiles IS NOT NULL, - NULL, - udfs.glam_histogram_to_struct(non_norm_histogram) - ) AS struct_non_norm_histogram, - percentiles AS existing_percentiles, - non_norm_percentiles AS existing_non_norm_percentiles - FROM + `moz-fx-data-glam-prod-fca7.glam_etl.glam_desktop_nightly_aggregates` +AS +SELECT + * +FROM `moz-fx-data-glam-prod-fca7.glam_etl.glam_desktop_nightly_aggregates_v1` - WHERE +WHERE -- filter based on https://github.com/mozilla/python_mozaggregator/blob/6c0119bfd0b535346c37cb3f707d998039d3e24b/mozaggregator/service.py#L51 ( metric NOT LIKE r"%search\_counts%" @@ -28,66 +15,3 @@ AND metric NOT LIKE r"%manager\_message\_size%" AND metric NOT LIKE r"%dropped\_frames\_proportion%" ) - ), - calculated_percentiles AS ( - SELECT - * EXCEPT (struct_histogram, struct_non_norm_histogram), - IF( - struct_histogram IS NOT NULL, - mozfun.glam.histogram_cast_json( - ARRAY>[ - ('0.1', mozfun.glam.percentile(0.1, struct_histogram, metric_type)), - ('1', mozfun.glam.percentile(1, struct_histogram, metric_type)), - ('5', mozfun.glam.percentile(5, struct_histogram, metric_type)), - ('25', mozfun.glam.percentile(25, struct_histogram, metric_type)), - ('50', mozfun.glam.percentile(50, struct_histogram, metric_type)), - ('75', mozfun.glam.percentile(75, struct_histogram, metric_type)), - ('95', mozfun.glam.percentile(95, struct_histogram, metric_type)), - ('99', mozfun.glam.percentile(99, struct_histogram, metric_type)), - ('99.9', mozfun.glam.percentile(99.9, struct_histogram, metric_type)) - ] - ), - existing_percentiles - ) AS percentiles, - IF( - struct_non_norm_histogram IS NOT NULL, - mozfun.glam.histogram_cast_json( - ARRAY>[ - ('0.1', mozfun.glam.percentile(0.1, struct_non_norm_histogram, metric_type)), - ('1', mozfun.glam.percentile(1, struct_non_norm_histogram, metric_type)), - ('5', mozfun.glam.percentile(5, struct_non_norm_histogram, metric_type)), - ('25', mozfun.glam.percentile(25, struct_non_norm_histogram, metric_type)), - ('50', mozfun.glam.percentile(50, struct_non_norm_histogram, metric_type)), - ('75', mozfun.glam.percentile(75, struct_non_norm_histogram, metric_type)), - ('95', mozfun.glam.percentile(95, struct_non_norm_histogram, metric_type)), - ('99', mozfun.glam.percentile(99, struct_non_norm_histogram, metric_type)), - ('99.9', mozfun.glam.percentile(99.9, struct_non_norm_histogram, metric_type)) - ] - ), - existing_non_norm_percentiles - ) AS non_norm_percentiles - FROM - base - ) - SELECT - version, - os, - build_id, - process, - metric, - metric_key, - client_agg_type, - metric_type, - total_users, - histogram, - percentiles, - total_sample, - non_norm_histogram, - IF( - metric_type IN ("scalar", "keyed-scalar"), - percentiles, - non_norm_percentiles - ) AS non_norm_percentiles - FROM - calculated_percentiles - ) diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-glam-prod-fca7/glam_etl/glam_desktop_release_aggregates/view.sql /tmp/workspace/generated-sql/sql/moz-fx-data-glam-prod-fca7/glam_etl/glam_desktop_release_aggregates/view.sql --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-glam-prod-fca7/glam_etl/glam_desktop_release_aggregates/view.sql 2024-07-23 16:58:34.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-glam-prod-fca7/glam_etl/glam_desktop_release_aggregates/view.sql 2024-07-23 16:59:16.000000000 +0000 @@ -1,24 +1,11 @@ CREATE OR REPLACE VIEW - `moz-fx-data-glam-prod-fca7.glam_etl.glam_desktop_release_aggregates` AS ( - WITH base AS ( - SELECT - * EXCEPT (percentiles, non_norm_percentiles), - IF( - percentiles IS NOT NULL, - NULL, - udfs.glam_histogram_to_struct(histogram) - ) AS struct_histogram, - IF( - metric_type IN ("scalar", "keyed-scalar") - OR non_norm_percentiles IS NOT NULL, - NULL, - udfs.glam_histogram_to_struct(non_norm_histogram) - ) AS struct_non_norm_histogram, - percentiles AS existing_percentiles, - non_norm_percentiles AS existing_non_norm_percentiles - FROM + `moz-fx-data-glam-prod-fca7.glam_etl.glam_desktop_release_aggregates` +AS +SELECT + * +FROM `moz-fx-data-glam-prod-fca7.glam_etl.glam_desktop_release_aggregates_v1` - WHERE +WHERE -- filter based on https://github.com/mozilla/python_mozaggregator/blob/6c0119bfd0b535346c37cb3f707d998039d3e24b/mozaggregator/service.py#L51 ( metric NOT LIKE r"%search\_counts%" @@ -28,66 +15,3 @@ AND metric NOT LIKE r"%manager\_message\_size%" AND metric NOT LIKE r"%dropped\_frames\_proportion%" ) - ), - calculated_percentiles AS ( - SELECT - * EXCEPT (struct_histogram, struct_non_norm_histogram), - IF( - struct_histogram IS NOT NULL, - mozfun.glam.histogram_cast_json( - ARRAY>[ - ('0.1', mozfun.glam.percentile(0.1, struct_histogram, metric_type)), - ('1', mozfun.glam.percentile(1, struct_histogram, metric_type)), - ('5', mozfun.glam.percentile(5, struct_histogram, metric_type)), - ('25', mozfun.glam.percentile(25, struct_histogram, metric_type)), - ('50', mozfun.glam.percentile(50, struct_histogram, metric_type)), - ('75', mozfun.glam.percentile(75, struct_histogram, metric_type)), - ('95', mozfun.glam.percentile(95, struct_histogram, metric_type)), - ('99', mozfun.glam.percentile(99, struct_histogram, metric_type)), - ('99.9', mozfun.glam.percentile(99.9, struct_histogram, metric_type)) - ] - ), - existing_percentiles - ) AS percentiles, - IF( - struct_non_norm_histogram IS NOT NULL, - mozfun.glam.histogram_cast_json( - ARRAY>[ - ('0.1', mozfun.glam.percentile(0.1, struct_non_norm_histogram, metric_type)), - ('1', mozfun.glam.percentile(1, struct_non_norm_histogram, metric_type)), - ('5', mozfun.glam.percentile(5, struct_non_norm_histogram, metric_type)), - ('25', mozfun.glam.percentile(25, struct_non_norm_histogram, metric_type)), - ('50', mozfun.glam.percentile(50, struct_non_norm_histogram, metric_type)), - ('75', mozfun.glam.percentile(75, struct_non_norm_histogram, metric_type)), - ('95', mozfun.glam.percentile(95, struct_non_norm_histogram, metric_type)), - ('99', mozfun.glam.percentile(99, struct_non_norm_histogram, metric_type)), - ('99.9', mozfun.glam.percentile(99.9, struct_non_norm_histogram, metric_type)) - ] - ), - existing_non_norm_percentiles - ) AS non_norm_percentiles - FROM - base - ) - SELECT - version, - os, - build_id, - process, - metric, - metric_key, - client_agg_type, - metric_type, - total_users, - histogram, - percentiles, - total_sample, - non_norm_histogram, - IF( - metric_type IN ("scalar", "keyed-scalar"), - percentiles, - non_norm_percentiles - ) AS non_norm_percentiles - FROM - calculated_percentiles - ) diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-glam-prod-fca7/glam_etl/glam_fenix_beta_aggregates/view.sql /tmp/workspace/generated-sql/sql/moz-fx-data-glam-prod-fca7/glam_etl/glam_fenix_beta_aggregates/view.sql --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-glam-prod-fca7/glam_etl/glam_fenix_beta_aggregates/view.sql 2024-07-23 16:58:34.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-glam-prod-fca7/glam_etl/glam_fenix_beta_aggregates/view.sql 2024-07-23 16:59:16.000000000 +0000 @@ -1,24 +1,11 @@ CREATE OR REPLACE VIEW - `moz-fx-data-glam-prod-fca7.glam_etl.glam_fenix_beta_aggregates` AS ( - WITH base AS ( - SELECT - * EXCEPT (percentiles, non_norm_percentiles), - IF( - percentiles IS NOT NULL, - NULL, - udfs.glam_histogram_to_struct(histogram) - ) AS struct_histogram, - IF( - metric_type IN ("counter", "labeled_counter", "quantity") - OR non_norm_percentiles IS NOT NULL, - NULL, - udfs.glam_histogram_to_struct(non_norm_histogram) - ) AS struct_non_norm_histogram, - percentiles AS existing_percentiles, - non_norm_percentiles AS existing_non_norm_percentiles - FROM + `moz-fx-data-glam-prod-fca7.glam_etl.glam_fenix_beta_aggregates` +AS +SELECT + * +FROM `moz-fx-data-glam-prod-fca7.glam_etl.glam_fenix_beta_aggregates_v1` - WHERE +WHERE -- filter based on https://github.com/mozilla/python_mozaggregator/blob/6c0119bfd0b535346c37cb3f707d998039d3e24b/mozaggregator/service.py#L51 ( metric NOT LIKE r"%search\_counts%" @@ -28,69 +15,3 @@ AND metric NOT LIKE r"%manager\_message\_size%" AND metric NOT LIKE r"%dropped\_frames\_proportion%" ) - ), - calculated_percentiles AS ( - SELECT - * EXCEPT (struct_histogram, struct_non_norm_histogram), - IF( - struct_histogram IS NOT NULL, - mozfun.glam.histogram_cast_json( - ARRAY>[ - ('0.1', mozfun.glam.percentile(0.1, struct_histogram, metric_type)), - ('1', mozfun.glam.percentile(1, struct_histogram, metric_type)), - ('5', mozfun.glam.percentile(5, struct_histogram, metric_type)), - ('25', mozfun.glam.percentile(25, struct_histogram, metric_type)), - ('50', mozfun.glam.percentile(50, struct_histogram, metric_type)), - ('75', mozfun.glam.percentile(75, struct_histogram, metric_type)), - ('95', mozfun.glam.percentile(95, struct_histogram, metric_type)), - ('99', mozfun.glam.percentile(99, struct_histogram, metric_type)), - ('99.9', mozfun.glam.percentile(99.9, struct_histogram, metric_type)) - ] - ), - existing_percentiles - ) AS percentiles, - IF( - struct_non_norm_histogram IS NOT NULL, - mozfun.glam.histogram_cast_json( - ARRAY>[ - ('0.1', mozfun.glam.percentile(0.1, struct_non_norm_histogram, metric_type)), - ('1', mozfun.glam.percentile(1, struct_non_norm_histogram, metric_type)), - ('5', mozfun.glam.percentile(5, struct_non_norm_histogram, metric_type)), - ('25', mozfun.glam.percentile(25, struct_non_norm_histogram, metric_type)), - ('50', mozfun.glam.percentile(50, struct_non_norm_histogram, metric_type)), - ('75', mozfun.glam.percentile(75, struct_non_norm_histogram, metric_type)), - ('95', mozfun.glam.percentile(95, struct_non_norm_histogram, metric_type)), - ('99', mozfun.glam.percentile(99, struct_non_norm_histogram, metric_type)), - ('99.9', mozfun.glam.percentile(99.9, struct_non_norm_histogram, metric_type)) - ] - ), - existing_non_norm_percentiles - ) AS non_norm_percentiles - FROM - base - ) - SELECT - channel, - version, - ping_type, - os, - build_id, - build_date, - metric, - metric_type, - metric_key, - client_agg_type, - total_users, - histogram, - percentiles, - app_id, - total_sample, - non_norm_histogram, - IF( - metric_type IN ("counter", "labeled_counter", "quantity"), - percentiles, - non_norm_percentiles - ) AS non_norm_percentiles - FROM - calculated_percentiles - ) diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-glam-prod-fca7/glam_etl/glam_fenix_nightly_aggregates/view.sql /tmp/workspace/generated-sql/sql/moz-fx-data-glam-prod-fca7/glam_etl/glam_fenix_nightly_aggregates/view.sql --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-glam-prod-fca7/glam_etl/glam_fenix_nightly_aggregates/view.sql 2024-07-23 16:58:34.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-glam-prod-fca7/glam_etl/glam_fenix_nightly_aggregates/view.sql 2024-07-23 16:59:16.000000000 +0000 @@ -1,24 +1,11 @@ CREATE OR REPLACE VIEW - `moz-fx-data-glam-prod-fca7.glam_etl.glam_fenix_nightly_aggregates` AS ( - WITH base AS ( - SELECT - * EXCEPT (percentiles, non_norm_percentiles), - IF( - percentiles IS NOT NULL, - NULL, - udfs.glam_histogram_to_struct(histogram) - ) AS struct_histogram, - IF( - metric_type IN ("counter", "labeled_counter", "quantity") - OR non_norm_percentiles IS NOT NULL, - NULL, - udfs.glam_histogram_to_struct(non_norm_histogram) - ) AS struct_non_norm_histogram, - percentiles AS existing_percentiles, - non_norm_percentiles AS existing_non_norm_percentiles - FROM + `moz-fx-data-glam-prod-fca7.glam_etl.glam_fenix_nightly_aggregates` +AS +SELECT + * +FROM `moz-fx-data-glam-prod-fca7.glam_etl.glam_fenix_nightly_aggregates_v1` - WHERE +WHERE -- filter based on https://github.com/mozilla/python_mozaggregator/blob/6c0119bfd0b535346c37cb3f707d998039d3e24b/mozaggregator/service.py#L51 ( metric NOT LIKE r"%search\_counts%" @@ -28,69 +15,3 @@ AND metric NOT LIKE r"%manager\_message\_size%" AND metric NOT LIKE r"%dropped\_frames\_proportion%" ) - ), - calculated_percentiles AS ( - SELECT - * EXCEPT (struct_histogram, struct_non_norm_histogram), - IF( - struct_histogram IS NOT NULL, - mozfun.glam.histogram_cast_json( - ARRAY>[ - ('0.1', mozfun.glam.percentile(0.1, struct_histogram, metric_type)), - ('1', mozfun.glam.percentile(1, struct_histogram, metric_type)), - ('5', mozfun.glam.percentile(5, struct_histogram, metric_type)), - ('25', mozfun.glam.percentile(25, struct_histogram, metric_type)), - ('50', mozfun.glam.percentile(50, struct_histogram, metric_type)), - ('75', mozfun.glam.percentile(75, struct_histogram, metric_type)), - ('95', mozfun.glam.percentile(95, struct_histogram, metric_type)), - ('99', mozfun.glam.percentile(99, struct_histogram, metric_type)), - ('99.9', mozfun.glam.percentile(99.9, struct_histogram, metric_type)) - ] - ), - existing_percentiles - ) AS percentiles, - IF( - struct_non_norm_histogram IS NOT NULL, - mozfun.glam.histogram_cast_json( - ARRAY>[ - ('0.1', mozfun.glam.percentile(0.1, struct_non_norm_histogram, metric_type)), - ('1', mozfun.glam.percentile(1, struct_non_norm_histogram, metric_type)), - ('5', mozfun.glam.percentile(5, struct_non_norm_histogram, metric_type)), - ('25', mozfun.glam.percentile(25, struct_non_norm_histogram, metric_type)), - ('50', mozfun.glam.percentile(50, struct_non_norm_histogram, metric_type)), - ('75', mozfun.glam.percentile(75, struct_non_norm_histogram, metric_type)), - ('95', mozfun.glam.percentile(95, struct_non_norm_histogram, metric_type)), - ('99', mozfun.glam.percentile(99, struct_non_norm_histogram, metric_type)), - ('99.9', mozfun.glam.percentile(99.9, struct_non_norm_histogram, metric_type)) - ] - ), - existing_non_norm_percentiles - ) AS non_norm_percentiles - FROM - base - ) - SELECT - channel, - version, - ping_type, - os, - build_id, - build_date, - metric, - metric_type, - metric_key, - client_agg_type, - total_users, - histogram, - percentiles, - app_id, - total_sample, - non_norm_histogram, - IF( - metric_type IN ("counter", "labeled_counter", "quantity"), - percentiles, - non_norm_percentiles - ) AS non_norm_percentiles - FROM - calculated_percentiles - ) diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-glam-prod-fca7/glam_etl/glam_fenix_release_aggregates/view.sql /tmp/workspace/generated-sql/sql/moz-fx-data-glam-prod-fca7/glam_etl/glam_fenix_release_aggregates/view.sql --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-glam-prod-fca7/glam_etl/glam_fenix_release_aggregates/view.sql 2024-07-23 16:58:34.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-glam-prod-fca7/glam_etl/glam_fenix_release_aggregates/view.sql 2024-07-23 16:59:16.000000000 +0000 @@ -1,24 +1,11 @@ CREATE OR REPLACE VIEW - `moz-fx-data-glam-prod-fca7.glam_etl.glam_fenix_release_aggregates` AS ( - WITH base AS ( - SELECT - * EXCEPT (percentiles, non_norm_percentiles), - IF( - percentiles IS NOT NULL, - NULL, - udfs.glam_histogram_to_struct(histogram) - ) AS struct_histogram, - IF( - metric_type IN ("counter", "labeled_counter", "quantity") - OR non_norm_percentiles IS NOT NULL, - NULL, - udfs.glam_histogram_to_struct(non_norm_histogram) - ) AS struct_non_norm_histogram, - percentiles AS existing_percentiles, - non_norm_percentiles AS existing_non_norm_percentiles - FROM + `moz-fx-data-glam-prod-fca7.glam_etl.glam_fenix_release_aggregates` +AS +SELECT + * +FROM `moz-fx-data-glam-prod-fca7.glam_etl.glam_fenix_release_aggregates_v1` - WHERE +WHERE -- filter based on https://github.com/mozilla/python_mozaggregator/blob/6c0119bfd0b535346c37cb3f707d998039d3e24b/mozaggregator/service.py#L51 ( metric NOT LIKE r"%search\_counts%" @@ -28,69 +15,3 @@ AND metric NOT LIKE r"%manager\_message\_size%" AND metric NOT LIKE r"%dropped\_frames\_proportion%" ) - ), - calculated_percentiles AS ( - SELECT - * EXCEPT (struct_histogram, struct_non_norm_histogram), - IF( - struct_histogram IS NOT NULL, - mozfun.glam.histogram_cast_json( - ARRAY>[ - ('0.1', mozfun.glam.percentile(0.1, struct_histogram, metric_type)), - ('1', mozfun.glam.percentile(1, struct_histogram, metric_type)), - ('5', mozfun.glam.percentile(5, struct_histogram, metric_type)), - ('25', mozfun.glam.percentile(25, struct_histogram, metric_type)), - ('50', mozfun.glam.percentile(50, struct_histogram, metric_type)), - ('75', mozfun.glam.percentile(75, struct_histogram, metric_type)), - ('95', mozfun.glam.percentile(95, struct_histogram, metric_type)), - ('99', mozfun.glam.percentile(99, struct_histogram, metric_type)), - ('99.9', mozfun.glam.percentile(99.9, struct_histogram, metric_type)) - ] - ), - existing_percentiles - ) AS percentiles, - IF( - struct_non_norm_histogram IS NOT NULL, - mozfun.glam.histogram_cast_json( - ARRAY>[ - ('0.1', mozfun.glam.percentile(0.1, struct_non_norm_histogram, metric_type)), - ('1', mozfun.glam.percentile(1, struct_non_norm_histogram, metric_type)), - ('5', mozfun.glam.percentile(5, struct_non_norm_histogram, metric_type)), - ('25', mozfun.glam.percentile(25, struct_non_norm_histogram, metric_type)), - ('50', mozfun.glam.percentile(50, struct_non_norm_histogram, metric_type)), - ('75', mozfun.glam.percentile(75, struct_non_norm_histogram, metric_type)), - ('95', mozfun.glam.percentile(95, struct_non_norm_histogram, metric_type)), - ('99', mozfun.glam.percentile(99, struct_non_norm_histogram, metric_type)), - ('99.9', mozfun.glam.percentile(99.9, struct_non_norm_histogram, metric_type)) - ] - ), - existing_non_norm_percentiles - ) AS non_norm_percentiles - FROM - base - ) - SELECT - channel, - version, - ping_type, - os, - build_id, - build_date, - metric, - metric_type, - metric_key, - client_agg_type, - total_users, - histogram, - percentiles, - app_id, - total_sample, - non_norm_histogram, - IF( - metric_type IN ("counter", "labeled_counter", "quantity"), - percentiles, - non_norm_percentiles - ) AS non_norm_percentiles - FROM - calculated_percentiles - ) diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-glam-prod-fca7/glam_etl/glam_fog_beta_aggregates/view.sql /tmp/workspace/generated-sql/sql/moz-fx-data-glam-prod-fca7/glam_etl/glam_fog_beta_aggregates/view.sql --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-glam-prod-fca7/glam_etl/glam_fog_beta_aggregates/view.sql 2024-07-23 16:58:34.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-glam-prod-fca7/glam_etl/glam_fog_beta_aggregates/view.sql 2024-07-23 16:59:16.000000000 +0000 @@ -1,24 +1,11 @@ CREATE OR REPLACE VIEW - `moz-fx-data-glam-prod-fca7.glam_etl.glam_fog_beta_aggregates` AS ( - WITH base AS ( - SELECT - * EXCEPT (percentiles, non_norm_percentiles), - IF( - percentiles IS NOT NULL, - NULL, - udfs.glam_histogram_to_struct(histogram) - ) AS struct_histogram, - IF( - metric_type IN ("counter", "labeled_counter", "quantity") - OR non_norm_percentiles IS NOT NULL, - NULL, - udfs.glam_histogram_to_struct(non_norm_histogram) - ) AS struct_non_norm_histogram, - percentiles AS existing_percentiles, - non_norm_percentiles AS existing_non_norm_percentiles - FROM + `moz-fx-data-glam-prod-fca7.glam_etl.glam_fog_beta_aggregates` +AS +SELECT + * +FROM `moz-fx-data-glam-prod-fca7.glam_etl.glam_fog_beta_aggregates_v1` - WHERE +WHERE -- filter based on https://github.com/mozilla/python_mozaggregator/blob/6c0119bfd0b535346c37cb3f707d998039d3e24b/mozaggregator/service.py#L51 ( metric NOT LIKE r"%search\_counts%" @@ -28,69 +15,3 @@ AND metric NOT LIKE r"%manager\_message\_size%" AND metric NOT LIKE r"%dropped\_frames\_proportion%" ) - ), - calculated_percentiles AS ( - SELECT - * EXCEPT (struct_histogram, struct_non_norm_histogram), - IF( - struct_histogram IS NOT NULL, - mozfun.glam.histogram_cast_json( - ARRAY>[ - ('0.1', mozfun.glam.percentile(0.1, struct_histogram, metric_type)), - ('1', mozfun.glam.percentile(1, struct_histogram, metric_type)), - ('5', mozfun.glam.percentile(5, struct_histogram, metric_type)), - ('25', mozfun.glam.percentile(25, struct_histogram, metric_type)), - ('50', mozfun.glam.percentile(50, struct_histogram, metric_type)), - ('75', mozfun.glam.percentile(75, struct_histogram, metric_type)), - ('95', mozfun.glam.percentile(95, struct_histogram, metric_type)), - ('99', mozfun.glam.percentile(99, struct_histogram, metric_type)), - ('99.9', mozfun.glam.percentile(99.9, struct_histogram, metric_type)) - ] - ), - existing_percentiles - ) AS percentiles, - IF( - struct_non_norm_histogram IS NOT NULL, - mozfun.glam.histogram_cast_json( - ARRAY>[ - ('0.1', mozfun.glam.percentile(0.1, struct_non_norm_histogram, metric_type)), - ('1', mozfun.glam.percentile(1, struct_non_norm_histogram, metric_type)), - ('5', mozfun.glam.percentile(5, struct_non_norm_histogram, metric_type)), - ('25', mozfun.glam.percentile(25, struct_non_norm_histogram, metric_type)), - ('50', mozfun.glam.percentile(50, struct_non_norm_histogram, metric_type)), - ('75', mozfun.glam.percentile(75, struct_non_norm_histogram, metric_type)), - ('95', mozfun.glam.percentile(95, struct_non_norm_histogram, metric_type)), - ('99', mozfun.glam.percentile(99, struct_non_norm_histogram, metric_type)), - ('99.9', mozfun.glam.percentile(99.9, struct_non_norm_histogram, metric_type)) - ] - ), - existing_non_norm_percentiles - ) AS non_norm_percentiles - FROM - base - ) - SELECT - app_id, - channel, - version, - ping_type, - os, - build_id, - build_date, - metric, - metric_type, - metric_key, - client_agg_type, - total_users, - histogram, - percentiles, - total_sample, - non_norm_histogram, - IF( - metric_type IN ("counter", "labeled_counter", "quantity"), - percentiles, - non_norm_percentiles - ) AS non_norm_percentiles - FROM - calculated_percentiles - ) diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-glam-prod-fca7/glam_etl/glam_fog_nightly_aggregates/view.sql /tmp/workspace/generated-sql/sql/moz-fx-data-glam-prod-fca7/glam_etl/glam_fog_nightly_aggregates/view.sql --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-glam-prod-fca7/glam_etl/glam_fog_nightly_aggregates/view.sql 2024-07-23 16:58:34.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-glam-prod-fca7/glam_etl/glam_fog_nightly_aggregates/view.sql 2024-07-23 16:59:16.000000000 +0000 @@ -1,24 +1,11 @@ CREATE OR REPLACE VIEW - `moz-fx-data-glam-prod-fca7.glam_etl.glam_fog_nightly_aggregates` AS ( - WITH base AS ( - SELECT - * EXCEPT (percentiles, non_norm_percentiles), - IF( - percentiles IS NOT NULL, - NULL, - udfs.glam_histogram_to_struct(histogram) - ) AS struct_histogram, - IF( - metric_type IN ("counter", "labeled_counter", "quantity") - OR non_norm_percentiles IS NOT NULL, - NULL, - udfs.glam_histogram_to_struct(non_norm_histogram) - ) AS struct_non_norm_histogram, - percentiles AS existing_percentiles, - non_norm_percentiles AS existing_non_norm_percentiles - FROM + `moz-fx-data-glam-prod-fca7.glam_etl.glam_fog_nightly_aggregates` +AS +SELECT + * +FROM `moz-fx-data-glam-prod-fca7.glam_etl.glam_fog_nightly_aggregates_v1` - WHERE +WHERE -- filter based on https://github.com/mozilla/python_mozaggregator/blob/6c0119bfd0b535346c37cb3f707d998039d3e24b/mozaggregator/service.py#L51 ( metric NOT LIKE r"%search\_counts%" @@ -28,69 +15,3 @@ AND metric NOT LIKE r"%manager\_message\_size%" AND metric NOT LIKE r"%dropped\_frames\_proportion%" ) - ), - calculated_percentiles AS ( - SELECT - * EXCEPT (struct_histogram, struct_non_norm_histogram), - IF( - struct_histogram IS NOT NULL, - mozfun.glam.histogram_cast_json( - ARRAY>[ - ('0.1', mozfun.glam.percentile(0.1, struct_histogram, metric_type)), - ('1', mozfun.glam.percentile(1, struct_histogram, metric_type)), - ('5', mozfun.glam.percentile(5, struct_histogram, metric_type)), - ('25', mozfun.glam.percentile(25, struct_histogram, metric_type)), - ('50', mozfun.glam.percentile(50, struct_histogram, metric_type)), - ('75', mozfun.glam.percentile(75, struct_histogram, metric_type)), - ('95', mozfun.glam.percentile(95, struct_histogram, metric_type)), - ('99', mozfun.glam.percentile(99, struct_histogram, metric_type)), - ('99.9', mozfun.glam.percentile(99.9, struct_histogram, metric_type)) - ] - ), - existing_percentiles - ) AS percentiles, - IF( - struct_non_norm_histogram IS NOT NULL, - mozfun.glam.histogram_cast_json( - ARRAY>[ - ('0.1', mozfun.glam.percentile(0.1, struct_non_norm_histogram, metric_type)), - ('1', mozfun.glam.percentile(1, struct_non_norm_histogram, metric_type)), - ('5', mozfun.glam.percentile(5, struct_non_norm_histogram, metric_type)), - ('25', mozfun.glam.percentile(25, struct_non_norm_histogram, metric_type)), - ('50', mozfun.glam.percentile(50, struct_non_norm_histogram, metric_type)), - ('75', mozfun.glam.percentile(75, struct_non_norm_histogram, metric_type)), - ('95', mozfun.glam.percentile(95, struct_non_norm_histogram, metric_type)), - ('99', mozfun.glam.percentile(99, struct_non_norm_histogram, metric_type)), - ('99.9', mozfun.glam.percentile(99.9, struct_non_norm_histogram, metric_type)) - ] - ), - existing_non_norm_percentiles - ) AS non_norm_percentiles - FROM - base - ) - SELECT - app_id, - channel, - version, - ping_type, - os, - build_id, - build_date, - metric, - metric_type, - metric_key, - client_agg_type, - total_users, - histogram, - percentiles, - total_sample, - non_norm_histogram, - IF( - metric_type IN ("counter", "labeled_counter", "quantity"), - percentiles, - non_norm_percentiles - ) AS non_norm_percentiles - FROM - calculated_percentiles - ) diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-glam-prod-fca7/glam_etl/glam_fog_release_aggregates/view.sql /tmp/workspace/generated-sql/sql/moz-fx-data-glam-prod-fca7/glam_etl/glam_fog_release_aggregates/view.sql --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-glam-prod-fca7/glam_etl/glam_fog_release_aggregates/view.sql 2024-07-23 16:58:34.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-glam-prod-fca7/glam_etl/glam_fog_release_aggregates/view.sql 2024-07-23 16:59:16.000000000 +0000 @@ -1,24 +1,11 @@ CREATE OR REPLACE VIEW - `moz-fx-data-glam-prod-fca7.glam_etl.glam_fog_release_aggregates` AS ( - WITH base AS ( - SELECT - * EXCEPT (percentiles, non_norm_percentiles), - IF( - percentiles IS NOT NULL, - NULL, - udfs.glam_histogram_to_struct(histogram) - ) AS struct_histogram, - IF( - metric_type IN ("counter", "labeled_counter", "quantity") - OR non_norm_percentiles IS NOT NULL, - NULL, - udfs.glam_histogram_to_struct(non_norm_histogram) - ) AS struct_non_norm_histogram, - percentiles AS existing_percentiles, - non_norm_percentiles AS existing_non_norm_percentiles - FROM + `moz-fx-data-glam-prod-fca7.glam_etl.glam_fog_release_aggregates` +AS +SELECT + * +FROM `moz-fx-data-glam-prod-fca7.glam_etl.glam_fog_release_aggregates_v1` - WHERE +WHERE -- filter based on https://github.com/mozilla/python_mozaggregator/blob/6c0119bfd0b535346c37cb3f707d998039d3e24b/mozaggregator/service.py#L51 ( metric NOT LIKE r"%search\_counts%" @@ -28,69 +15,3 @@ AND metric NOT LIKE r"%manager\_message\_size%" AND metric NOT LIKE r"%dropped\_frames\_proportion%" ) - ), - calculated_percentiles AS ( - SELECT - * EXCEPT (struct_histogram, struct_non_norm_histogram), - IF( - struct_histogram IS NOT NULL, - mozfun.glam.histogram_cast_json( - ARRAY>[ - ('0.1', mozfun.glam.percentile(0.1, struct_histogram, metric_type)), - ('1', mozfun.glam.percentile(1, struct_histogram, metric_type)), - ('5', mozfun.glam.percentile(5, struct_histogram, metric_type)), - ('25', mozfun.glam.percentile(25, struct_histogram, metric_type)), - ('50', mozfun.glam.percentile(50, struct_histogram, metric_type)), - ('75', mozfun.glam.percentile(75, struct_histogram, metric_type)), - ('95', mozfun.glam.percentile(95, struct_histogram, metric_type)), - ('99', mozfun.glam.percentile(99, struct_histogram, metric_type)), - ('99.9', mozfun.glam.percentile(99.9, struct_histogram, metric_type)) - ] - ), - existing_percentiles - ) AS percentiles, - IF( - struct_non_norm_histogram IS NOT NULL, - mozfun.glam.histogram_cast_json( - ARRAY>[ - ('0.1', mozfun.glam.percentile(0.1, struct_non_norm_histogram, metric_type)), - ('1', mozfun.glam.percentile(1, struct_non_norm_histogram, metric_type)), - ('5', mozfun.glam.percentile(5, struct_non_norm_histogram, metric_type)), - ('25', mozfun.glam.percentile(25, struct_non_norm_histogram, metric_type)), - ('50', mozfun.glam.percentile(50, struct_non_norm_histogram, metric_type)), - ('75', mozfun.glam.percentile(75, struct_non_norm_histogram, metric_type)), - ('95', mozfun.glam.percentile(95, struct_non_norm_histogram, metric_type)), - ('99', mozfun.glam.percentile(99, struct_non_norm_histogram, metric_type)), - ('99.9', mozfun.glam.percentile(99.9, struct_non_norm_histogram, metric_type)) - ] - ), - existing_non_norm_percentiles - ) AS non_norm_percentiles - FROM - base - ) - SELECT - app_id, - channel, - version, - ping_type, - os, - build_id, - build_date, - metric, - metric_type, - metric_key, - client_agg_type, - total_users, - histogram, - percentiles, - total_sample, - non_norm_histogram, - IF( - metric_type IN ("counter", "labeled_counter", "quantity"), - percentiles, - non_norm_percentiles - ) AS non_norm_percentiles - FROM - calculated_percentiles - ) diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/accounts_backend_derived/users_services_daily_v1/checks.sql /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/accounts_backend_derived/users_services_daily_v1/checks.sql --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/accounts_backend_derived/users_services_daily_v1/checks.sql 2024-07-23 16:58:34.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/accounts_backend_derived/users_services_daily_v1/checks.sql 2024-07-23 16:59:16.000000000 +0000 @@ -141,7 +141,7 @@ OR ABS(events_new.count_new - events_old.count_old) / LEAST( events_new.count_new, events_old.count_old - ) > 0.05 + ) > 0.02 ) ) SELECT diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/accounts_frontend_derived/reg_engagement_funnel_v1/metadata.yaml /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/accounts_frontend_derived/reg_engagement_funnel_v1/metadata.yaml --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/accounts_frontend_derived/reg_engagement_funnel_v1/metadata.yaml 2024-07-23 16:59:08.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/accounts_frontend_derived/reg_engagement_funnel_v1/metadata.yaml 1970-01-01 00:00:00.000000000 +0000 @@ -1,26 +0,0 @@ -friendly_name: Reg Engagement Funnel -description: |- - Please provide a description for the query -owners: -- ksiegler@mozilla.org -labels: - incremental: true - dag: bqetl_generated_funnels - owner1: ksiegler -scheduling: - dag_name: bqetl_generated_funnels -bigquery: - time_partitioning: - type: day - field: submission_date - require_partition_filter: false - expiration_days: null - range_partitioning: null - clustering: null -workgroup_access: -- role: roles/bigquery.dataViewer - members: - - workgroup:mozilla-confidential -references: - query.sql: - - mozdata.accounts_frontend.events_stream diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/accounts_frontend_derived/reg_engagement_funnel_v1/query.sql /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/accounts_frontend_derived/reg_engagement_funnel_v1/query.sql --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/accounts_frontend_derived/reg_engagement_funnel_v1/query.sql 2024-07-23 16:59:08.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/accounts_frontend_derived/reg_engagement_funnel_v1/query.sql 1970-01-01 00:00:00.000000000 +0000 @@ -1,870 +0,0 @@ --- extract the relevant fields for each funnel step and segment if necessary -WITH registration_password_age_engage_reg_view AS ( - SELECT - metrics.string.session_flow_id AS join_key, - IF( - COALESCE( - NULLIF(metrics.string.relying_party_oauth_client_id, ''), - NULLIF(metrics.string.relying_party_service, '') - ) = 'sync', - '5882386c6d801776', - COALESCE( - NULLIF(metrics.string.relying_party_oauth_client_id, ''), - NULLIF(metrics.string.relying_party_service, '') - ) - ) AS service, - DATE(submission_timestamp) AS submission_date, - metrics.string.account_user_id_sha256 AS client_id, - metrics.string.session_flow_id AS column - FROM - mozdata.accounts_frontend.events_stream - WHERE - {% if is_init() %} - DATE(submission_timestamp) >= DATE("2024-07-10") - {% else %} - DATE(submission_timestamp) = @submission_date - {% endif %} - AND event = 'reg.view' - AND metrics.string.session_flow_id != '' -), -registration_password_age_engage_reg_password_age_engage AS ( - SELECT - metrics.string.session_flow_id AS join_key, - prev.service AS service, - DATE(submission_timestamp) AS submission_date, - metrics.string.account_user_id_sha256 AS client_id, - metrics.string.session_flow_id AS column - FROM - mozdata.accounts_frontend.events_stream - INNER JOIN - registration_password_age_engage_reg_view AS prev - ON prev.submission_date = DATE(submission_timestamp) - AND prev.join_key = metrics.string.session_flow_id - WHERE - {% if is_init() %} - DATE(submission_timestamp) >= DATE("2024-07-10") - {% else %} - DATE(submission_timestamp) = @submission_date - {% endif %} - AND event = 'reg.engage' - AND metrics.string.session_flow_id != '' -), -registration_create_account_submit_reg_view AS ( - SELECT - metrics.string.session_flow_id AS join_key, - IF( - COALESCE( - NULLIF(metrics.string.relying_party_oauth_client_id, ''), - NULLIF(metrics.string.relying_party_service, '') - ) = 'sync', - '5882386c6d801776', - COALESCE( - NULLIF(metrics.string.relying_party_oauth_client_id, ''), - NULLIF(metrics.string.relying_party_service, '') - ) - ) AS service, - DATE(submission_timestamp) AS submission_date, - metrics.string.account_user_id_sha256 AS client_id, - metrics.string.session_flow_id AS column - FROM - mozdata.accounts_frontend.events_stream - WHERE - {% if is_init() %} - DATE(submission_timestamp) >= DATE("2024-07-10") - {% else %} - DATE(submission_timestamp) = @submission_date - {% endif %} - AND event = 'reg.view' - AND metrics.string.session_flow_id != '' -), -registration_create_account_submit_reg_create_account_submit AS ( - SELECT - metrics.string.session_flow_id AS join_key, - prev.service AS service, - DATE(submission_timestamp) AS submission_date, - metrics.string.account_user_id_sha256 AS client_id, - metrics.string.session_flow_id AS column - FROM - mozdata.accounts_frontend.events_stream - INNER JOIN - registration_create_account_submit_reg_view AS prev - ON prev.submission_date = DATE(submission_timestamp) - AND prev.join_key = metrics.string.session_flow_id - WHERE - {% if is_init() %} - DATE(submission_timestamp) >= DATE("2024-07-10") - {% else %} - DATE(submission_timestamp) = @submission_date - {% endif %} - AND event = 'reg.submit' - AND metrics.string.session_flow_id != '' -), -registration_create_account_submit_success_reg_view AS ( - SELECT - metrics.string.session_flow_id AS join_key, - IF( - COALESCE( - NULLIF(metrics.string.relying_party_oauth_client_id, ''), - NULLIF(metrics.string.relying_party_service, '') - ) = 'sync', - '5882386c6d801776', - COALESCE( - NULLIF(metrics.string.relying_party_oauth_client_id, ''), - NULLIF(metrics.string.relying_party_service, '') - ) - ) AS service, - DATE(submission_timestamp) AS submission_date, - metrics.string.account_user_id_sha256 AS client_id, - metrics.string.session_flow_id AS column - FROM - mozdata.accounts_frontend.events_stream - WHERE - {% if is_init() %} - DATE(submission_timestamp) >= DATE("2024-07-10") - {% else %} - DATE(submission_timestamp) = @submission_date - {% endif %} - AND event = 'reg.view' - AND metrics.string.session_flow_id != '' -), -registration_create_account_submit_success_reg_create_account_submit_success AS ( - SELECT - metrics.string.session_flow_id AS join_key, - prev.service AS service, - DATE(submission_timestamp) AS submission_date, - metrics.string.account_user_id_sha256 AS client_id, - metrics.string.session_flow_id AS column - FROM - mozdata.accounts_frontend.events_stream - INNER JOIN - registration_create_account_submit_success_reg_view AS prev - ON prev.submission_date = DATE(submission_timestamp) - AND prev.join_key = metrics.string.session_flow_id - WHERE - {% if is_init() %} - DATE(submission_timestamp) >= DATE("2024-07-10") - {% else %} - DATE(submission_timestamp) = @submission_date - {% endif %} - AND event = 'reg.submit_success' - AND metrics.string.session_flow_id != '' -), -registration_change_email_engage_reg_view AS ( - SELECT - metrics.string.session_flow_id AS join_key, - IF( - COALESCE( - NULLIF(metrics.string.relying_party_oauth_client_id, ''), - NULLIF(metrics.string.relying_party_service, '') - ) = 'sync', - '5882386c6d801776', - COALESCE( - NULLIF(metrics.string.relying_party_oauth_client_id, ''), - NULLIF(metrics.string.relying_party_service, '') - ) - ) AS service, - DATE(submission_timestamp) AS submission_date, - metrics.string.account_user_id_sha256 AS client_id, - metrics.string.session_flow_id AS column - FROM - mozdata.accounts_frontend.events_stream - WHERE - {% if is_init() %} - DATE(submission_timestamp) >= DATE("2024-07-10") - {% else %} - DATE(submission_timestamp) = @submission_date - {% endif %} - AND event = 'reg.view' - AND metrics.string.session_flow_id != '' -), -registration_change_email_engage_reg_change_email_engage AS ( - SELECT - metrics.string.session_flow_id AS join_key, - prev.service AS service, - DATE(submission_timestamp) AS submission_date, - metrics.string.account_user_id_sha256 AS client_id, - metrics.string.session_flow_id AS column - FROM - mozdata.accounts_frontend.events_stream - INNER JOIN - registration_change_email_engage_reg_view AS prev - ON prev.submission_date = DATE(submission_timestamp) - AND prev.join_key = metrics.string.session_flow_id - WHERE - {% if is_init() %} - DATE(submission_timestamp) >= DATE("2024-07-10") - {% else %} - DATE(submission_timestamp) = @submission_date - {% endif %} - AND event = 'reg.change_email_link_click' - AND metrics.string.session_flow_id != '' -), -registration_whydoweask_engage_reg_view AS ( - SELECT - metrics.string.session_flow_id AS join_key, - IF( - COALESCE( - NULLIF(metrics.string.relying_party_oauth_client_id, ''), - NULLIF(metrics.string.relying_party_service, '') - ) = 'sync', - '5882386c6d801776', - COALESCE( - NULLIF(metrics.string.relying_party_oauth_client_id, ''), - NULLIF(metrics.string.relying_party_service, '') - ) - ) AS service, - DATE(submission_timestamp) AS submission_date, - metrics.string.account_user_id_sha256 AS client_id, - metrics.string.session_flow_id AS column - FROM - mozdata.accounts_frontend.events_stream - WHERE - {% if is_init() %} - DATE(submission_timestamp) >= DATE("2024-07-10") - {% else %} - DATE(submission_timestamp) = @submission_date - {% endif %} - AND event = 'reg.view' - AND metrics.string.session_flow_id != '' -), -registration_whydoweask_engage_reg_whydoweask_engage AS ( - SELECT - metrics.string.session_flow_id AS join_key, - prev.service AS service, - DATE(submission_timestamp) AS submission_date, - metrics.string.account_user_id_sha256 AS client_id, - metrics.string.session_flow_id AS column - FROM - mozdata.accounts_frontend.events_stream - INNER JOIN - registration_whydoweask_engage_reg_view AS prev - ON prev.submission_date = DATE(submission_timestamp) - AND prev.join_key = metrics.string.session_flow_id - WHERE - {% if is_init() %} - DATE(submission_timestamp) >= DATE("2024-07-10") - {% else %} - DATE(submission_timestamp) = @submission_date - {% endif %} - AND event = 'reg.why_do_we_ask_link_click' - AND metrics.string.session_flow_id != '' -), -registration_marketing_engage_reg_view AS ( - SELECT - metrics.string.session_flow_id AS join_key, - IF( - COALESCE( - NULLIF(metrics.string.relying_party_oauth_client_id, ''), - NULLIF(metrics.string.relying_party_service, '') - ) = 'sync', - '5882386c6d801776', - COALESCE( - NULLIF(metrics.string.relying_party_oauth_client_id, ''), - NULLIF(metrics.string.relying_party_service, '') - ) - ) AS service, - DATE(submission_timestamp) AS submission_date, - metrics.string.account_user_id_sha256 AS client_id, - metrics.string.session_flow_id AS column - FROM - mozdata.accounts_frontend.events_stream - WHERE - {% if is_init() %} - DATE(submission_timestamp) >= DATE("2024-07-10") - {% else %} - DATE(submission_timestamp) = @submission_date - {% endif %} - AND event = 'reg.view' - AND metrics.string.session_flow_id != '' -), -registration_marketing_engage_reg_marketing_engage AS ( - SELECT - metrics.string.session_flow_id AS join_key, - prev.service AS service, - DATE(submission_timestamp) AS submission_date, - metrics.string.account_user_id_sha256 AS client_id, - metrics.string.session_flow_id AS column - FROM - mozdata.accounts_frontend.events_stream - INNER JOIN - registration_marketing_engage_reg_view AS prev - ON prev.submission_date = DATE(submission_timestamp) - AND prev.join_key = metrics.string.session_flow_id - WHERE - {% if is_init() %} - DATE(submission_timestamp) >= DATE("2024-07-10") - {% else %} - DATE(submission_timestamp) = @submission_date - {% endif %} - AND event = 'reg.marketing_engage' - AND metrics.string.session_flow_id != '' -), -registration_cwts_engage_reg_view AS ( - SELECT - metrics.string.session_flow_id AS join_key, - IF( - COALESCE( - NULLIF(metrics.string.relying_party_oauth_client_id, ''), - NULLIF(metrics.string.relying_party_service, '') - ) = 'sync', - '5882386c6d801776', - COALESCE( - NULLIF(metrics.string.relying_party_oauth_client_id, ''), - NULLIF(metrics.string.relying_party_service, '') - ) - ) AS service, - DATE(submission_timestamp) AS submission_date, - metrics.string.account_user_id_sha256 AS client_id, - metrics.string.session_flow_id AS column - FROM - mozdata.accounts_frontend.events_stream - WHERE - {% if is_init() %} - DATE(submission_timestamp) >= DATE("2024-07-10") - {% else %} - DATE(submission_timestamp) = @submission_date - {% endif %} - AND event = 'reg.view' - AND metrics.string.session_flow_id != '' -), -registration_cwts_engage_reg_cwts_engage AS ( - SELECT - metrics.string.session_flow_id AS join_key, - prev.service AS service, - DATE(submission_timestamp) AS submission_date, - metrics.string.account_user_id_sha256 AS client_id, - metrics.string.session_flow_id AS column - FROM - mozdata.accounts_frontend.events_stream - INNER JOIN - registration_cwts_engage_reg_view AS prev - ON prev.submission_date = DATE(submission_timestamp) - AND prev.join_key = metrics.string.session_flow_id - WHERE - {% if is_init() %} - DATE(submission_timestamp) >= DATE("2024-07-10") - {% else %} - DATE(submission_timestamp) = @submission_date - {% endif %} - AND event = 'reg.cwts_engage' - AND metrics.string.session_flow_id != '' -), -registration_google_engage_reg_view AS ( - SELECT - metrics.string.session_flow_id AS join_key, - IF( - COALESCE( - NULLIF(metrics.string.relying_party_oauth_client_id, ''), - NULLIF(metrics.string.relying_party_service, '') - ) = 'sync', - '5882386c6d801776', - COALESCE( - NULLIF(metrics.string.relying_party_oauth_client_id, ''), - NULLIF(metrics.string.relying_party_service, '') - ) - ) AS service, - DATE(submission_timestamp) AS submission_date, - metrics.string.account_user_id_sha256 AS client_id, - metrics.string.session_flow_id AS column - FROM - mozdata.accounts_frontend.events_stream - WHERE - {% if is_init() %} - DATE(submission_timestamp) >= DATE("2024-07-10") - {% else %} - DATE(submission_timestamp) = @submission_date - {% endif %} - AND event = 'reg.view' - AND metrics.string.session_flow_id != '' -), -registration_google_engage_reg_google_engage AS ( - SELECT - metrics.string.session_flow_id AS join_key, - prev.service AS service, - DATE(submission_timestamp) AS submission_date, - metrics.string.account_user_id_sha256 AS client_id, - metrics.string.session_flow_id AS column - FROM - mozdata.accounts_frontend.events_stream - INNER JOIN - registration_google_engage_reg_view AS prev - ON prev.submission_date = DATE(submission_timestamp) - AND prev.join_key = metrics.string.session_flow_id - WHERE - {% if is_init() %} - DATE(submission_timestamp) >= DATE("2024-07-10") - {% else %} - DATE(submission_timestamp) = @submission_date - {% endif %} - AND event = 'reg.google_oauth_reg_start' - AND metrics.string.session_flow_id != '' -), -registration_apple_engage_reg_view AS ( - SELECT - metrics.string.session_flow_id AS join_key, - IF( - COALESCE( - NULLIF(metrics.string.relying_party_oauth_client_id, ''), - NULLIF(metrics.string.relying_party_service, '') - ) = 'sync', - '5882386c6d801776', - COALESCE( - NULLIF(metrics.string.relying_party_oauth_client_id, ''), - NULLIF(metrics.string.relying_party_service, '') - ) - ) AS service, - DATE(submission_timestamp) AS submission_date, - metrics.string.account_user_id_sha256 AS client_id, - metrics.string.session_flow_id AS column - FROM - mozdata.accounts_frontend.events_stream - WHERE - {% if is_init() %} - DATE(submission_timestamp) >= DATE("2024-07-10") - {% else %} - DATE(submission_timestamp) = @submission_date - {% endif %} - AND event = 'reg.view' - AND metrics.string.session_flow_id != '' -), -registration_apple_engage_reg_apple_engage AS ( - SELECT - metrics.string.session_flow_id AS join_key, - prev.service AS service, - DATE(submission_timestamp) AS submission_date, - metrics.string.account_user_id_sha256 AS client_id, - metrics.string.session_flow_id AS column - FROM - mozdata.accounts_frontend.events_stream - INNER JOIN - re ```

⚠️ Only part of the diff is displayed.

Link to full diff

dataops-ci-bot commented 2 months ago

Integration report for "Merge branch 'main' into ctroy-ob-views"

`sql.diff`

Click to expand!

```diff Only in /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/monitoring_derived: outerbounds_cost_per_flow_run_v1 Only in /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/monitoring_derived: outerbounds_cost_per_flow_v1 diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/monitoring_derived/outerbounds_cost_per_flow_run_v1/metadata.yaml /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/monitoring_derived/outerbounds_cost_per_flow_run_v1/metadata.yaml --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/monitoring_derived/outerbounds_cost_per_flow_run_v1/metadata.yaml 1970-01-01 00:00:00.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/monitoring_derived/outerbounds_cost_per_flow_run_v1/metadata.yaml 2024-07-23 17:43:22.000000000 +0000 @@ -0,0 +1,13 @@ +friendly_name: Outerbounds Cost Per Flow Run V1 +description: |- + Please provide a description for the query +owners: [] +labels: {} +bigquery: null +workgroup_access: +- role: roles/bigquery.dataViewer + members: + - workgroup:mozilla-confidential +references: + view.sql: + - moz-fx-data-shared-prod.billing_syndicate.gcp_billing_export_resource_v1_01E7D5_97288E_E2EBA0 diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/monitoring_derived/outerbounds_cost_per_flow_run_v1/view.sql /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/monitoring_derived/outerbounds_cost_per_flow_run_v1/view.sql --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/monitoring_derived/outerbounds_cost_per_flow_run_v1/view.sql 1970-01-01 00:00:00.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/monitoring_derived/outerbounds_cost_per_flow_run_v1/view.sql 2024-07-23 17:41:32.000000000 +0000 @@ -0,0 +1,21 @@ +CREATE OR REPLACE VIEW + `moz-fx-data-shared-prod.monitoring_derived.outerbounds_cost_per_flow_run_v1` +AS +SELECT + DATE_TRUNC(usage_start_time, DAY) AS invoice_day, + labels.value AS run_id, + IFNULL(SAFE.LEFT(labels.value, INSTR(labels.value, '-', -1) - 1), labels.value) AS flow_name, + SUM(cost) AS cost_usd +FROM + `moz-fx-data-shared-prod.billing_syndicate.gcp_billing_export_resource_v1_01E7D5_97288E_E2EBA0` +JOIN + UNNEST(labels) AS labels + ON labels.key = "k8s-label/workflows.argoproj.io/workflow" +WHERE + project.id = "moz-fx-mfouterbounds-prod-f98d" + AND usage_start_time >= '2024-07-10' +GROUP BY + 1, + 2 +ORDER BY + 3 DESC diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/monitoring_derived/outerbounds_cost_per_flow_v1/metadata.yaml /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/monitoring_derived/outerbounds_cost_per_flow_v1/metadata.yaml --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/monitoring_derived/outerbounds_cost_per_flow_v1/metadata.yaml 1970-01-01 00:00:00.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/monitoring_derived/outerbounds_cost_per_flow_v1/metadata.yaml 2024-07-23 17:43:22.000000000 +0000 @@ -0,0 +1,13 @@ +friendly_name: Outerbounds Cost Per Flow V1 +description: |- + Please provide a description for the query +owners: [] +labels: {} +bigquery: null +workgroup_access: +- role: roles/bigquery.dataViewer + members: + - workgroup:mozilla-confidential +references: + view.sql: + - moz-fx-data-shared-prod.billing_syndicate.gcp_billing_export_resource_v1_01E7D5_97288E_E2EBA0 diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/monitoring_derived/outerbounds_cost_per_flow_v1/view.sql /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/monitoring_derived/outerbounds_cost_per_flow_v1/view.sql --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/monitoring_derived/outerbounds_cost_per_flow_v1/view.sql 1970-01-01 00:00:00.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/monitoring_derived/outerbounds_cost_per_flow_v1/view.sql 2024-07-23 17:41:32.000000000 +0000 @@ -0,0 +1,33 @@ +CREATE OR REPLACE VIEW + `moz-fx-data-shared-prod.monitoring_derived.outerbounds_cost_per_flow_v1` +AS +WITH cost_by_run AS ( + SELECT + DATE_TRUNC(usage_start_time, DAY) AS invoice_day, + labels.value AS run_id, + IFNULL(SAFE.LEFT(labels.value, INSTR(labels.value, '-', -1) - 1), labels.value) AS flow_name, + SUM(cost) AS cost_usd + FROM + `moz-fx-data-shared-prod.billing_syndicate.gcp_billing_export_resource_v1_01E7D5_97288E_E2EBA0` + JOIN + UNNEST(labels) AS labels + ON labels.key = "k8s-label/workflows.argoproj.io/workflow" + WHERE + project.id = "moz-fx-mfouterbounds-prod-f98d" + AND usage_start_time >= '2024-07-10' + GROUP BY + 1, + 2 + ORDER BY + 3 DESC +) +SELECT + flow_name, + SUM(cost_usd) AS total_cost_usd, + COUNT(flow_name) AS num_runs +FROM + cost_by_run +GROUP BY + 1 +ORDER BY + 2 DESC ```

Link to full diff

dataops-ci-bot commented 2 months ago

Integration report for "Merge branch 'main' into ctroy-ob-views"

`sql.diff`

Click to expand!

```diff Only in /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/monitoring_derived: outerbounds_cost_per_flow_run_v1 Only in /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/monitoring_derived: outerbounds_cost_per_flow_v1 diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/monitoring_derived/outerbounds_cost_per_flow_run_v1/metadata.yaml /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/monitoring_derived/outerbounds_cost_per_flow_run_v1/metadata.yaml --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/monitoring_derived/outerbounds_cost_per_flow_run_v1/metadata.yaml 1970-01-01 00:00:00.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/monitoring_derived/outerbounds_cost_per_flow_run_v1/metadata.yaml 2024-07-24 15:19:54.000000000 +0000 @@ -0,0 +1,13 @@ +friendly_name: Outerbounds Cost Per Flow Run V1 +description: |- + Please provide a description for the query +owners: [] +labels: {} +bigquery: null +workgroup_access: +- role: roles/bigquery.dataViewer + members: + - workgroup:mozilla-confidential +references: + view.sql: + - moz-fx-data-shared-prod.billing_syndicate.gcp_billing_export_resource_v1_01E7D5_97288E_E2EBA0 diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/monitoring_derived/outerbounds_cost_per_flow_run_v1/view.sql /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/monitoring_derived/outerbounds_cost_per_flow_run_v1/view.sql --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/monitoring_derived/outerbounds_cost_per_flow_run_v1/view.sql 1970-01-01 00:00:00.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/monitoring_derived/outerbounds_cost_per_flow_run_v1/view.sql 2024-07-24 15:17:49.000000000 +0000 @@ -0,0 +1,21 @@ +CREATE OR REPLACE VIEW + `moz-fx-data-shared-prod.monitoring_derived.outerbounds_cost_per_flow_run_v1` +AS +SELECT + DATE_TRUNC(usage_start_time, DAY) AS invoice_day, + labels.value AS run_id, + IFNULL(SAFE.LEFT(labels.value, INSTR(labels.value, '-', -1) - 1), labels.value) AS flow_name, + SUM(cost) AS cost_usd +FROM + `moz-fx-data-shared-prod.billing_syndicate.gcp_billing_export_resource_v1_01E7D5_97288E_E2EBA0` +JOIN + UNNEST(labels) AS labels + ON labels.key = "k8s-label/workflows.argoproj.io/workflow" +WHERE + project.id = "moz-fx-mfouterbounds-prod-f98d" + AND usage_start_time >= '2024-07-10' +GROUP BY + 1, + 2 +ORDER BY + 3 DESC diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/monitoring_derived/outerbounds_cost_per_flow_v1/metadata.yaml /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/monitoring_derived/outerbounds_cost_per_flow_v1/metadata.yaml --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/monitoring_derived/outerbounds_cost_per_flow_v1/metadata.yaml 1970-01-01 00:00:00.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/monitoring_derived/outerbounds_cost_per_flow_v1/metadata.yaml 2024-07-24 15:19:54.000000000 +0000 @@ -0,0 +1,13 @@ +friendly_name: Outerbounds Cost Per Flow V1 +description: |- + Please provide a description for the query +owners: [] +labels: {} +bigquery: null +workgroup_access: +- role: roles/bigquery.dataViewer + members: + - workgroup:mozilla-confidential +references: + view.sql: + - moz-fx-data-shared-prod.billing_syndicate.gcp_billing_export_resource_v1_01E7D5_97288E_E2EBA0 diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/monitoring_derived/outerbounds_cost_per_flow_v1/view.sql /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/monitoring_derived/outerbounds_cost_per_flow_v1/view.sql --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/monitoring_derived/outerbounds_cost_per_flow_v1/view.sql 1970-01-01 00:00:00.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/monitoring_derived/outerbounds_cost_per_flow_v1/view.sql 2024-07-24 15:17:49.000000000 +0000 @@ -0,0 +1,33 @@ +CREATE OR REPLACE VIEW + `moz-fx-data-shared-prod.monitoring_derived.outerbounds_cost_per_flow_v1` +AS +WITH cost_by_run AS ( + SELECT + DATE_TRUNC(usage_start_time, DAY) AS invoice_day, + labels.value AS run_id, + IFNULL(SAFE.LEFT(labels.value, INSTR(labels.value, '-', -1) - 1), labels.value) AS flow_name, + SUM(cost) AS cost_usd + FROM + `moz-fx-data-shared-prod.billing_syndicate.gcp_billing_export_resource_v1_01E7D5_97288E_E2EBA0` + JOIN + UNNEST(labels) AS labels + ON labels.key = "k8s-label/workflows.argoproj.io/workflow" + WHERE + project.id = "moz-fx-mfouterbounds-prod-f98d" + AND usage_start_time >= '2024-07-10' + GROUP BY + 1, + 2 + ORDER BY + 3 DESC +) +SELECT + flow_name, + SUM(cost_usd) AS total_cost_usd, + COUNT(flow_name) AS num_runs +FROM + cost_by_run +GROUP BY + 1 +ORDER BY + 2 DESC ```

Link to full diff

chelseatroy commented 2 months ago

@BenWu on monitoring vs monitoring_derived; happy to move it, want to first get a solid consensus on where this is supposed to go. @gkaberere thoughts?

^ Update on this: I can't tag George for some reason so I Slacked him!

dataops-ci-bot commented 2 months ago

Integration report for "remove unused CTE and replace numeric order markers with col names"

`sql.diff`

Click to expand!

```diff Only in /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/monitoring_derived: outerbounds_cost_per_flow_run_v1 Only in /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/monitoring_derived: outerbounds_cost_per_flow_v1 diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/monitoring_derived/outerbounds_cost_per_flow_run_v1/metadata.yaml /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/monitoring_derived/outerbounds_cost_per_flow_run_v1/metadata.yaml --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/monitoring_derived/outerbounds_cost_per_flow_run_v1/metadata.yaml 1970-01-01 00:00:00.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/monitoring_derived/outerbounds_cost_per_flow_run_v1/metadata.yaml 2024-07-24 16:42:32.000000000 +0000 @@ -0,0 +1,13 @@ +friendly_name: Outerbounds Cost Per Flow Run V1 +description: |- + Please provide a description for the query +owners: [] +labels: {} +bigquery: null +workgroup_access: +- role: roles/bigquery.dataViewer + members: + - workgroup:mozilla-confidential +references: + view.sql: + - moz-fx-data-shared-prod.billing_syndicate.gcp_billing_export_resource_v1_01E7D5_97288E_E2EBA0 diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/monitoring_derived/outerbounds_cost_per_flow_run_v1/view.sql /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/monitoring_derived/outerbounds_cost_per_flow_run_v1/view.sql --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/monitoring_derived/outerbounds_cost_per_flow_run_v1/view.sql 1970-01-01 00:00:00.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/monitoring_derived/outerbounds_cost_per_flow_run_v1/view.sql 2024-07-24 16:40:32.000000000 +0000 @@ -0,0 +1,21 @@ +CREATE OR REPLACE VIEW + `moz-fx-data-shared-prod.monitoring_derived.outerbounds_cost_per_flow_run_v1` +AS +SELECT + DATE_TRUNC(usage_start_time, DAY) AS invoice_day, + labels.value AS run_id, + IFNULL(SAFE.LEFT(labels.value, INSTR(labels.value, '-', -1) - 1), labels.value) AS flow_name, + SUM(cost) AS cost_usd +FROM + `moz-fx-data-shared-prod.billing_syndicate.gcp_billing_export_resource_v1_01E7D5_97288E_E2EBA0` +JOIN + UNNEST(labels) AS labels + ON labels.key = "k8s-label/workflows.argoproj.io/workflow" +WHERE + project.id = "moz-fx-mfouterbounds-prod-f98d" + AND usage_start_time >= '2024-07-10' +GROUP BY + invoice_day, + run_id +ORDER BY + flow_name DESC diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/monitoring_derived/outerbounds_cost_per_flow_v1/metadata.yaml /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/monitoring_derived/outerbounds_cost_per_flow_v1/metadata.yaml --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/monitoring_derived/outerbounds_cost_per_flow_v1/metadata.yaml 1970-01-01 00:00:00.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/monitoring_derived/outerbounds_cost_per_flow_v1/metadata.yaml 2024-07-24 16:42:32.000000000 +0000 @@ -0,0 +1,13 @@ +friendly_name: Outerbounds Cost Per Flow V1 +description: |- + Please provide a description for the query +owners: [] +labels: {} +bigquery: null +workgroup_access: +- role: roles/bigquery.dataViewer + members: + - workgroup:mozilla-confidential +references: + view.sql: + - moz-fx-data-shared-prod.monitoring_derived.outerbounds_cost_per_flow_run_v1 diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/monitoring_derived/outerbounds_cost_per_flow_v1/view.sql /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/monitoring_derived/outerbounds_cost_per_flow_v1/view.sql --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/monitoring_derived/outerbounds_cost_per_flow_v1/view.sql 1970-01-01 00:00:00.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/monitoring_derived/outerbounds_cost_per_flow_v1/view.sql 2024-07-24 16:40:32.000000000 +0000 @@ -0,0 +1,13 @@ +CREATE OR REPLACE VIEW + `moz-fx-data-shared-prod.monitoring_derived.outerbounds_cost_per_flow_v1` +AS +SELECT + flow_name, + SUM(cost_usd) AS total_cost_usd, + COUNT(flow_name) AS num_runs +FROM + `moz-fx-data-shared-prod.monitoring_derived.outerbounds_cost_per_flow_run_v1` +GROUP BY + flow_name +ORDER BY + total_cost_usd DESC ```

Link to full diff

chelseatroy commented 2 months ago

@BenWu for now I'm leaving it in monitoring_derived, but accepted your suggestions and also did some cleanup to remove the (now unused) CTE and group by column names!

dataops-ci-bot commented 2 months ago

Integration report for "Merge branch 'main' into ctroy-ob-views"

`sql.diff`

Click to expand!

```diff Only in /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/monitoring_derived: outerbounds_cost_per_flow_run_v1 Only in /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/monitoring_derived: outerbounds_cost_per_flow_v1 diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/monitoring_derived/outerbounds_cost_per_flow_run_v1/metadata.yaml /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/monitoring_derived/outerbounds_cost_per_flow_run_v1/metadata.yaml --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/monitoring_derived/outerbounds_cost_per_flow_run_v1/metadata.yaml 1970-01-01 00:00:00.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/monitoring_derived/outerbounds_cost_per_flow_run_v1/metadata.yaml 2024-07-24 21:39:35.000000000 +0000 @@ -0,0 +1,13 @@ +friendly_name: Outerbounds Cost Per Flow Run V1 +description: |- + Please provide a description for the query +owners: [] +labels: {} +bigquery: null +workgroup_access: +- role: roles/bigquery.dataViewer + members: + - workgroup:mozilla-confidential +references: + view.sql: + - moz-fx-data-shared-prod.billing_syndicate.gcp_billing_export_resource_v1_01E7D5_97288E_E2EBA0 diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/monitoring_derived/outerbounds_cost_per_flow_run_v1/view.sql /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/monitoring_derived/outerbounds_cost_per_flow_run_v1/view.sql --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/monitoring_derived/outerbounds_cost_per_flow_run_v1/view.sql 1970-01-01 00:00:00.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/monitoring_derived/outerbounds_cost_per_flow_run_v1/view.sql 2024-07-24 21:37:40.000000000 +0000 @@ -0,0 +1,21 @@ +CREATE OR REPLACE VIEW + `moz-fx-data-shared-prod.monitoring_derived.outerbounds_cost_per_flow_run_v1` +AS +SELECT + DATE_TRUNC(usage_start_time, DAY) AS invoice_day, + labels.value AS run_id, + IFNULL(SAFE.LEFT(labels.value, INSTR(labels.value, '-', -1) - 1), labels.value) AS flow_name, + SUM(cost) AS cost_usd +FROM + `moz-fx-data-shared-prod.billing_syndicate.gcp_billing_export_resource_v1_01E7D5_97288E_E2EBA0` +JOIN + UNNEST(labels) AS labels + ON labels.key = "k8s-label/workflows.argoproj.io/workflow" +WHERE + project.id = "moz-fx-mfouterbounds-prod-f98d" + AND usage_start_time >= '2024-07-10' +GROUP BY + invoice_day, + run_id +ORDER BY + flow_name DESC diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/monitoring_derived/outerbounds_cost_per_flow_v1/metadata.yaml /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/monitoring_derived/outerbounds_cost_per_flow_v1/metadata.yaml --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/monitoring_derived/outerbounds_cost_per_flow_v1/metadata.yaml 1970-01-01 00:00:00.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/monitoring_derived/outerbounds_cost_per_flow_v1/metadata.yaml 2024-07-24 21:39:35.000000000 +0000 @@ -0,0 +1,13 @@ +friendly_name: Outerbounds Cost Per Flow V1 +description: |- + Please provide a description for the query +owners: [] +labels: {} +bigquery: null +workgroup_access: +- role: roles/bigquery.dataViewer + members: + - workgroup:mozilla-confidential +references: + view.sql: + - moz-fx-data-shared-prod.monitoring_derived.outerbounds_cost_per_flow_run_v1 diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/monitoring_derived/outerbounds_cost_per_flow_v1/view.sql /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/monitoring_derived/outerbounds_cost_per_flow_v1/view.sql --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/monitoring_derived/outerbounds_cost_per_flow_v1/view.sql 1970-01-01 00:00:00.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/monitoring_derived/outerbounds_cost_per_flow_v1/view.sql 2024-07-24 21:37:40.000000000 +0000 @@ -0,0 +1,13 @@ +CREATE OR REPLACE VIEW + `moz-fx-data-shared-prod.monitoring_derived.outerbounds_cost_per_flow_v1` +AS +SELECT + flow_name, + SUM(cost_usd) AS total_cost_usd, + COUNT(flow_name) AS num_runs +FROM + `moz-fx-data-shared-prod.monitoring_derived.outerbounds_cost_per_flow_run_v1` +GROUP BY + flow_name +ORDER BY + total_cost_usd DESC ```

Link to full diff

chelseatroy commented 2 months ago

@edugfilho I think the failure here is that same udf.sql::glam.histogram_cast_struct test. I know you issued a fix for this; could you please let me know when it merges? I'll update once that's done 😺

edugfilho commented 2 months ago

sorry for this whole thing. @lelilia removed the failing test on https://github.com/mozilla/bigquery-etl/pull/5971 and I updated this branch, so it should pass now.

dataops-ci-bot commented 2 months ago

Integration report for "Merge branch 'main' into ctroy-ob-views"

`sql.diff`

Click to expand!

```diff Only in /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/monitoring_derived: outerbounds_cost_per_flow_run_v1 Only in /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/monitoring_derived: outerbounds_cost_per_flow_v1 diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/monitoring_derived/outerbounds_cost_per_flow_run_v1/metadata.yaml /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/monitoring_derived/outerbounds_cost_per_flow_run_v1/metadata.yaml --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/monitoring_derived/outerbounds_cost_per_flow_run_v1/metadata.yaml 1970-01-01 00:00:00.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/monitoring_derived/outerbounds_cost_per_flow_run_v1/metadata.yaml 2024-07-25 13:45:35.000000000 +0000 @@ -0,0 +1,13 @@ +friendly_name: Outerbounds Cost Per Flow Run V1 +description: |- + Please provide a description for the query +owners: [] +labels: {} +bigquery: null +workgroup_access: +- role: roles/bigquery.dataViewer + members: + - workgroup:mozilla-confidential +references: + view.sql: + - moz-fx-data-shared-prod.billing_syndicate.gcp_billing_export_resource_v1_01E7D5_97288E_E2EBA0 diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/monitoring_derived/outerbounds_cost_per_flow_run_v1/view.sql /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/monitoring_derived/outerbounds_cost_per_flow_run_v1/view.sql --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/monitoring_derived/outerbounds_cost_per_flow_run_v1/view.sql 1970-01-01 00:00:00.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/monitoring_derived/outerbounds_cost_per_flow_run_v1/view.sql 2024-07-25 13:43:21.000000000 +0000 @@ -0,0 +1,21 @@ +CREATE OR REPLACE VIEW + `moz-fx-data-shared-prod.monitoring_derived.outerbounds_cost_per_flow_run_v1` +AS +SELECT + DATE_TRUNC(usage_start_time, DAY) AS invoice_day, + labels.value AS run_id, + IFNULL(SAFE.LEFT(labels.value, INSTR(labels.value, '-', -1) - 1), labels.value) AS flow_name, + SUM(cost) AS cost_usd +FROM + `moz-fx-data-shared-prod.billing_syndicate.gcp_billing_export_resource_v1_01E7D5_97288E_E2EBA0` +JOIN + UNNEST(labels) AS labels + ON labels.key = "k8s-label/workflows.argoproj.io/workflow" +WHERE + project.id = "moz-fx-mfouterbounds-prod-f98d" + AND usage_start_time >= '2024-07-10' +GROUP BY + invoice_day, + run_id +ORDER BY + flow_name DESC diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/monitoring_derived/outerbounds_cost_per_flow_v1/metadata.yaml /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/monitoring_derived/outerbounds_cost_per_flow_v1/metadata.yaml --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/monitoring_derived/outerbounds_cost_per_flow_v1/metadata.yaml 1970-01-01 00:00:00.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/monitoring_derived/outerbounds_cost_per_flow_v1/metadata.yaml 2024-07-25 13:45:35.000000000 +0000 @@ -0,0 +1,13 @@ +friendly_name: Outerbounds Cost Per Flow V1 +description: |- + Please provide a description for the query +owners: [] +labels: {} +bigquery: null +workgroup_access: +- role: roles/bigquery.dataViewer + members: + - workgroup:mozilla-confidential +references: + view.sql: + - moz-fx-data-shared-prod.monitoring_derived.outerbounds_cost_per_flow_run_v1 diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/monitoring_derived/outerbounds_cost_per_flow_v1/view.sql /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/monitoring_derived/outerbounds_cost_per_flow_v1/view.sql --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/monitoring_derived/outerbounds_cost_per_flow_v1/view.sql 1970-01-01 00:00:00.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/monitoring_derived/outerbounds_cost_per_flow_v1/view.sql 2024-07-25 13:43:21.000000000 +0000 @@ -0,0 +1,13 @@ +CREATE OR REPLACE VIEW + `moz-fx-data-shared-prod.monitoring_derived.outerbounds_cost_per_flow_v1` +AS +SELECT + flow_name, + SUM(cost_usd) AS total_cost_usd, + COUNT(flow_name) AS num_runs +FROM + `moz-fx-data-shared-prod.monitoring_derived.outerbounds_cost_per_flow_run_v1` +GROUP BY + flow_name +ORDER BY + total_cost_usd DESC ```

Link to full diff

dataops-ci-bot commented 2 months ago

Integration report for "Merge branch 'main' into ctroy-ob-views"

`sql.diff`

Click to expand!

```diff Only in /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/ads/ppa_measurements: schema.yaml Only in /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/monitoring/shredder_progress: schema.yaml Only in /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/monitoring_derived: outerbounds_cost_per_flow_run_v1 Only in /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/monitoring_derived: outerbounds_cost_per_flow_v1 Only in /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/pocket/pocket_reach_mau: schema.yaml Only in /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/search_terms/aggregated_search_terms_daily: schema.yaml Only in /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/search_terms/sanitization_job_data_validation_metrics: schema.yaml Only in /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/search_terms/sanitization_job_languages: schema.yaml Only in /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/search_terms/search_terms_daily: schema.yaml Only in /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/telemetry/buildhub2: schema.yaml diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/ads/ppa_measurements/schema.yaml /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/ads/ppa_measurements/schema.yaml --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/ads/ppa_measurements/schema.yaml 2024-07-25 15:07:19.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/ads/ppa_measurements/schema.yaml 1970-01-01 00:00:00.000000000 +0000 @@ -1 +0,0 @@ -{} diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/app_store/firefox_app_store_territory_source_type_report/schema.yaml /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/app_store/firefox_app_store_territory_source_type_report/schema.yaml --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/app_store/firefox_app_store_territory_source_type_report/schema.yaml 2024-07-25 15:07:19.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/app_store/firefox_app_store_territory_source_type_report/schema.yaml 2024-07-25 14:56:42.000000000 +0000 @@ -1 +1,34 @@ -{} +fields: +- name: app_id + type: INTEGER + mode: NULLABLE +- name: date + type: TIMESTAMP + mode: NULLABLE +- name: source_type + type: STRING + mode: NULLABLE +- name: territory + type: STRING + mode: NULLABLE +- name: _fivetran_synced + type: TIMESTAMP + mode: NULLABLE +- name: impressions + type: INTEGER + mode: NULLABLE +- name: impressions_unique_device + type: INTEGER + mode: NULLABLE +- name: meets_threshold + type: BOOLEAN + mode: NULLABLE +- name: page_views + type: INTEGER + mode: NULLABLE +- name: page_views_unique_device + type: INTEGER + mode: NULLABLE +- name: date_pst + type: TIMESTAMP + mode: NULLABLE diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/app_store/firefox_app_store_territory_web_referrer_report/schema.yaml /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/app_store/firefox_app_store_territory_web_referrer_report/schema.yaml --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/app_store/firefox_app_store_territory_web_referrer_report/schema.yaml 2024-07-25 15:07:19.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/app_store/firefox_app_store_territory_web_referrer_report/schema.yaml 2024-07-25 14:56:42.000000000 +0000 @@ -1 +1,34 @@ -{} +fields: +- name: app_id + type: INTEGER + mode: NULLABLE +- name: date + type: TIMESTAMP + mode: NULLABLE +- name: territory + type: STRING + mode: NULLABLE +- name: web_referrer + type: STRING + mode: NULLABLE +- name: _fivetran_synced + type: TIMESTAMP + mode: NULLABLE +- name: impressions + type: INTEGER + mode: NULLABLE +- name: impressions_unique_device + type: INTEGER + mode: NULLABLE +- name: meets_threshold + type: BOOLEAN + mode: NULLABLE +- name: page_views + type: INTEGER + mode: NULLABLE +- name: page_views_unique_device + type: INTEGER + mode: NULLABLE +- name: date_pst + type: TIMESTAMP + mode: NULLABLE diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/app_store/firefox_downloads_territory_source_type_report/schema.yaml /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/app_store/firefox_downloads_territory_source_type_report/schema.yaml --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/app_store/firefox_downloads_territory_source_type_report/schema.yaml 2024-07-25 15:07:19.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/app_store/firefox_downloads_territory_source_type_report/schema.yaml 2024-07-25 14:56:42.000000000 +0000 @@ -1 +1,31 @@ -{} +fields: +- name: app_id + type: INTEGER + mode: NULLABLE +- name: date + type: TIMESTAMP + mode: NULLABLE +- name: source_type + type: STRING + mode: NULLABLE +- name: territory + type: STRING + mode: NULLABLE +- name: _fivetran_synced + type: TIMESTAMP + mode: NULLABLE +- name: first_time_downloads + type: INTEGER + mode: NULLABLE +- name: meets_threshold + type: BOOLEAN + mode: NULLABLE +- name: redownloads + type: INTEGER + mode: NULLABLE +- name: total_downloads + type: INTEGER + mode: NULLABLE +- name: date_pst + type: TIMESTAMP + mode: NULLABLE diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/app_store/firefox_downloads_territory_web_referrer_report/schema.yaml /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/app_store/firefox_downloads_territory_web_referrer_report/schema.yaml --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/app_store/firefox_downloads_territory_web_referrer_report/schema.yaml 2024-07-25 15:07:19.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/app_store/firefox_downloads_territory_web_referrer_report/schema.yaml 2024-07-25 14:56:42.000000000 +0000 @@ -1 +1,31 @@ -{} +fields: +- name: app_id + type: INTEGER + mode: NULLABLE +- name: date + type: TIMESTAMP + mode: NULLABLE +- name: territory + type: STRING + mode: NULLABLE +- name: web_referrer + type: STRING + mode: NULLABLE +- name: _fivetran_synced + type: TIMESTAMP + mode: NULLABLE +- name: first_time_downloads + type: INTEGER + mode: NULLABLE +- name: meets_threshold + type: BOOLEAN + mode: NULLABLE +- name: redownloads + type: INTEGER + mode: NULLABLE +- name: total_downloads + type: INTEGER + mode: NULLABLE +- name: date_pst + type: TIMESTAMP + mode: NULLABLE diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/app_store/firefox_usage_territory_source_type_report/schema.yaml /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/app_store/firefox_usage_territory_source_type_report/schema.yaml --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/app_store/firefox_usage_territory_source_type_report/schema.yaml 2024-07-25 15:07:19.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/app_store/firefox_usage_territory_source_type_report/schema.yaml 2024-07-25 14:56:42.000000000 +0000 @@ -1 +1,37 @@ -{} +fields: +- name: app_id + type: INTEGER + mode: NULLABLE +- name: date + type: TIMESTAMP + mode: NULLABLE +- name: source_type + type: STRING + mode: NULLABLE +- name: territory + type: STRING + mode: NULLABLE +- name: _fivetran_synced + type: TIMESTAMP + mode: NULLABLE +- name: active_devices + type: INTEGER + mode: NULLABLE +- name: active_devices_last_30_days + type: INTEGER + mode: NULLABLE +- name: deletions + type: INTEGER + mode: NULLABLE +- name: installations + type: INTEGER + mode: NULLABLE +- name: meets_threshold + type: BOOLEAN + mode: NULLABLE +- name: sessions + type: INTEGER + mode: NULLABLE +- name: date_pst + type: TIMESTAMP + mode: NULLABLE diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/app_store/firefox_usage_territory_web_referrer_report/schema.yaml /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/app_store/firefox_usage_territory_web_referrer_report/schema.yaml --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/app_store/firefox_usage_territory_web_referrer_report/schema.yaml 2024-07-25 15:07:19.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/app_store/firefox_usage_territory_web_referrer_report/schema.yaml 2024-07-25 14:56:42.000000000 +0000 @@ -1 +1,37 @@ -{} +fields: +- name: app_id + type: INTEGER + mode: NULLABLE +- name: date + type: TIMESTAMP + mode: NULLABLE +- name: territory + type: STRING + mode: NULLABLE +- name: web_referrer + type: STRING + mode: NULLABLE +- name: _fivetran_synced + type: TIMESTAMP + mode: NULLABLE +- name: active_devices + type: INTEGER + mode: NULLABLE +- name: active_devices_last_30_days + type: INTEGER + mode: NULLABLE +- name: deletions + type: INTEGER + mode: NULLABLE +- name: installations + type: INTEGER + mode: NULLABLE +- name: meets_threshold + type: BOOLEAN + mode: NULLABLE +- name: sessions + type: INTEGER + mode: NULLABLE +- name: date_pst + type: TIMESTAMP + mode: NULLABLE diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/contextual_services/adm_forecasting/schema.yaml /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/contextual_services/adm_forecasting/schema.yaml --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/contextual_services/adm_forecasting/schema.yaml 2024-07-25 15:07:19.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/contextual_services/adm_forecasting/schema.yaml 2024-07-25 14:59:18.000000000 +0000 @@ -1 +1,43 @@ -{} +fields: +- name: product + type: STRING + mode: NULLABLE +- name: submission_date + type: DATE + mode: NULLABLE +- name: country + type: STRING + mode: NULLABLE +- name: device + type: STRING + mode: NULLABLE +- name: eligible_share_country + type: FLOAT + mode: NULLABLE +- name: clients + type: INTEGER + mode: NULLABLE +- name: p_amazon + type: FLOAT + mode: NULLABLE +- name: p_other + type: FLOAT + mode: NULLABLE +- name: amazon_clients + type: FLOAT + mode: NULLABLE +- name: other_clients + type: FLOAT + mode: NULLABLE +- name: amazon_clicks + type: INTEGER + mode: NULLABLE +- name: other_clicks + type: INTEGER + mode: NULLABLE +- name: amazon_clicks_per_client + type: FLOAT + mode: NULLABLE +- name: other_clicks_per_client + type: FLOAT + mode: NULLABLE 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-07-25 15:07:19.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/contextual_services/event_aggregates/schema.yaml 2024-07-25 14:59:17.000000000 +0000 @@ -1 +1,49 @@ -{} +fields: +- name: submission_date + type: DATE + mode: NULLABLE +- name: source + type: STRING + mode: NULLABLE +- name: event_type + type: STRING + mode: NULLABLE +- name: form_factor + type: STRING + mode: NULLABLE +- name: country + type: STRING + mode: NULLABLE +- name: subdivision1 + type: STRING + mode: NULLABLE +- name: advertiser + type: STRING + mode: NULLABLE +- name: release_channel + type: STRING + mode: NULLABLE +- name: position + type: INTEGER + mode: NULLABLE +- name: provider + type: STRING + mode: NULLABLE +- name: match_type + type: STRING + mode: NULLABLE +- name: normalized_os + type: STRING + mode: NULLABLE +- name: suggest_data_sharing_enabled + type: BOOLEAN + mode: NULLABLE +- name: event_count + type: INTEGER + mode: NULLABLE +- name: user_count + type: INTEGER + 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_spons_tiles/schema.yaml /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/contextual_services/event_aggregates_spons_tiles/schema.yaml --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/contextual_services/event_aggregates_spons_tiles/schema.yaml 2024-07-25 15:07:19.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/contextual_services/event_aggregates_spons_tiles/schema.yaml 2024-07-25 14:59:18.000000000 +0000 @@ -1 +1,31 @@ -{} +fields: +- name: submission_date + type: DATE + mode: NULLABLE +- name: form_factor + type: STRING + mode: NULLABLE +- name: country + type: STRING + mode: NULLABLE +- name: advertiser + type: STRING + mode: NULLABLE +- name: normalized_os + type: STRING + mode: NULLABLE +- name: release_channel + type: STRING + mode: NULLABLE +- name: position + type: INTEGER + mode: NULLABLE +- name: provider + type: STRING + mode: NULLABLE +- name: impression_count + type: INTEGER + mode: NULLABLE +- name: click_count + type: INTEGER + 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-07-25 15:07:19.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/contextual_services/event_aggregates_suggest/schema.yaml 2024-07-25 14:59:18.000000000 +0000 @@ -1 +1,40 @@ -{} +fields: +- name: submission_date + type: DATE + mode: NULLABLE +- name: form_factor + type: STRING + mode: NULLABLE +- name: country + type: STRING + mode: NULLABLE +- name: advertiser + type: STRING + mode: NULLABLE +- name: normalized_os + type: STRING + mode: NULLABLE +- name: release_channel + type: STRING + mode: NULLABLE +- name: position + type: INTEGER + mode: NULLABLE +- name: provider + type: STRING + mode: NULLABLE +- name: match_type + type: STRING + mode: NULLABLE +- name: suggest_data_sharing_enabled + type: BOOLEAN + mode: NULLABLE +- name: impression_count + type: INTEGER + mode: NULLABLE +- name: click_count + type: INTEGER + 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/quicksuggest_click_live/schema.yaml /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/contextual_services/quicksuggest_click_live/schema.yaml --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/contextual_services/quicksuggest_click_live/schema.yaml 2024-07-25 15:07:19.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/contextual_services/quicksuggest_click_live/schema.yaml 2024-07-25 14:59:17.000000000 +0000 @@ -1 +1,174 @@ -{} +fields: +- name: additional_properties + type: STRING + mode: NULLABLE +- name: advertiser + type: STRING + mode: NULLABLE +- name: block_id + type: INTEGER + mode: NULLABLE +- name: context_id + type: STRING + mode: NULLABLE +- name: document_id + type: STRING + mode: NULLABLE +- name: experiments + type: RECORD + mode: REPEATED + fields: + - name: key + type: STRING + mode: NULLABLE + - name: value + type: RECORD + mode: NULLABLE + fields: + - name: branch + type: STRING + mode: NULLABLE +- name: locale + type: STRING + mode: NULLABLE +- name: metadata + type: RECORD + mode: NULLABLE + fields: + - name: geo + type: RECORD + mode: NULLABLE + fields: + - name: city + type: STRING + mode: NULLABLE + - name: country + type: STRING + mode: NULLABLE + - name: db_version + type: STRING + mode: NULLABLE + - name: subdivision1 + type: STRING + mode: NULLABLE + - name: subdivision2 + type: STRING + mode: NULLABLE + - name: header + type: RECORD + mode: NULLABLE + fields: + - name: date + type: STRING + mode: NULLABLE + - name: dnt + type: STRING + mode: NULLABLE + - name: x_debug_id + type: STRING + mode: NULLABLE + - name: x_pingsender_version + type: STRING + mode: NULLABLE + - name: x_source_tags + type: STRING + mode: NULLABLE + - name: x_telemetry_agent + type: STRING + mode: NULLABLE + - name: x_foxsec_ip_reputation + type: STRING + mode: NULLABLE + - name: x_lb_tags + type: STRING + mode: NULLABLE + - name: parsed_date + type: TIMESTAMP + mode: NULLABLE + - name: parsed_x_source_tags + type: STRING + mode: REPEATED + - name: parsed_x_lb_tags + type: RECORD + mode: NULLABLE + fields: + - name: tls_version + type: STRING + mode: NULLABLE + - name: tls_cipher_hex + type: STRING + mode: NULLABLE + - name: isp + type: RECORD + mode: NULLABLE + fields: + - name: db_version + type: STRING + mode: NULLABLE + - name: name + type: STRING + mode: NULLABLE + - name: organization + type: STRING + mode: NULLABLE + - name: user_agent + type: RECORD + mode: NULLABLE + fields: + - name: browser + type: STRING + mode: NULLABLE + - name: os + type: STRING + mode: NULLABLE + - name: version + type: STRING + mode: NULLABLE +- name: normalized_app_name + type: STRING + mode: NULLABLE +- name: normalized_channel + type: STRING + mode: NULLABLE +- name: normalized_country_code + type: STRING + mode: NULLABLE +- name: normalized_os + type: STRING + mode: NULLABLE +- name: normalized_os_version + type: STRING + mode: NULLABLE +- name: position + type: INTEGER + mode: NULLABLE +- name: release_channel + type: STRING + mode: NULLABLE +- name: reporting_url + type: STRING + mode: NULLABLE +- name: sample_id + type: INTEGER + mode: NULLABLE +- name: submission_timestamp + type: TIMESTAMP + mode: NULLABLE +- name: version + type: STRING + mode: NULLABLE +- name: scenario + type: STRING + mode: NULLABLE +- name: request_id + type: STRING + mode: NULLABLE +- name: match_type + type: STRING + mode: NULLABLE +- name: improve_suggest_experience_checked + type: BOOLEAN + mode: NULLABLE +- name: source + type: STRING + mode: NULLABLE diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/contextual_services/quicksuggest_impression_live/schema.yaml /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/contextual_services/quicksuggest_impression_live/schema.yaml --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/contextual_services/quicksuggest_impression_live/schema.yaml 2024-07-25 15:07:19.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/contextual_services/quicksuggest_impression_live/schema.yaml 2024-07-25 14:59:17.000000000 +0000 @@ -1 +1,177 @@ -{} +fields: +- name: additional_properties + type: STRING + mode: NULLABLE +- name: advertiser + type: STRING + mode: NULLABLE +- name: block_id + type: INTEGER + mode: NULLABLE +- name: context_id + type: STRING + mode: NULLABLE +- name: document_id + type: STRING + mode: NULLABLE +- name: experiments + type: RECORD + mode: REPEATED + fields: + - name: key + type: STRING + mode: NULLABLE + - name: value + type: RECORD + mode: NULLABLE + fields: + - name: branch + type: STRING + mode: NULLABLE +- name: is_clicked + type: BOOLEAN + mode: NULLABLE +- name: locale + type: STRING + mode: NULLABLE +- name: metadata + type: RECORD + mode: NULLABLE + fields: + - name: geo + type: RECORD + mode: NULLABLE + fields: + - name: city + type: STRING + mode: NULLABLE + - name: country + type: STRING + mode: NULLABLE + - name: db_version + type: STRING + mode: NULLABLE + - name: subdivision1 + type: STRING + mode: NULLABLE + - name: subdivision2 + type: STRING + mode: NULLABLE + - name: header + type: RECORD + mode: NULLABLE + fields: + - name: date + type: STRING + mode: NULLABLE + - name: dnt + type: STRING + mode: NULLABLE + - name: x_debug_id + type: STRING + mode: NULLABLE + - name: x_pingsender_version + type: STRING + mode: NULLABLE + - name: x_source_tags + type: STRING + mode: NULLABLE + - name: x_telemetry_agent + type: STRING + mode: NULLABLE + - name: x_foxsec_ip_reputation + type: STRING + mode: NULLABLE + - name: x_lb_tags + type: STRING + mode: NULLABLE + - name: parsed_date + type: TIMESTAMP + mode: NULLABLE + - name: parsed_x_source_tags + type: STRING + mode: REPEATED + - name: parsed_x_lb_tags + type: RECORD + mode: NULLABLE + fields: + - name: tls_version + type: STRING + mode: NULLABLE + - name: tls_cipher_hex + type: STRING + mode: NULLABLE + - name: isp + type: RECORD + mode: NULLABLE + fields: + - name: db_version + type: STRING + mode: NULLABLE + - name: name + type: STRING + mode: NULLABLE + - name: organization + type: STRING + mode: NULLABLE + - name: user_agent + type: RECORD + mode: NULLABLE + fields: + - name: browser + type: STRING + mode: NULLABLE + - name: os + type: STRING + mode: NULLABLE + - name: version + type: STRING + mode: NULLABLE +- name: normalized_app_name + type: STRING + mode: NULLABLE +- name: normalized_channel + type: STRING + mode: NULLABLE +- name: normalized_country_code + type: STRING + mode: NULLABLE +- name: normalized_os + type: STRING + mode: NULLABLE +- name: normalized_os_version + type: STRING + mode: NULLABLE +- name: position + type: INTEGER + mode: NULLABLE +- name: release_channel + type: STRING + mode: NULLABLE +- name: reporting_url + type: STRING + mode: NULLABLE +- name: sample_id + type: INTEGER + mode: NULLABLE +- name: submission_timestamp + type: TIMESTAMP + mode: NULLABLE +- name: version + type: STRING + mode: NULLABLE +- name: scenario + type: STRING + mode: NULLABLE +- name: request_id + type: STRING + mode: NULLABLE +- name: match_type + type: STRING + mode: NULLABLE +- name: improve_suggest_experience_checked + type: BOOLEAN + mode: NULLABLE +- name: source + type: STRING + mode: NULLABLE diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/contextual_services/request_payload_suggest/schema.yaml /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/contextual_services/request_payload_suggest/schema.yaml --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/contextual_services/request_payload_suggest/schema.yaml 2024-07-25 15:07:19.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/contextual_services/request_payload_suggest/schema.yaml 2024-07-25 14:59:18.000000000 +0000 @@ -1 +1,28 @@ -{} +fields: +- name: form_factor + type: STRING + mode: NULLABLE +- name: flagged_fraud + type: BOOLEAN + mode: NULLABLE +- name: submission_date + type: DATE + mode: NULLABLE +- name: country_code + type: STRING + mode: NULLABLE +- name: region_code + type: STRING + mode: NULLABLE +- name: os_family + type: STRING + mode: NULLABLE +- name: product_version + type: INTEGER + mode: NULLABLE +- name: impression_count + type: INTEGER + mode: NULLABLE +- name: click_count + type: INTEGER + mode: NULLABLE diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/contextual_services/request_payload_tiles/schema.yaml /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/contextual_services/request_payload_tiles/schema.yaml --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/contextual_services/request_payload_tiles/schema.yaml 2024-07-25 15:07:19.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/contextual_services/request_payload_tiles/schema.yaml 2024-07-25 14:59:17.000000000 +0000 @@ -1 +1,34 @@ -{} +fields: +- name: form_factor + type: STRING + mode: NULLABLE +- name: flagged_fraud + type: BOOLEAN + mode: NULLABLE +- name: submission_date + type: DATE + mode: NULLABLE +- name: begin_timestamp + type: TIMESTAMP + mode: NULLABLE +- name: end_timestamp + type: TIMESTAMP + mode: NULLABLE +- name: country_code + type: STRING + mode: NULLABLE +- name: region_code + type: STRING + mode: NULLABLE +- name: os_family + type: STRING + mode: NULLABLE +- name: product_version + type: INTEGER + mode: NULLABLE +- name: impression_count + type: INTEGER + mode: NULLABLE +- name: click_count + type: INTEGER + mode: NULLABLE diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/contextual_services/suggest_revenue_levers_daily/schema.yaml /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/contextual_services/suggest_revenue_levers_daily/schema.yaml --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/contextual_services/suggest_revenue_levers_daily/schema.yaml 2024-07-25 15:07:19.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/contextual_services/suggest_revenue_levers_daily/schema.yaml 2024-07-25 14:59:18.000000000 +0000 @@ -1 +1,34 @@ -{} +fields: +- name: country + type: STRING + mode: NULLABLE +- name: submission_date + type: DATE + mode: NULLABLE +- name: device + type: STRING + mode: NULLABLE +- name: eligible_share_country + type: FLOAT + mode: NULLABLE +- name: live_market_dau + type: INTEGER + mode: NULLABLE +- name: urlbar_search_dau + type: INTEGER + mode: NULLABLE +- name: suggest_exposed_clients + type: INTEGER + mode: NULLABLE +- name: urlbar_search + type: INTEGER + mode: NULLABLE +- name: total_impressions + type: INTEGER + mode: NULLABLE +- name: spons_impressions + type: INTEGER + mode: NULLABLE +- name: spons_clicks + type: INTEGER + mode: NULLABLE diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/contextual_services/topsites_click_live/schema.yaml /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/contextual_services/topsites_click_live/schema.yaml --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/contextual_services/topsites_click_live/schema.yaml 2024-07-25 15:07:19.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/contextual_services/topsites_click_live/schema.yaml 2024-07-25 14:59:17.000000000 +0000 @@ -1 +1,162 @@ -{} +fields: +- name: additional_properties + type: STRING + mode: NULLABLE +- name: advertiser + type: STRING + mode: NULLABLE +- name: context_id + type: STRING + mode: NULLABLE +- name: document_id + type: STRING + mode: NULLABLE +- name: experiments + type: RECORD + mode: REPEATED + fields: + - name: key + type: STRING + mode: NULLABLE + - name: value + type: RECORD + mode: NULLABLE + fields: + - name: branch + type: STRING + mode: NULLABLE +- name: locale + type: STRING + mode: NULLABLE +- name: metadata + type: RECORD + mode: NULLABLE + fields: + - name: geo + type: RECORD + mode: NULLABLE + fields: + - name: city + type: STRING + mode: NULLABLE + - name: country + type: STRING + mode: NULLABLE + - name: db_version + type: STRING + mode: NULLABLE + - name: subdivision1 + type: STRING + mode: NULLABLE + - name: subdivision2 + type: STRING + mode: NULLABLE + - name: header + type: RECORD + mode: NULLABLE + fields: + - name: date + type: STRING + mode: NULLABLE + - name: dnt + type: STRING + mode: NULLABLE + - name: x_debug_id + type: STRING + mode: NULLABLE + - name: x_pingsender_version + type: STRING + mode: NULLABLE + - name: x_source_tags + type: STRING + mode: NULLABLE + - name: x_telemetry_agent + type: STRING + mode: NULLABLE + - name: x_foxsec_ip_reputation + type: STRING + mode: NULLABLE + - name: x_lb_tags + type: STRING + mode: NULLABLE + - name: parsed_date + type: TIMESTAMP + mode: NULLABLE + - name: parsed_x_source_tags + type: STRING + mode: REPEATED + - name: parsed_x_lb_tags + type: RECORD + mode: NULLABLE + fields: + - name: tls_version + type: STRING + mode: NULLABLE + - name: tls_cipher_hex + type: STRING + mode: NULLABLE + - name: isp + type: RECORD + mode: NULLABLE + fields: + - name: db_version + type: STRING + mode: NULLABLE + - name: name + type: STRING + mode: NULLABLE + - name: organization + type: STRING + mode: NULLABLE + - name: user_agent + type: RECORD + mode: NULLABLE + fields: + - name: browser + type: STRING + mode: NULLABLE + - name: os + type: STRING + mode: NULLABLE + - name: version + type: STRING + mode: NULLABLE +- name: normalized_app_name + type: STRING + mode: NULLABLE +- name: normalized_channel + type: STRING + mode: NULLABLE +- name: normalized_country_code + type: STRING + mode: NULLABLE +- name: normalized_os + type: STRING + mode: NULLABLE +- name: normalized_os_version + type: STRING + mode: NULLABLE +- name: position + type: INTEGER + mode: NULLABLE +- name: release_channel + type: STRING + mode: NULLABLE +- name: reporting_url + type: STRING + mode: NULLABLE +- name: sample_id + type: INTEGER + mode: NULLABLE +- name: submission_timestamp + type: TIMESTAMP + mode: NULLABLE +- name: tile_id + type: INTEGER + mode: NULLABLE +- name: version + type: STRING + mode: NULLABLE +- name: source + type: STRING + mode: NULLABLE diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/contextual_services/topsites_impression_live/schema.yaml /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/contextual_services/topsites_impression_live/schema.yaml --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/contextual_services/topsites_impression_live/schema.yaml 2024-07-25 15:07:19.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/contextual_services/topsites_impression_live/schema.yaml 2024-07-25 14:59:18.000000000 +0000 @@ -1 +1,162 @@ -{} +fields: +- name: additional_properties + type: STRING + mode: NULLABLE +- name: advertiser + type: STRING + mode: NULLABLE +- name: context_id + type: STRING + mode: NULLABLE +- name: document_id + type: STRING + mode: NULLABLE +- name: experiments + type: RECORD + mode: REPEATED + fields: + - name: key + type: STRING + mode: NULLABLE + - name: value + type: RECORD + mode: NULLABLE + fields: + - name: branch + type: STRING + mode: NULLABLE +- name: locale + type: STRING + mode: NULLABLE +- name: metadata + type: RECORD + mode: NULLABLE + fields: + - name: geo + type: RECORD + mode: NULLABLE + fields: + - name: city + type: STRING + mode: NULLABLE + - name: country + type: STRING + mode: NULLABLE + - name: db_version + type: STRING + mode: NULLABLE + - name: subdivision1 + type: STRING + mode: NULLABLE + - name: subdivision2 + type: STRING + mode: NULLABLE + - name: header + type: RECORD + mode: NULLABLE + fields: + - name: date + type: STRING + mode: NULLABLE + - name: dnt + type: STRING + mode: NULLABLE + - name: x_debug_id + type: STRING + mode: NULLABLE + - name: x_pingsender_version + type: STRING + mode: NULLABLE + - name: x_source_tags + type: STRING + mode: NULLABLE + - name: x_telemetry_agent + type: STRING + mode: NULLABLE + - name: x_foxsec_ip_reputation + type: STRING + mode: NULLABLE + - name: x_lb_tags + type: STRING + mode: NULLABLE + - name: parsed_date + type: TIMESTAMP + mode: NULLABLE + - name: parsed_x_source_tags + type: STRING + mode: REPEATED + - name: parsed_x_lb_tags + type: RECORD + mode: NULLABLE + fields: + - name: tls_version + type: STRING + mode: NULLABLE + - name: tls_cipher_hex + type: STRING + mode: NULLABLE + - name: isp + type: RECORD + mode: NULLABLE + fields: + - name: db_version + type: STRING + mode: NULLABLE + - name: name + type: STRING + mode: NULLABLE + - name: organization + type: STRING + mode: NULLABLE + - name: user_agent + type: RECORD + mode: NULLABLE + fields: + - name: browser + type: STRING + mode: NULLABLE + - name: os + type: STRING + mode: NULLABLE + - name: version + type: STRING + mode: NULLABLE +- name: normalized_app_name + type: STRING + mode: NULLABLE +- name: normalized_channel + type: STRING + mode: NULLABLE +- name: normalized_country_code + type: STRING + mode: NULLABLE +- name: normalized_os + type: STRING + mode: NULLABLE +- name: normalized_os_version + type: STRING + mode: NULLABLE +- name: position + type: INTEGER + mode: NULLABLE +- name: release_channel + type: STRING + mode: NULLABLE +- name: reporting_url + type: STRING + mode: NULLABLE +- name: sample_id + type: INTEGER + mode: NULLABLE +- name: submission_timestamp + type: TIMESTAMP + mode: NULLABLE +- name: tile_id + type: INTEGER + mode: NULLABLE +- name: version + type: STRING + mode: NULLABLE +- name: source + type: STRING + mode: NULLABLE diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/fenix/client_deduplication/schema.yaml /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/fenix/client_deduplication/schema.yaml --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/fenix/client_deduplication/schema.yaml 2024-07-25 15:07:19.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/fenix/client_deduplication/schema.yaml 2024-07-25 14:56:57.000000000 +0000 @@ -1 +1,391 @@ -{} +fields: +- name: client_id + type: STRING + mode: NULLABLE +- name: hashed_ad_id + type: STRING + mode: NULLABLE +- name: valid_advertising_id + type: BOOLEAN + mode: NULLABLE +- name: submission_date + type: DATE + mode: NULLABLE +- name: normalized_app_id + type: STRING + mode: NULLABLE +- name: additional_properties + type: STRING + mode: NULLABLE +- name: client_info + type: RECORD + mode: NULLABLE + fields: + - name: android_sdk_version + type: STRING + mode: NULLABLE + - name: app_build + type: STRING + mode: NULLABLE + - name: app_channel + type: STRING + mode: NULLABLE + - name: app_display_version + type: STRING + mode: NULLABLE + - name: architecture + type: STRING + mode: NULLABLE + - name: build_date + type: STRING + mode: NULLABLE + - name: client_id + type: STRING + mode: NULLABLE + - name: device_manufacturer + type: STRING + mode: NULLABLE + - name: device_model + type: STRING + mode: NULLABLE + - name: first_run_date + type: STRING + mode: NULLABLE + - name: locale + type: STRING + mode: NULLABLE + - name: os + type: STRING + mode: NULLABLE + - name: os_version + type: STRING + mode: NULLABLE + - name: telemetry_sdk_build + type: STRING + mode: NULLABLE + - name: windows_build_number + type: INTEGER + mode: NULLABLE + - name: session_count + type: INTEGER + mode: NULLABLE + - name: session_id + type: STRING + mode: NULLABLE +- name: document_id + type: STRING + mode: NULLABLE +- name: events + type: RECORD + mode: REPEATED + fields: + - name: category + type: STRING + mode: NULLABLE + - name: extra + type: RECORD + mode: REPEATED + fields: + - name: key + type: STRING + mode: NULLABLE + - name: value + type: STRING + mode: NULLABLE + - name: name + type: STRING + mode: NULLABLE + - name: timestamp + type: INTEGER + mode: NULLABLE +- name: metadata + type: RECORD + mode: NULLABLE + fields: + - name: geo + type: RECORD + mode: NULLABLE + fields: + - name: city + type: STRING + mode: NULLABLE + - name: country + type: STRING + mode: NULLABLE + - name: db_version + type: STRING + mode: NULLABLE + - name: subdivision1 + type: STRING + mode: NULLABLE + - name: subdivision2 + type: STRING + mode: NULLABLE + - name: header + type: RECORD + mode: NULLABLE + fields: + - name: date + type: STRING + mode: NULLABLE + - name: dnt + type: STRING + mode: NULLABLE + - name: x_debug_id + type: STRING + mode: NULLABLE + - name: x_foxsec_ip_reputation + type: STRING + mode: NULLABLE + - name: x_lb_tags + type: STRING + mode: NULLABLE + - name: x_pingsender_version + type: STRING + mode: NULLABLE + - name: x_source_tags + type: STRING + mode: NULLABLE + - name: x_telemetry_agent + type: STRING + mode: NULLABLE + - name: parsed_date + type: TIMESTAMP + mode: NULLABLE + - name: parsed_x_source_tags + type: STRING + mode: REPEATED + - name: parsed_x_lb_tags + type: RECORD + mode: NULLABLE + fields: + - name: tls_version + type: STRING + mode: NULLABLE + - name: tls_cipher_hex + type: STRING + mode: NULLABLE + - name: isp + type: RECORD + mode: NULLABLE + fields: + - name: db_version + type: STRING + mode: NULLABLE + - name: name + type: STRING + mode: NULLABLE + - name: organization + type: STRING + mode: NULLABLE + - name: user_agent + type: RECORD + mode: NULLABLE + fields: + - name: browser + type: STRING + mode: NULLABLE + - name: os + type: STRING + mode: NULLABLE + - name: version + type: STRING + mode: NULLABLE +- name: metrics + type: RECORD + mode: NULLABLE + fields: + - name: boolean + type: RECORD + mode: NULLABLE + fields: + - name: client_deduplication_valid_advertising_id + type: BOOLEAN + mode: NULLABLE + - name: counter + type: RECORD + mode: NULLABLE + fields: + - name: events_normal_and_private_uri_count + type: INTEGER + mode: NULLABLE + - name: metrics_tabs_open_count + type: INTEGER + mode: NULLABLE + - name: labeled_counter + type: RECORD + mode: NULLABLE + fields: + - name: browser_search_ad_clicks + type: RECORD + mode: REPEATED + fields: + - name: key + type: STRING + mode: NULLABLE + - name: value + type: INTEGER + mode: NULLABLE + - name: browser_search_in_content + type: RECORD + mode: REPEATED + fields: + - name: key + type: STRING + mode: NULLABLE + - name: value + type: INTEGER + mode: NULLABLE + - name: browser_search_with_ads + type: RECORD + mode: REPEATED + fields: + - name: key + type: STRING + mode: NULLABLE + - name: value + type: INTEGER + mode: NULLABLE + - name: glean_error_invalid_label + type: RECORD + mode: REPEATED + fields: + - name: key + type: STRING + mode: NULLABLE + - name: value + type: INTEGER + mode: NULLABLE + - name: glean_error_invalid_overflow + type: RECORD + mode: REPEATED + fields: + - name: key + type: STRING + mode: NULLABLE + - name: value + type: INTEGER + mode: NULLABLE + - name: glean_error_invalid_state + type: RECORD + mode: REPEATED + fields: + - name: key + type: STRING + mode: NULLABLE + - name: value + type: INTEGER + mode: NULLABLE + - name: glean_error_invalid_value + type: RECORD + mode: REPEATED + fields: + - name: key + type: STRING + mode: NULLABLE + - name: value + type: INTEGER + mode: NULLABLE + - name: metrics_search_count + type: RECORD + mode: REPEATED + fields: + - name: key + type: STRING + mode: NULLABLE + - name: value + type: INTEGER + mode: NULLABLE + - name: string + type: RECORD + mode: NULLABLE + fields: + - name: activation_identifier + type: STRING + mode: NULLABLE + - name: client_deduplication_experiment_timeframe + type: STRING + mode: NULLABLE + - name: search_default_engine_code + type: STRING + mode: NULLABLE + - name: search_default_engine_name + type: STRING + mode: NULLABLE + - name: client_deduplication_hashed_gaid + type: STRING + mode: NULLABLE + - name: glean_client_annotation_experimentation_id + type: STRING + mode: NULLABLE +- name: normalized_app_name + type: STRING + mode: NULLABLE +- name: normalized_channel + type: STRING + mode: NULLABLE + description: Normalized channel name +- name: normalized_country_code + type: STRING + mode: NULLABLE +- name: normalized_os + type: STRING + mode: NULLABLE +- name: normalized_os_version + type: STRING + mode: NULLABLE +- name: ping_info + type: RECORD + mode: NULLABLE + fields: + - name: end_time + type: STRING + mode: NULLABLE + - name: experiments + type: RECORD + mode: REPEATED + fields: + - name: key + type: STRING + mode: NULLABLE + - name: value + type: RECORD + mode: NULLABLE + fields: + - name: branch + type: STRING + mode: NULLABLE + - name: extra + type: RECORD + mode: NULLABLE + fields: + - name: type + type: STRING + mode: NULLABLE + - name: enrollment_id + type: STRING + mode: NULLABLE + - name: ping_type + type: STRING + mode: NULLABLE + - name: reason + type: STRING + mode: NULLABLE + - name: seq + type: INTEGER + mode: NULLABLE + - name: start_time + type: STRING + mode: NULLABLE + - name: parsed_start_time + type: TIMESTAMP + mode: NULLABLE + - name: parsed_end_time + type: TIMESTAMP + mode: NULLABLE +- name: sample_id + type: INTEGER + mode: NULLABLE +- name: submission_timestamp + type: TIMESTAMP + mode: NULLABLE diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/fenix/use_counters/schema.yaml /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/fenix/use_counters/schema.yaml --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/fenix/use_counters/schema.yaml 2024-07-25 15:07:19.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/fenix/use_counters/schema.yaml 2024-07-25 14:58:56.000000000 +0000 @@ -1,11 +1,11 @@ fields: - name: normalized_app_id - mode: NULLABLE type: STRING + mode: NULLABLE description: App ID of the channel data was received from - name: normalized_channel - mode: NULLABLE type: STRING + mode: NULLABLE 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/klar_android/use_counters/schema.yaml /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/klar_android/use_counters/schema.yaml --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/klar_android/use_counters/schema.yaml 2024-07-25 15:07:18.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/klar_android/use_counters/schema.yaml 2024-07-25 15:03:07.000000000 +0000 @@ -1,11 +1,11 @@ fields: - name: normalized_app_id - mode: NULLABLE type: STRING + mode: NULLABLE description: App ID of the channel data was received from - name: normalized_channel - mode: NULLABLE type: STRING + mode: NULLABLE 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/monitoring/shredder_progress/schema.yaml /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/monitoring/shredder_progress/schema.yaml --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/monitoring/shredder_progress/schema.yaml 2024-07-25 15:07:19.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/monitoring/shredder_progress/schema.yaml 1970-01-01 00:00:00.000000000 +0000 @@ -1 +0,0 @@ -{} diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/monitoring_derived/outerbounds_cost_per_flow_run_v1/metadata.yaml /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/monitoring_derived/outerbounds_cost_per_flow_run_v1/metadata.yaml --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/monitoring_derived/outerbounds_cost_per_flow_run_v1/metadata.yaml 1970-01-01 00:00:00.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/monitoring_derived/outerbounds_cost_per_flow_run_v1/metadata.yaml 2024-07-25 15:06:30.000000000 +0000 @@ -0,0 +1,13 @@ +friendly_name: Outerbounds Cost Per Flow Run V1 +description: |- + Please provide a description for the query +owners: [] +labels: {} +bigquery: null +workgroup_access: +- role: roles/bigquery.dataViewer + members: + - workgroup:mozilla-confidential +references: + view.sql: + - moz-fx-data-shared-prod.billing_syndicate.gcp_billing_export_resource_v1_01E7D5_97288E_E2EBA0 diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/monitoring_derived/outerbounds_cost_per_flow_run_v1/view.sql /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/monitoring_derived/outerbounds_cost_per_flow_run_v1/view.sql --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/monitoring_derived/outerbounds_cost_per_flow_run_v1/view.sql 1970-01-01 00:00:00.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/monitoring_derived/outerbounds_cost_per_flow_run_v1/view.sql 2024-07-25 14:48:41.000000000 +0000 @@ -0,0 +1,21 @@ +CREATE OR REPLACE VIEW + `moz-fx-data-shared-prod.monitoring_derived.outerbounds_cost_per_flow_run_v1` +AS +SELECT + DATE_TRUNC(usage_start_time, DAY) AS invoice_day, + labels.value AS run_id, + IFNULL(SAFE.LEFT(labels.value, INSTR(labels.value, '-', -1) - 1), labels.value) AS flow_name, + SUM(cost) AS cost_usd +FROM + `moz-fx-data-shared-prod.billing_syndicate.gcp_billing_export_resource_v1_01E7D5_97288E_E2EBA0` +JOIN + UNNEST(labels) AS labels + ON labels.key = "k8s-label/workflows.argoproj.io/workflow" +WHERE + project.id = "moz-fx-mfouterbounds-prod-f98d" + AND usage_start_time >= '2024-07-10' +GROUP BY + invoice_day, + run_id +ORDER BY + flow_name DESC diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/monitoring_derived/outerbounds_cost_per_flow_v1/metadata.yaml /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/monitoring_derived/outerbounds_cost_per_flow_v1/metadata.yaml --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/monitoring_derived/outerbounds_cost_per_flow_v1/metadata.yaml 1970-01-01 00:00:00.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/monitoring_derived/outerbounds_cost_per_flow_v1/metadata.yaml 2024-07-25 15:06:30.000000000 +0000 @@ -0,0 +1,13 @@ +friendly_name: Outerbounds Cost Per Flow V1 +description: |- + Please provide a description for the query +owners: [] +labels: {} +bigquery: null +workgroup_access: +- role: roles/bigquery.dataViewer + members: + - workgroup:mozilla-confidential +references: + view.sql: + - moz-fx-data-shared-prod.monitoring_derived.outerbounds_cost_per_flow_run_v1 diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/monitoring_derived/outerbounds_cost_per_flow_v1/view.sql /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/monitoring_derived/outerbounds_cost_per_flow_v1/view.sql --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/monitoring_derived/outerbounds_cost_per_flow_v1/view.sql 1970-01-01 00:00:00.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/monitoring_derived/outerbounds_cost_per_flow_v1/view.sql 2024-07-25 14:48:41.000000000 +0000 @@ -0,0 +1,13 @@ +CREATE OR REPLACE VIEW + `moz-fx-data-shared-prod.monitoring_derived.outerbounds_cost_per_flow_v1` +AS +SELECT + flow_name, + SUM(cost_usd) AS total_cost_usd, + COUNT(flow_name) AS num_runs +FROM + `moz-fx-data-shared-prod.monitoring_derived.outerbounds_cost_per_flow_run_v1` +GROUP BY + flow_name +ORDER BY + total_cost_usd DESC diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/org_mozilla_fenix/client_deduplication/schema.yaml /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/org_mozilla_fenix/client_deduplication/schema.yaml --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/org_mozilla_fenix/client_deduplication/schema.yaml 2024-07-25 15:07:19.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/org_mozilla_fenix/client_deduplication/schema.yaml 2024-07-25 15:00:39.000000000 +0000 @@ -1 +1,381 @@ -{} +fields: +- name: submission_date + type: DATE + mode: NULLABLE +- name: normalized_app_id + type: STRING + mode: NULLABLE +- name: additional_properties + type: STRING + mode: NULLABLE +- name: client_info + type: RECORD + mode: NULLABLE + fields: + - name: android_sdk_version + type: STRING + mode: NULLABLE + - name: app_build + type: STRING + mode: NULLABLE + - name: app_channel + type: STRING + mode: NULLABLE + - name: app_display_version + type: STRING + mode: NULLABLE + - name: architecture + type: STRING + mode: NULLABLE + - name: build_date + type: STRING + mode: NULLABLE + - name: client_id + type: STRING + mode: NULLABLE + - name: device_manufacturer + type: STRING + mode: NULLABLE + - name: device_model + type: STRING + mode: NULLABLE + - name: first_run_date + type: STRING + mode: NULLABLE + - name: locale + type: STRING + mode: NULLABLE + - name: os + type: STRING + mode: NULLABLE + - name: os_version + type: STRING + mode: NULLABLE + - name: telemetry_sdk_build + type: STRING + mode: NULLABLE + - name: windows_build_number + type: INTEGER + mode: NULLABLE + - name: session_count + type: INTEGER + mode: NULLABLE + - name: session_id + type: STRING + mode: NULLABLE +- name: document_id + type: STRING + mode: NULLABLE +- name: events + type: RECORD + mode: REPEATED + fields: + - name: category + type: STRING + mode: NULLABLE + - name: extra + type: RECORD + mode: REPEATED + fields: + - name: key + type: STRING + mode: NULLABLE + - name: value + type: STRING + mode: NULLABLE + - name: name + type: STRING + mode: NULLABLE + - name: timestamp + type: INTEGER + mode: NULLABLE +- name: metadata + type: RECORD + mode: NULLABLE + fields: + - name: geo + type: RECORD + mode: NULLABLE + fields: + - name: city + type: STRING + mode: NULLABLE + - name: country + type: STRING + mode: NULLABLE + - name: db_version + type: STRING + mode: NULLABLE + - name: subdivision1 + type: STRING + mode: NULLABLE + - name: subdivision2 + type: STRING + mode: NULLABLE + - name: header + type: RECORD + mode: NULLABLE + fields: + - name: date + type: STRING + mode: NULLABLE + - name: dnt + type: STRING + mode: NULLABLE + - name: x_debug_id + type: STRING + mode: NULLABLE + - name: x_foxsec_ip_reputation + type: STRING + mode: NULLABLE + - name: x_lb_tags + type: STRING + mode: NULLABLE + - name: x_pingsender_version + type: STRING + mode: NULLABLE + - name: x_source_tags + type: STRING + mode: NULLABLE + - name: x_telemetry_agent + type: STRING + mode: NULLABLE + - name: parsed_date + type: TIMESTAMP + mode: NULLABLE + - name: parsed_x_source_tags + type: STRING + mode: REPEATED + - name: parsed_x_lb_tags + type: RECORD + mode: NULLABLE + fields: + - name: tls_version + type: STRING + mode: NULLABLE + - name: tls_cipher_hex + type: STRING + mode: NULLABLE + - name: isp + type: RECORD + mode: NULLABLE + fields: + - name: db_version + type: STRING + mode: NULLABLE + - name: name + type: STRING + mode: NULLABLE + - name: organization + type: STRING + mode: NULLABLE + - name: user_agent + type: RECORD + mode: NULLABLE + fields: + - name: browser + type: STRING + mode: NULLABLE + - name: os + type: STRING + mode: NULLABLE + - name: version + type: STRING + mode: NULLABLE +- name: metrics + type: RECORD + mode: NULLABLE + fields: + - name: boolean + type: RECORD + mode: NULLABLE + fields: + - name: client_deduplication_valid_advertising_id + type: BOOLEAN + mode: NULLABLE + - name: counter + type: RECORD + mode: NULLABLE + fields: + - name: events_normal_and_private_uri_count + type: INTEGER + mode: NULLABLE + - name: metrics_tabs_open_count + type: INTEGER + mode: NULLABLE + - name: labeled_counter + type: RECORD + mode: NULLABLE + fields: + - name: browser_search_ad_clicks + type: RECORD + mode: REPEATED + fields: + - name: key + type: STRING + mode: NULLABLE + - name: value + type: INTEGER + mode: NULLABLE + - name: browser_search_in_content + type: RECORD + mode: REPEATED + fields: + - name: key + type: STRING + mode: NULLABLE + - name: value + type: INTEGER + mode: NULLABLE + - name: browser_search_with_ads + type: RECORD + mode: REPEATED + fields: + - name: key + type: STRING + mode: NULLABLE + - name: value + type: INTEGER + mode: NULLABLE + - name: glean_error_invalid_label + type: RECORD + mode: REPEATED + fields: + - name: key + type: STRING + mode: NULLABLE + - name: value + type: INTEGER + mode: NULLABLE + - name: glean_error_invalid_overflow + type: RECORD + mode: REPEATED + fields: + - name: key + type: STRING + mode: NULLABLE + - name: value + type: INTEGER + mode: NULLABLE + - name: glean_error_invalid_state + type: RECORD + mode: REPEATED + fields: + - name: key + type: STRING + mode: NULLABLE + - name: value + type: INTEGER + mode: NULLABLE + - name: glean_error_invalid_value + type: RECORD + mode: REPEATED + fields: + - name: key + type: STRING + mode: NULLABLE + - name: value + type: INTEGER + mode: NULLABLE + - name: metrics_search_count + type: RECORD + mode: REPEATED + fields: + - name: key + type: STRING + mode: NULLABLE + - name: value + type: INTEGER + mode: NULLABLE + - name: string + type: RECORD + mode: NULLABLE + fields: + - name: activation_identifier + type: STRING + mode: NULLABLE + - name: client_deduplication_experiment_timeframe + type: STRING + mode: NULLABLE + - name: search_default_engine_code + type: STRING + mode: NULLABLE + - name: search_default_engine_name + type: STRING + mode: NULLABLE + - name: client_deduplication_hashed_gaid + type: STRING + mode: NULLABLE + - name: glean_client_annotation_experimentation_id + type: STRING + mode: NULLABLE +- name: normalized_app_name + type: STRING + mode: NULLABLE +- name: normalized_channel + type: STRING + mode: NULLABLE +- name: normalized_country_code + type: STRING + mode: NULLABLE +- name: normalized_os + type: STRING + mode: NULLABLE +- name: normalized_os_version + type: STRING + mode: NULLABLE +- name: ping_info + type: RECORD + mode: NULLABLE + fields: + - name: end_time + type: STRING + mode: NULLABLE + - name: experiments + type: RECORD + mode: REPEATED + fields: + - name: key + type: STRING + mode: NULLABLE + - name: value + type: RECORD + mode: NULLABLE + fields: + - name: branch + type: STRING + mode: NULLABLE + - name: extra + type: RECORD + mode: NULLABLE + fields: + - name: type + type: STRING + mode: NULLABLE + - name: enrollment_id + type: STRING + mode: NULLABLE + - name: ping_type + type: STRING + mode: NULLABLE + - name: reason + type: STRING + mode: NULLABLE + - name: seq + type: INTEGER + mode: NULLABLE + - name: start_time + type: STRING + mode: NULLABLE + - name: parsed_start_time + type: TIMESTAMP + mode: NULLABLE + - name: parsed_end_time + type: TIMESTAMP + mode: NULLABLE +- name: sample_id + type: INTEGER + mode: NULLABLE +- name: submission_timestamp + type: TIMESTAMP + mode: NULLABLE diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/org_mozilla_firefox/client_deduplication/schema.yaml /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/org_mozilla_firefox/client_deduplication/schema.yaml --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/org_mozilla_firefox/client_deduplication/schema.yaml 2024-07-25 15:07:19.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/org_mozilla_firefox/client_deduplication/schema.yaml 2024-07-25 14:59:04.000000000 +0000 @@ -1 +1,381 @@ -{} +fields: +- name: submission_date + type: DATE + mode: NULLABLE +- name: normalized_app_id + type: STRING + mode: NULLABLE +- name: additional_properties + type: STRING + mode: NULLABLE +- name: client_info + type: RECORD + mode: NULLABLE + fields: + - name: android_sdk_version + type: STRING + mode: NULLABLE + - name: app_build + type: STRING + mode: NULLABLE + - name: app_channel + type: STRING + mode: NULLABLE + - name: app_display_version + type: STRING + mode: NULLABLE + - name: architecture + type: STRING + mode: NULLABLE + - name: build_date + type: STRING + mode: NULLABLE + - name: client_id + type: STRING + mode: NULLABLE + - name: device_manufacturer + type: STRING + mode: NULLABLE + - name: device_model + type: STRING + mode: NULLABLE + - name: first_run_date + type: STRING + mode: NULLABLE + - name: locale + type: STRING + mode: NULLABLE + - name: os + type: STRING + mode: NULLABLE + - name: os_version + type: STRING + mode: NULLABLE + - name: telemetry_sdk_build + type: STRING + mode: NULLABLE + - name: windows_build_number + type: INTEGER + mode: NULLABLE + - name: session_count + type: INTEGER + mode: NULLABLE + - name: session_id + type: STRING + mode: NULLABLE +- name: document_id + type: STRING + mode: NULLABLE +- name: events + type: RECORD + mode: REPEATED + fields: + - name: category + type: STRING + mode: NULLABLE + - name: extra + type: RECORD + mode: REPEATED + fields: + - name: key + type: STRING + mode: NULLABLE + - name: value + type: STRING + mode: NULLABLE + - name: name + type: STRING + mode: NULLABLE + - name: timestamp + type: INTEGER + mode: NULLABLE +- name: metadata + type: RECORD + mode: NULLABLE + fields: + - name: geo + type: RECORD + mode: NULLABLE + fields: + - name: city + type: STRING + mode: NULLABLE + - name: country + type: STRING + mode: NULLABLE + - name: db_version + type: STRING + mode: NULLABLE + - name: subdivision1 + type: STRING + mode: NULLABLE + - name: subdivision2 + type: STRING + mode: NULLABLE + - name: header + type: RECORD + mode: NULLABLE + fields: + - name: date + type: STRING + mode: NULLABLE + - name: dnt + type: STRING + mode: NULLABLE + - name: x_debug_id + type: STRING + mode: NULLABLE + - name: x_foxsec_ip_reputation + type: STRING + mode: NULLABLE + - name: x_lb_tags + type: STRING + mode: NULLABLE + - name: x_pingsender_version + type: STRING + mode: NULLABLE + - name: x_source_tags + type: STRING + mode: NULLABLE + - name: x_telemetry_agent + type: STRING + mode: NULLABLE + - name: parsed_date + type: TIMESTAMP + mode: NULLABLE + - name: parsed_x_source_tags + type: STRING + mode: REPEATED + - name: parsed_x_lb_tags + type: RECORD + mode: NULLABLE + fields: + - name: tls_version + type: STRING + mode: NULLABLE + - name: tls_cipher_hex + type: STRING + mode: NULLABLE + - name: isp + type: RECORD + mode: NULLABLE + fields: + - name: db_version + type: STRING + mode: NULLABLE + - name: name + type: STRING + mode: NULLABLE + - name: organization + type: STRING + mode: NULLABLE + - name: user_agent + type: RECORD + mode: NULLABLE + fields: + - name: browser + type: STRING + mode: NULLABLE + - name: os + type: STRING + mode: NULLABLE + - name: version + type: STRING + mode: NULLABLE +- name: metrics + type: RECORD + mode: NULLABLE + fields: + - name: boolean + type: RECORD + mode: NULLABLE + fields: + - name: client_deduplication_valid_advertising_id + type: BOOLEAN + mode: NULLABLE + - name: counter + type: RECORD + mode: NULLABLE + fields: + - name: events_normal_and_private_uri_count + type: INTEGER + mode: NULLABLE + - name: metrics_tabs_open_count + type: INTEGER + mode: NULLABLE + - name: labeled_counter + type: RECORD + mode: NULLABLE + fields: + - name: browser_search_ad_clicks + type: RECORD + mode: REPEATED + fields: + - name: key + type: STRING + mode: NULLABLE + - name: value + type: INTEGER + mode: NULLABLE + - name: browser_search_in_content + type: RECORD + mode: REPEATED + fields: + - name: key + type: STRING + mode: NULLABLE + - name: value + type: INTEGER + mode: NULLABLE + - name: browser_search_with_ads + type: RECORD + mode: REPEATED + fields: + - name: key + type: STRING + mode: NULLABLE + - name: value + type: INTEGER + mode: NULLABLE + - name: glean_error_invalid_label + type: RECORD + mode: REPEATED + fields: + - name: key + type: STRING + mode: NULLABLE + - name: value + type: INTEGER + mode: NULLABLE + - name: glean_error_invalid_overflow + type: RECORD + mode: REPEATED + fields: + - name: key + type: STRING + mode: NULLABLE + - name: value + type: INTEGER + mode: NULLABLE + - name: glean_error_invalid_state + type: RECORD + mode: REPEATED + fields: + - name: key + type: STRING + mode: NULLABLE + - name: value + type: INTEGER + mode: NULLABLE + - name: glean_error_invalid_value + type: RECORD + mode: REPEATED + fields: + - name: key + type: STRING + mode: NULLABLE + - name: value + type: INTEGER + mode: NULLABLE + - name: me ```

⚠️ Only part of the diff is displayed.

Link to full diff

mozilla / bigquery-etl