diff options
| -rw-r--r-- | notes/tasks/2020-07-22_processing_holes.md | 101 | ||||
| -rw-r--r-- | sql/dump_unextracted_pdf.sql | 11 | ||||
| -rw-r--r-- | sql/dump_unextracted_pdf_petabox.sql | 18 | ||||
| -rw-r--r-- | sql/dump_ungrobid_pdf.sql | 13 | ||||
| -rw-r--r-- | sql/dump_ungrobid_pdf_petabox.sql | 17 |
5 files changed, 150 insertions, 10 deletions
diff --git a/notes/tasks/2020-07-22_processing_holes.md b/notes/tasks/2020-07-22_processing_holes.md new file mode 100644 index 0000000..363989a --- /dev/null +++ b/notes/tasks/2020-07-22_processing_holes.md @@ -0,0 +1,101 @@ + +Want to clean up missing/partial processing (GROBID, `pdf_meta`, `file_meta`) +in sandcrawler database. + + +## `pdf_meta` for petabox rows + +Ran `dump_unextracted_pdf_petabox.sql` SQL, which resulted in a .json file. + + wc -l dump_unextracted_pdf_petabox.2020-07-22.json + 1503086 dump_unextracted_pdf_petabox.2020-07-22.json + +Great, 1.5 million, not too many. Start small: + + head -n1000 dump_unextracted_pdf_petabox.2020-07-22.json | rg -v "\\\\" | jq . -c | pv -l | kafkacat -P -b wbgrp-svc263.us.archive.org -t sandcrawler-prod.unextracted -p -1 + +Full batch: + + cat dump_unextracted_pdf_petabox.2020-07-22.json | rg -v "\\\\" | jq . -c | pv -l | kafkacat -P -b wbgrp-svc263.us.archive.org -t sandcrawler-prod.unextracted -p -1 + +## `pdf_meta` missing CDX rows + +First, the GROBID-ized rows but only if has a fatcat file as well. + +10,755,365! That is a lot still to process. + + cat dump_unextracted_pdf.fatcat.2020-07-22.json | rg -v "\\\\" | jq . -c | pv -l | kafkacat -P -b wbgrp-svc263.us.archive.org -t sandcrawler-prod.unextracted -p -1 + +## `GROBID` missing petabox rows + + wc -l /grande/snapshots/dump_ungrobided_pdf_petabox.2020-07-22.json + 972221 /grande/snapshots/dump_ungrobided_pdf_petabox.2020-07-22.json + +Start small: + + head -n1000 dump_ungrobided_pdf_petabox.2020-07-22.json | rg -v "\\\\" | jq . -c | pv -l | kafkacat -P -b wbgrp-svc263.us.archive.org -t sandcrawler-prod.ungrobided-pg -p -1 + +Full batch: + + cat dump_ungrobided_pdf_petabox.2020-07-22.json | rg -v "\\\\" | jq . -c | pv -l | kafkacat -P -b wbgrp-svc263.us.archive.org -t sandcrawler-prod.ungrobided-pg -p -1 + +## `GROBID` for missing CDX rows in fatcat + + wc -l dump_ungrobided_pdf.fatcat.2020-07-22.json + 1808580 dump_ungrobided_pdf.fatcat.2020-07-22.json + +Full batch: + + cat dump_ungrobided_pdf.fatcat.2020-07-22.json | rg -v "\\\\" | jq . -c | pv -l | kafkacat -P -b wbgrp-svc263.us.archive.org -t sandcrawler-prod.ungrobided-pg -p -1 + +## `GROBID` for bad status + +Eg, wayback errors. + +TODO + +## `pdf_trio` for OA journal crawls + +TODO + +## `pdf_trio` for "included by heuristic", not in fatcat + +TODO + +## Live-ingest missing arxiv papers + + ./fatcat_ingest.py --allow-non-oa --limit 10000 query arxiv_id:* > /srv/fatcat/snapshots/arxiv_10k_ingest_requests.json + => Expecting 1505184 release objects in search queries + + cat /srv/fatcat/snapshots/arxiv_10k_ingest_requests.json | rg -v "\\\\" | jq . -c | kafkacat -P -b wbgrp-svc263.us.archive.org -t sandcrawler-prod.ingest-file-requests -p 22 + +Repeating this every few days should (?) result in all the backlog of arxiv +papers getting indexed. Could focus on recent years to start (with query +filter). + +## re-ingest spn2 errors (all time) + +Eg: + + spn2-cdx-lookup-failure: 143963 + spn-error: 101773 + spn2-error: 16342 + +TODO + +## re-try CDX errors + +Eg, for unpaywall only, bulk ingest all `cdx-error`. + +TODO + +## live ingest unpaywall `no-capture` URLs + +After re-trying the CDX errors for unpaywall URLs (see above), count all the +no-capture URLs, and if reasonable recrawl them all in live more ("reasonable" +meaning fewer than 200k or so URLs). + +Could also force recrawl (not using CDX lookups) for some publisher platforms +if that made sense. + +TODO diff --git a/sql/dump_unextracted_pdf.sql b/sql/dump_unextracted_pdf.sql index 5c7e7a7..7b5e823 100644 --- a/sql/dump_unextracted_pdf.sql +++ b/sql/dump_unextracted_pdf.sql @@ -1,19 +1,20 @@ -- Run like: --- psql sandcrawler < dump_unextracted_pdf.sql > dump_unextracted_pdf.2019-09-23.json +-- psql sandcrawler < dump_unextracted_pdf.sql BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE READ ONLY DEFERRABLE; COPY ( - SELECT row_to_json(cdx) + SELECT DISTINCT ON (cdx.sha1hex) row_to_json(cdx) FROM grobid LEFT JOIN cdx ON grobid.sha1hex = cdx.sha1hex --LEFT JOIN fatcat_file ON grobid.sha1hex = fatcat_file.sha1hex + LEFT JOIN pdf_meta ON grobid.sha1hex = pdf_meta.sha1hex WHERE cdx.sha1hex IS NOT NULL - --AND fatcat_file.sha1hex IS NOT NULL + --AND fatcat_file.sha1hex IS NOT NULL + AND pdf_meta.sha1hex IS NULL ) ---TO '/grande/snapshots/dump_unextracted_pdf.2020-06-25.json'; -TO STDOUT +TO '/grande/snapshots/dump_unextracted_pdf.fatcat.2020-07-22.json' WITH NULL ''; ROLLBACK; diff --git a/sql/dump_unextracted_pdf_petabox.sql b/sql/dump_unextracted_pdf_petabox.sql new file mode 100644 index 0000000..7db34fb --- /dev/null +++ b/sql/dump_unextracted_pdf_petabox.sql @@ -0,0 +1,18 @@ + +-- Run like: +-- psql sandcrawler < dump_unextracted_pdf_petabox.sql + +BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE READ ONLY DEFERRABLE; + +COPY ( + SELECT DISTINCT ON (petabox.sha1hex) row_to_json(petabox) + FROM grobid + LEFT JOIN petabox ON grobid.sha1hex = petabox.sha1hex + LEFT JOIN pdf_meta ON grobid.sha1hex = pdf_meta.sha1hex + WHERE petabox.sha1hex IS NOT NULL + AND pdf_meta.sha1hex IS NULL +) +TO '/grande/snapshots/dump_unextracted_pdf_petabox.2020-07-22.json' +WITH NULL ''; + +ROLLBACK; diff --git a/sql/dump_ungrobid_pdf.sql b/sql/dump_ungrobid_pdf.sql index 3e6d782..e65edd5 100644 --- a/sql/dump_ungrobid_pdf.sql +++ b/sql/dump_ungrobid_pdf.sql @@ -1,15 +1,18 @@ -- Run like: --- psql sandcrawler < dump_ungrobid_pdf.sql | sort -S 4G | uniq -w 40 | cut -f2 > dump_ungrobid_pdf.2019-09-23.json +-- psql sandcrawler < dump_ungrobid_pdf.sql BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE READ ONLY DEFERRABLE; COPY ( - SELECT cdx.sha1hex, row_to_json(cdx) FROM cdx - WHERE cdx.mimetype = 'application/pdf' - AND NOT EXISTS (SELECT grobid.sha1hex FROM grobid WHERE cdx.sha1hex = grobid.sha1hex) + SELECT DISTINCT ON (cdx.sha1hex) row_to_json(cdx) + FROM cdx + WHERE cdx.mimetype = 'application/pdf' + AND NOT EXISTS (SELECT grobid.sha1hex FROM grobid WHERE cdx.sha1hex = grobid.sha1hex AND grobid.status IS NOT NULL) + -- uncomment/comment this to control whether only fatcat files are included + --AND EXISTS (SELECT fatcat_file.sha1hex FROM fatcat_file WHERE cdx.sha1hex = fatcat_file.sha1hex) ) -TO STDOUT +TO '/grande/snapshots/dump_ungrobided_pdf.fatcat.2020-08-04.json' WITH NULL ''; ROLLBACK; diff --git a/sql/dump_ungrobid_pdf_petabox.sql b/sql/dump_ungrobid_pdf_petabox.sql new file mode 100644 index 0000000..f758ec2 --- /dev/null +++ b/sql/dump_ungrobid_pdf_petabox.sql @@ -0,0 +1,17 @@ + +-- Run like: +-- psql sandcrawler < dump_ungrobid_pdf_petabox.sql + +BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE READ ONLY DEFERRABLE; + +COPY ( + SELECT DISTINCT ON (petabox.sha1hex) row_to_json(petabox) + FROM petabox + WHERE NOT EXISTS (SELECT grobid.sha1hex FROM grobid WHERE petabox.sha1hex = grobid.sha1hex AND grobid.status IS NOT NULL) + -- uncomment/comment this to control whether only fatcat files are included + AND EXISTS (SELECT fatcat_file.sha1hex FROM fatcat_file WHERE petabox.sha1hex = fatcat_file.sha1hex) +) +TO '/grande/snapshots/dump_ungrobided_pdf_petabox.2020-08-04.json' +WITH NULL ''; + +ROLLBACK; |