1180051 - Create Redshift table for raw FxOS FTU data

Status: RESOLVED WONTFIX

(Cloud Services :: Metrics: Product Metrics, defect, P5)

Description

[Dave Zeber [:dzeber]]

We'd like to make the entire Firefox OS FTU dataset available in a SQL query-able table. 

Currently the raw data is stored with telemetry in S3, and we run daily scripts to sanitize and flatten it into rows, which are outputted as CSV snapshots of the dataset. We want to replace the CSV output with a Redshift table that will more convenient for us to maintain and for the consumers of the data to access.

Comment 1

[Mark Reid [:mreid]]

(In reply to Dave Zeber [:dzeber] from comment #0)
> We'd like to make the entire Firefox OS FTU dataset available in a SQL
> query-able table. 
> 
> Currently the raw data is stored with telemetry in S3, and we run daily
> scripts to sanitize and flatten it into rows, which are outputted as CSV
> snapshots of the dataset. We want to replace the CSV output with a Redshift
> table that will more convenient for us to maintain and for the consumers of
> the data to access.

Can you attach or link to the sanitize/flatten scripts?

Comment 2

[Dave Zeber [:dzeber]]

The code for sanitizing the raw FTU payloads is here: https://github.com/dzeber/fxos-metrics/blob/master/awsjobs/dump_format_ftu.py. It is currently run daily as a mapreduce job with the only filter conditions {reason == "ftu", appName == "FirefoxOS", and submission_date in a relevant date range}. 

The filter rules called by this script are located in https://github.com/dzeber/fxos-metrics/tree/master/utils. 

The list of fields that are outputted by this script (to be considered as columns) is at https://github.com/dzeber/fxos-metrics/blob/master/utils/dump_schema.py#L11. These field names are either those used in the raw payload or those added or modified in the sanitization script. If any of these fields is absent from the payload, it is recorded as missing.

I can send a sample output CSV over email.

Comment 3

[Mark Reid [:mreid]]

Yes, please do send a sample.

Comment 4

[Dave Zeber [:dzeber]]

Additional details: the output of the mapreduce job is (list of field values, # of payloads with the same set of values). The output table is basically occurrence counts of the segments defined by the column values.

Comment 5

[Thomas Huelbert]

Hello, is this still needed?

Comment 6

[:Dominik Strohmeier [:dstrohmeier]]

Let me reply to this NI after meeting with Katie and Georg next week, please.
Thanks.

Comment 7

[:Dominik Strohmeier [:dstrohmeier]]

Hi Thomas,
how much of a pain would it be to implement these?

Reason why I am asking is that the current answer would be 'no', but I can see us coming to a stage where different Connected Devices trains will start submitting activation data and then, these tables would actually make it very comfortable for me to quickly do analysis by simply being able to connect R with the Redshift tables.

Dominik

Comment 8

[Sylvestre Ledru [:Sylvestre]]

I don't think we are going to work on this anymore. Closing.