swirrl / table2qb Goto Github PK
View Code? Open in Web Editor NEWA generic pipeline for converting tabular data into rdf data cubes
License: Eclipse Public License 1.0
A generic pipeline for converting tabular data into rdf data cubes
License: Eclipse Public License 1.0
Create and package a table2qb
shell script for running the CLI which wraps the java -jar path/to/jar
invocation.
One thing I picked up reading the w3c csvw stuff is URI Templating: https://tools.ietf.org/html/rfc6570. I think this could be useful to us in a couple of ways...
Most obviously for grafter... there's a clojure implementation here we might adopt https://github.com/mwkuster/uritemplate-clj. e.g.
(uritemplate-clj.core/uritemplate "http://pmd.com/data/area{/gss}/period{/date}{/measure}{/unit}"
{ "gss" "E010000001"
"date" "2017"
"measure" "count"
"unit" "people" })
#=> "http://pmd.com/data/area/E010000001/period/2017/count/people"
We could simply use it like a labelled add-path-segments
or even extend further because the template would know which fields it needs to build a URI so it would a) not need to be told which fields to use and b) be able to validate and catch missing-slug bugs. Maybe we could even do compile-time checks?
Another possibility is for making URIs shorter. If we publish the template e.g. as part of a vocabulary, then we wouldn't necessarily have to explain the segments within in the URI. The above case would become http://pmd.com/data/{/gss}{/date}{/measure}{/unit}
.
With #27 we're now passing the json metadata to csv2rdf with an in-memory map (rather than writing it out to disk). This is designed to make the whole process more memory efficient when called via grafter-server/ pmd.
Ideally we would also support the original use case - so that people could use table2qb as a standalone, writing the csvw output (i.e json and csv files). This would allow users to pick a different csv2rdf implementation, or to target csvw itself.
Currently, CSV observation data submitted to table2qb online pipelines is via HTTP POST and multipart/form-data.
It would be good to have the option to pass a URL to table2qb and have the CSV observation data fetched by HTTP GET.
This would allow:
Several functions hard-coded column headers to make the example work. These ought to be replaced with headers looked-up from the column configuration:
is-dimension?
, is-attribute?
, values
and measures
for determining column rolestandardise-measure
, slugise-columns
, and replace-symbols
for doing column-specific cleaning magic (hopefully #18 can resolve this)observation-template
for the ordering of component slugs in the URIGeneral
Deriving URIs
Import vs update with / without PMD
dataset a qb:DataSet
triple).Dataset URIs
in order to namespace entity URIs we require a slug (which we have so far found by querying the dataset name).
Create travis build for running tests, building uberjar and creating releases.
test issue for now...
Lines 10 to 30 in 30e4ae5
With #25 we're no longer deleting the temporary files that are used to record the intermediate representation that table2qb passes to csv2rdf.
Since these are required until the complete sequence of output quads has been created (and table2qb doesn't control that), we don't really know when it's safe to delete these files.
Create a release for the 'legacy' version of table2qb
which contains an embedded columns.csv
specifying the column configuration for ONS.
component->property-uri
and columns.property_template
(user needs to know URI convention, can we make this explicit?)https://www.w3.org/TR/tabular-data-model/
Of particular note is the overall json schema and URI templating approach. There may be re-usable components and there's probably a benefit to standardising where relevant.
Move each pipeline definition into its own table2qb.pipeline.[pipeline-name]
namespace.
Rick has suggested that we might want to consider supporting edn alongside, if not instead of, json because edn allows comments.
Based on the input csv I would define the configuration as follows.
Label,Description,Component Type,Codelist
Geography,Geographical reference Area,Dimension,
Quarter,Reference Period,Dimension,
Gender,The state of being male or female,Dimension,http://statistics.gov.scot/def/concept-scheme/gender
Measure Type,What is being measured,Dimension,
### however doe not see how to indicate that this is the qb:measureType
Unit,Unit of Measurement,Attribute,
Value,Measured quantity,Measure,
In the example configuration.csv only Gender has been included.
How does the system know that geography and quarter are also dimensions?
What to do if the local URI standards are different from the uri pattern used over here e.g. having def# or ending with #id ?
What to do if one wants to use the sdmx dimension id's (refArea, refPeriod) instead of local ones?
Then the second line describes the values of the Measure Type dimension.
Does the conversion expect a fixed header 'Measure Type'?
What if your cube structure doesn't need a measure type?
Does the conversion expect a fixed header 'Value'? How does it know for the moment that this column contains the measured value?
Need to confirm that having multiple values for the measure types dimension is possible with this approach.
In the describe
task, an example invocation for executing the pipeline is generated. The example values for pipeline parameters are based on the parameter name. Add an example value for the invocation to the parameter definition to be used instead.
Some dependencies currently only exist in the private swirrl repositories. Deploy any required versions to a public repository and update dependencies. Remove the private repository configuration from the build.
Allow pipelines to be run from the command line. Different pipelines take different arguments so we need a command to describe each pipeline as well as to run it. The suggested interface is:
table2qb list
Lists all pipelinestable2qb describe [pipeline name]
Describes the pipeline and its argumentstable2qb run [pipeline name] args
Runs the pipeline with the specified argumentsAn example usage with the current pipelines might be:
table2qb list
codelist-pipeline components-pipeline cube-pipeline
table2qb describe cube-pipeline
Outputs a datacube for a dataset from an input file Arguments: --input-csv The CSV file containing observation data --dataset-name Name of the dataset --dataset-slug URI slug for the dataset --output-file File to write the generated RDF to
The --output-file
parameter will be common to all pipelines.
table2qb run cube-pipeline --input-csv input.csv --dataset-name 'Test Dataset' --dataset-slug test-dataset --output-file out.ttl
Create a table2qb.cli.tasks
namespace and move implementations for the CLI tasks (currently defined in table2qb.main
) into it.
We currently create skos:topConceptOf
and skos:hasTopConcept
relations between every code and the codelist. We should only create those relations when the parent notation is null (i.e. only those codes without parents are at the top of the hierarchy).
As I understand it, csvw does not provide a way to control the output of one cell based upon the value of another. The conditional processing based upon cell values requirement (which may have supported our use case) was deferred.
I'd be delighted to know if there is a way to do this within csvw/ csv2rdf!
One possible workaround is to create a new column that only contains a non-null value when the row is a top concept.
At the moment you get an unhelpful Index Out of Bounds error if you provide an input with the wrong column headings.
We should provide some validation of the codelist input:
Label
field only - throw exception if missingNotation
, Parent Notation
, Sort Priority
and Description
The new loading architecture suggests to me a possible solution to the stringy-identifiers problem:
datatype
as string
:
value-template
, then cell value should be treated as ready for that template (i.e. already slugged or a code without spaces)qb:codeList
then use the value in that cell to lookup a code by it's labeldatatype
is number
:
datatype
is xsd:anyURI
Given that multiple column names could theoretically be used to populate the same component, this gives us quite a bit of flexibility. For example, for specifying a reference period you might provide three columns all having sdmx-dim:refPeriod
in their property_template
configuration, but each with a different value_template
:
"http://reference.data.gov.uk/id/year/{year}"
accepting values like "2018"
"http://reference.data.gov.uk/id/government-year/{government-year}"
accepting values like "2017-2018"
"http://reference.data.gov.uk/id/{reference_period}"
(a generic fallback) accepting values like "government-quarter/2009-2010/Q1"
Thus the uploader would indicate which kind of date they'd provided by using the appropriate column heading. They could then provide more than one kind of interval by either splitting the upload or by providing multiple (non-overlapping) columns in the table (i.e. some rows having Years, others Government Years but none both).
One implication of this is that we wouldn't be slugging anything in the observations input csv! We could still need to do so as part of the code-list pipeline (i.e. to create an skos:notation
where none was provided).
Remove table2qb.demo
namespace and extract the example into a shell script with the corresponding CLI invocations.
Remove resources/columns.csv
. Some of the tests depend on this file so either re-work them to construct a new configuration inline or make it a test resource.
Lifting this issue out of slack, for more general discussion/clarification. It may or may not be a problem.
Generally is CSVW perhaps best understood as a method for describing things after the fact, and therefore it’s almost purely declarative. It’s not really about processing, so there’s no notion of process or any real notion of inputs/outputs or parameterisable arguments etc.
It seems like it might be best thought of as a markup format for google to understand CSV files rather than a method to transform data. Obviously at some level they're the same thing, but specific areas where this distinction is perhaps important are that processes can be reused with different inputs.
Yet CSVW appears to have no notion of how you might parameterise a CSVW transformation process.
How do "process ideas" such as parameters/arguments/environments/contexts apply here?
Version 0.2.2 of grafter-extra
has been deployed to swirrl-jars-releases. Also release it to clojars.
The csvw rdf-cube example includes the DSD in the metadata.json as json-ld. The immediate output attaches the DSD to the identifier given in the @id
key - at this stage it's just a csvw:Table
. It doesn't become a qb:DataSet
until after the qb-normalisation post processing step. Indeed setting a @type
key seems to cause the RDF::Tabular implementation, at least, to output nothing.
Rather than putting the DSD etc into json, then requiring a post-processing step, perhaps we should just create this RDF directly as an output of the preparation step.
Update the build to deploy tagged builds to clojars.
This could be:
When processing the ons trade example with RDF::Tabular, the dataset-uri seems to be ignored and instead a blank node is created for the csvw:Table
.
We've set the dataset-uri by setting the "@id"
key in the dataset metadata json as per step 4.1 of the csv2rdf spec in and cube example (json input and ttl output) but this doesn't appears to work.
The base URI is currently configured through a BASE_URI
environment variable. Configure it through a common command line parameter instead.
My assumption was that csv2rdf would identify columns on the basis of the csvw:title
field in the column schema. It seems like they're being interpreted on the basis of column order. We had to change the flow-directions.csv slightly to make sure it worked.
If the assumption is correct then we ought to patch csv2rdf to find columns by title not position.
If the assumption is mistaken then:
csvw:title
is!Alex reported 5xx issues on Thursday 17 May in the ONS when using table2qb
See: https://gssdataalpha.slack.com/archives/C8SSF9FLJ/p1526562752000086
@lkitching please can you elaborate in here with the details?
Configure a logging framework for use during development and in the uberjar (if only to remove the SLF4J warnings on startup). Consider using the logging framework for outputting from the CLI.
We currently take the measures in the values of a measure type column. This is consistent with the measures-dimension approach.
We could also look to support the multi-measure approach within table2qb. That would mean providing one or more columns like e.g.
{
:title "Observation Value"
:component_attachment qb:measure
:property_template sdmx-dim:obsValue
}
In the single-measure case (as above but only one such column) we could decide to create a qb:measureType
dimension anyway (with only one value) although allowing the user to specify would make this easier to configure.
We currently have the configuration in columns.csv. It should be stored in the database and extracted by a query. This would allow users to define permissible columns in a table of observations by uploading the components.
It would also be useful to be able to run table2qb as a standalone service - i.e. we should also allow the configuration to be specified by the csv as a fallback (either defaults taken from the packaged resource or e.g. as another command line option).
We could probably re-use the csvw vocabulary. This would mean creating a dataset of csvw:Column
resources each having a csvw:title
, csvw:name
, csvw:propertyUrl
, csvw:valueUrl
and csv:datatype
.
We could extract the "component_attachment" attribute (the predicate applied to a qb:ComponentSpec
to identify the qb:ComponentProperty
) which isn't in csvw with a query like:
?column # e.g. :date_column
csvw:propertyUrl/a ?property_type . # e.g. qb:DimensionProperty
?component_attachment # e.g. qb:dimension
rdfs:subPropertyOf qb:componentProperty;
rdfs:range ?property_type .
When loading the columns configuration: we would need to ensure that columns we unambiguously identified by the csvw:title
(i.e. only one column for a given title, although a given column could have more than one title).
When loading observations (i.e. when using the column configuration): We could permit columns to re-use the csvw:names
across a database (e.g. two columns titled "Double" and "Integer" which both yield a column named "value" but apply different datatypes) but, since this is used as a reference within URI templates, only one column of each name would be permitted within the context of a given csv file/ csvw:tableSchema
.
We ought to generate the csvw:Column
when the component itself is created (by users) with a seed supplied by us for external properties (e.g. sdmx-dim:refPeriod
). We could add the above csvw fields to the components pipeline, with defaults as follows:
csvw:title
: "{label"} take from the label e.g. "SITC Section"csvw:name
: URI template-safe version of the label e.g. "sitc_section" (note the underscore instead of a hyphen)csvw:propertyUrl
: "{domain}/def/{component_type_slug}/{component_slug}" e.g. "http://statistics.data.gov.uk/def/dimension/sitc-section"csvw:valueUrl
: "{domain}/def/concept/{component_slug}/{{_name}}" e.g. "http://statistics.data.gov.uk/def/concept/sitc-section/{sitc_section}" (possibly can't use a template to return a template here?)csv:datatype
: "string"That would mean adding another metadata file e.g. columns.json that would point at components.csv.
How should I specify that a cell's value e.g. "http://purl.org/linked-data/sdmx/2009/measure#obsValue"
should be treated as an object-node rather than a literal?
If you give "valueUrl": "{colname}"
then the string is you get <file://path/to/filename.csv/http%3A%2F%2Fpurl.org%2Flinked-data%2Fsdmx%2F2009%2Fmeasure%23obsValue>
. This is node, but the URI has been encoded and appended to a base.
If we give "datatype": "anyURI"
in the columns spec this leads to e.g. "http://purl.org/linked-data/sdmx/2009/measure#obsValue"^^xsd:anyURI
i.e. a typed-literal.
I only seem to be able to do this by first creating a string value instead e.g. "obsValue"
and specifying a template with "valueUrl": "http://purl.org/linked-data/sdmx/2009/measure#{colname}"
to get <http://purl.org/linked-data/sdmx/2009/measure#obsValue>
in the output.
But what if you've already got a URI in your csv cells? What about a CURIE?
Pipelines currently invoke csv2rdf in standard mode which includes statements about the table structure in the output RDF - specify minimal mode instead. In future, the output should be configurable.
Remove explicit require’s between components & use ig/load-namespaces to ensure components in config map are loaded instead. e.g. main.clj
shouldn’t need to :require [table2qb.core]
The following provides a specification of what we'll need from this integration.
We're currently loading the configuration from a csv file when the project is initialised. Instead we ought to be able to load them from SPARQL when the transformation pipeline is called (#21).
This would allow users to modify how the transformation pipeline was configured. You would first upload your new column configurations (with a suitable pipeline), then upload a spreadsheet of observations using the columns you'd just configured. This will be critical for handling e.g. new dimensions given that, unlike past pmd transformation pipelines, we're no longer creating vocabularies at the same time as observations.
Specifically this requires:
name->component
(via dependency injection, atom/redef, or configuration monad)columns.csv
and create e.g. csvw:Column
- see issue #21)Similarly we will ultimately want to lookup codelists in order to a) lookup URIs for codes from labels, and (potentially) b) validate the inputs to fail early (before generating rdf).
table2qb will output multiple csv and json files. The integration will need to orchestrate this as per these calls - i.e. having 3 grafter pipelines:
Necessarily we would need to pass the uploaded input csv in each case to table2qb, then take the resulting json and csv and pass those to csv2rdf.
Optionally we could persist the intermediate outputs potentially hosting them for remote retreival. Indeed it seems that the csv2rdf standard requires that the json metadata refer to the csv file with a url
property and that implementations are expected to retreive their inputs from this - further this leads to outputs like e.g. <row> csvw:url <file://input.csv#row=1>
in standard mode (i.e. the row URIs extend these urls). See #11 for more discussion.
The practical consequence of hosting the intermediate files is that we could use them for a) debugging (such that the validation report could link to the cell/ column/ row that violated a given rule) and b) as a pre-made tabular serialisation (although this might only be a subset of the observation in a cube).
Note that we might want to ignore this optional persistence until we've implemented csv2rdf standard mode (since tracking cell inputs isn't part of the minimal mode we're targeting at this point).
At the moment, table2qb groups together multiple files by writing/ reading from the same directory. If we're dealing with pipelines that use file-inputs we could a) submit a tar archive (which has the benefit of being a single request) or b) maintain state of partial requests (since the "job" would consist of several requests).
We'd need to be able to run this as a standalone grafter pipeline and within the context of grafter-server.
It makes sense to distinguish two opportunities for validation:
We would want to run tabular validations during the table2qb phase. We could use csvw's tabular metadata specification for describing the validation. Since the definition of "valid" will depend upon the column configuration, we would need to generate such schema based upon what's in the database. Other validations (e.g. all cells are populated) would be context-free. Some examples of criteria:
qb:MeasureProperty
column provided?qb:MeasureProperty
s) provided?We would want to run graph validations after the csv2rdf phase. We could use grafter-extra pmd/ qb validators (SPARQL ask), or something like the shapes report we've built for nhs. In any case, the validator should be able to query the live contents of the database.
Notice that there is some overlap - specifically in looking-up codes and ensuring partial-uploads have a common DSD (and thus observation-uri structure). It might make more sense to do this in the context of the graph (where you can point at the codelists/ DSD that are already loaded) but failing-fast (i.e at the table stage) may be preferable (and we've been able to return understandable errors at this stage in the past).
Some of the information within the input CSV for components-pipeline
e.g. components.csv duplicates the data in the columns configuration. Is it possible to derive this data from the columns configuration and observations file?
Running demo.sh
causes the following warnings to be logged:
At path ["tableSchema" "columns" 9]: Invalid common property key 'value'
This is emitted by csv2rdf
due to an invalid property key in the metadata document. Remove or replace this key with a valid value.
The json-ld standard is relatively new as is the support for it in rdf4j. Turtle should be equivalent. We know grafter and our other tools have good support for turtle.
We would need to support json-ld eventually if we wanted to offer a clojure implementation of csv2rdf.
We would at least need to read json-ld if we wanted to use the csv2rdf test cases (these would probably save us some effort since they provide a ready-made spec for TDD etc).
Was thinking about better prefix strategies and using RDF’s @base
earlier… Mentioned this to @ricroberts earlier, and we discovered you can do use BASE to prefix relative to path segments!
e.g. if we changed our naming strategy we could relativise names to a dataset and write more beautiful turtle/uri’s. The below sample is valid and parses in RDF4j perfectly:
@base <http://statistics.gov.scot/data/reconvictions> .
@prefix qb: <http://purl.org/linked-data/cube#> .
@prefix : <http://blah/> .
<> qb:structure </dsd> .
</dsd> a qb:DataStructureDefinition ;
qb:component </comp-spec/refPeriod> ;
qb:component </comp-spec/refArea> .
</comp-spec/refPeriod> :blah :blah .
</comp-spec/refArea> :blah :blah .
</obs/cca113731b4867901a8d722973c3a9dc39562b8a> qb:dataSet <> .
</obs/f99028cacf3551e9863ce1b8a8d6c5c803efcad0> qb:dataSet <> .
</obs/136f1d181aec3d64e89dfe87a71697bf7369795e> qb:dataSet <> .
From RDF 1.1 Concepts and Abstract Syntax:
Relative IRIs: Some concrete RDF syntaxes permit relative IRIs as a convenient shorthand that allows authoring of documents independently from their final publishing location. Relative IRIs must be resolved against a base IRI to make them absolute. Therefore, the RDF graph serialized in such syntaxes is well-defined only if a base IRI can be established [RFC3986].
Implementations might have slightly different behaviours regarding BASE
precedence. e.g. stardog/sparql lets you override a default base
from the store by setting it in your construct query.
However when reading a file with RDF4j, the base from the file is used rather than from your code... i.e. the precedence is reverse that of SPARQL. This might be a bug in RDF4j that they're willing to change though.
Another super useful option is the idea that you can set BASE
to nil
to preserve the relative paths. JSONLD lets you do this in the spec itself; but RDF4j fails to read such graphs :-( . So this might be a bug or feature or outside the general spec of RDF behaviour; but worth investigating whether we can get this fixed/changed.
It might be nice to resolve our approach to URI slugging:
https://github.com/Swirrl/table2qb/blob/master/architecture.md#uri-slugs
It's not clear that we can use transformation definitions, or even what they are for. (Do they transform individual cells, or whole formats/files as pre requisites?). They appear to be poorly specified.
I've opened an issue on the CSVW repo here: w3c/csvw#863 as their examples/tests also seem broken in this regard.
The order of columns in observations input data affects the order in which slugs are inserted into URIs. This means that 2 spreadsheets having different column orders could lead to 2 different URIs for the same observation. We need to make sure that the column order is consistent to avoid partial uploads (e.g. initial insert then subsequent update) creating duplicates.
In the past we have stored the initial order of the upload using the qb:order
property of the component spec (this was then later retrieved to enable the re-ordering of subsequent uploads).
In this case we could define this by configuration - e.g. the order the columns are specified in the columns.csv
. Once we store the configuration in the database (#21) this would need to be recorded as an explicit column order property.
This configuration would be used to reorder the columns before passing to csv2rdf. We could also create e.g. <compspec1> qb:order 1
triples.
in cube.ttl http://statistics.gov.scot/def/measure-properties/count
while in components.ttl http://statistics.gov.scot/def/measure/count
As it is now example fails on IC-3.
DSD includes measure
Every qb:DataStructureDefinition must include at least one declared measure.
ASK { ?dsd a qb:DataStructureDefinition . FILTER NOT EXISTS { ?dsd qb:component [qb:componentProperty [a qb:MeasureProperty]] } }
A declarative, efficient, and flexible JavaScript library for building user interfaces.
🖖 Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.
TypeScript is a superset of JavaScript that compiles to clean JavaScript output.
An Open Source Machine Learning Framework for Everyone
The Web framework for perfectionists with deadlines.
A PHP framework for web artisans
Bring data to life with SVG, Canvas and HTML. 📊📈🎉
JavaScript (JS) is a lightweight interpreted programming language with first-class functions.
Some thing interesting about web. New door for the world.
A server is a program made to process requests and deliver data to clients.
Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.
Some thing interesting about visualization, use data art
Some thing interesting about game, make everyone happy.
We are working to build community through open source technology. NB: members must have two-factor auth.
Open source projects and samples from Microsoft.
Google ❤️ Open Source for everyone.
Alibaba Open Source for everyone
Data-Driven Documents codes.
China tencent open source team.