OpenRefine Data Cleaning Workbench on AWS User Guide

Overview

This guide covers deploying and using OpenRefine on AWS from the cloudimg AWS Marketplace AMI. OpenRefine is a free, open source power tool for working with messy data. You import tabular data (CSV, TSV, Excel, JSON, XML) into a project workbench, then explore it with facets and filters, clean and normalise values with GREL, Jython or Clojure expressions, cluster near duplicate entries, and reconcile your data against external sources such as Wikidata, all through a browser based interface. Every change is recorded as a reproducible operation history. OpenRefine is released under the BSD 3 Clause licence.

OpenRefine assumes a single trusted local user and ships with no built in authentication of its own, so this image never exposes it directly. The OpenRefine server is bound to loopback only (127.0.0.1:3333, headless) and fronted by nginx on port 443 over HTTPS with HTTP Basic Auth, so the workbench is never reachable without a credential. At first boot, openrefine-firstboot.service generates a unique per instance Basic Auth password and a per instance self signed TLS certificate (there is no default login baked into the image), reloads nginx, proves an authenticated round trip, and writes the password to /root/openrefine-credentials.txt (mode 0600, root only).

What is included:

• OpenRefine 3.10.1 (official Linux distribution), pinned and sha256 verified, installed under /opt/openrefine

• OpenJDK 17 headless runtime (OpenRefine requires Java 11 to 21), with the JVM heap pinned to a conservative 2 GB maximum

• openrefine.service systemd unit running the workbench as the unprivileged openrefine user, bound to loopback with a hardened service sandbox

• openrefine-firstboot.service systemd oneshot that generates the per instance HTTP Basic Auth password and TLS certificate, reloads nginx, proves an authenticated round trip and writes /root/openrefine-credentials.txt

• nginx on port 443 (HTTPS) reverse proxying to the loopback bound OpenRefine listener, with HTTP Basic Auth and an unauthenticated /healthz for load balancer probes; port 80 serves the same health endpoint and redirects everything else to HTTPS

• A dedicated EBS data volume mounted at /var/lib/openrefine for your projects, imported datasets and operation history, independently resizable from the OS disk

• Ubuntu 24.04 LTS base with the latest security patches

• 24/7 cloudimg support with guaranteed 24 hour response SLA

Connecting to your instance

Connect over SSH on port 22 as the default login user for your AMI variant, using the EC2 key pair you selected at launch:

ssh ubuntu@<vm-ip>

openrefine.service and nginx.service start automatically, and openrefine-firstboot.service generates the per instance Basic Auth password and TLS certificate on the first boot.

Prerequisites

• An AWS account and a subscription to the OpenRefine listing on AWS Marketplace

• An EC2 key pair in the target region and a security group allowing TCP 22 from your management IP and TCP 443 from your client IPs (TCP 80 is optional; it only redirects to HTTPS)

Recommended instance type: m5.large (2 vCPU, 8 GB RAM) for general use. For large datasets and heavier transforms, use a larger instance and raise the JVM heap in /opt/openrefine/openrefine-3.10.1/refine.ini (REFINE_MEMORY).

Step 1: Launch from the AWS Console

Subscribe to the listing in AWS Marketplace, then Launch an instance from the AMI. Choose your instance type, key pair and subnet, and a security group that allows TCP 22 from your management IP and TCP 443 from your client IPs. OpenRefine is bound to loopback, so only nginx (ports 80 and 443) is reachable, and the workbench itself is protected by HTTP Basic Auth over HTTPS.

Step 2: Launch from the AWS CLI

aws ec2 run-instances --region us-east-1 \
  --image-id ami-xxxxxxxxxxxxxxxxx \
  --instance-type m5.large \
  --key-name my-key \
  --security-group-ids sg-xxxxxxxx \
  --subnet-id subnet-xxxxxxxx \
  --tag-specifications 'ResourceType=instance,Tags=[{Key=Name,Value=openrefine}]'

Step 3: Verify the OpenRefine service

sudo systemctl is-active openrefine.service nginx.service
sudo test -f /var/lib/cloudimg/openrefine-firstboot.done && echo FIRSTBOOT_DONE

Both services should report active, and the first boot sentinel should be present.

Step 4: Inspect the listeners and runtime

The OpenRefine server is bound to 127.0.0.1:3333, and nginx serves it over HTTPS on port 443. Nothing but nginx listens on a public interface. The JVM heap is pinned in refine.ini.

sudo ss -tln | grep -E ':443|:3333' || true
grep -E 'REFINE_MEMORY|REFINE_MIN_MEMORY' /opt/openrefine/openrefine-3.10.1/refine.ini

You will see nginx on port 443, OpenRefine on loopback 3333, and the pinned 2 GB maximum heap.

Step 5: Confirm the dedicated data volume

Your projects live on a dedicated EBS volume mounted at /var/lib/openrefine, separate from the OS disk:

findmnt -no SOURCE,TARGET,FSTYPE /var/lib/openrefine

Step 6: Retrieve the access password

The per instance HTTP Basic Auth password is generated at first boot and stored in a root only file:

sudo cat /root/openrefine-credentials.txt

You will see the sign in user, the per instance password, and the endpoint URLs:

OPENREFINE_HTTP_USER=admin
OPENREFINE_BASIC_AUTH_PASSWORD=<OPENREFINE_BASIC_AUTH_PASSWORD>
OPENREFINE_URL=https://<vm-ip>/
OPENREFINE_HEALTHZ=https://<vm-ip>/healthz
OPENREFINE_LOCAL_HTTP=http://127.0.0.1:3333/

Step 7: Verify access control over HTTPS

OpenRefine is reached only through the authenticated nginx proxy over HTTPS. Confirm that an unauthenticated request is rejected, that the per instance password works, and that the command API responds. (The -k flag accepts the per instance self signed certificate; replace it with your own trusted certificate for production.)

PW=$(sudo grep '^OPENREFINE_BASIC_AUTH_PASSWORD=' /root/openrefine-credentials.txt | cut -d= -f2-)
curl -ks -o /dev/null -w 'no creds: %{http_code}\n' https://127.0.0.1/
curl -ks -o /dev/null -w 'wrong pw: %{http_code}\n' -u "admin:wrong-password" https://127.0.0.1/
curl -ks -o /dev/null -w 'workbench index: %{http_code}\n' -u "admin:$PW" https://127.0.0.1/
curl -ks -u "admin:$PW" https://127.0.0.1/command/core/get-version

The unauthenticated request returns 401, a wrong password returns 401, the authenticated workbench index returns 200, and the command API returns the OpenRefine version JSON. The unauthenticated /healthz endpoint stays open for load balancer probes.

Step 8: Open the workbench in a browser

Open https://<vm-ip>/ from your workstation (assuming the security group allows TCP 443). Because the certificate is self signed and unique to this instance, your browser shows a certificate warning the first time; accept it to proceed, or terminate TLS at nginx with your own domain and certificate. Your browser then prompts for HTTP Basic Auth: sign in with user admin and the per instance password from /root/openrefine-credentials.txt. OpenRefine opens on the Create Project screen, where you choose a data source:

Step 9: Create a project by importing data

On This Computer, choose a data file (CSV, TSV, Excel, JSON or XML) and click Next. OpenRefine shows a parsing preview where you confirm the column separator, header rows and value types, then click Create Project. Your data loads into the project workbench as a grid of rows and columns.

Step 10: Explore the data grid

The workbench shows your data as an editable grid. Each column header has a dropdown menu of operations, and the Facet / Filter panel on the left is where you slice and clean the data. This sample import of customer signups shows the kind of inconsistency OpenRefine is built to fix, mixed spellings of the same country and inconsistent casing:

Step 11: Facet and filter

Open a column's dropdown menu and choose Facet, Text facet to see every distinct value in that column with a count. Here the country column reveals many variants of what should be a handful of countries (UK and uk, Ireland and IRELAND and South-Africa), each with its row count. Click Cluster to let OpenRefine group near duplicate values automatically, or click a value to filter the grid to just those rows:

Step 12: Clean and transform with GREL

Open a column menu and choose Edit cells, Transform to run a GREL expression across every cell, with a live before and after preview. For example value.trim().toTitlecase() normalises casing and strips stray whitespace. Transforms are recorded in the operation history, so you can undo them or replay the whole cleanup on a fresh dataset.

Step 13: Export the cleaned data

When the data is clean, use the Export menu at the top right to write it back out as CSV, TSV, Excel, JSON, HTML or a custom template, or to export the operation history as JSON so the same cleanup can be applied to future imports. Projects are stored on the dedicated data volume at /var/lib/openrefine.

Step 14: Server components

Step 15: Managing the service

sudo systemctl status openrefine.service --no-pager
sudo journalctl -u openrefine.service --no-pager -n 5

To restart the workbench after changing the heap or options, run sudo systemctl restart openrefine.service.

Step 16: Security recommendations

• Use a trusted certificate in production by replacing the per instance self signed certificate at /etc/nginx/ssl/openrefine.crt / .key with your own, or terminate TLS at an Application Load Balancer with an ACM certificate

• Rotate the Basic Auth password by regenerating /etc/nginx/.openrefine_htpasswd with sudo htpasswd -B /etc/nginx/.openrefine_htpasswd admin and reloading nginx, and store the password in AWS Secrets Manager

• Keep the OpenRefine listener on loopback as shipped; expose only nginx, and restrict the security group so port 443 only reaches trusted client networks, because a data cleaning workbench often holds sensitive data

• Make the workbench public only if intended by removing the auth_basic lines from /etc/nginx/sites-available/cloudimg-openrefine and reloading nginx

• Back up the workspace at /var/lib/openrefine, or take EBS snapshots, so your projects and operation histories are preserved

• Raise the JVM heap in refine.ini (REFINE_MEMORY) when working with large datasets, and use a larger instance type accordingly

• Patch the OS monthly with sudo apt-get update && sudo apt-get upgrade && sudo reboot

Step 17: Support and licensing

OpenRefine is licensed under the BSD 3 Clause licence. There are no per node, per CPU, or per GB fees. This image is produced by cloudimg and is not affiliated with, endorsed by, or sponsored by the OpenRefine project; OpenRefine is a trademark of its respective owner and is used here only to describe the software included in the image.

cloudimg provides commercial support for this image separately from the upstream project.

• Email: support@cloudimg.co.uk

• Website: www.cloudimg.co.uk

• Support hours: 24/7 with guaranteed 24 hour response SLA

Need Help?

Our support team is available 24/7.

support@cloudimg.co.uk