Ckanext-Datastore

Background and strategic fit

https://github.com/ckan/ckan/blob/2.9/doc/maintaining/datastore.rst

This extension provides the possibility to upload tabular data into a separate database. Apart from direc6t visualisation inside CKAN, this provides the possibility to directly work with raw data through Datastore API. Hence, this could be quite useful for HEF members as many of their data are stored in tabular form. In addition, it is possible to control access on the Datastore db.

Installations and Requirements

Datastore is added to CKAN folder when CKAN core is installed. However, a set of configuration is required to have a running Datastore:

1. Add the datastore plugin to your CKAN config file:

   ckan.plugins = datastore

2. Set-up the database:

   Step by step

   The DataStore requires a separate PostgreSQL database to save the DataStore resources to.

   
   

   Make sure that you follow the steps in Set Permissions below correctly. Wrong settings could lead to serious security issues.

   List existing databases:

   sudo -u postgres psql -l

   Check that the encoding of databases is UTF8, if not internationalisation may be a problem. Since changing the encoding of PostgreSQL may mean deleting existing databases, it is suggested that this is fixed before continuing with the datastore setup.

   Create users and databases

   If your CKAN database and DataStore databases are on different servers, then you need to create a new database user on the server where the DataStore database will be created. As in :doc:`installing/install-from-source` we'll name the database user |database_user|:

   sudo -u postgres createuser -S -D -R -P -l |database_user|

   
   Create a database_user called |datastore_user|. This user will be given read-only access to your DataStore database in the Set Permissions step below:

   sudo -u postgres createuser -S -D -R -P -l |datastore_user|

   
   Create the database (owned by |database_user|), which we'll call |datastore|:

   sudo -u postgres createdb -O |database_user| |datastore| -E utf-8

   Set URLs

   Now, uncomment the :ref:`ckan.datastore.write_url` and :ref:`ckan.datastore.read_url` lines in your CKAN config file and edit them if necessary, for example:

   ckan.datastore.write_url = postgresql://|database_user|:pass@localhost/|datastore|
   ckan.datastore.read_url = postgresql://|datastore_user|:pass@localhost/|datastore|

   Replace pass with the passwords, you created for your |database_user| and |datastore_user| database users.

   Set Permissions

   
   

   Once the DataStore database and the users are created, the permissions on the DataStore and CKAN database have to be set. CKAN provides a ckan command to help you correctly set these permissions.

   If you are able to use the psql command to connect to your database as a superuser, you can use the datastore set-permissions command to emit the appropriate SQL to set the permissions.

   For example, if you can connect to your database server as the postgres superuser using:

   sudo -u postgres psql
   

   Then you can use this connection to set the permissions:

   ckan -c |ckan.ini| datastore set-permissions | sudo -u postgres psql --set ON_ERROR_STOP=1

   Note

   If you performed a package install, you will need to replace all references to 'ckan -c |ckan.ini| ...' with 'sudo ckan ...' and provide the path to the config file, e.g.:

   sudo ckan datastore set-permissions | sudo -u postgres psql --set ON_ERROR_STOP=1

   
   

   If your database server is not local, but you can access it over SSH, you can pipe the permissions script over SSH:

   ckan -c |ckan.ini| datastore set-permissions | ssh dbserver sudo -u postgres psql --set ON_ERROR_STOP=1

   
   

   If you can't use the psql command in this way, you can simply copy and paste the output of:

   ckan -c |ckan.ini| datastore set-permissions

   into a |postgres| superuser console.

3. Test the set-up

   Test

   The DataStore is now set-up. To test the set-up, (re)start CKAN and run the following command to list all DataStore resources:

   curl -X GET "http://127.0.0.1:5000/api/3/action/datastore_search?resource_id=_table_metadata"

   
   

   This should return a JSON page without errors.

   To test the whether the set-up allows writing, you can create a new DataStore resource. To do so, run the following command:

   curl -X POST http://127.0.0.1:5000/api/3/action/datastore_create -H "Authorization: {YOUR-API-KEY}" -d '{"resource": {"package_id": "{PACKAGE-ID}"}, "fields": [ {"id": "a"}, {"id": "b"} ], "records": [ { "a": 1, "b": "xyz"}, {"a": 2, "b": "zzz"} ]}'

   Replace{YOUR-API-KEY}with a valid API key and{PACKAGE-ID}with the id of an existing CKAN dataset.

   A table named after the resource id should have been created on your DataStore database. Visiting this URL should return a response from the DataStore with the records inserted above:

   http://127.0.0.1:5000/api/3/action/datastore_search?resource_id={RESOURCE_ID}

   Replace {RESOURCE-ID} with the resource id that was returned as part of the response of the previous API call.

   You can now delete the DataStore table with:

   curl -X POST http://127.0.0.1:5000/api/3/action/datastore_delete -H "Authorization: {YOUR-API-KEY}" -d '{"resource_id": "{RESOURCE-ID}"}'

   To find out more about the DataStore API, see The DataStore API.

4. Datapusher: refer to this link → https://docs.ckan.org/en/2.9/maintaining/datastore.html#datapusher-automatically-add-data-to-the-datastore

5. Data Dictionary: refer to this link → https://docs.ckan.org/en/2.9/maintaining/datastore.html#data-dictionary
   
   

6. Restart CKAN. For example, if you've deployed CKAN with Apache on Ubuntu:

   sudo service apache2 reload

User interaction and design

All required information is provided under this link:

https://docs.ckan.org/en/2.9/maintaining/datastore.html#downloading-resources

Questions and Answers

Below is a list of Q&A from user sides:

Further steps