12 December 2016  •  Releases  •  Joshua Beemster

SQL Runner 0.5.0 released

We are pleased to announce version 0.5.0 of SQL Runner. This release adds some powerful new features, including local and Consul-based remote locking to ensure that SQL Runner runs your playbooks as singletons.

  1. Locking your run
  2. Checking and deleting locks
  3. Running a single query
  4. Other changes
  5. Upgrading
  6. Getting help

1. Locking your run

This release adds the ability to lock your run - this ensures that you cannot accidentally start another job whilst one is already running. This feature is aimed primarily at automation, whereby you schedule your jobs on a cron or some other scheduler and you need to ensure that each job is a singleton.

We have also introduced two types of locks that you can use:

  1. Hard lock: This lock will be set and removed only if a job completes successfully; if the job fails then this lock will persist and will subsequently prevent this job from being run again until the lock is removed. The default lock type for SQL Runner
  2. Soft lock: This lock will be set and removed regardless of if the job completes successfully; it simply prevents concurrent runs from occurring

For those scheduling jobs randomly over a cluster we have also added the ability to set locks using Consul. This allows you to set and delete a lock which is then automatically available to all members of the Consul cluster.

To use Consul instead of the local filesystem simply append the -consul argument.

To set a hard lock:

$ ./sql-runner -playbook ${path_to_playbook} -lock /locks/hard/1
$ ./sql-runner -playbook ${path_to_playbook} -lock locks/hard/1 -consul ${consul_server_uri}

Here’s the run failing because of an existing lockfile:

$ ./sql-runner -playbook ${path_to_playbook} -lock /locks/hard/1
2016/11/08 13:46:16 Error: /locks/hard/1 found on start, previous run failed or is ongoing. Cannot start

To set a soft lock:

$ ./sql-runner -playbook ${path_to_playbook} -softLock /locks/soft/1
$ ./sql-runner -playbook ${path_to_playbook} -softLock locks/soft/1 -consul ${consul_server_uri}

2. Checking and deleting locks

On top of being able to set locks we have also added functions to check and delete these locks. This enables you to automate maintenance of locks based on your own logic.

To check for a lock’s existence:

$ ./sql-runner -checkLock /locks/hard/1
2016/11/08 13:47:57 Error: /locks/hard/1 found, previous run failed or is ongoing

$ ./sql-runner -checkLock locks/soft/1 -consul ${consul_server_uri}
2016/11/08 13:48:40 Success: locks/soft/1 does not exist

To delete a lock:

$ ./sql-runner -deleteLock /locks/hard/1
2016/11/08 13:49:31 Deleting lockfile at this key '/locks/hard/1'
2016/11/08 13:49:31 Success: /locks/hard/1 found and deleted

$ ./sql-runner -deleteLock locks/soft/1 -consul ${consul_server_uri}
2016/11/08 13:50:18 Error: locks/soft/1 does not exist, nothing to delete

3. Running a single query

This release also adds the ability to run a single query of a step in a playbook. This has two main uses:

  1. Allowing you to represent your playbook as a DAG of individual queries, making resumption from a failure point easier to reason about
  2. Allowing you to debug individual queries without having to first make new playbooks that contain only the queries in question

To run an individual query:

$ ./sql-runner -playbook .yml -runQuery "::"

Note the usage of a double colon between the elements of the runQuery value. For this function to work you must ensure that you do not include a double colon anywhere in either your step or query names.

Sample output from our integration test suite:

./sql-runner -playbook /vagrant/integration/resources/good-postgres.yml -var test_date=2016_11_08 -lock /vagrant/dist/integration-lock -runQuery "Parallel load::Parallel load 2"
2016/11/08 13:51:36 Checking and setting the lockfile at this key '/vagrant/dist/integration-lock'
2016/11/08 13:51:36 EXECUTING Parallel load 2 (in step Parallel load @ My Postgres database 2): /vagrant/integration/resources/postgres-sql/good/2b.sql
2016/11/08 13:51:36 EXECUTING Parallel load 2 (in step Parallel load @ My Postgres database 1): /vagrant/integration/resources/postgres-sql/good/2b.sql
2016/11/08 13:51:36 SUCCESS: Parallel load 2 (step Parallel load @ target My Postgres database 2), ROWS AFFECTED: 1
2016/11/08 13:51:36 SUCCESS: Parallel load 2 (step Parallel load @ target My Postgres database 1), ROWS AFFECTED: 1
2016/11/08 13:51:36 Deleting lockfile at this key '/vagrant/dist/integration-lock'
2016/11/08 13:51:36 SUCCESS: 2 queries executed against 2 targets

4. Other changes

The playbook & SQL loading interfaces in our Golang code have been abstracted to allow for easy extensibility (#62). Big thanks to community member Lincoln Ritter for contributing this functionality!

Under the hood, this release also bumps our Postgres library dependency to pg.v5 (#73), and adds tcpKeepAlive=true to help with long-running queries via NAT (#57).

5. Upgrading

SQL Runner 0.5.0 is available as a standalone binary for 64-bit Linux, Windows and macOS on Bintray. Download them as follows:

# Linux
$ wget http://dl.bintray.com/snowplow/snowplow-generic/sql_runner_0.5.0_linux_amd64.zip

# Windows
C:\> Invoke-WebRequest -OutFile sql_runner_0.5.0_windows_amd64.zip http://dl.bintray.com/snowplow/snowplow-generic/sql_runner_0.5.0_windows_amd64.zip

# macOS
$ wget http://dl.bintray.com/snowplow/snowplow-generic/sql_runner_0.5.0_darwin_amd64.zip

Once downloaded, unzip it (Linux for example):

$ unzip sql_runner_0.5.0_linux_amd64.zip

Run it like so:

$ ./sql-runner
sql-runner version: 0.5.0
Run playbooks of SQL scripts in series and parallel on Redshift and Postgres
Usage:
  -checkLock string
      Checks whether the lockfile already exists
  -consul string
      The address of a consul server with playbooks and SQL files stored in KV pairs
  -deleteLock string
      Will attempt to delete a lockfile if it exists
  -dryRun
      Runs through a playbook without executing any of the SQL
  -fromStep string
      Starts from a given step defined in your playbook
  -help
      Shows this message
  -lock string
      Optional argument which checks and sets a lockfile to ensure this run is a singleton. Deletes lock on run completing successfully
  -playbook string
      Playbook of SQL scripts to execute
  -runQuery string
      Will run a single query in the playbook
  -softLock string
      Optional argument, like '-lock' but the lockfile will be deleted even if the run fails
  -sqlroot string
      Absolute path to SQL scripts. Use PLAYBOOK, BINARY and PLAYBOOK_CHILD for those respective paths (default "PLAYBOOK")
  -var value
      Variables to be passed to the playbook, in the key=value format (default map[])
  -version
      Shows the program version

6. Getting help

For more details on this release, please check out the SQL Runner 0.5.0 release notes on GitHub.

If you have any questions or run into any problems, please raise an issue or get in touch with us through the usual channels.

Thoughts or questions? Come join us in our Discourse forum!
Joshua Beemster
Josh is a data engineer at Snowplow. You can find him on and LinkedIn.