Recipe CKAN Integration - dathere/qsv GitHub Wiki

Recipe: CKAN Integration

Tier: Intermediate Commands used: safenames, applydp (in qsvdp), to postgres, to sqlite, jsonl, sniff, validate, describegpt Anchor dataset: any CKAN instance — examples use demo.ckan.org, catalog.data.gov, data.cnra.ca.gov

Problem

You manage a CKAN data portal — or you ingest data from one. qsv has a CKAN-aware integration surface that handles:

pulling dataset / resource / user / org metadata into CSVs
preparing CSVs for the CKAN Datastore (safe column names, reserved fields, length caps)
pushing CSVs into Postgres for DataPusher+ ingestion
validating CSVs against schemas before publishing
generating data dictionaries

This recipe expands the original Cookbook entry. The original short snippets are preserved at the bottom of Cookbook.

Data

A live CKAN instance plus ckanapi (Python) and jq installed:

pipx install ckanapi
brew install jq      # or apt install jq

Solution — pull metadata FROM CKAN

1. Dump all datasets / users / groups / orgs as CSV

ckanapi -r https://demo.ckan.org dump datasets --all      | qsv jsonl > datasets.csv
ckanapi -r https://demo.ckan.org dump users    --all      | qsv jsonl > users.csv
ckanapi -r https://demo.ckan.org dump groups   --all      | qsv jsonl > groups.csv
ckanapi -r https://demo.ckan.org dump organizations --all | qsv jsonl > organizations.csv

ckanapi emits JSONL; qsv jsonl flattens it to CSV.

2. Get the resource list for a single dataset

ckanapi -r https://catalog.data.gov action package_show \
  id=low-altitude-aerial-imagery-obtained-with-unmanned-aerial-systems-uas-flights-over-black-beach \
  | jq -c '.resources[]' \
  | qsv jsonl > resources.csv

3. Download the latest CSV of a named resource and run stats on it

ckanapi -r https://data.cnra.ca.gov action package_show id="wellstar-oil-and-gas-wells1" \
  > wellstar.json

cat wellstar.json \
  | jq -c '.resources[] | select(.name=="CSV") | .url' \
  | xargs -L 1 wget -O wellstar.csv

qsv stats --everything wellstar.csv > wellstar-stats.csv

4. Health-check a whole CKAN catalog without downloading every resource

# resources.csv has columns: id, name, url, format, ...
qsv select url resources.csv \
  | qsv behead \
  | xargs -I {} qsv sniff --no-infer --json {} > catalog_health.jsonl

sniff --no-infer returns just the MIME type, content length, and last-modified — fast even for stale or moved resources.

Solution — push CSVs TO CKAN

5. Make column names CKAN-Datastore-safe

qsv safenames raw_export.csv > step1.csv

safenames enforces:

lowercase, snake_case, alphanumeric + underscore only
≤ 60 bytes (snapped to UTF-8 character boundary)
duplicates get numeric suffixes
columns named _id (reserved by CKAN) are renamed to reserved__id
columns starting with _ get an unsafe_ prefix

qsv safenames --mode V raw_export.csv     # audit mode, no rewrite
# stderr: 4 unsafe header/s: ["12_col", "Col with Spaces!", "", "_id"]

6. Clean values with `applydp` (DataPusher+ optimized)

If you're inside the qsvdp variant (the slim DataPusher+ build):

qsvdp applydp operations trim,lower email step1.csv > step2.csv

If you're using the full qsv binary, use apply with the same operations:

qsv apply operations trim,lower email step1.csv > step2.csv

There is no cast operation in apply/applydp. To coerce a column's type, use schema/validate or an sqlp CAST(...) expression. A string-to-date CAST fails the whole query on a value it can't parse; TRY_CAST(...) nulls that row instead. Format inference has no month/day/year pattern, so use STRPTIME for US-style dates.

7. Load into Postgres for DataPusher+

qsv to postgres 'postgresql://datapusher:secret@localhost:5432/datastore' step2.csv
# Creates a table named after the CSV file stem

DataPusher+ takes it from there.

8. Generate a Frictionless Data Package

qsv to datapackage --stats datapackage.json step2.csv

--stats embeds qsv stats output in the Data Package descriptor — gives downstream consumers schema + range information without an extra round-trip.

9. Generate CKAN + DCAT-US metadata with `profile`

# ckan block (what DataPusher+ consumes) + a DCAT-US v3 Catalog, validated against GSA's JSON Schema
qsv profile step2.csv --validate --catalog -o step2.metadata.json

# data.gov-bound: fail instead of warn when a Required field is missing
qsv profile step2.csv --validate --strict --catalog \
  --initial-context dcat-init-context.json -o step2.metadata.json

A bare CSV can't supply description, contactPoint or identifier, so --strict refuses it until you seed them with --initial-context (or a --spec). Without --strict, the gaps are listed in projection_warnings. dcat-init-context.json is a fully populated example to copy. See Metadata & Profiling.

As of 24.0.0, the projection uses canonical unprefixed DCAT-US v3 keys (the ckan block is unchanged), and with the seed file above it passes --strict. On 23.0.1 and earlier the same run is rejected, for value shapes 24.0.0 fixes (rights, inSeries, accrualPeriodicity) — upgrade before a data.gov submission, or run without --strict and review projection_warnings.

Solution — generate a CKAN-ready data dictionary

qsv describegpt step2.csv \
  --all \
  --tag-vocab ckan_tag_vocabulary.csv \
  -u http://localhost:11434/v1 \
  --model deepseek-r1:14b \
  > step2_dictionary.md

--all produces description + tags + dictionary
--tag-vocab constrains tags to a curated CKAN-aligned vocabulary
Local Ollama keeps sensitive data on-premise

See AI & Documentation → describegpt for prompt customization.

Variations

Validate before publishing

qsv schema step2.csv         # produces step2.csv.schema.json
# Edit the schema to tighten rules, add dynamicEnum lookups, ...
qsv validate step2.csv step2.csv.schema.json

See Recipe: JSON Schema Validation.

Use `dynamicEnum` against a CKAN resource

In a schema, validate that a agency column only contains values that appear in a CKAN-hosted lookup CSV:

{
  "properties": {
    "agency": {
      "type": "string",
      "dynamicEnum": "ckan://nyc-agencies-resource-id"
    }
  }
}

qsv resolves the ckan:// URL via the CKAN action API. See Lookup Tables.

`sniff` as a CKAN harvester accelerator

Use sniff --no-infer against every CKAN resource URL to detect dead links, MIME type mismatches, and content-length regressions — without downloading anything.

qsv sniff --no-infer https://example.com/data.xlsx

Performance notes

safenames runs in O(headers) — instantaneous regardless of file size.
applydp / apply are streaming.
to postgres uses Postgres's COPY FROM under the hood — millions of rows per minute on a local DB.
For CKAN catalog health-checks at scale, parallelize with xargs -P 8 against sniff --no-infer.