Ensure 100% table coverage in BigQuery

[rviscomi]

https://discuss.httparchive.org/t/missing-2016-02-15-chrome-requests/1310 is a bug report that some 2016_02_15 tables are missing.

We should take inventory of all tables across all dates and reprocess anything that's missing.

This can be a good first bug for first time contributors. Overview of the expected workflow:

• use the bq command line interface to list the contents of each dataset
• export results to a spreadsheet
  • graph the results to make it obvious if there are any gaps
• or write a script to check if any YYYY_MM_[01, 15] tables are missing
  • some early tables are not necessarily DD=[01, 15]
• ignore tables that are expected to be missing, eg lighthouse.YYYY_MM_DD_desktop, or others missing as a result of known data loss bugs (citation needed)

[paulcalvano]

I’ll work on this one.

[rviscomi]

Thanks Paul!

[paulcalvano]

Using the following query to extract all of this data. I'm looking at row counts by table names in each dataset -

SELECT *  FROM httparchive.har.__TABLES__ 
UNION ALL 
SELECT *  FROM httparchive.lighthouse.__TABLES__ 
UNION ALL 
SELECT *  FROM httparchive.pages.__TABLES__ 
UNION ALL 
SELECT *  FROM httparchive.requests.__TABLES__ 
UNION ALL 
SELECT *  FROM httparchive.response_bodies.__TABLES__ 
UNION ALL 
SELECT *  FROM httparchive.runs.__TABLES__ 
UNION ALL 
SELECT *  FROM httparchive.summary_pages.__TABLES__ 
UNION ALL 
SELECT *  FROM httparchive.summary_requests.__TABLES__ 

Here's a graphical summary of the gaps for all the runs, summary_pages and summary_requests datasets.

In case the descriptions are difficult to read, here's a summary of the gaps -

(1) 12/1/2013: missing data for all summary tables
(2) 10/15/2014: requests data table is twice as large as previous months
(3) 1/15/2016: requests data table is missing data
(4) 12/15/2016: requests tables are missing data
              : requests_mobile table is missing
(5) 01/01/2017: missing requests and requests_mobile data
    01/15/2017: missing requests and requests_mobile data	
(6) 11/15/2017: missing data in requests_mobile
(7) 02/15/2017-06/15/2017: missing desktop pages summary

I'll work on the har, lighthouse, pages and requests datasets next.

[paulcalvano]

The har, lighthouse, pages and requests datasets do not appear to have any gaps between the old and new datasets. There are a few interesting things to note:

• Most of this data has major gaps on 10/15/2016 and 11/1/2016
• Starting around 7/15/2017 and ending by 02/01/2018 there was a drop in mobile response_bodies (request_bodies in the older dataset)

[rviscomi]

This is awesome thanks for compiling it, Paul. I'll work on rerunning the pipeline for any missing tables.

[paulcalvano]

Cool, thanks. Let me know when it’s done and I can update the visualization with the latest table data to confirm.