eac-ledger / scripts /export.sh
runitx's picture
Claude Opus 5.5
eac-ledger: ledger of the EAEU register of conformity documents
2a6941d unverified
Raw History Blame Contribute Delete
5.56 kB
#!/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 <<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