Express Loader - quickly load data into DataStore. A replacement for DataPusher.
.. You should enable this project on travis-ci.org and coveralls.io to make these badges work. The necessary Travis and Coverage config files have been generated for you.
.. image:: https://travis-ci.org/ckan/ckanext-xloader.svg?branch=master :target: https://travis-ci.org/ckan/ckanext-xloader
.. image:: https://img.shields.io/pypi/v/ckanext-xloader.svg :target: https://pypi.org/project/ckanext-xloader/ :alt: Latest Version
.. image:: https://img.shields.io/pypi/pyversions/ckanext-xloader.svg :target: https://pypi.org/project/ckanext-xloader/ :alt: Supported Python versions
.. image:: https://img.shields.io/pypi/status/ckanext-xloader.svg :target: https://pypi.org/project/ckanext-xloader/ :alt: Development Status
.. image:: https://img.shields.io/pypi/l/ckanext-xloader.svg :target: https://pypi.org/project/ckanext-xloader/ :alt: License
Loads CSV (and similar) data into CKAN's DataStore. Designed as a replacement for DataPusher because it offers ten times the speed and more robustness (hence the name, derived from "Express Loader")
OpenGov Inc. has sponsored this development, with the aim of benefitting open data infrastructure worldwide.
DataPusher - parses CSV rows, converts to detected column types, converts the data to a JSON string, calls datastore_create for each batch of rows, which reformats the data into an INSERT statement string, which is passed to PostgreSQL.
XLoader - pipes the CSV file directly into PostgreSQL using COPY.
tests <https://github.com/ckan/ckanext-xloader/issues/25>_, XLoader
is over ten times faster than DataPusher.
DataPusher - one cause of failure was when casting cells to a guessed type. The type of a column was decided by looking at the values of only the first few rows. So if a column is mainly numeric or dates, but a string (like "N/A") comes later on, then this will cause the load to error at that point, leaving it half-loaded into DataStore.
XLoader - loads all the cells as text, before allowing the admin to convert columns to the types they want (using the Data Dictionary feature). In future it could do automatic detection and conversion.
DataPusher - job queue is done by ckan-service-provider which is bespoke, complicated and stores jobs in its own database (sqlite by default).
XLoader - job queue is done by RQ, which is simpler, is backed by Redis, allows access to the CKAN model and is CKAN's default queue technology. You can also debug jobs easily using pdb. Job results are stored in Sqlite by default, and for production simply specify CKAN's database in the config and it's held there - easy.
(The other obvious candidate is Celery, but we don't need its heavyweight architecture and its jobs are not debuggable with pdb.)
DataPusher - has the complication that the queue jobs are done by a separate (Flask) web app, apart from CKAN. This was the design because the job requires intensive processing to convert every line of the data into JSON. However it means more complicated code as info needs to be passed between the services in http requests, more for the user to set-up and manage - another app config, another apache config, separate log files.
XLoader - the job runs in a worker process, in the same app as CKAN, so can access the CKAN config, db and logging directly and avoids many HTTP calls. This simplification makes sense because the xloader job doesn't need to do much processing - mainly it is streaming the CSV file from disk into PostgreSQL.
Note: With XLoader, all columns are stored in DataStore's database as 'text'
type (whereas DataPusher did some rudimentary type guessing - see 'Robustness'
above). However once a resource is xloaded, an admin can use the resource's
Data Dictionary tab to change these types to numeric or
datestamp and re-load the file. When migrating from DataPusher to XLoader you
can preserve the types of existing resources by using the
There is scope to add functionality for automatically guessing column type - offers to contribute this are welcomed.
Compatibility with core CKAN versions:
=============== ============= CKAN version Compatibility =============== ============= 2.7 no longer supported (last supported version: 0.12.2) 2.8 no longer supported (last supported version: 0.12.2) 2.9 yes (Python3) (last supported version for Python 2.7: 0.12.2)) 2.10 yes =============== =============
To install XLoader:
Activate your CKAN virtual environment, for example::
Install the ckanext-xloader Python package into your virtual environment::
pip install ckanext-xloader
pip install -r https://raw.githubusercontent.com/ckan/ckanext-xloader/master/requirements.txt pip install -U requests[security]
xloader to the
ckan.plugins setting in your CKAN
config file (by default the config file is located at
You should also remove
datapusher if it is in the list, to avoid them
both trying to load resources into the DataStore.
datastore is also listed, to enable CKAN DataStore.
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>
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
ckanext.xloader.jobs_db.uri = postgresql://ckan_default:[email protected]/ckan_default
(This step can be skipped when just developing or testing.)
Restart CKAN. For example if you've deployed CKAN with Apache on Ubuntu::
sudo service apache2 reload
Run the worker::
ckan -c /etc/ckan/default/ckan.ini jobs worker
See the extension's
config_declaration.yaml <ckanext/xloader/config_declaration.yaml>_ file.
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 pip install -e . pip install -r requirements.txt pip install -r dev-requirements.txt
To upgrade from DataPusher to XLoader:
Install XLoader as above, including running the xloader worker.
(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
If you've not already, change the enabled plugin in your config - on the
ckan.plugins line replace
(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:
Stop the datapusher worker::
sudo a2dissite datapusher
sudo service apache2 reload sudo service nginx reload
You can submit single or multiple resources to be xloaded using the command-line interface.
ckan -c /etc/ckan/default/ckan.ini xloader submit <dataset-name>
For debugging you can try xloading it synchronously (which does the load
directly, rather than asking the worker to do it) with the
ckan -c /etc/ckan/default/ckan.ini xloader submit <dataset-name> -s
See the status of jobs::
ckan -c /etc/ckan/default/ckan.ini xloader status
Submit all datasets' resources to the DataStore::
ckan -c /etc/ckan/default/ckan.ini xloader submit all
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)::
ckan -c /etc/ckan/default/ckan.ini xloader submit all-existing
Full list of XLoader CLI commands::
ckan -c /etc/ckan/default/ckan.ini xloader --help
Main docs for managing jobs: https://docs.ckan.org/en/latest/maintaining/background-tasks.html#managing-background-jobs
Main docs for running and managing workers are here: https://docs.ckan.org/en/latest/maintaining/background-tasks.html#running-background-jobs
Clear (delete) all outstanding jobs::
ckan -c /etc/ckan/default/ckan.ini jobs clear [QUEUES]
If having trouble with the worker process, restarting it can help::
sudo supervisorctl restart ckan-worker:*
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
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::
pytest ckan-ini=test.ini ckanext/xloader/tests
XLoader is available on PyPI as https://pypi.org/project/ckanext-xloader.
To publish a new version to PyPI follow these steps:
Update the version number in the
PEP 440 <http://legacy.python.org/dev/peps/pep-0440/#public-version-identifiers>_
for how to choose version numbers.
Update the CHANGELOG.
Make sure you have the latest version of necessary packages::
pip install --upgrade setuptools wheel twine
Create source and binary distributions of the new version::
python setup.py sdist bdist_wheel && twine check dist/*
Fix any errors you get.
Upload the source distribution to PyPI::
twine upload dist/*
Commit any outstanding changes::
git commit -a git push
Tag the new release of the project on GitHub with the version number from
setup.py file. For example if the version number in
0.0.1 then do::
git tag 0.0.1 git push --tags