1712332 - Create UNIONed views for Glean app-datasets across all channels

Status: RESOLVED FIXED

(Data Platform and Tools Graveyard :: Glean Platform, task, P3)

Description

[Frank Bertsch [:frank]]

In bug 1708166 (and friends), we created per-app datasets that contain derived datasets across all channels. We're looking to create ping-level views for just release there as well.

What we really want is to just be able to query the app-dataset; to do so, we need to configure the UNION views across all app channels. For example, we want the metrics ping for Fenix to be:

SELECT "release" AS channel, *
FROM mozdata.org_mozilla_firefox.metrics
UNION ALL
SELECT "nightly" AS channel, *
FROM mozdata.org_mozilla_fenix.metrics

The problem is the * select doesn't work, since different channels can have different dependencies. We need to use the superset of all fields from all tables, and order them appropriately for the UNION to work.

Comment 1

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

We need to use the superset of all fields from all tables, and order them appropriately for the UNION to work

Or, we use the superset of all fields, inserting NULL where a field is not defined for a given channel.

Comment 2

[William Lachance (:wlach)]

I have been working on adding app datasets to make Looker event explores work (https://github.com/mozilla/lookml-generator/issues/156). These app datasets currently only contain the release channel information, but making a note of this here for context.

Comment 3

[Anna Scholtz [:ascholtz]]

Attachment: GitHub Pull Request

Comment 4

[Anna Scholtz [:ascholtz]]

Views deployed successfully. All done