1594172 - Duplicate bug rows in moz-fx-data-shared-prod.eng_workflow_live.bmobugs_v1 when inserting using http://incoming.telemetry.mozilla.org/submit

Status: RESOLVED INACTIVE

(Data Platform and Tools :: General, defect, P3)

Description

[David Lawrence [:dkl]]

When querying using: https://sql.telemetry.mozilla.org/queries/65850/source?p_bugid_65850=1475381

SELECT *
FROM
moz-fx-data-shared-prod.eng_workflow_live.bmobugs_v1
WHERE
submission_timestamp > '2004-11-09'
and bug_id = {{ bugid }}

I am seeing 9 rows of similar data all having the same document_id. The submission_timestamp is different for each one for each time my cron job on BMO runs to send the data. Also the metadata values are slightly different.

According to https://github.com/mozilla/gcp-ingestion/blob/85863eef1ea8080438fa75c61d61ff4f8ce64598/docs/architecture/decoder_service_specification.md#deduplication I assumed that there should only be one row of bug data per document_id. So it seems that deduplication is not working in my case.

Is there something possibly wrong with how I am sending the data or some other issue?

Thanks

Comment 1

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

The deduplication is best-effort, so there are no real guarantees, but it is generally pretty efficient.

In your case, it looks like you've run the script on multiple different days (based on the submission_timestamps being from different days). Note that the cache of seen document_ids lasts only for a max of 24 hours, so if you ran the same cron job on two different days, the IDs would no longer be in the cache. Does that sound like it explains the behavior you're seeing?

Comment 2

[David Lawrence [:dkl]]

(In reply to Jeff Klukas [:klukas] (UTC-4) from comment #1)

The deduplication is best-effort, so there are no real guarantees, but it is generally pretty efficient.

In your case, it looks like you've run the script on multiple different days (based on the submission_timestamps being from different days). Note that the cache of seen document_ids lasts only for a max of 24 hours, so if you ran the same cron job on two different days, the IDs would no longer be in the cache. Does that sound like it explains the behavior you're seeing?

Very much so. So my questions are:

1. Is there a way to query the document_id in telemetry before sending the new row of data? Granted this will significantly slow down the import process.
2. Is there a way for me to delete the duplicates on my own systematically?
3. Is there a way for a dashboard author to use DISTINCT or similar function when writing the queries that will filter out any extra rows and only use the row with the latest submission_timestamp?

Thanks

Comment 3

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

1. You could run a BQ query to pull down the list of existing document_ids. This would require having GCP creds available for the service and using GCP client libraries. So it could be done, but is probably some effort.
2. The ingestion machinery really is not set up to handle deletion outside of the stated deduplication efforts.
3. Yes, it's reasonable to write a query that deduplicates. For example:

WITH
  base AS (
  SELECT
    *
  FROM
    `moz-fx-data-shar-nonprod-efed.eng_workflow_live.bmobugs_v1`
  WHERE
    submission_timestamp > '2019-01-01' ),
  numbered AS (
  SELECT
    ROW_NUMBER() OVER (PARTITION BY document_id ORDER BY submission_timestamp DESC) AS _n,
    *
  FROM
    base )
SELECT
  * EXCEPT (_n)
FROM
  numbered
WHERE
  _n = 1

But does the cron job always extract full history? If that's what you want to do, then submitting through structured ingestion may really not be what we want and we maybe need to consider a different way of approaching this problem.

Comment 4

[Simon Bennetts [:psiinon]]

:dkl how can we move this forwards?

Comment 5

[David Lawrence [:dkl]]

(In reply to Jeff Klukas [:klukas] (UTC-4) from comment #3)

But does the cron job always extract full history? If that's what you want to do, then submitting through structured ingestion may really not be what we want and we maybe need to consider a different way of approaching this problem.

The script has been updated for a while now to only send bug data for bugs changed since the last script run. This cuts down on the amount of data sent significantly.

So currently we run the script once a week so we cannot take advantage of the 24 hour window you stated where de-duplication happens. I suppose we could run the script every day but original requirements were that weekly was often enough.

So the end best solution here is to just always construct the queries used to remove duplicate entries manually?

Comment 6

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

(In reply to David Lawrence [:dkl] from comment #5)

So currently we run the script once a week so we cannot take advantage of the 24 hour window you stated where de-duplication happens. I suppose we could run the script every day but original requirements were that weekly was often enough.

I would advise against running the script every day to try to get the benefits of deduplication. The deduplication system isn't intended for periodic batch flows like this.

Running one a week and emitting only changed bugs seems reasonable.

So the end best solution here is to just always construct the queries used to remove duplicate entries manually?

For the amount of data involved here, it's tenable to always include global deduplication in the query.

If your concern is the ergonomics of always having to include the deduplication logic, then we could provision a view that handles that part. There's already a user-facing view eng_workflow.bmobugs that's automatically provisioned by the pipeline. We can customize that view to include the deduplication logic or deploy a new view with a different name.

A different approach to this problem would be to go back to sending all bugs on every run, but include a new field for script_run_time or something along those lines that would be set to the current time at the start of running the export script. Then, you could query for only rows from the most recent run.

Comment 7

[Mark Reid [:mreid]]

(In reply to Jeff Klukas [:klukas] (UTC-4) from comment #6)

If your concern is the ergonomics of always having to include the deduplication logic, then we could provision a view that handles that part. There's already a user-facing view eng_workflow.bmobugs that's automatically provisioned by the pipeline. We can customize that view to include the deduplication logic or deploy a new view with a different name.

:dkl, does this sound like it would address your needs here?