File size: 5,558 Bytes
2a6941d
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
#!/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