notes on file_meta task (from august)

1 files changed, 66 insertions, 0 deletions

diff --git a/notes/tasks/2020-08-20_file_meta.md b/notes/tasks/2020-08-20_file_meta.md
new file mode 100644
index 0000000..39c84dd
--- /dev/null
+++ b/notes/tasks/2020-08-20_file_meta.md
@@ -0,0 +1,66 @@
+
+Want to update fatcat file entities with "full" file metadata for those which are missing it.
+
+How many `file_meta` rows *still* don't have metadata?
+
+    SELECT COUNT(*) FROM file_meta WHERE sha256hex IS NULL;
+    => 62962
+
+First generate list of sha1hex from most recent bulk export which are missing
+at least some metadata (based on missing sha256):
+
+    zcat file_hashes.tsv.gz | rg '\t\t' | cut -f3 | sort -u -S 4G | pv -l > fatcat_file_partial_sha1hex.tsv
+    => 18.7M 0:05:46 [53.8k/s]
+
+Then dump the entire sandcrawler `file_meta` table as TSV, with first column
+sha1hex and second column JSON with all the file metadata fields:
+
+    COPY (
+      SELECT sha1hex, row_to_json(file_meta)
+      FROM file_meta
+      WHERE sha256hex IS NOT NULL
+      ORDER BY sha1hex ASC
+    )
+    TO '/grande/snapshots/file_meta_dump.tsv'
+    WITH NULL '';
+
+Join/cut:
+
+    export LC_ALL=C
+    join -t$'\t' fatcat_file_partial_sha1hex.tsv /grande/snapshots/file_meta_dump.tsv | uniq -w 40 | cut -f2 | pv -l > fatcat_file_partial.file_meta.json
+    => 18.1M 0:03:37 [83.2k/s]
+
+Check counts:
+
+    cat fatcat_file_partial.file_meta.json | jq .sha1hex -r | sort -u -S 4G | wc -l
+    => 18135313
+
+    zcat fatcat_file_partial.file_meta.json.gz | jq .mimetype -r | sort -S 4G | uniq -c | sort -nr
+    18103860 application/pdf
+      29977 application/octet-stream
+        876 text/html
+        199 application/postscript
+        171 application/gzip
+         84 text/plain
+         48 application/xml
+         38 application/vnd.ms-powerpoint
+         16 application/msword
+          8 application/vnd.openxmlformats-officedocument.wordprocessingml.document
+          6 image/jpeg
+          4 message/rfc822
+          4 application/zip
+          4 application/vnd.openxmlformats-officedocument.presentationml.presentation
+          3 text/x-tex
+          3 application/x-dosexec
+          2 application/x-tar
+          2 application/vnd.ms-tnef
+          1 video/mpeg
+          1 image/tiff
+          1 image/svg+xml
+          1 image/png
+          1 image/gif
+          1 audio/x-ape
+          1 application/vnd.ms-office
+          1 application/CDFV2-unknown
+
+TODO: fatcat importer