aboutsummaryrefslogtreecommitdiffstats
path: root/sql
diff options
context:
space:
mode:
Diffstat (limited to 'sql')
-rw-r--r--sql/dump_unextracted_pdf.sql11
-rw-r--r--sql/dump_unextracted_pdf_petabox.sql18
-rw-r--r--sql/dump_ungrobid_pdf.sql13
-rw-r--r--sql/dump_ungrobid_pdf_petabox.sql17
4 files changed, 49 insertions, 10 deletions
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;