openverse: Remove duplicated tags

Problem

Here’s an example on the frontend of an image with duplicated tags: https://search-staging.openverse.engineering/image/9fc28cae-ec9b-437a-a960-98f48db2cac8 (coffee, lol, lol).

Description

We should remove duplicated tags as part of the cleaning steps when loading ingested data.

For backfilling: Non-dated DAGs consume all the data on each run and ought to update old records on the next DAG run. For dated DAGs, we may need to write a backfill query.

Implementation

  • 🙋 I would be interested in implementing this feature.

About this issue

  • Original URL
  • State: closed
  • Created 5 years ago
  • Comments: 15 (9 by maintainers)

Most upvoted comments

It’s definitely nice to run the update through a DAG so we have a little more visibility, although not strictly necessary. I don’t see the branch you mentioned so I haven’t looked at your updates to the batched_update, but FWIW I believe the existing DAG can be made to work without changes using this select_query:

    WHERE identifier IN (
        SELECT identifier FROM (
            SELECT 
                identifier,
                tags || '[]'::jsonb AS tags,
                (select jsonb_agg(distinct x) from jsonb_array_elements(tags || '[]'::jsonb) t(x)) AS unique_tags
            FROM image
        ) u
        WHERE jsonb_array_length(u.tags) > jsonb_array_length(u.unique_tags)
    );

It’s a bit more nested than I’d like but that worked for me locally to update the 362 images with duplicate tags in about 14 seconds. When I ran just the SELECT in production for image it took a little over 2 hours, so I hope that shouldn’t be too terrible either 🤞

Either way we’ll want to wait to run this until after confirming with @sarayourfriend that the catalog deployments are complete and DAGs can be unpaused.

This is done ✅

@stacimc That’s awesome! Better if we don’t require modifications to the DAG, glad that you found a query that works! I’ll mark this as blocked until the other work is finished.

I don’t know that I have the full context on raw_tags and why that took the structure it did, though I suspect it might be a dictionary because we occasionally had other values in there, like source and accuracy. Might be relevant for #431!

That said though, if we could 1) ensure that the MediaStore class is removing duplicate tags from the provider before saving and 2) remove the existing duplicates using the batched update DAG, I think that’s a great path forward!

After some fiddling around, it looks like we are able to query for this!!

deploy@localhost:openledger> 
    SELECT *
    FROM (
        SELECT identifier, provider, updated_on,
            tags || '[]'::jsonb AS tags,
            (SELECT jsonb_agg(DISTINCT x) FROM jsonb_array_elements(tags || '[]'::jsonb) t(x)) AS unique_tags FROM image
    ) u
    WHERE jsonb_array_length(u.tags) > jsonb_array_length(u.unique_tags) limit 1;
-[ RECORD 1 ]-------------------------
identifier  | 95aeace6-f70d-420c-a08e-84271ba6cad3
provider    | animaldiversity
updated_on  | 2019-02-15 14:47:33.392219+00
tags        | [{"name": "Reproduction", "provider": "animaldiversity"}, {"name": "Body Parts", "provider": "animaldiversity"}, {"name": "Photo", "provider": "animaldiversity"}, {"name": "Live Animal", "provider": "animaldiversity"}, {"name": "Female", "provider": "animaldiversity"}, {"name": "Female", "provider": "animaldiversity"}, {"name": "Adult/Sexually Mature", "provider": "animaldiversity"}, {"name": "Reproductive Organ", "provider": "animaldiversity"}, {"name": "insect", "accuracy": "0.99904", "provider": "clarifai"}, {"name": "dragonfly", "accuracy": "0.99758", "provider": "clarifai"}, {"name": "nature", "accuracy": "0.99558", "provider": "clarifai"}, {"name": "fly", "accuracy": "0.99301", "provider": "clarifai"}, {"name": "animal", "accuracy": "0.99085", "provider": "clarifai"}, {"name": "wildlife", "accuracy": "0.98856", "provider": "clarifai"}, {"name": "pest", "accuracy": "0.98646", "provider": "clarifai"}, {"name": "invertebrate", "accuracy": "0.9839", "provider": "clarifai"}, {"name": "leaf", "accuracy": "0.97928", "provider": "clarifai"}, {"name": "damselfly", "accuracy": "0.96964", "provider": "clarifai"}, {"name": "outdoors", "accuracy": "0.96327", "provider": "clarifai"}, {"name": "little", "accuracy": "0.9622", "provider": "clarifai"}, {"name": "wing", "accuracy": "0.95252", "provider": "clarifai"}, {"name": "close", "accuracy": "0.95173", "provider": "clarifai"}, {"name": "spider", "accuracy": "0.94385", "provider": "clarifai"}, {"name": "wild", "accuracy": "0.94351", "provider": "clarifai"}, {"name": "closeup", "accuracy": "0.94158", "provider": "clarifai"}, {"name": "garden", "accuracy": "0.93974", "provider": "clarifai"}, {"name": "entomology", "accuracy": "0.93532", "provider": "clarifai"}]
unique_tags | [{"name": "Adult/Sexually Mature", "provider": "animaldiversity"}, {"name": "Body Parts", "provider": "animaldiversity"}, {"name": "Female", "provider": "animaldiversity"}, {"name": "Live Animal", "provider": "animaldiversity"}, {"name": "Photo", "provider": "animaldiversity"}, {"name": "Reproduction", "provider": "animaldiversity"}, {"name": "Reproductive Organ", "provider": "animaldiversity"}, {"name": "animal", "accuracy": "0.99085", "provider": "clarifai"}, {"name": "close", "accuracy": "0.95173", "provider": "clarifai"}, {"name": "closeup", "accuracy": "0.94158", "provider": "clarifai"}, {"name": "damselfly", "accuracy": "0.96964", "provider": "clarifai"}, {"name": "dragonfly", "accuracy": "0.99758", "provider": "clarifai"}, {"name": "entomology", "accuracy": "0.93532", "provider": "clarifai"}, {"name": "fly", "accuracy": "0.99301", "provider": "clarifai"}, {"name": "garden", "accuracy": "0.93974", "provider": "clarifai"}, {"name": "insect", "accuracy": "0.99904", "provider": "clarifai"}, {"name": "invertebrate", "accuracy": "0.9839", "provider": "clarifai"}, {"name": "leaf", "accuracy": "0.97928", "provider": "clarifai"}, {"name": "little", "accuracy": "0.9622", "provider": "clarifai"}, {"name": "nature", "accuracy": "0.99558", "provider": "clarifai"}, {"name": "outdoors", "accuracy": "0.96327", "provider": "clarifai"}, {"name": "pest", "accuracy": "0.98646", "provider": "clarifai"}, {"name": "spider", "accuracy": "0.94385", "provider": "clarifai"}, {"name": "wild", "accuracy": "0.94351", "provider": "clarifai"}, {"name": "wildlife", "accuracy": "0.98856", "provider": "clarifai"}, {"name": "wing", "accuracy": "0.95252", "provider": "clarifai"}]
SELECT 1
Time: 0.085s

(Note that a duplicate {'name': 'Female', 'provider': 'animaldiversity'}, was removed in the unique_tags 🎉)

I’m going to run a count for this to see how many we have 😄