#!/usr/bin/env bash # Export the ledger to Parquet for publication (Hugging Face dataset). # # scripts/export.sh [DB] [OUT] # # Requires the duckdb CLI. Output is privacy-safe: natural persons (sole # traders, farm heads, named individuals) appear without name, ID or # address; applicants are identified by a keyed hash (applicant_id); # contacts appear only as keyed hashes, and only when two or more # different firms share them. set -euo pipefail DB=${1:-data/eacl.db} OUT=${2:-dist/hf} [ -f "$DB" ] || { echo "no database at $DB" >&2; exit 1; } DB=$(realpath "$DB") mkdir -p "$OUT/data" OUT=$(realpath "$OUT") rm -rf "$OUT/data"/* PQ="FORMAT parquet, COMPRESSION zstd, ROW_GROUP_SIZE 250000, FILE_SIZE_BYTES '400MB', FILENAME_PATTERN 'part-{i}', OVERWRITE_OR_IGNORE" duckdb <= 2 ) SELECT c.hash AS contact_hash, c.channel, c.owner_kind, CASE WHEN c.owner_kind = 'applicant' THEN a.public_id ELSE m.public_id END AS owner_id FROM l.contacts c JOIN shared s ON s.hash = c.hash LEFT JOIN l.applicants a ON c.owner_kind = 'applicant' AND a.applicant_key = c.owner_key LEFT JOIN l.manufacturers m ON c.owner_kind = 'manufacturer' AND m.mfr_key = c.owner_key ORDER BY contact_hash ) TO '$OUT/data/shared_contacts' ($PQ); COPY (SELECT doc_key, TRY_CAST(day AS DATE) AS day, event, status_from, status_to, source FROM l.events ORDER BY day, id) TO '$OUT/data/events' ($PQ); COPY (SELECT * FROM l.chpl ORDER BY hs6) TO '$OUT/data/ref_chpl' ($PQ); COPY (SELECT * FROM l.brands ORDER BY brand) TO '$OUT/data/ref_brands' ($PQ); COPY (SELECT * FROM l.countries ORDER BY iso2) TO '$OUT/data/ref_countries' ($PQ); COPY (SELECT code, name, ua_occupied::BOOLEAN AS ua_occupied FROM l.regions ORDER BY code) TO '$OUT/data/ref_regions' ($PQ); COPY (SELECT file, kind, day, started_at, finished_at, records, inserted, updated, skipped, decode_errs, vanished FROM l.ingests ORDER BY id) TO '$OUT/data/ingests' ($PQ); SQL echo "exported to $OUT/data" du -sh "$OUT/data"/* | sort -h