copy code from marketing-prod to shared-prod, refactor query to pull …

Marlene-M-Hirose commented 2 months ago

…from shared-prod, change owner in metadata

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 "copy code from marketing-prod to shared-prod, refactor query to pull from shared-prod, change owner in metadata"

`sql.diff`

Click to expand!

```diff diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/dags/bqetl_google_analytics_derived_ga4.py /tmp/workspace/generated-sql/dags/bqetl_google_analytics_derived_ga4.py --- /tmp/workspace/main-generated-sql/dags/bqetl_google_analytics_derived_ga4.py 2024-08-08 22:47:37.000000000 +0000 +++ /tmp/workspace/generated-sql/dags/bqetl_google_analytics_derived_ga4.py 2024-08-08 22:48:45.000000000 +0000 @@ -382,6 +382,21 @@ depends_on_past=False, ) + mozilla_org_derived__blogs_landing_page_summary__v2 = bigquery_etl_query( + task_id="mozilla_org_derived__blogs_landing_page_summary__v2", + destination_table="blogs_landing_page_summary_v2", + dataset_id="mozilla_org_derived", + project_id="moz-fx-data-shared-prod", + owner="mhirose@mozilla.com", + email=[ + "kwindau@mozilla.com", + "mhirose@mozilla.com", + "telemetry-alerts@mozilla.com", + ], + date_partition_parameter="submission_date", + depends_on_past=False, + ) + mozilla_org_derived__blogs_sessions__v2 = bigquery_etl_query( task_id="mozilla_org_derived__blogs_sessions__v2", destination_table="blogs_sessions_v2", @@ -554,6 +569,18 @@ mozilla_org_derived__blogs_goals__v2.set_upstream(wait_for_blogs_events_table) + mozilla_org_derived__blogs_landing_page_summary__v2.set_upstream( + mozilla_org_derived__blogs_goals__v2 + ) + + mozilla_org_derived__blogs_landing_page_summary__v2.set_upstream( + mozilla_org_derived__blogs_sessions__v2 + ) + + mozilla_org_derived__blogs_landing_page_summary__v2.set_upstream( + wait_for_blogs_events_table + ) + mozilla_org_derived__blogs_sessions__v2.set_upstream(wait_for_blogs_events_table) mozilla_org_derived__ga_clients__v2.set_upstream( Only in /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/mozilla_org_derived: blogs_landing_page_summary_v2 diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/mozilla_org_derived/blogs_landing_page_summary_v2/metadata.yaml /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/mozilla_org_derived/blogs_landing_page_summary_v2/metadata.yaml --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/mozilla_org_derived/blogs_landing_page_summary_v2/metadata.yaml 1970-01-01 00:00:00.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/mozilla_org_derived/blogs_landing_page_summary_v2/metadata.yaml 2024-08-08 22:44:52.000000000 +0000 @@ -0,0 +1,32 @@ +friendly_name: Blogs Landing Page Summary V2 +description: |- + Daily summary Google analytics data for blog.mozilla.org landing page +owners: +- mhirose@mozilla.com +labels: + incremental: true + owner1: mhirose + dag: bqetl_google_analytics_derived_ga4 +scheduling: + dag_name: bqetl_google_analytics_derived_ga4 + depends_on: + - task_id: wait_for_blogs_events_table + dag_name: bqetl_google_analytics_derived_ga4 +bigquery: + time_partitioning: + type: day + field: date + require_partition_filter: true + expiration_days: null + range_partitioning: null + clustering: + fields: + - cleaned_landing_page + - browser + - blog + - subblog +workgroup_access: +- role: roles/bigquery.dataViewer + members: + - workgroup:mozilla-confidential +references: {} diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/mozilla_org_derived/blogs_landing_page_summary_v2/query.sql /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/mozilla_org_derived/blogs_landing_page_summary_v2/query.sql --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/mozilla_org_derived/blogs_landing_page_summary_v2/query.sql 1970-01-01 00:00:00.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/mozilla_org_derived/blogs_landing_page_summary_v2/query.sql 2024-08-08 22:42:57.000000000 +0000 @@ -0,0 +1,108 @@ +WITH landing_page_staging AS ( + SELECT + PARSE_DATE('%Y%m%d', event_date) AS `date`, + user_pseudo_id || '-' || CAST( + ( + SELECT + `value` + FROM + UNNEST(event_params) + WHERE + key = 'ga_session_id' + LIMIT + 1 + ).int_value AS STRING + ) AS visit_identifier, + ( + SELECT + `value` + FROM + UNNEST(event_params) + WHERE + key = 'page_location' + LIMIT + 1 + ).string_value AS landing_page, + ( + SELECT + `value` + FROM + UNNEST(event_params) + WHERE + key = 'entrances' + LIMIT + 1 + ).int_value AS is_entrance + FROM + `moz-fx-data-marketing-prod.analytics_314399816.events_*` + WHERE + _TABLE_SUFFIX = FORMAT_DATE('%Y%m%d', @submission_date) + QUALIFY + ROW_NUMBER() OVER (PARTITION BY visit_identifier ORDER BY event_timestamp ASC) = 1 +), +landing_page_stg AS ( + SELECT + `date`, + visit_identifier, + landing_page, + SPLIT(landing_page, '?')[OFFSET(0)] AS cleaned_landing_page, + SUM(is_entrance) AS page_sessions + FROM + landing_page_staging + GROUP BY + `date`, + visit_identifier, + landing_page, + cleaned_landing_page + HAVING + page_sessions > 0 +) +SELECT + sessions_table.`date`, + sessions_table.device_category, + sessions_table.operating_system, + sessions_table.browser, + sessions_table.`language`, + sessions_table.country, + standardized_country_list.standardized_country AS standardized_country_name, + sessions_table.source, + sessions_table.medium, + sessions_table.campaign, + sessions_table.content, + sessions_table.blog, + sessions_table.subblog, + lps.landing_page, + lps.cleaned_landing_page, + SUM(sessions_table.sessions) AS sessions, + SUM(goals_table.downloads) AS downloads, + SUM(goals_table.social_share) AS social_share, + SUM(goals_table.newsletter_subscription) AS newsletter_subscription, +FROM + `moz-fx-data-shared-prod.mozilla_org_derived.blogs_sessions_v2` AS sessions_table +LEFT JOIN + `moz-fx-data-shared-prod.mozilla_org_derived.blogs_goals_v2` AS goals_table + USING (`date`, visit_identifier) +LEFT JOIN + landing_page_stg AS lps + USING (`date`, visit_identifier) +LEFT JOIN + `moz-fx-data-shared-prod.static.third_party_standardized_country_names` AS standardized_country_list + ON sessions_table.country = standardized_country_list.raw_country +WHERE + sessions_table.date = @submission_date +GROUP BY + sessions_table.`date`, + sessions_table.device_category, + sessions_table.operating_system, + sessions_table.browser, + sessions_table.`language`, + sessions_table.country, + standardized_country_list.standardized_country, + sessions_table.source, + sessions_table.medium, + sessions_table.campaign, + sessions_table.content, + sessions_table.blog, + sessions_table.subblog, + lps.landing_page, + lps.cleaned_landing_page diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/mozilla_org_derived/blogs_landing_page_summary_v2/schema.yaml /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/mozilla_org_derived/blogs_landing_page_summary_v2/schema.yaml --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/mozilla_org_derived/blogs_landing_page_summary_v2/schema.yaml 1970-01-01 00:00:00.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/mozilla_org_derived/blogs_landing_page_summary_v2/schema.yaml 2024-08-08 22:42:57.000000000 +0000 @@ -0,0 +1,77 @@ +fields: +- mode: NULLABLE + name: date + type: DATE + description: Date of the entrance to the visit +- mode: NULLABLE + name: device_category + type: STRING + description: Device Category - The device category the visitor used to visit the site +- mode: NULLABLE + name: operating_system + type: STRING + description: Operating System - The operating system the visitor used to visit the site +- mode: NULLABLE + name: browser + type: STRING + description: Browser - The browser the visitor used to visit the site +- mode: NULLABLE + name: language + type: STRING + description: Language - Language of visitor's device +- mode: NULLABLE + name: country + type: STRING + description: Country - The country from which events were reported, based on IP address +- mode: NULLABLE + name: standardized_country_name + type: STRING + description: Standardized Country Name - The country from which events were reported, based on IP address +- mode: NULLABLE + name: source + type: STRING + description: Source - Referring partner domain +- mode: NULLABLE + name: medium + type: STRING + description: Medium - Category of the source, such as 'organic' for a search engine +- mode: NULLABLE + name: campaign + type: STRING + description: Campaign - Identifier for the marketing campaign +- mode: NULLABLE + name: content + type: STRING + description: Content - Indicates the particular link within a campaign +- mode: NULLABLE + name: blog + type: STRING + description: Blog +- mode: NULLABLE + name: subblog + type: STRING + description: Subblog +- mode: NULLABLE + name: landing_page + type: STRING + description: Landing Page +- mode: NULLABLE + name: cleaned_landing_page + type: STRING + description: Cleaned Landing Page - The URL portion prior to any question mark symbol +- mode: NULLABLE + name: sessions + type: INT64 + description: Sessions - The number of sessions with an entrances value of 1 +- mode: NULLABLE + name: downloads + type: INT64 + description: Downloads - The number of download click events +- mode: NULLABLE + name: social_share + type: INT64 + description: Social Shares - The number of social share events +- mode: NULLABLE + name: newsletter_subscription + type: INT64 + description: Newsletter Subscription - The number of newsletter subscribe events ```

Link to full diff

dataops-ci-bot commented 2 months ago

Integration report for "Merge branch 'main' into deng_4198_move_blogs_landing_page_summary_v2_from_marketing_prod_to_shared_prod"

`sql.diff`

Click to expand!

```diff diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/dags/bqetl_google_analytics_derived_ga4.py /tmp/workspace/generated-sql/dags/bqetl_google_analytics_derived_ga4.py --- /tmp/workspace/main-generated-sql/dags/bqetl_google_analytics_derived_ga4.py 2024-08-08 23:02:49.000000000 +0000 +++ /tmp/workspace/generated-sql/dags/bqetl_google_analytics_derived_ga4.py 2024-08-08 23:04:31.000000000 +0000 @@ -397,6 +397,21 @@ depends_on_past=False, ) + mozilla_org_derived__blogs_landing_page_summary__v2 = bigquery_etl_query( + task_id="mozilla_org_derived__blogs_landing_page_summary__v2", + destination_table="blogs_landing_page_summary_v2", + dataset_id="mozilla_org_derived", + project_id="moz-fx-data-shared-prod", + owner="mhirose@mozilla.com", + email=[ + "kwindau@mozilla.com", + "mhirose@mozilla.com", + "telemetry-alerts@mozilla.com", + ], + date_partition_parameter="submission_date", + depends_on_past=False, + ) + mozilla_org_derived__blogs_sessions__v2 = bigquery_etl_query( task_id="mozilla_org_derived__blogs_sessions__v2", destination_table="blogs_sessions_v2", @@ -577,6 +592,18 @@ mozilla_org_derived__blogs_goals__v2.set_upstream(wait_for_blogs_events_table) + mozilla_org_derived__blogs_landing_page_summary__v2.set_upstream( + mozilla_org_derived__blogs_goals__v2 + ) + + mozilla_org_derived__blogs_landing_page_summary__v2.set_upstream( + mozilla_org_derived__blogs_sessions__v2 + ) + + mozilla_org_derived__blogs_landing_page_summary__v2.set_upstream( + wait_for_blogs_events_table + ) + mozilla_org_derived__blogs_sessions__v2.set_upstream(wait_for_blogs_events_table) mozilla_org_derived__ga_clients__v2.set_upstream( Only in /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/mozilla_org_derived: blogs_landing_page_summary_v2 diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/mozilla_org_derived/blogs_landing_page_summary_v2/metadata.yaml /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/mozilla_org_derived/blogs_landing_page_summary_v2/metadata.yaml --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/mozilla_org_derived/blogs_landing_page_summary_v2/metadata.yaml 1970-01-01 00:00:00.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/mozilla_org_derived/blogs_landing_page_summary_v2/metadata.yaml 2024-08-08 23:00:36.000000000 +0000 @@ -0,0 +1,32 @@ +friendly_name: Blogs Landing Page Summary V2 +description: |- + Daily summary Google analytics data for blog.mozilla.org landing page +owners: +- mhirose@mozilla.com +labels: + incremental: true + owner1: mhirose + dag: bqetl_google_analytics_derived_ga4 +scheduling: + dag_name: bqetl_google_analytics_derived_ga4 + depends_on: + - task_id: wait_for_blogs_events_table + dag_name: bqetl_google_analytics_derived_ga4 +bigquery: + time_partitioning: + type: day + field: date + require_partition_filter: true + expiration_days: null + range_partitioning: null + clustering: + fields: + - cleaned_landing_page + - browser + - blog + - subblog +workgroup_access: +- role: roles/bigquery.dataViewer + members: + - workgroup:mozilla-confidential +references: {} diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/mozilla_org_derived/blogs_landing_page_summary_v2/query.sql /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/mozilla_org_derived/blogs_landing_page_summary_v2/query.sql --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/mozilla_org_derived/blogs_landing_page_summary_v2/query.sql 1970-01-01 00:00:00.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/mozilla_org_derived/blogs_landing_page_summary_v2/query.sql 2024-08-08 22:58:11.000000000 +0000 @@ -0,0 +1,108 @@ +WITH landing_page_staging AS ( + SELECT + PARSE_DATE('%Y%m%d', event_date) AS `date`, + user_pseudo_id || '-' || CAST( + ( + SELECT + `value` + FROM + UNNEST(event_params) + WHERE + key = 'ga_session_id' + LIMIT + 1 + ).int_value AS STRING + ) AS visit_identifier, + ( + SELECT + `value` + FROM + UNNEST(event_params) + WHERE + key = 'page_location' + LIMIT + 1 + ).string_value AS landing_page, + ( + SELECT + `value` + FROM + UNNEST(event_params) + WHERE + key = 'entrances' + LIMIT + 1 + ).int_value AS is_entrance + FROM + `moz-fx-data-marketing-prod.analytics_314399816.events_*` + WHERE + _TABLE_SUFFIX = FORMAT_DATE('%Y%m%d', @submission_date) + QUALIFY + ROW_NUMBER() OVER (PARTITION BY visit_identifier ORDER BY event_timestamp ASC) = 1 +), +landing_page_stg AS ( + SELECT + `date`, + visit_identifier, + landing_page, + SPLIT(landing_page, '?')[OFFSET(0)] AS cleaned_landing_page, + SUM(is_entrance) AS page_sessions + FROM + landing_page_staging + GROUP BY + `date`, + visit_identifier, + landing_page, + cleaned_landing_page + HAVING + page_sessions > 0 +) +SELECT + sessions_table.`date`, + sessions_table.device_category, + sessions_table.operating_system, + sessions_table.browser, + sessions_table.`language`, + sessions_table.country, + standardized_country_list.standardized_country AS standardized_country_name, + sessions_table.source, + sessions_table.medium, + sessions_table.campaign, + sessions_table.content, + sessions_table.blog, + sessions_table.subblog, + lps.landing_page, + lps.cleaned_landing_page, + SUM(sessions_table.sessions) AS sessions, + SUM(goals_table.downloads) AS downloads, + SUM(goals_table.social_share) AS social_share, + SUM(goals_table.newsletter_subscription) AS newsletter_subscription, +FROM + `moz-fx-data-shared-prod.mozilla_org_derived.blogs_sessions_v2` AS sessions_table +LEFT JOIN + `moz-fx-data-shared-prod.mozilla_org_derived.blogs_goals_v2` AS goals_table + USING (`date`, visit_identifier) +LEFT JOIN + landing_page_stg AS lps + USING (`date`, visit_identifier) +LEFT JOIN + `moz-fx-data-shared-prod.static.third_party_standardized_country_names` AS standardized_country_list + ON sessions_table.country = standardized_country_list.raw_country +WHERE + sessions_table.date = @submission_date +GROUP BY + sessions_table.`date`, + sessions_table.device_category, + sessions_table.operating_system, + sessions_table.browser, + sessions_table.`language`, + sessions_table.country, + standardized_country_list.standardized_country, + sessions_table.source, + sessions_table.medium, + sessions_table.campaign, + sessions_table.content, + sessions_table.blog, + sessions_table.subblog, + lps.landing_page, + lps.cleaned_landing_page diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/mozilla_org_derived/blogs_landing_page_summary_v2/schema.yaml /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/mozilla_org_derived/blogs_landing_page_summary_v2/schema.yaml --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/mozilla_org_derived/blogs_landing_page_summary_v2/schema.yaml 1970-01-01 00:00:00.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/mozilla_org_derived/blogs_landing_page_summary_v2/schema.yaml 2024-08-08 22:58:11.000000000 +0000 @@ -0,0 +1,77 @@ +fields: +- mode: NULLABLE + name: date + type: DATE + description: Date of the entrance to the visit +- mode: NULLABLE + name: device_category + type: STRING + description: Device Category - The device category the visitor used to visit the site +- mode: NULLABLE + name: operating_system + type: STRING + description: Operating System - The operating system the visitor used to visit the site +- mode: NULLABLE + name: browser + type: STRING + description: Browser - The browser the visitor used to visit the site +- mode: NULLABLE + name: language + type: STRING + description: Language - Language of visitor's device +- mode: NULLABLE + name: country + type: STRING + description: Country - The country from which events were reported, based on IP address +- mode: NULLABLE + name: standardized_country_name + type: STRING + description: Standardized Country Name - The country from which events were reported, based on IP address +- mode: NULLABLE + name: source + type: STRING + description: Source - Referring partner domain +- mode: NULLABLE + name: medium + type: STRING + description: Medium - Category of the source, such as 'organic' for a search engine +- mode: NULLABLE + name: campaign + type: STRING + description: Campaign - Identifier for the marketing campaign +- mode: NULLABLE + name: content + type: STRING + description: Content - Indicates the particular link within a campaign +- mode: NULLABLE + name: blog + type: STRING + description: Blog +- mode: NULLABLE + name: subblog + type: STRING + description: Subblog +- mode: NULLABLE + name: landing_page + type: STRING + description: Landing Page +- mode: NULLABLE + name: cleaned_landing_page + type: STRING + description: Cleaned Landing Page - The URL portion prior to any question mark symbol +- mode: NULLABLE + name: sessions + type: INT64 + description: Sessions - The number of sessions with an entrances value of 1 +- mode: NULLABLE + name: downloads + type: INT64 + description: Downloads - The number of download click events +- mode: NULLABLE + name: social_share + type: INT64 + description: Social Shares - The number of social share events +- mode: NULLABLE + name: newsletter_subscription + type: INT64 + description: Newsletter Subscription - The number of newsletter subscribe events ```

