aboutsummaryrefslogtreecommitdiffstats
path: root/notes/ingest/2020-02_unpaywall.md
blob: e18a2fff3e273cdf5116da1ed7f2e2dcc4a56935 (plain)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
518
519
520
521
522
523
524
525
526
527
528
529
530
531
532
533
534
535
536
537
538
539
540
541
542
543
544
545
546
547
548
549
550
551
552
553
554
555
556
557
558
559
560
561
562
563
564
565
566
567
568
569
570
571
572
573
574
575
576
577
578
579
580
581
582
583
584
585
586
587
588
589
590
591
592
593
594
595
596
597
598
599
600
601
602
603
604
605
606
607
608
609
610
611
612
613
614
615
616
617
618
619
620
621
622
623
624

## Stats and Things

    zcat unpaywall_snapshot_2019-11-22T074546.jsonl.gz | jq .oa_locations[].url_for_pdf -r | rg -v ^null | cut -f3 -d/ | sort | uniq -c | sort -nr > top_domains.txt

## Transform

    zcat unpaywall_snapshot_2019-11-22T074546.jsonl.gz | ./unpaywall2ingestrequest.py - | pv -l > /dev/null
    => 22M 1:31:25 [   4k/s]

Shard it into batches of roughly 1 million (all are 1098096 +/- 1):

    zcat unpaywall_snapshot_2019-11-22.ingest_request.shuf.json.gz | split -n r/20 -d - unpaywall_snapshot_2019-11-22.ingest_request.split_ --additional-suffix=.json

Test ingest:

    head -n200 unpaywall_snapshot_2019-11-22.ingest_request.split_00.json | kafkacat -P -b wbgrp-svc263.us.archive.org -t sandcrawler-prod.ingest-file-requests-bulk -p -1

Add a single batch like:

    cat unpaywall_snapshot_2019-11-22.ingest_request.split_00.json | kafkacat -P -b wbgrp-svc263.us.archive.org -t sandcrawler-prod.ingest-file-requests-bulk -p -1

## Progress/Status

There are 21,961,928 lines total, in batches of 1,098,097.

    unpaywall_snapshot_2019-11-22.ingest_request.split_00.json
        => 2020-02-24 21:05 local: 1,097,523    ~22 results/sec (combined)
        => 2020-02-25 10:35 local: 0
    unpaywall_snapshot_2019-11-22.ingest_request.split_01.json
    unpaywall_snapshot_2019-11-22.ingest_request.split_02.json
    unpaywall_snapshot_2019-11-22.ingest_request.split_03.json
    unpaywall_snapshot_2019-11-22.ingest_request.split_04.json
        => 2020-02-25 11:26 local: 4,388,997
        => 2020-02-25 10:14 local: 1,115,821
        => 2020-02-26 16:00 local:   265,116
    unpaywall_snapshot_2019-11-22.ingest_request.split_05.json
    unpaywall_snapshot_2019-11-22.ingest_request.split_06.json
    unpaywall_snapshot_2019-11-22.ingest_request.split_07.json
    unpaywall_snapshot_2019-11-22.ingest_request.split_08.json
    unpaywall_snapshot_2019-11-22.ingest_request.split_09.json
        => 2020-02-26 16:01 local: 6,843,708
        => 2020-02-26 16:31 local: 4,839,618
        => 2020-02-28 10:30 local: 2,619,319
    unpaywall_snapshot_2019-11-22.ingest_request.split_10.json
    unpaywall_snapshot_2019-11-22.ingest_request.split_11.json
    unpaywall_snapshot_2019-11-22.ingest_request.split_12.json
    unpaywall_snapshot_2019-11-22.ingest_request.split_13.json
    unpaywall_snapshot_2019-11-22.ingest_request.split_14.json
    unpaywall_snapshot_2019-11-22.ingest_request.split_15.json
    unpaywall_snapshot_2019-11-22.ingest_request.split_16.json
    unpaywall_snapshot_2019-11-22.ingest_request.split_17.json
    unpaywall_snapshot_2019-11-22.ingest_request.split_18.json
    unpaywall_snapshot_2019-11-22.ingest_request.split_19.json
        => 2020-02-28 10:50 local: 13,551,887
        => 2020-03-01 23:38 local:  4,521,076
        => 2020-03-02 10:45 local:  2,827,071
        => 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

## Batch Ingest

Test small batch:

    head -n200 /grande/snapshots/unpaywall_nocapture_20200304.rows.json | jq . -c | kafkacat -P -b wbgrp-svc263.us.archive.org -t sandcrawler-prod.ingest-file-requests-bulk -p -1

