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.
- Locking your run
- Checking and deleting locks
- Running a single query
- Other changes
- Upgrading
- 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:
- 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
- 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 startTo 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 existTo 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 delete3. 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:
- Allowing you to represent your playbook as a DAG of individual queries, making resumption from a failure point easier to reason about
- 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 targets4. 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.zipOnce downloaded, unzip it (Linux for example):
$ unzip sql_runner_0.5.0_linux_amd64.zipRun 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 version6. 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.
More about
the author
Joshua Beemster
Josh is a Head of Engineering and enjoys mapping out how to manage infrastructure across multiple clouds using a combination of Terraform, K8s and Helm. He works mainly with the teams responsible for infrastructure and the core data processing teams at Snowplow.
View author
