aboutsummaryrefslogtreecommitdiffstats
path: root/notes
diff options
context:
space:
mode:
Diffstat (limited to 'notes')
-rw-r--r--notes/ingest/2020-02-14_unpaywall_ingest.md416
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
+