Figure out the sources of dataset gap between catalog and beta

[tdlowden]

There are 353,000 datasets on Catalog.data.gov and around 220,000 showing right now on Catalog-beta.data.gov. We need to figure out what this discrepancy is being caused by so we can fix it.

Feature/what we're after

The number of datasets on Catalog-beta need to be basically as much as we see on Catalog.

Anticipated/hypothesized benefits

• [benefit A]
• [benefit B]

Measurements/metrics

• In our H2.0 harvest record table, how many unique pairs of (source_id, identifier) where the most recent record has a "success" status? These are records that we have harvested that could be datasets. If we have many more than 353,000 of these, then the problem is not with the raw harvesting.
• In our H2.0 dataset table, how many rows are there? We know that this number is not as large as it "should" be.
• For any record candidates that are in the harvest record table but NOT in the dataset table, what features do they share? What harvest sources are they from? What organizations own those harvest sources? When were they harvested? What other patterns can we find?

References/background

• [notes about desirable attributes or context for the epic]
• [links to previous research, possible routes to take, things that multiple stories will likely need to refer to]

[rshewitt]

we currently have 511784 records on prod. that comes from the following query.

SELECT count(*) FROM (
SELECT DISTINCT ON (identifier,harvest_source_id) *
FROM harvest_record 
WHERE status = 'success'
ORDER BY identifier, harvest_source_id, date_created DESC ) sq
WHERE sq.action != 'delete';

update that query to count how many of those have a populated source_transform we get 248113.

unless there's something manipulating that number the problem is obviously search (specifically syncing with search)

there's 369129 records in the dataset table on harvester. there's 369784 records in the dataset table on the replica (updates probably need to be pushed into the replica).

[neilmb]

Today, this morning, there's over 431000 records in the dataset table. We are trying to index those datasets to Opensearch right now, but it does not appear that we will get the same number of documents indexed into Opensearch. (We are over 300,000 records into the opensearch sync process and only 199,000 datasets are appearing in the UI on catalog-beta.data.gov.)

The increase in the number of datasets is due to a corresponding decrease in the number of harvest records without source_transform. Some harvests that took place recently succeeded and the new values of source_transform got updated. We are down to 51,439 harvest records that are missing source_transform.

Using count on Reid's query from above:

SELECT COUNT(1) tot FROM (SELECT DISTINCT ON (harvest_source_id, identifier) * FROM harvest_record WHERE status = 'success' ORDER BY harvest_source_id, identifier, date_created DESC) candidates LEFT JOIN harvest_source hs ON (candidates.harvest_source_id = hs.id) LEFT JOIN organization ON (hs.organization_id = organization.id) WHERE candidates.action != 'delete' AND hs.schema_type = 'iso19115_2' AND candidates.source_transform IS NULL

  tot
-------
 51439

[neilmb]

Things that we know now can help with this: Get successful harvests with values of source_transform for these harvest sources:

SELECT hs.id, hs.name, organization.slug, COUNT(1) tot FROM (SELECT DISTINCT ON (harvest_source_id, identifier) * FROM harvest_record WHERE status = 'success' ORDER BY harvest_source_id, identifier, date_created DESC) candidates LEFT JOIN harvest_source hs ON (candidates.harvest_source_id = hs.id) LEFT JOIN organization ON (hs.organization_id = organization.id) WHERE candidates.action != 'delete' AND hs.schema_type = 'iso19115_2' AND candidates.source_transform IS NULL GROUP BY hs.id, hs.name, organization.id ORDER BY tot DESC LIMIT 50;

                  id                  |                         name                          |  slug  | tot
