1617630 - Backfill OOMAllocationSize and MozCrashReason in `telemetry.crash`

Status: RESOLVED FIXED

(Data Platform and Tools :: General, defect)

Description

[Chris [:wbeard] (EST)]

OOMAllocationSize and MozCrashReason are in telemetry.crash starting 2019-11-14, but only accessible from additional_properties before then. It would be useful to have these fields back to at least June, if not earlier than that for a project I'm working on.

According to bug #1595904 there are several other related missing fields as well that can be seen in this query.

Comment 1

[Jeff Klukas [:klukas] (UTC-4)]

This could likely be handled via query-based backfill. The query would look something like:

SELECT
  * REPLACE ( (
    SELECT
      AS STRUCT payload.* REPLACE ((
        SELECT
          AS STRUCT payload.metadata.* REPLACE (COALESCE(payload.metadata.oom_allocation_size,
              JSON_EXTRACT_SCALAR(additional_properties,
                '$.payload.metadata.OOMAllocationSize')) AS oom_allocation_size)) AS metadata)) AS payload)
FROM
  `moz-fx-data-shared-prod.telemetry_stable.crash_v4`
WHERE
  DATE(submission_timestamp) = @submission_date

And we'd likely want to process individual day partitions at a time. So the query reads a single partition and overwrites that partition as its output.

Comment 2

[William Lachance (:wlach)]

Backfilled to a temporary table in the backfill-1 project as described here: https://github.com/mozilla/bigquery-backfill/pull/5

@klukas -- can you copy these over to the production tables when you have a chance? Did some quick checking and the counts seem to sync up.

Comment 3

[Jeff Klukas [:klukas] (UTC-4)]

Copy is done. I left full details of the operations as a comment in the PR.

Comment 4

[William Lachance (:wlach)]

Excellent, cleaned this up and merged the PR:

https://github.com/mozilla/bigquery-backfill/tree/master/backfill/2020-02-24-crash-ping