Link to full diff

dataops-ci-bot commented 2 months ago

Integration report for "Merge branch 'main' into deng_4198_move_blogs_landing_page_summary_v2_from_marketing_prod_to_shared_prod"

`sql.diff`

Click to expand!

```diff diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/dags/bqetl_google_analytics_derived_ga4.py /tmp/workspace/generated-sql/dags/bqetl_google_analytics_derived_ga4.py --- /tmp/workspace/main-generated-sql/dags/bqetl_google_analytics_derived_ga4.py 2024-08-08 23:51:49.000000000 +0000 +++ /tmp/workspace/generated-sql/dags/bqetl_google_analytics_derived_ga4.py 2024-08-08 23:52:40.000000000 +0000 @@ -397,6 +397,21 @@ depends_on_past=False, ) + mozilla_org_derived__blogs_landing_page_summary__v2 = bigquery_etl_query( + task_id="mozilla_org_derived__blogs_landing_page_summary__v2", + destination_table="blogs_landing_page_summary_v2", + dataset_id="mozilla_org_derived", + project_id="moz-fx-data-shared-prod", + owner="mhirose@mozilla.com", + email=[ + "kwindau@mozilla.com", + "mhirose@mozilla.com", + "telemetry-alerts@mozilla.com", + ], + date_partition_parameter="submission_date", + depends_on_past=False, + ) + mozilla_org_derived__blogs_sessions__v2 = bigquery_etl_query( task_id="mozilla_org_derived__blogs_sessions__v2", destination_table="blogs_sessions_v2", @@ -577,6 +592,18 @@ mozilla_org_derived__blogs_goals__v2.set_upstream(wait_for_blogs_events_table) + mozilla_org_derived__blogs_landing_page_summary__v2.set_upstream( + mozilla_org_derived__blogs_goals__v2 + ) + + mozilla_org_derived__blogs_landing_page_summary__v2.set_upstream( + mozilla_org_derived__blogs_sessions__v2 + ) + + mozilla_org_derived__blogs_landing_page_summary__v2.set_upstream( + wait_for_blogs_events_table + ) + mozilla_org_derived__blogs_sessions__v2.set_upstream(wait_for_blogs_events_table) mozilla_org_derived__ga_clients__v2.set_upstream( 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/ads/ppa_measurements_limited: 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/mozilla_org_derived: blogs_landing_page_summary_v2 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-08-08 23:47:55.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/ads/ppa_measurements_limited/schema.yaml /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/ads/ppa_measurements_limited/schema.yaml --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/ads/ppa_measurements_limited/schema.yaml 2024-08-08 23:47:55.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/ads/ppa_measurements_limited/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-08-08 23:47:55.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/app_store/firefox_app_store_territory_source_type_report/schema.yaml 2024-08-08 23:44:06.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-08-08 23:47:55.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/app_store/firefox_app_store_territory_web_referrer_report/schema.yaml 2024-08-08 23:44:02.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-08-08 23:47:55.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/app_store/firefox_downloads_territory_source_type_report/schema.yaml 2024-08-08 23:44:04.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-08-08 23:47:55.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/app_store/firefox_downloads_territory_web_referrer_report/schema.yaml 2024-08-08 23:44:05.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-08-08 23:47:55.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/app_store/firefox_usage_territory_source_type_report/schema.yaml 2024-08-08 23:44:07.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-08-08 23:47:55.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/app_store/firefox_usage_territory_web_referrer_report/schema.yaml 2024-08-08 23:44:03.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-08-08 23:47:55.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/contextual_services/adm_forecasting/schema.yaml 2024-08-08 23:44:04.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-08-08 23:47:55.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/contextual_services/event_aggregates/schema.yaml 2024-08-08 23:43:56.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-08-08 23:47:55.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/contextual_services/event_aggregates_spons_tiles/schema.yaml 2024-08-08 23:43:59.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-08-08 23:47:55.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/contextual_services/event_aggregates_suggest/schema.yaml 2024-08-08 23:44:02.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-08-08 23:47:55.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/contextual_services/quicksuggest_click_live/schema.yaml 2024-08-08 23:43:57.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-08-08 23:47:55.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/contextual_services/quicksuggest_impression_live/schema.yaml 2024-08-08 23:44:03.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-08-08 23:47:55.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/contextual_services/request_payload_suggest/schema.yaml 2024-08-08 23:44:06.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-08-08 23:47:55.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/contextual_services/request_payload_tiles/schema.yaml 2024-08-08 23:43:57.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-08-08 23:47:55.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/contextual_services/suggest_revenue_levers_daily/schema.yaml 2024-08-08 23:44:00.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-08-08 23:47:55.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/contextual_services/topsites_click_live/schema.yaml 2024-08-08 23:44:07.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-08-08 23:47:55.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/contextual_services/topsites_impression_live/schema.yaml 2024-08-08 23:44:00.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-08-08 23:47:55.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/fenix/client_deduplication/schema.yaml 2024-08-08 23:43: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/focus_android/use_counters/schema.yaml /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/focus_android/use_counters/schema.yaml --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/focus_android/use_counters/schema.yaml 2024-08-08 23:47:55.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/focus_android/use_counters/schema.yaml 2024-08-08 23:46:55.000000000 +0000 @@ -1,11 +1,11 @@ fields: - name: normalized_app_id - type: STRING mode: NULLABLE + type: STRING description: App ID of the channel data was received from - name: normalized_channel - type: STRING mode: NULLABLE + type: STRING description: Normalized channel name - name: additional_properties type: STRING diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/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-08-08 23:47:55.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/klar_android/use_counters/schema.yaml 2024-08-08 23:46:52.000000000 +0000 @@ -1,11 +1,11 @@ fields: - name: normalized_app_id - type: STRING mode: NULLABLE + type: STRING description: App ID of the channel data was received from - name: normalized_channel - type: STRING mode: NULLABLE + type: STRING description: Normalized channel name - name: additional_properties type: STRING diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/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-08-08 23:47:54.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/table_partition_expirations_v1/query.sql /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/monitoring_derived/table_partition_expirations_v1/query.sql --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/monitoring_derived/table_partition_expirations_v1/query.sql 2024-08-08 23:47:54.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/monitoring_derived/table_partition_expirations_v1/query.sql 2024-08-08 23:43:49.000000000 +0000 @@ -1,4 +1,4 @@ -WITH first_partition_org_mozilla_tv_firefox_stable AS ( +WITH first_partition_firefox_desktop_background_tasks_stable AS ( SELECT table_catalog, table_schema, @@ -6,25 +6,25 @@ PARSE_DATE("%Y%m%d", partition_id) AS first_partition_current, total_rows AS first_partition_row_count, FROM - `moz-fx-data-shared-prod.org_mozilla_tv_firefox_stable.INFORMATION_SCHEMA.PARTITIONS` + `moz-fx-data-shared-prod.firefox_desktop_background_tasks_stable.INFORMATION_SCHEMA.PARTITIONS` WHERE partition_id != '__NULL__' QUALIFY ROW_NUMBER() OVER (PARTITION BY table_name ORDER BY partition_id) = 1 ), -first_non_empty_partition_org_mozilla_tv_firefox_stable AS ( +first_non_empty_partition_firefox_desktop_background_tasks_stable AS ( SELECT table_name, PARSE_DATE("%Y%m%d", MIN(partition_id)) AS first_non_empty_partition_current, FROM - `moz-fx-data-shared-prod.org_mozilla_tv_firefox_stable.INFORMATION_SCHEMA.PARTITIONS` + `moz-fx-data-shared-prod.firefox_desktop_background_tasks_stable.INFORMATION_SCHEMA.PARTITIONS` WHERE partition_id != '__NULL__' AND total_rows > 0 GROUP BY table_name ), -first_partition_pocket_stable AS ( +first_partition_mlhackweek_search_stable AS ( SELECT table_catalog, table_schema, @@ -32,25 +32,25 @@ PARSE_DATE("%Y%m%d", partition_id) AS first_partition_current, total_rows AS first_partition_row_count, FROM - `moz-fx-data-shared-prod.pocket_stable.INFORMATION_SCHEMA.PARTITIONS` + `moz-fx-data-shared-prod.mlhackweek_search_stable.INFORMATION_SCHEMA.PARTITIONS` WHERE partition_id != '__NULL__' QUALIFY ROW_NUMBER() OVER (PARTITION BY table_name ORDER BY partition_id) = 1 ), -first_non_empty_partition_pocket_stable AS ( +first_non_empty_partition_mlhackweek_search_stable AS ( SELECT table_name, PARSE_DATE("%Y%m%d", MIN(partition_id)) AS first_non_empty_partition_current, FROM - `moz-fx-data-shared-prod.pocket_stable.INFORMATION_SCHEMA.PARTITIONS` + `moz-fx-data-shared-prod.mlhackweek_search_stable.INFORMATION_SCHEMA.PARTITIONS` WHERE partition_id != '__NULL__' AND total_rows > 0 GROUP BY table_name ), -first_partition_org_mozilla_firefox_vpn_stable AS ( +first_partition_org_mozilla_focus_nightly_stable AS ( SELECT table_catalog, table_schema, @@ -58,25 +58,25 @@ PARSE_DATE("%Y%m%d", partition_id) AS first_partition_current, total_rows AS first_partition_row_count, FROM - `moz-fx-data-shared-prod.org_mozilla_firefox_vpn_stable.INFORMATION_SCHEMA.PARTITIONS` + `moz-fx-data-shared-prod.org_mozilla_focus_nightly_stable.INFORMATION_SCHEMA.PARTITIONS` WHERE partition_id != '__NULL__' QUALIFY ROW_NUMBER() OVER (PARTITION BY table_name ORDER BY partition_id) = 1 ), -first_non_empty_partition_org_mozilla_firefox_vpn_stable AS ( +first_non_empty_partition_org_mozilla_focus_nightly_stable AS ( SELECT table_name, PARSE_DATE("%Y%m%d", MIN(partition_id)) AS first_non_empty_partition_current, FROM - `moz-fx-data-shared-prod.org_mozilla_firefox_vpn_stable.INFORMATION_SCHEMA.PARTITIONS` + `moz-fx-data-shared-prod.org_mozilla_focus_nightly_stable.INFORMATION_SCHEMA.PARTITIONS` WHERE partition_id != '__NULL__' AND total_rows > 0 GROUP BY table_name ), -first_partition_org_mozilla_firefox_stable AS ( +first_partition_mozphab_stable AS ( SELECT table_catalog, table_schema, @@ -84,25 +84,25 @@ PARSE_DATE("%Y%m%d", partition_id) AS first_partition_current, total_rows AS first_partition_row_count, FROM - `moz-fx-data-shared-prod.org_mozilla_firefox_stable.INFORMATION_SCHEMA.PARTITIONS` + `moz-fx-data-shared-prod.mozphab_stable.INFORMATION_SCHEMA.PARTITIONS` WHERE partition_id != '__NULL__' QUALIFY ROW_NUMBER() OVER (PARTITION BY table_name ORDER BY partition_id) = 1 ), -first_non_empty_partition_org_mozilla_firefox_stable AS ( +first_non_empty_partition_mozphab_stable AS ( SELECT table_name, PARSE_DATE("%Y%m%d", MIN(partition_id)) AS first_non_empty_partition_current, FROM - `moz-fx-data-shared-prod.org_mozilla_firefox_stable.INFORMATION_SCHEMA.PARTITIONS` + `moz-fx-data-shared-prod.mozphab_stable.INFORMATION_SCHEMA.PARTITIONS` WHERE partition_id != '__NULL__' AND total_rows > 0 GROUP BY table_name ), -first_partition_org_mozilla_firefox_beta_stable AS ( +first_partition_webpagetest_stable AS ( SELECT table_catalog, table_schema, @@ -110,25 +110,25 @@ PARSE_DATE("%Y%m%d", partition_id) AS first_partition_current, total_rows AS first_partition_row_count, FROM - `moz-fx-data-shared-prod.org_mozilla_firefox_beta_stable.INFORMATION_SCHEMA.PARTITIONS` + `moz-fx-data-shared-prod.webpagetest_stable.INFORMATION_SCHEMA.PARTITIONS` WHERE partition_id != '__NULL__' QUALIFY ROW_NUMBER() OVER (PARTITION BY table_name ORDER BY partition_id) = 1 ), -first_non_empty_partition_org_mozilla_firefox_beta_stable AS ( +first_non_empty_partition_webpagetest_stable AS ( SELECT table_name, PARSE_DATE("%Y%m%d", MIN(partition_id)) AS first_non_empty_partition_current, FROM - `moz-fx-data-shared-prod.org_mozilla_firefox_beta_stable.INFORMATION_SCHEMA.PARTITIONS` + `moz-fx-data-shared-prod.webpagetest_stable.INFORMATION_SCHEMA.PARTITIONS` WHERE partition_id != '__NULL__' AND total_rows > 0 GROUP BY table_name ), -first_partition_org_mozilla_klar_stable AS ( +first_partition_org_mozilla_ios_firefoxvpn_network_extension_stable AS ( SELECT table_catalog, table_schema, @@ -136,25 +136,25 @@ PARSE_DATE("%Y%m%d", partition_id) AS first_partition_current, total_rows AS first_partition_row_count, FROM - `moz-fx-data-shared-prod.org_mozilla_klar_stable.INFORMATION_SCHEMA.PARTITIONS` + `moz-fx-data-shared-prod.org_mozilla_ios_firefoxvpn_network_extension_stable.INFORMATION_SCHEMA.PARTITIONS` WHERE partition_id != '__NULL__' QUALIFY ROW_NUMBER() OVER (PARTITION BY table_name ORDER BY partition_id) = 1 ), -first_non_empty_partition_org_mozilla_klar_stable AS ( +first_non_empty_partition_org_mozilla_ios_firefoxvpn_network_extension_stable AS ( SELECT table_name, PARSE_DATE("%Y%m%d", MIN(partition_id)) AS first_non_empty_partition_current, FROM - `moz-fx-data-shared-prod.org_mozilla_klar_stable.INFORMATION_SCHEMA.PARTITIONS` + `moz-fx-data-shared-prod.org_mozilla_ios_firefoxvpn_network_extension_stable.INFORMATION_SCHEMA.PARTITIONS` WHERE partition_id != '__NULL__' AND total_rows > 0 GROUP BY table_name ), -first_partition_relay_backend_stable AS ( +first_partition_telemetry_stable AS ( SELECT table_catalog, table_schema, @@ -162,25 +162,25 @@ PARSE_DATE("%Y%m%d", partition_id) AS first_partition_current, total_rows AS first_partition_row_count, FROM - `moz-fx-data-shared-prod.relay_backend_stable.INFORMATION_SCHEMA.PARTITIONS` + `moz-fx-data-shared-prod.telemetry_stable.INFORMATION_SCHEMA.PARTITIONS` WHERE partition_id != '__NULL__' QUALIFY ROW_NUMBER() OVER (PARTITION BY table_name ORDER BY partition_id) = 1 ), -first_non_empty_partition_relay_backend_stable AS ( +first_non_empty_partition_telemetry_stable AS ( SELECT table_name, PARSE_DATE("%Y%m%d", MIN(partition_id)) AS first_non_empty_partition_current, FROM - `moz-fx-data-shared-prod.relay_backend_stable.INFORMATION_SCHEMA.PARTITIONS` + `moz-fx-data-shared-prod.telemetry_stable.INFORMATION_SCHEMA.PARTITIONS` WHERE partition_id != '__NULL__' AND total_rows > 0 GROUP BY table_name ), -first_partition_firefox_desktop_background_defaultagent_stable AS ( +first_partition_ads_backend_stable AS ( SELECT table_catalog, table_schema, @@ -188,25 +188,25 @@ PARSE_DATE("%Y%m%d", partition_id) AS first_partition_current, total_rows AS first_partition_row_count, FROM - `moz-fx-data-shared-prod.firefox_desktop_background_defaultagent_stable.INFORMATION_SCHEMA.PARTITIONS` + `moz-fx-data-shared-prod.ads_backend_stable.INFORMATION_SCHEMA.PARTITIONS` WHERE partition_id != '__NULL__' QUALIFY ROW_NUMBER() OVER (PARTITION BY table_name ORDER BY partition_id) = 1 ), -first_non_empty_partition_firefox_desktop_background_defaultagent_stable AS ( +first_non_empty_partition_ads_backend_stable AS ( SELECT table_name, PARSE_DATE("%Y%m%d", MIN(partition_id)) AS first_non_empty_partition_current, FROM - `moz-fx-data-shared-prod.firefox_desktop_background_defaultagent_stable.INFORMATION_SCHEMA.PARTITIONS` + `moz-fx-data-shared-prod.ads_backend_stable.INFORMATION_SCHEMA.PARTITIONS` WHERE partition_id != '__NULL__' AND total_rows > 0 GROUP BY table_name ), -first_partition_webpagetest_stable AS ( +first_partition_org_mozilla_focus_stable AS ( SELECT table_catalog, table_schema, @@ -214,25 +214,25 @@ PARSE_DATE("%Y%m%d", partition_id) AS first_partition_current, total_rows AS first_partition_row_count, FROM - `moz-fx-data-shared-prod.webpagetest_stable.INFORMATION_SCHEMA.PARTITIONS` + `moz-fx-data-shared-prod.org_mozilla_focus_stable.INFORMATION_SCHEMA.PARTITIONS` WHERE partition_id != '__NULL__' QUALIFY ROW_NUMBER() OVER (PARTITION BY table_name ORDER BY partition_id) = 1 ), -first_non_empty_partition_webpagetest_stable AS ( +first_non_empty_partition_org_mozilla_focus_stable AS ( SELECT table_name, PARSE_DATE("%Y%m%d", MIN(partition_id)) AS first_non_empty_partition_current, FROM - `moz-fx-data-shared-prod.webpagetest_stable.INFORMATION_SCHEMA.PARTITIONS` + `moz-fx-data-shared-prod.org_mozilla_focus_stable.INFORMATION_SCHEMA.PARTITIONS` WHERE partition_id != '__NULL__' AND total_rows > 0 GROUP BY table_name ), -first_partition_coverage_stable AS ( +first_partition_org_mozilla_mozregression_stable AS ( SELECT table_catalog, table_schema, @@ -240,25 +240,25 @@ PARSE_DATE("%Y%m%d", partition_id) AS first_partition_current, total_rows AS first_partition_row_count, FROM - `moz-fx-data-shared-prod.coverage_stable.INFORMATION_SCHEMA.PARTITIONS` + `moz-fx-data-shared-prod.org_mozilla_mozregression_stable.INFORMATION_SCHEMA.PARTITIONS` WHERE partition_id != '__NULL__' QUALIFY ROW_NUMBER() OVER (PARTITION BY table_name ORDER BY partition_id) = 1 ), -first_non_empty_partition_coverage_stable AS ( +first_non_empty_partition_org_mozilla_mozregression_stable AS ( SELECT table_name, PARSE_DATE("%Y%m%d", MIN(partition_id)) AS first_non_empty_partition_current, FROM - `moz-fx-data-shared-prod.coverage_stable.INFORMATION_SCHEMA.PARTITIONS` + `moz-fx-data-shared-prod.org_mozilla_mozregression_stable.INFORMATION_SCHEMA.PARTITIONS` WHERE partition_id != '__NULL__' AND total_rows > 0 GROUP BY table_name ), -first_partition_moso_mastodon_backend_stable AS ( +first_partition_org_mozilla_ios_klar_stable AS ( SELECT table_catalog, table_schema, @@ -266,25 +266,25 @@ PARSE_DATE("%Y%m%d", partition_id) AS first_partition_current, total_rows AS first_partition_row_count, FROM - `moz-fx-data-shared-prod.moso_mastodon_backend_stable.INFORMATION_SCHEMA.PARTITIONS` + `moz-fx-data-shared-prod.org_mozilla_ios_klar_stable.INFORMATION_SCHEMA.PARTITIONS` WHERE partition_id != '__NULL__' QUALIFY ROW_NUMBER() OVER (PARTITION BY table_name ORDER BY partition_id) = 1 ), -first_non_empty_partition_moso_mastodon_backend_stable AS ( +first_non_empty_partition_org_mozilla_ios_klar_stable AS ( SELECT table_name, PARSE_DATE("%Y%m%d", MIN(partition_id)) AS first_non_empty_partition_current, FROM - `moz-fx-data-shared-prod.moso_mastodon_backend_stable.INFORMATION_SCHEMA.PARTITIONS` + `moz-fx-data-shared-prod.org_mozilla_ios_klar_stable.INFORMATION_SCHEMA.PARTITIONS` WHERE partition_id != '__NULL__' AND total_rows > 0 GROUP BY table_name ), -first_partition_org_mozilla_focus_stable AS ( +first_partition_org_mozilla_firefox_vpn_stable AS ( SELECT table_catalog, table_schema, @@ -292,25 +292,25 @@ PARSE_DATE("%Y%m%d", partition_id) AS first_partition_current, total_rows AS first_partition_row_count, FROM - `moz-fx-data-shared-prod.org_mozilla_focus_stable.INFORMATION_SCHEMA.PARTITIONS` + `moz-fx-data-shared-prod.org_mozilla_firefox_vpn_stable.INFORMATION_SCHEMA.PARTITIONS` WHERE partition_id != '__NULL__' QUALIFY ROW_NUMBER() OVER (PARTITION BY table_name ORDER BY partition_id) = 1 ), -first_non_empty_partition_org_mozilla_focus_stable AS ( +first_non_empty_partition_org_mozilla_firefox_vpn_stable AS ( SELECT table_name, PARSE_DATE("%Y%m%d", MIN(partition_id)) AS first_non_empty_partition_current, FROM - `moz-fx-data-shared-prod.org_mozilla_focus_stable.INFORMATION_SCHEMA.PARTITIONS` + `moz-fx-data-shared-prod.org_mozilla_firefox_vpn_stable.INFORMATION_SCHEMA.PARTITIONS` WHERE partition_id != '__NULL__' AND total_rows > 0 GROUP BY table_name ), -first_partition_mozphab_stable AS ( +first_partition_org_mozilla_bergamot_stable AS ( SELECT table_catalog, table_schema, @@ -318,18 +318,18 @@ PARSE_DATE("%Y%m%d", partition_id) AS first_partition_current, total_rows AS first_partition_row_count, FROM - `moz-fx-data-shared-prod.mozphab_stable.INFORMATION_SCHEMA.PARTITIONS` + `moz-fx-data-shared-prod.org_mozilla_bergamot_stable.INFORMATION_SCHEMA.PARTITIONS` WHERE partition_id != '__NULL__' QUALIFY ROW_NUMBER() OVER (PARTITION BY table_name ORDER BY partition_id) = 1 ), -first_non_empty_partition_mozphab_stable AS ( +first_non_empty_partition_org_mozilla_bergamot_stable AS ( SELECT table_name, PARSE_DATE("%Y%m%d", MIN(partition_id)) AS first_non_empty_partition_current, FROM - `moz-fx-data-shared-prod.mozphab_stable.INFORMATION_SCHEMA.PARTITIONS` + `moz-fx-data-shared-prod.org_mozilla_bergamot_stable.INFORMATION_SCHEMA.PARTITIONS` WHERE partition_id != '__NULL__' AND total_rows > 0 @@ -362,7 +362,7 @@ GROUP BY table_name ), -first_partition_org_mozilla_ios_firefoxvpn_stable AS ( +first_partition_burnham_stable AS ( SELECT table_catalog, table_schema, @@ -370,25 +370,25 @@ PARSE_DATE("%Y%m%d", partition_id) AS first_partition_current, total_rows AS first_partition_row_count, FROM - `moz-fx-data-shared-prod.org_mozilla_ios_firefoxvpn_stable.INFORMATION_SCHEMA.PARTITIONS` + `moz-fx-data-shared-prod.burnham_stable.INFORMATION_SCHEMA.PARTITIONS` WHERE partition_id != '__NULL__' QUALIFY ROW_NUMBER() OVER (PARTITION BY table_name ORDER BY partition_id) = 1 ), -first_non_empty_partition_org_mozilla_ios_firefoxvpn_stable AS ( +first_non_empty_partition_burnham_stable AS ( SELECT table_name, PARSE_DATE("%Y%m%d", MIN(partition_id)) AS first_non_empty_partition_current, FROM - `moz-fx-data-shared-prod.org_mozilla_ios_firefoxvpn_stable.INFORMATION_SCHEMA.PARTITIONS` + `moz-fx-data-shared-prod.burnham_stable.INFORMATION_SCHEMA.PARTITIONS` WHERE partition_id != '__NULL__' AND total_rows > 0 GROUP BY table_name ), -first_partition_org_mozilla_fennec_aurora_stable AS ( +first_partition_org_mozilla_ios_tiktok_reporter_stable AS ( SELECT table_catalog, table_schema, @@ -396,25 +396,25 @@ PARSE_DATE("%Y%m%d", partition_id) AS first_partition_current, total_rows AS first_partition_row_count, FROM - `moz-fx-data-shared-prod.org_mozilla_fennec_aurora_stable.INFORMATION_SCHEMA.PARTITIONS` + `moz-fx-data-shared-prod.org_mozilla_ios_tiktok_reporter_stable.INFORMATION_SCHEMA.PARTITIONS` WHERE partition_id != '__NULL__' QUALIFY ROW_NUMBER() OVER (PARTITION BY table_name ORDER BY partition_id) = 1 ), -first_non_empty_partition_org_mozilla_fennec_aurora_stable AS ( +first_non_empty_partition_org_mozilla_ios_tiktok_reporter_stable AS ( SELECT table_name, PARSE_DATE("%Y%m%d", MIN(partition_id)) AS first_non_empty_partition_current, FROM - `moz-fx-data-shared-prod.org_mozilla_fennec_aurora_stable.INFORMATION_SCHEMA.PARTITIONS` + `moz-fx-data-shared-prod.o ```

⚠️ Only part of the diff is displayed.

Link to full diff

dataops-ci-bot commented 1 month ago

Integration report for "Merge branch 'main' into deng_4198_move_blogs_landing_page_summary_v2_from_marketing_prod_to_shared_prod"

`sql.diff`

Click to expand!

```diff diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/dags/bqetl_google_analytics_derived_ga4.py /tmp/workspace/generated-sql/dags/bqetl_google_analytics_derived_ga4.py --- /tmp/workspace/main-generated-sql/dags/bqetl_google_analytics_derived_ga4.py 2024-08-09 16:22:42.000000000 +0000 +++ /tmp/workspace/generated-sql/dags/bqetl_google_analytics_derived_ga4.py 2024-08-09 16:23:13.000000000 +0000 @@ -397,6 +397,21 @@ depends_on_past=False, ) + mozilla_org_derived__blogs_landing_page_summary__v2 = bigquery_etl_query( + task_id="mozilla_org_derived__blogs_landing_page_summary__v2", + destination_table="blogs_landing_page_summary_v2", + dataset_id="mozilla_org_derived", + project_id="moz-fx-data-shared-prod", + owner="mhirose@mozilla.com", + email=[ + "kwindau@mozilla.com", + "mhirose@mozilla.com", + "telemetry-alerts@mozilla.com", + ], + date_partition_parameter="submission_date", + depends_on_past=False, + ) + mozilla_org_derived__blogs_sessions__v2 = bigquery_etl_query( task_id="mozilla_org_derived__blogs_sessions__v2", destination_table="blogs_sessions_v2", @@ -577,6 +592,18 @@ mozilla_org_derived__blogs_goals__v2.set_upstream(wait_for_blogs_events_table) + mozilla_org_derived__blogs_landing_page_summary__v2.set_upstream( + mozilla_org_derived__blogs_goals__v2 + ) + + mozilla_org_derived__blogs_landing_page_summary__v2.set_upstream( + mozilla_org_derived__blogs_sessions__v2 + ) + + mozilla_org_derived__blogs_landing_page_summary__v2.set_upstream( + wait_for_blogs_events_table + ) + mozilla_org_derived__blogs_sessions__v2.set_upstream(wait_for_blogs_events_table) mozilla_org_derived__ga_clients__v2.set_upstream( Only in /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/mozilla_org_derived: blogs_landing_page_summary_v2 diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/mozilla_org_derived/blogs_landing_page_summary_v2/metadata.yaml /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/mozilla_org_derived/blogs_landing_page_summary_v2/metadata.yaml --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/mozilla_org_derived/blogs_landing_page_summary_v2/metadata.yaml 1970-01-01 00:00:00.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/mozilla_org_derived/blogs_landing_page_summary_v2/metadata.yaml 2024-08-09 16:19:12.000000000 +0000 @@ -0,0 +1,32 @@ +friendly_name: Blogs Landing Page Summary V2 +description: |- + Daily summary Google analytics data for blog.mozilla.org landing page +owners: +- mhirose@mozilla.com +labels: + incremental: true + owner1: mhirose + dag: bqetl_google_analytics_derived_ga4 +scheduling: + dag_name: bqetl_google_analytics_derived_ga4 + depends_on: + - task_id: wait_for_blogs_events_table + dag_name: bqetl_google_analytics_derived_ga4 +bigquery: + time_partitioning: + type: day + field: date + require_partition_filter: true + expiration_days: null + range_partitioning: null + clustering: + fields: + - cleaned_landing_page + - browser + - blog + - subblog +workgroup_access: +- role: roles/bigquery.dataViewer + members: + - workgroup:mozilla-confidential +references: {} diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/mozilla_org_derived/blogs_landing_page_summary_v2/query.sql /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/mozilla_org_derived/blogs_landing_page_summary_v2/query.sql --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/mozilla_org_derived/blogs_landing_page_summary_v2/query.sql 1970-01-01 00:00:00.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/mozilla_org_derived/blogs_landing_page_summary_v2/query.sql 2024-08-09 16:17:10.000000000 +0000 @@ -0,0 +1,108 @@ +WITH landing_page_staging AS ( + SELECT + PARSE_DATE('%Y%m%d', event_date) AS `date`, + user_pseudo_id || '-' || CAST( + ( + SELECT + `value` + FROM + UNNEST(event_params) + WHERE + key = 'ga_session_id' + LIMIT + 1 + ).int_value AS STRING + ) AS visit_identifier, + ( + SELECT + `value` + FROM + UNNEST(event_params) + WHERE + key = 'page_location' + LIMIT + 1 + ).string_value AS landing_page, + ( + SELECT + `value` + FROM + UNNEST(event_params) + WHERE + key = 'entrances' + LIMIT + 1 + ).int_value AS is_entrance + FROM + `moz-fx-data-marketing-prod.analytics_314399816.events_*` + WHERE + _TABLE_SUFFIX = FORMAT_DATE('%Y%m%d', @submission_date) + QUALIFY + ROW_NUMBER() OVER (PARTITION BY visit_identifier ORDER BY event_timestamp ASC) = 1 +), +landing_page_stg AS ( + SELECT + `date`, + visit_identifier, + landing_page, + SPLIT(landing_page, '?')[OFFSET(0)] AS cleaned_landing_page, + SUM(is_entrance) AS page_sessions + FROM + landing_page_staging + GROUP BY + `date`, + visit_identifier, + landing_page, + cleaned_landing_page + HAVING + page_sessions > 0 +) +SELECT + sessions_table.`date`, + sessions_table.device_category, + sessions_table.operating_system, + sessions_table.browser, + sessions_table.`language`, + sessions_table.country, + standardized_country_list.standardized_country AS standardized_country_name, + sessions_table.source, + sessions_table.medium, + sessions_table.campaign, + sessions_table.content, + sessions_table.blog, + sessions_table.subblog, + lps.landing_page, + lps.cleaned_landing_page, + SUM(sessions_table.sessions) AS sessions, + SUM(goals_table.downloads) AS downloads, + SUM(goals_table.social_share) AS social_share, + SUM(goals_table.newsletter_subscription) AS newsletter_subscription, +FROM + `moz-fx-data-shared-prod.mozilla_org_derived.blogs_sessions_v2` AS sessions_table +LEFT JOIN + `moz-fx-data-shared-prod.mozilla_org_derived.blogs_goals_v2` AS goals_table + USING (`date`, visit_identifier) +LEFT JOIN + landing_page_stg AS lps + USING (`date`, visit_identifier) +LEFT JOIN + `moz-fx-data-shared-prod.static.third_party_standardized_country_names` AS standardized_country_list + ON sessions_table.country = standardized_country_list.raw_country +WHERE + sessions_table.date = @submission_date +GROUP BY + sessions_table.`date`, + sessions_table.device_category, + sessions_table.operating_system, + sessions_table.browser, + sessions_table.`language`, + sessions_table.country, + standardized_country_list.standardized_country, + sessions_table.source, + sessions_table.medium, + sessions_table.campaign, + sessions_table.content, + sessions_table.blog, + sessions_table.subblog, + lps.landing_page, + lps.cleaned_landing_page diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/mozilla_org_derived/blogs_landing_page_summary_v2/schema.yaml /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/mozilla_org_derived/blogs_landing_page_summary_v2/schema.yaml --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/mozilla_org_derived/blogs_landing_page_summary_v2/schema.yaml 1970-01-01 00:00:00.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/mozilla_org_derived/blogs_landing_page_summary_v2/schema.yaml 2024-08-09 16:17:10.000000000 +0000 @@ -0,0 +1,77 @@ +fields: +- mode: NULLABLE + name: date + type: DATE + description: Date of the entrance to the visit +- mode: NULLABLE + name: device_category + type: STRING + description: Device Category - The device category the visitor used to visit the site +- mode: NULLABLE + name: operating_system + type: STRING + description: Operating System - The operating system the visitor used to visit the site +- mode: NULLABLE + name: browser + type: STRING + description: Browser - The browser the visitor used to visit the site +- mode: NULLABLE + name: language + type: STRING + description: Language - Language of visitor's device +- mode: NULLABLE + name: country + type: STRING + description: Country - The country from which events were reported, based on IP address +- mode: NULLABLE + name: standardized_country_name + type: STRING + description: Standardized Country Name - The country from which events were reported, based on IP address +- mode: NULLABLE + name: source + type: STRING + description: Source - Referring partner domain +- mode: NULLABLE + name: medium + type: STRING + description: Medium - Category of the source, such as 'organic' for a search engine +- mode: NULLABLE + name: campaign + type: STRING + description: Campaign - Identifier for the marketing campaign +- mode: NULLABLE + name: content + type: STRING + description: Content - Indicates the particular link within a campaign +- mode: NULLABLE + name: blog + type: STRING + description: Blog +- mode: NULLABLE + name: subblog + type: STRING + description: Subblog +- mode: NULLABLE + name: landing_page + type: STRING + description: Landing Page +- mode: NULLABLE + name: cleaned_landing_page + type: STRING + description: Cleaned Landing Page - The URL portion prior to any question mark symbol +- mode: NULLABLE + name: sessions + type: INT64 + description: Sessions - The number of sessions with an entrances value of 1 +- mode: NULLABLE + name: downloads + type: INT64 + description: Downloads - The number of download click events +- mode: NULLABLE + name: social_share + type: INT64 + description: Social Shares - The number of social share events +- mode: NULLABLE + name: newsletter_subscription + type: INT64 + description: Newsletter Subscription - The number of newsletter subscribe events ```

Link to full diff

mozilla / bigquery-etl