stephen-soltesz commented 5 years ago

Also see #124 In addition to https://github.com/m-lab/etl-gardener/issues/121 -- Gardener may fail to make progress due to some task_filenames using old source buckets e.g. archive-mlab-staging scraper-mlab-staging etc.

The new gardener source only uses archive-measurement-lab. The old source rows should be deleted. e.g.

Find bad rows:

SELECT
    "ndt" AS dataset,
    COUNT(*) as count,
    COUNTIF(REGEXP_CONTAINS(task_filename, r'gs://(archive|scraper)-mlab-staging')) as delete_count
FROM   `mlab-staging.base_tables.ndt`

Delete those rows.

DELETE FROM `mlab-staging.base_tables.ndt`
WHERE REGEXP_CONTAINS(task_filename, r'gs://(archive|scraper)-mlab-staging')

stephen-soltesz commented 5 years ago

Attempting to delete from the table generates an error:

UPDATE or DELETE statement over table mlab-staging.base_tables.ndt would affect rows in the streaming buffer, which is not supported

It may be necessary to suspend gardener, drain the streaming buffer and then run the delete operation?

stephen-soltesz commented 5 years ago

Attempting to exclude the unpartitioned data using hints from https://cloud.google.com/bigquery/docs/querying-partitioned-tables#ingestion-time_partitioned_tables_unpartitioned_partition i.e. (WHERE _PARTITIONTIME IS NOT NULL) does not seem to work either.

stephen-soltesz commented 5 years ago

The original report is partly correct, and partly mistaken. Partly mistaken because the daily pipelines still add archive-<project> task filenames every day. Partly correct because none of those archives should be earlier than, say, 2017 when we turned on the external pipeline.

Recent attempts to delete some rows in staging and oti tables may have inadvertently deleted some archive-<project> records. Eventually these will be overwritten by gardener.
mlab-staging will add task-filenames that are not present in archive-measurement-lab due to scraper collection from canary machines. These feed the staging pipeline and are distinct archives from those collected by mlab-oti (different timestamps) and possibly different contents.

In practice this likely means that staging will have more unique task_filenames in base_tables (includes canary archive) than the batch tables (includes only archive-measurement-lab).

stephen-soltesz commented 5 years ago

Looking at the task_filenames per day in BQ and GCS there appear to be a significant number of GCS files that are not being processed.

  SELECT REGEXP_EXTRACT(task_filename, r"/(201812[0-9]{2})T") as day, COUNT(distinct task_filename) as tasks
  FROM  `mlab-oti.batch.ndt`                                          
  WHERE _PARTITIONTIME >= TIMESTAMP("2018-12-01") AND _PARTITIONTIME <= TIMESTAMP("2018-12-31")
  GROUP BY day
  ORDER BY day

Note: the gsutil list of a month is quite slow (8-10min).

gsutil ls gs://archive-measurement-lab/ndt/2018/12/*/* | tr '/T' ' ' | awk '{print $7}' | sort | uniq -c

For example:

row	Date	BQ batch	GCS Files
1	20181201	42042	42413
2	20181202	40355	40758
3	20181203	41274	41682
4	20181204	43073	43476
5	20181205	42954	43351
6	20181206	43286	43709

stephen-soltesz commented 5 years ago

If gardener+parsers are deterministic, then I would not expect the content of the batch tables to be out of sync between mlab-staging & mlab-oti -- both of which source only gs://archive-measurement-lab buckets. (except possibly days currently being processed).

However, we find ~83 rows that have different task_filename counts.

SELECT day, staging.tasks as staging_tasks, oti.tasks as oti_tasks
FROM (
  SELECT REGEXP_EXTRACT(task_filename, r"/(20[0-9]{6})T") as day, COUNT(distinct task_filename) as tasks
  FROM  `mlab-staging.batch.ndt`                                          
  GROUP BY day
) as staging FULL JOIN (
  SELECT REGEXP_EXTRACT(task_filename, r"/(20[0-9]{6})T") as day, COUNT(distinct task_filename) as tasks
  FROM  `mlab-oti.batch.ndt`                                          
  GROUP BY day
) as oti USING(day)
WHERE staging.tasks != oti.tasks
ORDER BY day

Row	day	tasks	tasks2
9	20120110	504	497
10	20120113	539	546
11	20120115	502	504
12	20120122	697	685
13	20120126	473	478

... 49 | 20170601 | 10833 | 10746 | 50 | 20170602 | 11034 | 11075 | 51 | 20170603 | 10045 | 9977 | 52 | 20170604 | 10014 | 9980 | 53 | 20170605 | 10584 | 10532 | ...