Download scripts/export.sh from abr00kx/eac-ledger: direct link, hf CLI and curl.
- Browser
- Download file 5.56 kB
-
https://huggingface.co/abr00kx/eac-ledger/resolve/main/scripts/export.sh
- Command line
-
hf download hf://abr00kx/eac-ledger/scripts/export.sh
-
curl -L -o export.sh https://huggingface.co/abr00kx/eac-ledger/resolve/main/scripts/export.sh
5.56 kB
| # 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 <<SQL | |
| INSTALL sqlite; LOAD sqlite; | |
| SET threads TO 8; | |
| SET preserve_insertion_order = false; | |
| ATTACH '$DB' AS l (TYPE sqlite, READ_ONLY); | |
| COPY ( | |
| SELECT d.doc_key, d.doc_id, d.issuing_country, d.kind, d.kind_code, | |
| TRY_CAST(d.start_date AS DATE) AS start_date, | |
| TRY_CAST(d.validity_date AS DATE) AS validity_date, | |
| d.status_code, | |
| CASE d.status_code WHEN '01' THEN 'valid' WHEN '02' THEN 'suspended' WHEN '03' THEN 'terminated' | |
| WHEN '04' THEN 'extended' WHEN '05' THEN 'resumed' WHEN '09' THEN 'expired' ELSE NULL END AS status, | |
| TRY_CAST(d.status_start AS DATE) AS status_start, | |
| TRY_CAST(d.status_end AS DATE) AS status_end, | |
| d.status_note, d.object_kind, d.tech_regs, d.schemes, | |
| a.public_id AS applicant_id, a.country AS applicant_country, a.region AS applicant_region, | |
| d.authority_id, d.authority_name, d.authority_country, | |
| d.foreign_mfr::BOOLEAN AS foreign_manufacturer, | |
| d.coalition_mfr::BOOLEAN AS coalition_manufacturer, | |
| d.chp_tier, d.single_list::BOOLEAN AS single_list, d.version, | |
| TRY_CAST(d.updated_at AS TIMESTAMP) AS updated_at, | |
| TRY_CAST(d.first_seen AS DATE) AS first_seen, | |
| TRY_CAST(d.last_seen AS DATE) AS last_seen, | |
| TRY_CAST(d.vanished_on AS DATE) AS vanished_on, | |
| d.deleted::BOOLEAN AS deleted | |
| FROM l.docs d LEFT JOIN l.applicants a ON a.applicant_key = d.applicant_key | |
| ORDER BY start_date, doc_key | |
| ) TO '$OUT/data/documents' ($PQ); | |
| COPY ( | |
| SELECT a.public_id AS applicant_id, a.country, | |
| CASE WHEN a.natural_person = 1 THEN NULL ELSE a.name END AS name, | |
| CASE WHEN a.natural_person = 1 THEN NULL ELSE a.name_norm END AS name_norm, | |
| a.natural_person::BOOLEAN AS natural_person, | |
| a.id_kind, | |
| CASE WHEN a.natural_person = 1 THEN NULL ELSE a.id_value END AS id_value, | |
| a.region, r.name AS region_name, COALESCE(r.ua_occupied, 0)::BOOLEAN AS ua_occupied, | |
| CASE WHEN a.natural_person = 1 THEN NULL ELSE a.address END AS address | |
| FROM l.applicants a LEFT JOIN l.regions r ON r.code = a.region | |
| ORDER BY applicant_id | |
| ) TO '$OUT/data/applicants' ($PQ); | |
| COPY ( | |
| SELECT m.public_id AS manufacturer_id, | |
| CASE WHEN m.natural_person = 1 THEN NULL ELSE m.name END AS name, | |
| CASE WHEN m.natural_person = 1 THEN NULL ELSE m.name_norm END AS name_norm, | |
| m.natural_person::BOOLEAN AS natural_person, m.country, c.grp AS country_group | |
| FROM l.manufacturers m LEFT JOIN l.countries c ON c.iso2 = m.country | |
| ORDER BY manufacturer_id | |
| ) TO '$OUT/data/manufacturers' ($PQ); | |
| COPY (SELECT dm.doc_key, m.public_id AS manufacturer_id FROM l.doc_manufacturers dm JOIN l.manufacturers m ON m.mfr_key = dm.mfr_key ORDER BY dm.doc_key) | |
| TO '$OUT/data/document_manufacturers' ($PQ); | |
| COPY (SELECT doc_key, idx, name, trade_names, text FROM l.doc_products ORDER BY doc_key, idx) | |
| TO '$OUT/data/products' ($PQ); | |
| COPY (SELECT h.doc_key, h.hs, h.hs6, c.tier AS chp_tier FROM l.doc_hs h LEFT JOIN l.chpl c ON c.hs6 = h.hs6 ORDER BY h.doc_key, h.hs) | |
| TO '$OUT/data/hs_codes' ($PQ); | |
| COPY (SELECT doc_key, brand, source FROM l.doc_brands ORDER BY doc_key, brand) | |
| TO '$OUT/data/brand_matches' ($PQ); | |
| COPY (SELECT doc_key, idx, lab_name, accreditation, evidence_id, TRY_CAST(evidence_date AS DATE) AS evidence_date, evidence_name | |
| FROM l.doc_labs ORDER BY doc_key, idx) | |
| TO '$OUT/data/evidence' ($PQ); | |
| COPY ( | |
| WITH shared AS ( | |
| SELECT hash FROM l.contacts GROUP BY hash HAVING COUNT(DISTINCT owner_kind || owner_key) >= 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 | |