--------------------------------------+-------------------------------------------------------+--------+------
 ce0b52a7-27cb-44ac-b5bd-d00d4c6e6b03 | ngdc-mgg-multibeam                                    | noaa   | 3806
 670f3d18-bcc0-47d9-97d3-a332d77f658d | 2018-edges                                            | census | 3232
 e145d033-f7b5-4a40-a050-8069401945d6 | 2020-roads                                            | census | 3232
 706d21b2-d4a3-4bc3-b681-d9fcdc54d8de | 2018-faces                                            | census | 3232
 f25e9b14-ee8a-49ec-87ed-d66eb4d2b0d7 | 2017-facesah                                          | census | 3230
 28a2d36a-a667-4ad9-a8e4-1d8794cd2607 | 2017-addrfn                                           | census | 3220
 2cd365f3-a59e-4bfb-adb1-b33e7b7c2185 | 2019-faces                                            | census | 3193
 19440316-20ce-4ea5-a315-7bf13fc9c909 | 2019_addrfn                                           | census | 3180
 cf936e66-2e56-4f22-b58d-4501578304b1 | 2019-addr                                             | census | 3180
 d3960e40-8cba-4498-89fd-3640ef0b2df6 | 2019-addrfeat                                         | census | 3180
 7f0405ca-b674-4687-9fa4-627a8a540969 | nesdis-ngdc-mgg-nos-h00001-h02000                     | noaa   | 2533
 6d7055ce-7949-410c-b294-50629bcc0127 | nesdis-ngdc-mgg-nos-h02001-h04000                     | noaa   | 2143
 63703e7b-b96b-4918-bc73-c743435ed497 | environmental-dataset-gateway-iso-geospatial-metadata | epa    | 2123
 9eead21f-619e-4d32-9ecc-58282ac8cc7a | nesdis-ngdc-mgg-nos-l00001-l02000                     | noaa   | 2049
 e6f70723-3fb4-4e8a-aa4b-c02f1fbd9cce | ngdc-mgg-sonar-water-column                           | noaa   | 1794
 7789ad45-48e1-4330-9b08-d0dd2df3d3df | nesdis-ngdc-mgg-nos-h10001-h12000                     | noaa   | 1627
 dea9c4c7-9aed-48c0-8f22-f8fc94f3a3ff | ioos                                                  | noaa   | 1405
 a45ed5dd-b949-415c-9f26-555c232bb379 | cartographic-boundary-files-2020                      | census | 1128
 f27e3922-4784-4cba-8300-0a4b57e55006 | cartographic-boundary-files-2023                      | census | 1007
 4705e424-3de1-44e6-8ba8-971d1758125a | ncddc-oer                                             | noaa   |  219
 55a4a0c9-54e8-4022-9156-4882dffce412 | nesdis-ngdc-mgg-nos-l02001-l04000                     | noaa   |  175
 7fffc7ae-c50e-4a2c-9023-6061e0347296 | ngdc-mgg-geology                                      | noaa   |   78
 f7606217-feab-4229-97cc-4d36981eab39 | 2016-faces                                            | census |   64
 c336e76e-f708-4138-8e9d-173e635735e3 | 2019cb-cousub                                         | census |   57
 482dfb78-d0f5-40fd-b1d3-1a727de34649 | 2019cb-county-withn-ua                                | census |   57
 d97a0a9b-1039-404e-ad13-f2b03e4bb5df | 2019cb-unsd                                           | census |   57
 2039d603-22b0-4456-b5e4-3d508a268780 | 2017-cousub-500                                       | census |   56
 3ad9384d-cda7-4922-bca6-5c5ebbbf437f | 2019-arealm                                           | census |   56
 36ef68bb-c74b-4cd0-85e4-dd031fc52eb0 | 2020-place                                            | census |   56
 adba226b-cde3-42c1-a671-e8ec662fdc57 | 2017-bg-500                                           | census |   56
 2b9d42fa-e392-4b52-a747-4f0beca27f72 | 2017-place-500                                        | census |   56
2aeba96e-c00c-4b8c-a64e-09f422698b8e | 2019-tabblock10                                       | census |   56
 d8ca1c78-27ec-4566-b302-0502f49f66e1 | 2017-place-500kml                                     | census |   56
 6e525640-21f9-4d93-acb4-3ba7d3fcd927 | 2019cb-bgkml                                          | census |   56
 6a011314-1304-4d42-a269-cce59a8e36e0 | noaa-nesdis-ncei-accessions                           | noaa   |   56
 6af93f20-1224-45dd-acf6-b5aee68c0752 | current-census-tract-state-based                      | census |   56
 4b37ec68-ce63-43af-a7a3-fb370b11aeed | 2019-cousub                                           | census |   56
 64d047d2-3cc0-4a76-99fb-6d7e776a34f3 | 2017-county-within-ua-500                             | census |   56
 a4af9d35-5a53-422e-94e9-70c7b61379fc | 2017-bg-500kml                                        | census |   56
 2358ea20-e8ef-4390-948c-06f59c99add6 | 2019-bg                                               | census |   56
 05bc009c-00b2-48c5-96f0-88ff93f5fd31 | 2017-cousub                                           | census |   56
 3e1ea9b3-a12e-4c90-b260-0b91616e225a | 2020-tract                                            | census |   56
 b39af4ee-629c-486c-881f-a03f17beeff7 | 2018-tract                                            | census |   56
 fd29894e-a7af-4609-82d2-6870d3c68eb7 | 2018-prisecroads                                      | census |   56
 fdbe8b0d-6f3d-4cd4-b6a7-170b28e24b41 | current-block-group-state-based                       | census |   56
 3cf7e261-e22a-4a4a-9e4d-85fcd3398683 | current-puma10                                        | census |   54
 93812f71-aeee-4256-8088-4efa2b1033e8 | 2019cb-puma10kml                                      | census |   54
 9373a61c-f9ae-4b43-bdba-bf928c292bc0 | 2018-puma10                                           | census |   54
 3302ffce-3e05-4445-a2e3-592bd43ba99f | 2017-unsd                                             | census |   54
 dd95793c-dbad-495b-bd85-986c56b1fac7 | current-state-legislative-district-sld-upper-chamber  | census |   52

