search

mozilla / bigquery-etl

Bigquery ETL
https://mozilla.github.io/bigquery-etl
Mozilla Public License 2.0
258 stars 101 forks source link

Create more usable FxA content and auth server datasets, or fix current ones #135

Closed irrationalagent closed 5 years ago

irrationalagent commented 5 years ago

Pulling from the fxa content server logs, here's an example of a query that would give us a usable content server dataset for trailhead. there's a similar query for the auth server that I could make.

Basically, we need easily queryable datasets that contain non-null values from jsonPayload.fields.user_properties and jsonPayload.fields.event_properties.

The current jobs that create fxa_content_events_v1 and fxa_auth_events_v1 only have null values for the fields contained in jsonPayload.fields.user_properties and jsonPayload.fields.event_properties.

So, we could either fix those jobs or create new ones based on queries like that above.

@jklukas opinions?

(you may need to refresh the link to the query above, I forgot to save it)

jklukas commented 5 years ago

The current jobs that create fxa_content_events_v1 and fxa_auth_events_v1 only have null values for the fields contained in jsonPayload.fields.user_properties and jsonPayload.fields.event_properties.

I see non-null values for both these fields in fxa_content_events_v1.

So, we could either fix those jobs or create new ones based on queries like that above.

The easiest route would be to create VIEWs on top of the existing tables to handle the json extraction. We wouldn't have to deploy any new ETL jobs; the views would simply hit the existing tables like fxa_content_events_v1. We would codify the view as a new file under sql/ in https://github.com/mozilla/bigquery-etl and then I'd manually deploy it. See fxa_all_events_v1.sql for an example of a view definition.

irrationalagent commented 5 years ago

I see non-null values for both these fields in fxa_content_events_v1.

ah yea, you're right they're there, its just the exploded jsonPayload.user_properties.* that are null.

The easiest route would be to create VIEWs on top of the existing tables to handle the json extraction. We wouldn't have to deploy any new ETL jobs; the views would simply hit the existing tables like fxa_content_events_v1. We would codify the view as a new file under sql/ in https://github.com/mozilla/bigquery-etl and then I'd manually deploy it. See fxa_all_events_v1.sql for an example of a view definition.

cool, I'll see if I can get a PR up for that.