Full batch:

    cat /grande/snapshots/unpaywall_nocapture_20200304.rows.json | jq . -c | kafkacat -P -b wbgrp-svc263.us.archive.org -t sandcrawler-prod.ingest-file-requests-bulk -p -1

    # there was a broken line in there, so...
    # parse error: Expected separator between values at line 1367873, column 175
    # tail -n+1367875 /grande/snapshots/unpaywall_nocapture_20200304.rows.json | rg -v "\\\\" | jq . -c > /dev/null
    tail -n+1367875 /grande/snapshots/unpaywall_nocapture_20200304.rows.json | rg -v "\\\\" | jq . -c | kafkacat -P -b wbgrp-svc263.us.archive.org -t sandcrawler-prod.ingest-file-requests-bulk -p -1

Note that the crawl is not entirely complete and not all CDX seem to have been
loaded, so may need to iterate. About 10% are still "no capture". May want or
need to additionally crawl the terminal URLs, not the base URLs.

## Post-ingest stats

Overall status:

    SELECT ingest_file_result.status, 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'
    GROUP BY status
    ORDER BY COUNT DESC
    LIMIT 20;

             status          |  count
    -------------------------+----------
     success                 | 17354494
     no-pdf-link             |  1471076
     no-capture              |  1135992
     redirect-loop           |   837842
     terminal-bad-status     |   803081
     cdx-error               |   219746
     wrong-mimetype          |   100723
     link-loop               |    16013
     wayback-error           |    12448
     null-body               |     9444
     redirects-exceeded      |      600
     petabox-error           |      411
     bad-redirect            |       17
     bad-gzip-encoding       |        4
     spn2-cdx-lookup-failure |        3
     gateway-timeout         |        1
     spn2-error:job-failed   |        1
     spn2-error              |        1
    (18 rows)

Failures 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'
    ) 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
    -----------------------------------+---------------------+--------
     academic.oup.com                  | no-pdf-link         | 330211
     watermark.silverchair.com         | terminal-bad-status | 324599
     www.tandfonline.com               | no-pdf-link         | 242724
     journals.sagepub.com              | no-pdf-link         | 202050
     iopscience.iop.org                | terminal-bad-status | 144063
     files-journal-api.frontiersin.org | terminal-bad-status | 121719
     pubs.acs.org                      | no-pdf-link         | 104535
     www.ahajournals.org               | no-pdf-link         | 102653
     society.kisti.re.kr               | no-pdf-link         | 101787
     www.degruyter.com                 | redirect-loop       |  95130
     www.nature.com                    | redirect-loop       |  87534
     onlinelibrary.wiley.com           | no-pdf-link         |  84432
     www.cell.com                      | redirect-loop       |  61496
     www.degruyter.com                 | terminal-bad-status |  42919
     babel.hathitrust.org              | terminal-bad-status |  41813
     www.ncbi.nlm.nih.gov              | redirect-loop       |  40488
     scialert.net                      | no-pdf-link         |  38341
     ashpublications.org               | no-pdf-link         |  34889
     dialnet.unirioja.es               | terminal-bad-status |  32076
     www.journal.csj.jp                | no-pdf-link         |  30881
     pure.mpg.de                       | redirect-loop       |  26163
     www.jci.org                       | redirect-loop       |  24701
     espace.library.uq.edu.au          | redirect-loop       |  24591
     www.valueinhealthjournal.com      | redirect-loop       |  23740
     www.vr-elibrary.de                | no-pdf-link         |  23332
     aip.scitation.org                 | wrong-mimetype      |  22144
     osf.io                            | redirect-loop       |  18513
     www.journals.elsevier.com         | no-pdf-link         |  16710
     www.spandidos-publications.com    | redirect-loop       |  15711
     www.biorxiv.org                   | wrong-mimetype      |  15513
    (30 rows)

Dump lists for another iteration of bulk ingest:

    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_20200323.rows.json';
    => 278,876

    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_20200323.rows.json';
    =>


    ./scripts/ingestrequest_row2json.py /grande/snapshots/unpaywall_nocapture_20200323.rows.json > unpaywall_nocapture_20200323.json

    cat unpaywall_nocapture_20200323.json | rg -v "\\\\" | jq . -c | kafkacat -P -b wbgrp-svc263.us.archive.org -t sandcrawler-prod.ingest-file-requests-bulk -p -1