ckanext-xloader 0.12.1

Key differences from DataPusher

Requirements

Compatibility with core CKAN versions:

Installation

To install XLoader:

1. Activate your CKAN virtual environment, for example:

   . /usr/lib/ckan/default/bin/activate

2. Install the ckanext-xloader Python package into your virtual environment:

   pip install ckanext-xloader

3. Install dependencies:

   pip install -r https://raw.githubusercontent.com/ckan/ckanext-xloader/master/requirements.txt
   pip install -U requests[security]

4. If you are using CKAN version before 2.8.x you need to define the populate_full_text_trigger in your database

   sudo -u postgres psql datastore_default -f full_text_function.sql

   If successful it will print

   CREATE FUNCTION
   ALTER FUNCTION

   NB this assumes you used the defaults for the database name and username. If in doubt, check your config’s ckan.datastore.write_url. If you don’t have database name datastore_default and username ckan_default then adjust the psql option and full_text_function.sql before running this.

5. Add xloader to the ckan.plugins setting in your CKAN config file (by default the config file is located at /etc/ckan/default/production.ini).

   You should also remove datapusher if it is in the list, to avoid them both trying to load resources into the DataStore.

   Ensure datastore is also listed, to enable CKAN DataStore.

6. Starting CKAN 2.10 you will need to set an API Token to be able to execute jobs against the server:

   ckanext.xloader.api_token = <your-CKAN-generated-API-Token>

7. If it is a production server, you’ll want to store jobs info in a more robust database than the default sqlite file. It can happily use the main CKAN postgres db by adding this line to the config, but with the same value as you have for sqlalchemy.url:

   ckanext.xloader.jobs_db.uri = postgresql://ckan_default:pass@localhost/ckan_default

   (This step can be skipped when just developing or testing.)

8. Restart CKAN. For example if you’ve deployed CKAN with Apache on Ubuntu:

   sudo service apache2 reload

9. Run the worker. First test it on the command-line. If you have CKAN version 2.9 or above:

   ckan -c /etc/ckan/default/ckan.ini jobs worker

   otherwise:

   paster --plugin=ckan jobs -c /etc/ckan/default/ckan.ini worker

   or if you have CKAN version 2.6.x or less (and are therefore using ckanext-rq):

   paster --plugin=ckanext-rq jobs -c /etc/ckan/default/ckan.ini worker

   Test it will load a CSV ok by submitting a CSV in the web interface or in another shell:

   paster --plugin=ckanext-xloader xloader submit <dataset-name> -c /etc/ckan/default/ckan.ini

   Clearly, running the worker on the command-line is only for testing - for production services see:

   http://docs.ckan.org/en/ckan-2.7.0/maintaining/background-tasks.html#using-supervisor

   If you have CKAN version 2.6.x or less then you’ll need to download supervisor-ckan-worker.conf and adjust the command to reference ckanext-rq.

Config settings

Configuration:

# The connection string for the jobs database used by XLoader. The
# default of an sqlite file is fine for development. For production use a
# Postgresql database.
ckanext.xloader.jobs_db.uri = sqlite:////tmp/xloader_jobs.db

# The formats that are accepted. If the value of the resource.format is
# anything else then it won't be 'xloadered' to DataStore (and will therefore
# only be available to users in the form of the original download/link).
# Case insensitive.
# (optional, defaults are listed in plugin.py - DEFAULT_FORMATS).
ckanext.xloader.formats = csv application/csv xls application/vnd.ms-excel

# The maximum size of files to load into DataStore. In bytes. Default is 1 GB.
ckanext.xloader.max_content_length = 1000000000

# By default, xloader will first try to add tabular data to the DataStore
# with a direct PostgreSQL COPY. This is relatively fast, but does not
# guess column types. If this fails, xloader falls back to a method more
# like DataPusher's behaviour. This has the advantage that the column types
# are guessed. However it is more error prone and far slower.
# To always skip the direct PostgreSQL COPY and use type guessing, set
# this option to True.
ckanext.xloader.use_type_guessing = False

# Deprecated: use ckanext.xloader.use_type_guessing instead.
ckanext.xloader.just_load_with_messytables = False

# Whether ambiguous dates should be parsed day first. Defaults to False.
# If set to True, dates like '01.02.2022' will be parsed as day = 01,
# month = 02.
# NB: isoformat dates like '2022-01-02' will be parsed as YYYY-MM-DD, and
# this option will not override that.
# See https://dateutil.readthedocs.io/en/stable/parser.html#dateutil.parser.parse
# for more details.
ckanext.xloader.parse_dates_dayfirst = False

# Whether ambiguous dates should be parsed year first. Defaults to False.
# If set to True, dates like '01.02.03' will be parsed as year = 2001,
# month = 02, day = 03. See https://dateutil.readthedocs.io/en/stable/parser.html#dateutil.parser.parse
# for more details.
ckanext.xloader.parse_dates_yearfirst = False

# The maximum time for the loading of a resource before it is aborted.
# Give an amount in seconds. Default is 60 minutes
ckanext.xloader.job_timeout = 3600

# Ignore the file hash when submitting to the DataStore, if set to True
# resources are always submitted (if their format matches), if set to
# False (default), resources are only submitted if their hash has changed.
ckanext.xloader.ignore_hash = False

# When loading a file that is bigger than `max_content_length`, xloader can
# still try and load some of the file, which is useful to display a
# preview. Set this option to the desired number of lines/rows that it
# loads in this case.
# If the file-type is supported (CSV, TSV) an excerpt with the number of
# `max_excerpt_lines` lines will be submitted while the `max_content_length`
# is not exceeded.
# If set to 0 (default) files that exceed the `max_content_length` will
# not be loaded into the datastore.
ckanext.xloader.max_excerpt_lines = 100

