diff options
| -rw-r--r-- | notes/ingest/2020-02-14_unpaywall_ingest.md | 416 |
1 files changed, 416 insertions, 0 deletions
diff --git a/notes/ingest/2020-02-14_unpaywall_ingest.md b/notes/ingest/2020-02-14_unpaywall_ingest.md index df4795b..0bedfdb 100644 --- a/notes/ingest/2020-02-14_unpaywall_ingest.md +++ b/notes/ingest/2020-02-14_unpaywall_ingest.md @@ -58,3 +58,419 @@ There are 21,961,928 lines total, in batches of 1,098,097. => 2020-03-02 21:06 local: 1,257,176 added about 500k bulk re-ingest to try and work around cdx errors => 2020-03-02 21:30 local: 1,733,654 + +## Investigate Failures + +Guessing than some domains are ultimately going to need direct "recrawl" via +SPNv2. + + -- top domain failures for unpaywall GWB history ingest + SELECT domain, status, COUNT((domain, status)) + FROM ( + SELECT + ingest_file_result.ingest_type, + ingest_file_result.status, + substring(ingest_file_result.terminal_url FROM '[^/]+://([^/]*)') AS domain + FROM ingest_file_result + LEFT JOIN ingest_request + ON ingest_file_result.ingest_type = ingest_request.ingest_type + AND ingest_file_result.base_url = ingest_request.base_url + WHERE + ingest_file_result.ingest_type = 'pdf' + AND ingest_request.link_source = 'unpaywall' + ) t1 + WHERE t1.domain != '' + AND t1.status != 'success' + AND t1.status != 'no-capture' + GROUP BY domain, status + ORDER BY COUNT DESC + LIMIT 30; + + domain | status | count + -----------------------------------+---------------------+-------- + watermark.silverchair.com | terminal-bad-status | 258432 + www.tandfonline.com | no-pdf-link | 203873 + journals.sagepub.com | no-pdf-link | 126317 + iopscience.iop.org | terminal-bad-status | 112526 + files-journal-api.frontiersin.org | terminal-bad-status | 112499 + pubs.acs.org | no-pdf-link | 94772 + www.degruyter.com | redirect-loop | 89801 + www.ahajournals.org | no-pdf-link | 84025 + society.kisti.re.kr | no-pdf-link | 72849 + www.nature.com | redirect-loop | 53575 + babel.hathitrust.org | terminal-bad-status | 41063 + www.ncbi.nlm.nih.gov | redirect-loop | 40363 + scialert.net | no-pdf-link | 38340 + www.degruyter.com | terminal-bad-status | 34913 + www.journal.csj.jp | no-pdf-link | 30881 + espace.library.uq.edu.au | redirect-loop | 24570 + www.jci.org | redirect-loop | 24409 + aip.scitation.org | wrong-mimetype | 22144 + www.vr-elibrary.de | no-pdf-link | 17436 + www.biorxiv.org | wrong-mimetype | 15524 + ajph.aphapublications.org | no-pdf-link | 15083 + zookeys.pensoft.net | redirect-loop | 14867 + dialnet.unirioja.es | redirect-loop | 14486 + asa.scitation.org | wrong-mimetype | 14261 + www.nrcresearchpress.com | no-pdf-link | 14254 + dl.acm.org | redirect-loop | 14223 + osf.io | redirect-loop | 14103 + www.oecd-ilibrary.org | redirect-loop | 12835 + journals.sagepub.com | redirect-loop | 12229 + iopscience.iop.org | redirect-loop | 11825 + (30 rows) + + -- top no-capture terminal domains + SELECT domain, status, COUNT((domain, status)) + FROM ( + SELECT + ingest_file_result.ingest_type, + ingest_file_result.status, + substring(ingest_file_result.terminal_url FROM '[^/]+://([^/]*)') AS domain + FROM ingest_file_result + LEFT JOIN ingest_request + ON ingest_file_result.ingest_type = ingest_request.ingest_type + AND ingest_file_result.base_url = ingest_request.base_url + WHERE + ingest_file_result.ingest_type = 'pdf' + AND ingest_request.link_source = 'unpaywall' + ) t1 + WHERE t1.domain != '' + AND t1.status = 'no-capture' + GROUP BY domain, status + ORDER BY COUNT DESC + LIMIT 30; + + => very few from any domain, interesting. Guess many of these are URLs that have truely never been crawled + + -- top no-capture base domains + SELECT domain, status, COUNT((domain, status)) + FROM ( + SELECT + ingest_file_result.ingest_type, + ingest_file_result.status, + substring(ingest_file_result.base_url FROM '[^/]+://([^/]*)') AS domain + FROM ingest_file_result + LEFT JOIN ingest_request + ON ingest_file_result.ingest_type = ingest_request.ingest_type + AND ingest_file_result.base_url = ingest_request.base_url + WHERE + ingest_file_result.ingest_type = 'pdf' + AND ingest_request.link_source = 'unpaywall' + ) t1 + WHERE t1.domain != '' + AND t1.status = 'no-capture' + GROUP BY domain, status + ORDER BY COUNT DESC + LIMIT 30; + + domain | status | count + ------------------------------+------------+-------- + academic.oup.com | no-capture | 429888 + www.nature.com | no-capture | 273825 + dergipark.org.tr | no-capture | 119847 + www.biodiversitylibrary.org | no-capture | 110220 + escholarship.org | no-capture | 106307 + onlinelibrary.wiley.com | no-capture | 89771 + journals.sagepub.com | no-capture | 79297 + www.cell.com | no-capture | 64242 + deepblue.lib.umich.edu | no-capture | 58080 + babel.hathitrust.org | no-capture | 52286 + hal.archives-ouvertes.fr | no-capture | 48549 + iopscience.iop.org | no-capture | 42591 + dash.harvard.edu | no-capture | 40767 + www.tandfonline.com | no-capture | 40638 + discovery.ucl.ac.uk | no-capture | 40633 + www.jstage.jst.go.jp | no-capture | 39780 + www.doiserbia.nb.rs | no-capture | 39261 + dspace.mit.edu | no-capture | 37703 + zookeys.pensoft.net | no-capture | 34562 + repositorio.unesp.br | no-capture | 34437 + ashpublications.org | no-capture | 34112 + www.cambridge.org | no-capture | 33959 + kclpure.kcl.ac.uk | no-capture | 31455 + society.kisti.re.kr | no-capture | 30427 + pure.mpg.de | no-capture | 27650 + download.atlantis-press.com | no-capture | 27253 + dialnet.unirioja.es | no-capture | 26886 + link.springer.com | no-capture | 26257 + www.valueinhealthjournal.com | no-capture | 24798 + dspace.library.uu.nl | no-capture | 23234 + (30 rows) + + -- top no-capture base domains + SELECT domain, status, COUNT((domain, status)) + FROM ( + SELECT + ingest_file_result.ingest_type, + ingest_file_result.status, + substring(ingest_file_result.base_url FROM '[^/]+://([^/]*)') AS domain + FROM ingest_file_result + LEFT JOIN ingest_request + ON ingest_file_result.ingest_type = ingest_request.ingest_type + AND ingest_file_result.base_url = ingest_request.base_url + WHERE + ingest_file_result.ingest_type = 'pdf' + AND ingest_request.link_source = 'unpaywall' + ) t1 + WHERE t1.domain != '' + AND t1.status = 'no-capture' + GROUP BY domain, status + ORDER BY COUNT DESC + LIMIT 30; + + domain | status | count + ------------------------------+------------+-------- + academic.oup.com | no-capture | 429888 + www.nature.com | no-capture | 273825 + dergipark.org.tr | no-capture | 119847 + www.biodiversitylibrary.org | no-capture | 110220 + escholarship.org | no-capture | 106307 + onlinelibrary.wiley.com | no-capture | 89771 + journals.sagepub.com | no-capture | 79297 + www.cell.com | no-capture | 64242 + deepblue.lib.umich.edu | no-capture | 58080 + babel.hathitrust.org | no-capture | 52286 + hal.archives-ouvertes.fr | no-capture | 48549 + iopscience.iop.org | no-capture | 42591 + dash.harvard.edu | no-capture | 40767 + www.tandfonline.com | no-capture | 40638 + discovery.ucl.ac.uk | no-capture | 40633 + www.jstage.jst.go.jp | no-capture | 39780 + www.doiserbia.nb.rs | no-capture | 39261 + dspace.mit.edu | no-capture | 37703 + zookeys.pensoft.net | no-capture | 34562 + repositorio.unesp.br | no-capture | 34437 + ashpublications.org | no-capture | 34112 + www.cambridge.org | no-capture | 33959 + kclpure.kcl.ac.uk | no-capture | 31455 + society.kisti.re.kr | no-capture | 30427 + pure.mpg.de | no-capture | 27650 + download.atlantis-press.com | no-capture | 27253 + dialnet.unirioja.es | no-capture | 26886 + link.springer.com | no-capture | 26257 + www.valueinhealthjournal.com | no-capture | 24798 + dspace.library.uu.nl | no-capture | 23234 + (30 rows) + + -- how many ingest requests not crawled at all? + SELECT count(*) + FROM ingest_request + LEFT JOIN ingest_file_result + ON ingest_file_result.ingest_type = ingest_request.ingest_type + AND ingest_file_result.base_url = ingest_request.base_url + WHERE + ingest_request.ingest_type = 'pdf' + AND ingest_request.link_source = 'unpaywall' + AND ingest_file_result.status IS NULL; + => 0 + + -- "cookie absent" terminal pages, by domain + SELECT domain, status, COUNT((domain, status)) + FROM ( + SELECT + ingest_file_result.ingest_type, + ingest_file_result.status, + substring(ingest_file_result.terminal_url FROM '[^/]+://([^/]*)') AS domain + FROM ingest_file_result + LEFT JOIN ingest_request + ON ingest_file_result.ingest_type = ingest_request.ingest_type + AND ingest_file_result.base_url = ingest_request.base_url + WHERE + ingest_file_result.ingest_type = 'pdf' + AND ingest_request.link_source = 'unpaywall' + AND ingest_file_result.terminal_url LIKE '%/cookieAbsent' + ) t1 + WHERE t1.domain != '' + AND t1.status != 'success' + AND t1.status != 'no-capture' + GROUP BY domain, status + ORDER BY COUNT DESC + LIMIT 30; + + domain | status | count + --------------------------------+----------------+-------- + journals.sagepub.com | no-pdf-link | 126295 + www.tandfonline.com | no-pdf-link | 116690 + pubs.acs.org | no-pdf-link | 94619 + www.ahajournals.org | no-pdf-link | 84016 + www.journal.csj.jp | no-pdf-link | 30881 + aip.scitation.org | wrong-mimetype | 22143 + www.vr-elibrary.de | no-pdf-link | 17436 + ajph.aphapublications.org | no-pdf-link | 15080 + asa.scitation.org | wrong-mimetype | 14261 + www.nrcresearchpress.com | no-pdf-link | 14253 + journals.ametsoc.org | no-pdf-link | 10500 + www.journals.uchicago.edu | no-pdf-link | 6917 + www.icevirtuallibrary.com | no-pdf-link | 6484 + www.journals.uchicago.edu | wrong-mimetype | 6191 + www.healthaffairs.org | no-pdf-link | 5732 + pubsonline.informs.org | no-pdf-link | 5672 + pinnacle-secure.allenpress.com | no-pdf-link | 5013 + www.worldscientific.com | no-pdf-link | 4560 + www.ajronline.org | wrong-mimetype | 4523 + ehp.niehs.nih.gov | no-pdf-link | 4514 + www.future-science.com | no-pdf-link | 4091 + pubs.acs.org | wrong-mimetype | 4015 + aip.scitation.org | no-pdf-link | 3916 + www.futuremedicine.com | no-pdf-link | 3821 + asa.scitation.org | no-pdf-link | 3644 + www.liebertpub.com | no-pdf-link | 3345 + physicstoday.scitation.org | no-pdf-link | 3005 + pubs.cif-ifc.org | no-pdf-link | 2761 + epubs.siam.org | wrong-mimetype | 2583 + www.ajronline.org | no-pdf-link | 2563 + (30 rows) + + -- "cookie absent" terminal pages, by domain + SELECT count(*) + FROM ingest_file_result + LEFT JOIN ingest_request + ON ingest_file_result.ingest_type = ingest_request.ingest_type + AND ingest_file_result.base_url = ingest_request.base_url + WHERE + ingest_file_result.ingest_type = 'pdf' + AND ingest_request.link_source = 'unpaywall' + AND ingest_file_result.status != 'success' + AND ingest_file_result.terminal_url LIKE '%/cookieAbsent'; + + => 654885 + + -- NOT "cookie absent" terminal page failures, total count + SELECT count(*) + FROM ingest_file_result + LEFT JOIN ingest_request + ON ingest_file_result.ingest_type = ingest_request.ingest_type + AND ingest_file_result.base_url = ingest_request.base_url + WHERE + ingest_file_result.ingest_type = 'pdf' + AND ingest_request.link_source = 'unpaywall' + AND ingest_file_result.status != 'success' + AND ingest_file_result.terminal_url NOT LIKE '%/cookieAbsent'; + + => 1403837 + +Looks like these domains are almost all "cookieAbsent" blocking: +- journals.sagepub.com +- pubs.acs.org +- ahajournals.org +- www.journal.csj.jp +- aip.scitation.org + +Grab some individual URLs to test: + + SELECT ingest_file_result.status, ingest_file_result.base_url, ingest_file_result.terminal_url + FROM ingest_file_result + LEFT JOIN ingest_request + ON ingest_file_result.ingest_type = ingest_request.ingest_type + AND ingest_file_result.base_url = ingest_request.base_url + WHERE + ingest_file_result.ingest_type = 'pdf' + AND ingest_request.link_source = 'unpaywall' + AND ingest_file_result.status != 'success' + AND ingest_file_result.terminal_url NOT LIKE '%/cookieAbsent' + ORDER BY updated DESC + LIMIT 25; + +NOT cookieAbsent testing with regular ingest tool: +- iopscience.iop.org, terminal-bad-status, SPNv2 fetch, success +- academic.oup.com => silverchair, terminal-bad-status, SPNv2 fetch, succes +- osf.io success + + SELECT ingest_file_result.status, ingest_file_result.base_url, ingest_file_result.terminal_url + FROM ingest_file_result + LEFT JOIN ingest_request + ON ingest_file_result.ingest_type = ingest_request.ingest_type + AND ingest_file_result.base_url = ingest_request.base_url + WHERE + ingest_file_result.ingest_type = 'pdf' + AND ingest_request.link_source = 'unpaywall' + AND ingest_file_result.status != 'success' + AND ingest_file_result.terminal_url LIKE '%/cookieAbsent' + ORDER BY updated DESC + LIMIT 25; + +cookieAbsent testing with regular ingest tool: +- www.tandfonline.com failure (no-pdf-link via wayback), but force-recrawl works + +The main distinguisher is status. terminal-bad-status can be ingested (live) +successfully, while no-pdf-link, redirect-loop, etc need to be re-crawled. + +## Heritrix Plan + +Generate following ingest request batches: + +- no-capture status from unpaywall +- all other failures except /cookieAbsent +- /cookieAbsent failures + +Plan will be to crawl no-capture first (to completion), then try the other +non-/cookieAbsent failures. /cookieAbsent means we'll need to use SPNv2. + +Because there are so few "no-capture on second hop" cases, will not enqueue +both terminal urls and base urls, only base urls. + +Should definitely skip/filter: + +- www.ncbi.nlm.nih.gov + +## Ingest Request Export + + COPY ( + SELECT row_to_json(ingest_request.*) FROM ingest_request + LEFT JOIN ingest_file_result + ON ingest_file_result.ingest_type = ingest_request.ingest_type + AND ingest_file_result.base_url = ingest_request.base_url + WHERE + ingest_request.ingest_type = 'pdf' + AND ingest_request.link_source = 'unpaywall' + AND ingest_file_result.status = 'no-capture' + ) TO '/grande/snapshots/unpaywall_nocapture_20200304.rows.json'; + => 4,855,142 + + COPY ( + SELECT row_to_json(ingest_request.*) FROM ingest_request + LEFT JOIN ingest_file_result + ON ingest_file_result.ingest_type = ingest_request.ingest_type + AND ingest_file_result.base_url = ingest_request.base_url + WHERE + ingest_request.ingest_type = 'pdf' + AND ingest_request.link_source = 'unpaywall' + AND ingest_file_result.status != 'success' + AND ingest_file_result.terminal_url NOT LIKE '%/cookieAbsent' + ) TO '/grande/snapshots/unpaywall_fail_nocookie_20200304.rows.json'; + => 1,403,837 + + ./scripts/ingestrequest_row2json.py /grande/snapshots/unpaywall_nocapture_20200304.rows.json > unpaywall_nocapture_20200304.json + ./scripts/ingestrequest_row2json.py /grande/snapshots/unpaywall_fail_nocookie_20200304.rows.json > unpaywall_fail_nocookie_20200304.json + +Note: will probably end up re-running the below after crawling+ingesting the above: + + COPY ( + SELECT row_to_json(ingest_request.*) FROM ingest_request + LEFT JOIN ingest_file_result + ON ingest_file_result.ingest_type = ingest_request.ingest_type + AND ingest_file_result.base_url = ingest_request.base_url + WHERE + ingest_request.ingest_type = 'pdf' + AND ingest_request.link_source = 'unpaywall' + AND ingest_file_result.status != 'success' + AND ingest_file_result.status = 'terminal-bad-status' + AND ingest_file_result.terminal_url LIKE '%/cookieAbsent' + ) TO '/grande/snapshots/unpaywall_fail_cookie_badstatus_20200304.rows.json'; + => 0 + + COPY ( + SELECT row_to_json(ingest_request.*) FROM ingest_request + LEFT JOIN ingest_file_result + ON ingest_file_result.ingest_type = ingest_request.ingest_type + AND ingest_file_result.base_url = ingest_request.base_url + WHERE + ingest_request.ingest_type = 'pdf' + AND ingest_request.link_source = 'unpaywall' + AND ingest_file_result.status != 'success' + AND ingest_file_result.status != 'terminal-bad-status' + AND ingest_file_result.terminal_url LIKE '%/cookieAbsent' + ) TO '/grande/snapshots/unpaywall_fail_cookie_other_20200304.rows.json'; + => 654,885 + |