Skip to content

Instantly share code, notes, and snippets.

@tdsmith
Created January 30, 2021 02:43
Show Gist options
  • Save tdsmith/94302e1cfe6426a3a1447b367672daf5 to your computer and use it in GitHub Desktop.
Save tdsmith/94302e1cfe6426a3a1447b367672daf5 to your computer and use it in GitHub Desktop.
-- bq --format csv query -n 999999999 < query_event.sql > events_hdd.csv
DECLARE slugs ARRAY<STRUCT<slug STRING, name STRING>> DEFAULT [
("bug-1685741-pref-unenrollment-normandy-test-with-no-op-pref-in-fi-nightly-86-100", "string"),
("bug-1685740-pref-unenrollment-normandy-test-with-no-op-pref-in-fi-nightly-86-100", "boolean"),
("bug-1685742-pref-unenrollment-normandy-test-with-no-op-pref-in-fi-nightly-86-100", "integer")
];
DECLARE start_date DATE DEFAULT "2021-01-12";
WITH events AS (
SELECT
DATE(submission_timestamp) AS submission_date,
environment.system.hdd.profile.type AS hdd,
environment.system.cpu.count AS logical_cpu,
`moz-fx-data-shared-prod`.udf.deanonymize_event(e).*
FROM mozdata.telemetry.event
CROSS JOIN UNNEST(payload.events.parent) AS e
WHERE
DATE(submission_timestamp) >= start_date
AND normalized_os = "Windows"
)
, experiment_events AS (
SELECT
submission_date,
mozfun.map.get_key(event_map_values, "enrollmentId") AS enrollment_id,
s.name AS experiment,
event_method,
event_object,
mozfun.map.get_key(event_map_values, "branch") AS branch,
mozfun.map.get_key(event_map_values, "reason") AS reason,
hdd,
logical_cpu,
FROM events
CROSS JOIN UNNEST(slugs) s
WHERE
event_category = 'normandy'
AND event_string_value = s.slug
)
SELECT * FROM experiment_events
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment