Home

Awesome

OpenRefine Python Client with extended command line interface (⌨️ for 💎)

Codacy Badge Docker PyPI Binder

The OpenRefine Python Client from PaulMakepeace provides a library for communicating with an OpenRefine server. This fork extends the command line interface (CLI) and is distributed as a convenient one-file-executable (Windows, Linux, macOS). It is also available via Docker Hub, PyPI and Binder.

works with OpenRefine 2.7, 2.8, 3.0, 3.1, 3.2, 3.3, 3.4, 3.4.1, 3.5.0

Download

One-file-executables:

For Docker containers, native Python installation and free Binder on-demand server see the corresponding chapters below.

Peek

A short video loop that demonstrates the basic features (list, create, apply, export):

video loop that demonstrates basic features

Usage

Ensure you have OpenRefine running (i.e. available at http://localhost:3333 or another URL).

To use the client:

  1. Open a terminal pointing to the folder where you have downloaded the one-file-executable (e.g. Downloads in your home directory).

    • Windows: Open PowerShell and enter following command

      cd ~\Downloads
      
    • macOS: Open Terminal (Finder > Applications > Utilities > Terminal) and enter following command

      cd ~/Downloads
      
    • Linux: Open terminal app (Terminal, Konsole, xterm, ...) and enter following command

      cd ~/Downloads
      
  2. Make the file executable.

    • Windows: not necessary

    • macOS:

      chmod +x openrefine-client_0-3-10_macos
      
    • Linux:

      chmod +x openrefine-client_0-3-10_linux
      
  3. Execute the file.

    • Windows:

      .\openrefine-client_0-3-10_windows.exe
      
    • macOS:

      ./openrefine-client_0-3-10_macos
      
    • Linux:

      ./openrefine-client_0-3-10_linux
      

Using tab completion and command history is highly recommended:

Basic commands

Execute the client by entering its filename followed by the desired command.

The following example will download two small files (duplicates.csv and duplicates-deletion.json) into the current directory and will create a new OpenRefine project from file duplicates.csv.

Download example data (--download) and create project from file (--create):

Other commands:

Getting help

Check --help for further options.

Please file an issue if you miss some features in the command line interface or if you have tracked a bug. And you are welcome to ask any questions!

Change URL

By default the client connects to the usual URL of OpenRefine http://localhost:3333. If your OpenRefine server is running somewhere else then you may set hostname and port with additional command line options (e.g. http://example.com):

Templating

The OpenRefine Templating supports exporting data in any text format (i.e. to construct JSON or XML). The graphical user interface offers four input fields:

  1. prefix
  2. row template
    • supports GREL inside two curly brackets, e.g. {{jsonize(cells["name"].value)}}
  3. row separator
  4. suffix

This templating functionality is available via the openrefine-client command line interface. It even provides an additional feature for splitting results into multiple files.

To try out the functionality create another project from the example file above.

--create duplicates.csv --projectName=advanced

The following example code will export...

macOS/Linux Terminal (multi-line input with \ ):

"advanced" \
--prefix='{ "events" : [
' \
--template='    { "name" : {{jsonize(cells["name"].value)}}, "purchase" : {{jsonize(cells["purchase"].value)}} }' \
--rowSeparator=',
' \
--suffix='
] }' \
--filterQuery='^F$' \
--filterColumn='gender'

Windows PowerShell (multi-line input with `; quotes needs to be doubled):

"advanced" `
--prefix='{ ""events"" : [
' `
--template='    { ""name"" : {{jsonize(cells[""name""].value)}}, ""purchase"" : {{jsonize(cells[""purchase""].value)}} }' `
--rowSeparator=',
' `
--suffix='
] }' `
--filterQuery='^F$' `
--filterColumn='gender'

Add the following options to the last command (recall with ) to store the results in multiple files. Each file will contain the prefix, an processed row, and the suffix.

--output=advanced.json --splitToFiles=true

Filenames are suffixed with the row number by default (e.g. advanced_1.json, advanced_2.json etc.). There is another option to use the value in the first column instead:

--output=advanced.json --splitToFiles=true --suffixById=true

Because our project "advanced" contains duplicates in the first column "email" this command will overwrite files (e.g. advanced_melanie.white@example2.edu.json). When using this option, the first column should contain unique identifiers.

Append data to an existing project

OpenRefine does not support appending rows to an existing project. As long as the feature request is not yet implemented, you can use the openrefine-client to script a workaround:

  1. export existing project as csv
  2. put old and new data into a zip archive
  3. create new project by importing the zip archive

Here is an example that replaces the existing project:

openrefine-client --export myproject --output old.csv
openrefine-client --delete myproject
zip combined.zip old.csv new.csv
openrefine-client --create combined.zip --format csv --projectName myproject

Note that the project id will change. If you want to distinguish between old and new data, you can use the additional flag includeFileSources:

openrefine-client --create combined.zip --format csv --projectName myproject --includeFileSources true

See also

Docker

felixlohmeier/openrefine-client Docker

docker pull felixlohmeier/openrefine-client:v0.3.10

Option 1: Dockerized client

Run client and mount current directory as workspace:

docker run --rm --network=host -v ${PWD}:/data:z felixlohmeier/openrefine-client:v0.3.10

The docker option --network=host allows you to connect to a local or remote OpenRefine via the host network:

Usage: same commands as explained above (see Basic Commands and Advanced Templating)

Option 2: Dockerized client and dockerized OpenRefine

Run openrefine-client linked to a dockerized OpenRefine (felixlohmeier/openrefine Docker):

  1. Create docker network

    docker network create openrefine
    
  2. Run server (will be available at http://localhost:3333)

    docker run -d -p 3333:3333 --network=openrefine --name=openrefine-server felixlohmeier/openrefine:3.5.0
    
  3. Run client with some basic commands: 1. download example files, 2. create project from file, 3. list projects, 4. show metadata, 5. export to terminal, 6. apply transformation rules (deduplication), 7. export again to terminal, 8. export to xls file and 9. delete project

    docker run --rm --network=openrefine -v ${PWD}:/data:z felixlohmeier/openrefine-client:v0.3.10 --download "https://git.io/fj5hF" --output=duplicates.csv
    docker run --rm --network=openrefine -v ${PWD}:/data:z felixlohmeier/openrefine-client:v0.3.10 --download "https://git.io/fj5ju" --output=duplicates-deletion.json
    docker run --rm --network=openrefine -v ${PWD}:/data:z felixlohmeier/openrefine-client:v0.3.10 -H openrefine-server --create duplicates.csv
    docker run --rm --network=openrefine -v ${PWD}:/data:z felixlohmeier/openrefine-client:v0.3.10 -H openrefine-server --list
    docker run --rm --network=openrefine -v ${PWD}:/data:z felixlohmeier/openrefine-client:v0.3.10 -H openrefine-server --info "duplicates"
    docker run --rm --network=openrefine -v ${PWD}:/data:z felixlohmeier/openrefine-client:v0.3.10 -H openrefine-server --export "duplicates"
    docker run --rm --network=openrefine -v ${PWD}:/data:z felixlohmeier/openrefine-client:v0.3.10 -H openrefine-server --apply duplicates-deletion.json "duplicates"
    docker run --rm --network=openrefine -v ${PWD}:/data:z felixlohmeier/openrefine-client:v0.3.10 -H openrefine-server --export "duplicates"
    docker run --rm --network=openrefine -v ${PWD}:/data:z felixlohmeier/openrefine-client:v0.3.10 -H openrefine-server --export --output=deduped.xls "duplicates"
    docker run --rm --network=openrefine -v ${PWD}:/data:z felixlohmeier/openrefine-client:v0.3.10 -H openrefine-server --delete "duplicates"
    
  4. Stop and delete server:

    docker stop openrefine-server
    docker rm openrefine-server
    
  5. Delete docker network:

    docker network rm openrefine
    

Customize OpenRefine server:

See also:

Python

openrefine-client PyPI (requires Python 2.x)

python2 -m pip install openrefine-client --user

This will install the package openrefine-client containing modules in google.refine.

A command line script openrefine-client will also be installed.

Option 1: command line script

openrefine-client --help

Usage: same commands as explained above (see Basic Commands and Advanced Templating)

Option 2: using cli functions in Python 2.x environment

Import module cli:

from google.refine import cli

Change URL (if necessary):

cli.refine.REFINE_HOST = 'localhost'
cli.refine.REFINE_PORT = '3333'

Help screen:

help(cli)

Commands:

Option 3: the upstream way

This fork can be used in the same way as the upstream Python client library.

Some functions in the python client library are not yet compatible with OpenRefine >=3.0 (cf. issue #19 in refine-client-py).

Import module refine:

from google.refine import refine

Server Commands:

Project commands:

See also:

Binder

Binder

Development

If you would like to contribute to the Python client library please consider a pull request to the upstream repository refine-client-py.

Tests

Ensure you have OpenRefine running (i.e. available at http://localhost:3333). If necessary set the environment variables OPENREFINE_HOST and OPENREFINE_PORT to change the URL.

The Python client library includes several unit tests.

There is also a script that uses docker images to run the unit tests with different versions of OpenRefine.

For Linux there are also functional tests for all command line options.

Distributing

Note to myself: When releasing a new version...

  1. Run functional tests

    for v in 2.7 2.8 3.0 3.1 3.2 3.3 3.4 3.4.1 3.5.0; do
       ./tests-cli.sh $v
    done
    
  2. Make final changes in Git

  3. Build executables with PyInstaller

    • Run PyInstaller in Python 2 environments on native Windows, macOS and Linux. Should be "the oldest version of the OS you need to support"! Current release is built with:

      • Ubuntu 16.04 LTS (64-bit)
      • macOS Sierra 10.12 (64-bit)
      • Windows 7 (32-bit)
    • One-file-executables will be available in dist/.

      git clone https://github.com/opencultureconsulting/openrefine-client.git
      cd openrefine-client
      python2 -m pip install pyinstaller --user
      python2 -m pip install urllib2_file --user
      python2 -m PyInstaller --onefile refine.py --hidden-import google.refine.__main__
      
  4. Run functional tests with Linux executable

    for v in 2.7 2.8 3.0 3.1 3.2 3.3 3.4 3.4.1 3.5.0; do
       ./tests-cli.sh $v openrefine-client_0-3-7_linux
    done
    
  5. Create release in GitHub

  6. Build package and upload to PyPI

    python3 setup.py sdist bdist_wheel
    python3 -m twine upload dist/*
    
  7. Update Docker container

    • add new autobuild for release version
    • trigger latest build
  8. Bump openrefine-client version in related projects

Credits

Paul Makepeace, author

David Huynh, [initial cut](<http://markmail.org/message/jsxzlcu3gn6drtb7)

Artfinder, inspiration

Felix Lohmeier, extended the CLI features

Some data used in the test suite has been used from publicly available sources: