referential integrity checks?

[tmtmtmtm]

If I have one CSV that includes a jurisdiction column, is there currently a way to validate that all the values in that column have a matching entry in a jurisdictions.csv file?

I can do that as a one-off check with qsv join --left-anti, but I'm not familiar enough with JSON Schema to be able to work out if qsv validate can handle this sort of integrity checking.

[a5dur]

qsv join --left-anti should do the job, i'm not sure if qsv validate (which uses JSON schema) is suitable for this (value based) type of cross-file validation, it can still be useful for validating the structure and content of individual CSV files.

You can use it to ensure that all fields in the csv file are of the correct type, within specified ranges, or match certain patterns.

Although you can try creating a JSON Schema file from jurisdictions.csv file using the qsv schema command. you might need to fine tune it with the jurisdiction values

See https://github.com/jqnatividad/qsv/blob/master/src/cmd/validate.rs#L2
and https://json-schema.org/draft/2020-12/json-schema-validation.html

[jqnatividad]

The validate command is designed for high speed validation - if the jurisdictions domain is fairly static, encoding them as enums in JSONschema is the way to go.

Just start with a "training" CSV to run the schema command against to produce the starter JSONschema and modify it accordingly. You can then use the validate command against CSVs with an identical schema.

qsv/scripts/NYC_311_SR_2010-2020-sample-1M.csv.schema.json

Lines 30 to 67 in 1efa6b5

	"Agency": {
	"description": "Agency column from NYC_311_SR_2010-2020-sample-1M.csv",
	"minLength": 3,
	"maxLength": 42,
	"type": [
	"string"
	],
	"enum": [
	"3-1-1",
	"ACS",
	"DCA",
	"DCAS",
	"DCP",
	"DEP",
	"DFTA",
	"DHS",
	"DOB",
	"DOE",
	"DOF",
	"DOHMH",
	"DOITT",
	"DORIS",
	"DOT",
	"DPR",
	"DSNY",
	"DVS",
	"EDC",
	"FDNY",
	"HPD",
	"HRA",
	"MAYOR�S OFFICE OF SPECIAL ENFORCEMENT",
	"MOC",
	"NYCEM",
	"NYPD",
	"TAX",
	"TLC"
	]
	},

If the jurisdictions domain is dynamic, you may want to look into using the luau command's lookup table functions.

I have an extended example at https://github.com/dathere/qsv-lookup-tables.

[tmtmtmtm]

Thanks for the examples. What I think I'm looking for is a way for the enum to be a little more dynamic, e.g pointing at a column of another CSV, rather than a hard-coded list in the JSON. If that's not possible, then I guess I could always dynamically update the schema with those values before calling qsv validate using something like:

jq --argjson vals "$(
  qsv select id jurisdictions.csv | qsv behead | qsv fmt --quote-always | jq -s .
)" '.properties.jurisdiction |= $vals' schema.json | sponge schema.json

The luau lookup table approach seems intriguing, but that sales tax example, whilst really useful to show some of the more involved possibilities, makes it quite hard for me to imagine what that could look like for a much simpler version. How could that be used to verify that the State column in data.csv matched an Abbreviation from the states table?

[jqnatividad]

Now that the jsonschema crate supports custom keywords, I'm considering adding a dynlookup keyword to allow validating a field against a CSV - with the CSV being either remote (http/s scheme supported) or in the local filesystem.

Though I'm sure we can have a full-blown luau script to do custom validation, it's not as convenient as JSONschema validation.

WDYT?

[tmtmtmtm]

Oh, that sounds fantastic, and would be very useful!

[jqnatividad]

validate now supports the dynenum custom keyword for validating against a local CSV or on a URL (http/https schemes supported). 😄

#2166

For an example, see:

qsv/scripts/benchmark_data-dynenum.csv.schema.json

Lines 32 to 40 in 8ded63a

	"Agency": {
	"description": "Agency column using qsv's custom keyword - dynenum, for dynamic enumeration validation",
	"minLength": 3,
	"maxLength": 42,
	"type": [
	"string"
	],
	"dynenum": "NYC_agencies.csv"
	},

https://github.com/jqnatividad/qsv/blob/master/scripts/NYC_agencies.csv

Contrast that with using hardcoded enums:

qsv/scripts/NYC_311_SR_2010-2020-sample-1M.csv.schema.json

Lines 30 to 67 in 8ded63a

	"Agency": {
	"description": "Agency column from NYC_311_SR_2010-2020-sample-1M.csv",
	"minLength": 3,
	"maxLength": 42,
	"type": [
	"string"
	],
	"enum": [
	"3-1-1",
	"ACS",
	"DCA",
	"DCAS",
	"DCP",
	"DEP",
	"DFTA",
	"DHS",
	"DOB",
	"DOE",
	"DOF",
	"DOHMH",
	"DOITT",
	"DORIS",
	"DOT",
	"DPR",
	"DSNY",
	"DVS",
	"EDC",
	"FDNY",
	"HPD",
	"HRA",
	"MAYOR�S OFFICE OF SPECIAL ENFORCEMENT",
	"MOC",
	"NYCEM",
	"NYPD",
	"TAX",
	"TLC"
	]
	},