# Requests verifies SSL certificates for HTTPS requests. Setting verify to
# False should only be enabled during local development or testing. Default
# to True.
ckanext.xloader.ssl_verify = True

# Uses a specific API token for the xloader_submit action instead of the
# apikey of the site_user
ckanext.xloader.api_token = ckan-provided-api-token

Developer installation

To install XLoader for development, activate your CKAN virtualenv and in the directory up from your local ckan repo:

git clone https://github.com/ckan/ckanext-xloader.git
cd ckanext-xloader
python setup.py develop
pip install -r requirements.txt
pip install -r dev-requirements.txt

Upgrading from DataPusher

To upgrade from DataPusher to XLoader:

1. Install XLoader as above, including running the xloader worker.

2. (Optional) For existing datasets that have been datapushed to datastore, freeze the column types (in the data dictionaries), so that XLoader doesn’t change them back to string on next xload:

   ckan -c /etc/ckan/default/ckan.ini migrate_types

3. If you’ve not already, change the enabled plugin in your config - on the ckan.plugins line replace datapusher with xloader.

4. (Optional) If you wish, you can disable the direct loading and continue to just use tabulator - for more about this see the docs on config option: ckanext.xloader.use_type_guessing

5. Stop the datapusher worker:

   sudo a2dissite datapusher

6. Restart CKAN:

   sudo service apache2 reload
   sudo service nginx reload

Command-line interface

You can submit single or multiple resources to be xloaded using the command-line interface.

e.g.

[2.9] ckan -c /etc/ckan/default/ckan.ini xloader submit <dataset-name>
[pre-2.9] paster --plugin=ckanext-xloader xloader submit <dataset-name> -c /etc/ckan/default/ckan.ini

For debugging you can try xloading it synchronously (which does the load directly, rather than asking the worker to do it) with the -s option:

[2.9] ckan -c /etc/ckan/default/ckan.ini xloader submit <dataset-name> -s
[pre-2.9] paster --plugin=ckanext-xloader xloader submit <dataset-name> -s -c /etc/ckan/default/ckan.ini

See the status of jobs:

[2.9] ckan -c /etc/ckan/default/ckan.ini xloader status
[pre-2.9] paster --plugin=ckanext-xloader xloader status -c /etc/ckan/default/development.ini

Submit all datasets’ resources to the DataStore:

[2.9] ckan -c /etc/ckan/default/ckan.ini xloader submit all
[pre-2.9] paster --plugin=ckanext-xloader xloader submit all -c /etc/ckan/default/ckan.ini

Re-submit all the resources already in the DataStore (Ignores any resources that have not been stored in DataStore e.g. because they are not tabular):

[2.9] ckan -c /etc/ckan/default/ckan.ini xloader submit all-existing
[pre-2.9] paster --plugin=ckanext-xloader xloader submit all-existing -c /etc/ckan/default/ckan.ini

Full list of XLoader CLI commands:

[2.9] ckan -c /etc/ckan/default/ckan.ini xloader --help
[pre-2.9] paster --plugin=ckanext-xloader xloader --help

CKAN 2.9, Python 3 ckan -c /etc/ckan/default/ckan.ini jobs clear [QUEUES]
CKAN <2.9, Python 2 paster --plugin=ckanext-xloader xloader jobs clear [QUEUES] -c /etc/ckan/default/development.ini

sudo supervisorctl restart ckan-worker:*

Troubleshooting

KeyError: “Action ‘datastore_search’ not found”

You need to enable the datastore plugin in your CKAN config. See ‘Installation’ section above to do this and restart the worker.

ProgrammingError: (ProgrammingError) relation “_table_metadata” does not exist

Your DataStore permissions have not been set-up - see: <https://docs.ckan.org/en/latest/maintaining/datastore.html#set-permissions>

When editing a package, all its existing resources get re-loaded by xloader

This behavior was documented in Issue 75 and is related to a bug in CKAN that is fixed in versions 2.6.9, 2.7.7, 2.8.4 and 2.9.0+.

Running the Tests

The first time, your test datastore database needs the trigger applied:

sudo -u postgres psql datastore_test -f full_text_function.sql

To run the tests, do:

nosetests --nologcapture --with-pylons=test.ini

To run the tests and produce a coverage report, first make sure you have coverage installed in your virtualenv (pip install coverage) then run:

nosetests --nologcapture --with-pylons=test.ini --with-coverage --cover-package=ckanext.xloader --cover-inclusive --cover-erase --cover-tests

Releasing a New Version of XLoader

XLoader is available on PyPI as https://pypi-hypernode.com/project/ckanext-xloader.

To publish a new version to PyPI follow these steps:

1. Update the version number in the setup.py file. See PEP 440 for how to choose version numbers.

2. Update the CHANGELOG.

3. Make sure you have the latest version of necessary packages:

   pip install --upgrade setuptools wheel twine

4. Create source and binary distributions of the new version:

   python setup.py sdist bdist_wheel && twine check dist/*

   Fix any errors you get.

5. Upload the source distribution to PyPI:

   twine upload dist/*

6. Commit any outstanding changes:

   git commit -a
   git push

7. Tag the new release of the project on GitHub with the version number from the setup.py file. For example if the version number in setup.py is 0.0.1 then do:

   git tag 0.0.1
   git push --tags