[neilmb]

Also, we have to figure out why some of the rows in datasets are not appearing in Opensearch counts, even though the sync job (e.g. https://github.com/GSA/datagov-catalog/actions/runs/20378644950/job/58563535185) reports "100 successes and 0 errors" for every set of 100 documents.

Can we find a particular dataset in the dataset table that isn't in the Opensearch index? If so, can we figure out why it would appear to be successfully synced, but not in the index?

[neilmb]

Here's a way to find datasets that don't exist by ID in the opensearch index:

$ flask shell
>>> from app.routes import interface
>>> dataset_ids = set(interface.db.query(Dataset.id))
>>> len(dataset_ids)
433067
>>> from opensearchpy.helpers.document import Document
>>> missing_id = next(filter(lambda id: not Document.exists(id[0], using=interface.opensearch.client, index="datasets"), dataset_ids))
>>> missing_id
('f8318c34-300a-465a-836c-6fd9ce53af36',)
>>> Document.exists(missing_id[0], using=interface.opensearch.client, index="datasets")
False

This is just the first one found. Consuming the whole filter iterator into a set could provide a full list of datasets that are not in the index.

Still no clue on why these datasets wouldn't be making it into the index.

[neilmb]

Things that we know now can help with this: Get successful harvests with values of source_transform for these harvest sources:

Just spot-checking the top ten of them, these sources appear to still be in the queue waiting to be crawled from the most recent set of force_harvest jobs that we kicked off.

Hypothesis: by Monday, these records without source_transform will be mostly resolved.

[neilmb]

Example: https://catalog-beta.data.gov/dataset/the-lethality-of-hot-water-chlorine-and-ozone-on-aquatic-invasive-species is in the database with id f8318c34-300a-465a-836c-6fd9ce53af36 but not in the opensearch index, so searching for "lethality of hot water chlorine" brings up no results https://catalog-beta.data.gov/?q=lethality+of+hot+water+chlorine

[neilmb]

It doesn't appear to be impossible to index these datasets into Opensearch:

>>> interface.opensearch.index_datasets(Dataset.query.filter(Dataset.id==missing_ids[0]))
>>> Document.exists(missing_ids[0], using=interface.opensearch.client, index="datasets")
True

and now this search https://catalog-beta.data.gov/?q=lethality+of+hot+water+chlorine has a search result (watch out for the cache).

[neilmb]

Manual attempted clean up by indexing datasets that were missing from OpenSearch:

>>> missing_ids = []
>>> for id in filter(lambda id: not Document.exists(id[0], using=interface.opensearch.client, index="datasets"), dataset_ids):
...     missing_ids.append(id[0])
>>> import itertools
>>> for batch in itertools.batched(missing_ids, 100):
...     result = interface.opensearch.index_datasets(Dataset.query.filter(Dataset.id.in_(batch)))
...     print(result)
...

(from trying it: this is a very slow way to search for missing datasets)

[neilmb]

One tangential data point for future reference, it appears that this dataset https://catalog-beta.data.gov/dataset/4aa5d8cd-f4ac-4c3b-88d3-78c3cf495aa9 won't index into Opensearch. It could be something like our "timestamp" fix that we need to implement?

[tdlowden]

It appears @FuhuXia and @cmhedrickREI solved this last week! Christopher, can you summarize here what you all did?

[cmhedrickREI]

It appears @FuhuXia and @cmhedrickREI solved this last week! Christopher, can you summarize here what you all did?

@tdlowden, I believe all the credit really goes to @FuhuXia and his compare command. This command preserves most of the index avoiding the need to fully re-index all of the datasets and only adds and deletes datasets from the index. This is a more efficient process, which I believed solved the scheduled jobs previously failing issue (bonus). I think because of the lowered stress on Opensearch as well also contributes to the indexing being more of a success. So by getting rid of the dumping of the index and more carefully adding and deleting docs from the led to much better results. I believe is the gist and @FuhuXia can if I'm not 100% covering it.

Some other notes:
We also found there is an erronous dataset that is not matching the desired dcat schema where if you search 43281 it leads to an empty field which causes an noted error in the opensearch indexing. The particular dataset isn't reflected in the total count because it only exists in the DB. But the good news is that this is the only dataset in the db to be an issue and we can try deleting it and reharvesting to see if the harvest fails because of the error or if it makes it in. Doing the math from the most recent sync the DB matches opensearch. I think this could be a harvester ticket to improve which datasets that get into the db if deleting and reharvest doesn't work