ckan / ckan/ckanext-validation

Make the DataPusher / DataStore Table Schema aware

Open
#7 0 comments 0 reactions 0 assignees View on GitHub
enhancement
Dominant language
Python
Stars
30
Forks
33
PR merge metrics
No merged PRs in 30d

Description

The end goal is that data is properly stored in the DataStore using the correct field types (and even constraints).

For all the issues it causes, DataPusher is still the main way in which data gets into the DataStore. Implementation details aside and knowing there are probably better [alternatives](https://github.com/ckan/ckan/issues/3517) being proposed, the current DataPusher app is conceptually simple: it reads the data from the tabular file and uploads it in chunks to the DataStore via the API.

One problematic aspect of it is that in many cases the upload process fails because of badly defined field types for the actual data. This is because DataPusher uses a library called messytables to guess the file types based on a sample of the data.

Guessing is always going to be error-prone, even if we switch to a different library. So even if it isn't always possible, we should encourage publishers to describe their data, storing the fields definition in the `schema` field of the resource (See point 1). [Previous discussion](https://github.com/ckan/ideas-and-roadmap/issues/175).

Regardless of how this Schema object was stored in the resource (see point 6), if DataPusher gets a resource that has a schema, and this includes the field types, it will use it to create the [field definitions](http://docs.ckan.org/en/latest/maintaining/datastore.html#fields) that `datastore_create` supports, rather than guess the field types (this still can be the default behaviour if no schema is provided).

Table Schema is the perfect specification to use to define these fields: it's a light-weight, widely supported standard for describing tabular data. The mapping between the field types it offers and the Postgres-centric ones that the DataStore currently supports is also straight-forward.

| [DataStore field type](http://docs.ckan.org/en/latest/maintaining/datastore.html#valid-types) | [Table Schema field type](http://specs.frictionlessdata.io/table-schema/#types-and-formats) |
| -------------------- | ----------------------- |
| text | string |
| float | number |
| int | integer |
| bool | boolean |
| date | date |
| time | time |
| timestamp | datetime |
| json | object |
| Other complex types in Table Schema could default to text or object | duration, geopoint, geojson, ... |

As an example we need to convert this Table Schema

```json
{
"fields": [
{
"name": "id",
"title": "Identifier",
"type": "integer",
"description": "Unique identifier"
},
{
"name": "title",
"title": "Movie title",
"type": "string",
"description": "How the movie is known"
},
{
"name": "released",
"title": "Release date",
"type": "date",
"description": "Date the movie was released"
}
],
"primaryKey": "id"
}
```

into this `datastore_create()` call

```python

datastore_create(
resource_id='xxx',
fields=[
{
'id': 'id',
'type': 'int',
'info': {
'label': 'Identifier'
'notes': 'Unique identifier'
}
},
{
'id': 'title',
'type': 'text',
'info': {
'label': 'Movie title'
'notes': 'How the movie is known'
}
},
{
'id': 'released',
'type': 'date',
'info': {
'label': 'Release date'
'notes': 'Date the movie was released'
}
},
],
primary_key='id'
)

```

We have two options as to where this mapping from Table Schema to Datastore fields is done:

1. Mapping done in DataPusher: if there is a schema, DataPusher translate the field definitions to what DataStore currently understands
2. Mapping done in DataStore: apart from the current `fields` definition supported now (kept for comaptibility), DataStore supports directly Table Schema and each backend translates it internally to the relevant field types. DataPushers sends the `schema` from the resource to the DataStore if present.

My vote goes for 2 as it is more generic and it would allow to connect the DataStore into a wider ecosystem of tools that use Table Schema.

#### Implementation

Changes should be done in the [`create_table`](https://github.com/ckan/ckan/blob/26eb4768443ac76f4ecb64b53c2e22e515ad50ff/ckanext/datastore/backend/postgres.py#L829) and [`alter_table`](https://github.com/ckan/ckan/blob/26eb4768443ac76f4ecb64b53c2e22e515ad50ff/ckanext/datastore/backend/postgres.py#L924) functions of the Postgres backend. Right now they support a `fields` parameter to configure how the actual columns should be created. These should be kept for backwards compatibility but a new `schema` parameter should be supported, with similar logic but based on a Table Schema spec.

Down the line, tools like ckanext-validation or ckanext-datapackager will take care of setting and updating the `schema` field in resources when necessary, and DataPusher or Express Loader will need to pass on this field to the DataStore if present.

We will probably need a converter between table schema and the current DataStore fields definition. If necessary, this should be part of the [ckan-datapackage-tools](https://github.com/frictionlessdata/ckan-datapackage-tools) python package.

- [ ] Support `schema` object on `create_table`
- [ ] Support `schema` object on `alter_table`
- [ ] Make DataPusher pass the schema field if present in a resource

Optional: Return `schema` as part of `datastore_search`?

*Estimate: 7 days*

Contributor guide

No contributing guide indexed for this repository

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.