SQL Runner 0.4.0 released

We are pleased to announce version 0.4.0 of SQL Runner. SQL Runner is an open source app, written in Go, that makes it easy to execute SQL statements programmatically as part of a Snowplow data pipeline.

This release adds some powerful new features to SQL Runner – many thanks to community member Alessandro Andrioni for his huge contributions towards yet another release!

1. Consul support
2. Dry run mode
3. Environment variables template function
4. File loading order
5. Upgrading
6. Getting help

1. Consul support

This release adds support for running the SQL Runner with playbooks and SQL files being pulled directly from a Consul key-value store. This mode can be activated by passing the following argument:

$ sql-runner -consul localhost:8500 -playbook runner/playbook-1 ...

Note: Only pass the host name and port number.

This command will instruct the SQL Runner to search the key-value store of the Consul server for your playbook and associated SQL files. In the above example the key that would be used is runner/playbook-1.

In this mode you can also specify a new -sqlroot argument:

• -sqlroot PLAYBOOK_CHILD which will assume the sqlroot is the same as your playbook key: i.e. sqlroot == runner/playbook-1.
• This allows you to treat the key for your playbook as a data and folder node

By default the -sqlroot PLAYBOOK argument is applied which will result in: sqlroot == runner – essentially just starting one level higher in the key hierarchy.

2. Dry run mode

Dry run mode is activated with the -dryRun flag. This allows you to validate that SQL Runner can find and successfully populate (if templates) all of your SQL files. This is useful for asserting that all of your SQL files are valid before doing a live run against your database targets.

We are planning on expanding this mode in the future, to also check the connection to the target so that you can be sure that during a live run everything should work. (#51)

3. Environment variables template function

Expanding on our template options, you can now load variables directly from your environment. To use:

CREATE TABLE some_schema.{{systemEnv "ENV_TABLE_NAME"}} ( age int )<span class="p">;

You can of course also substitute ENV_TABLE_NAME for a custom variable passed either via the -var argument or inside your plabook:

CREATE TABLE some_schema.{{systemEnv .envTableNameVar}} ( age int )<span class="p">;

4. File loading order

Historically SQL Runner would load SQL files “just in time”, i.e. immediately prior to the execution of the associated step. This could prove problematic if a SQL file near the end of your playbook could not be found or failed to successfully template, or if changes were made to SQL files during execution.

All SQL files are now loaded into memory at start-up, and the SQL Runner will fail-fast if any of the SQL files are missing or fail templating.

5. Upgrading

SQL Runner 0.4.0 is available as a standalone binary for 64-bit Linux, Windows and OS X on Bintray. Download them like so:

# Linux $ wget http://dl.bintray.com/snowplow/snowplow-generic/sql_runner_0.4.0_linux_amd64.zip # Windows C:> Invoke-WebRequest -OutFile sql_runner_0.4.0_windows_amd64.zip http://dl.bintray.com/snowplow/snowplow-generic/sql_runner_0.4.0_windows_amd64.zip # OSX $ wget http://dl.bintray.com/snowplow/snowplow-generic/sql_runner_0.4.0_darwin_amd64.zip

Once downloaded, unzip it (Linux for example):

$ unzip sql_runner_0.4.0_linux_amd64.zip

Run it like so:

$ ./sql-runner sql-runner version: 0.4.0 Run playbooks of SQL scripts in series and parallel on Redshift and Postgres Usage: -help=false: Shows this message -playbook="": Playbook of SQL scripts to execute -sqlroot="PLAYBOOK": Absolute path to SQL scripts. Use PLAYBOOK, BINARY and CONSUL for those respective paths (default "PLAYBOOK") -fromStep="": Starts from a given step defined in your playbook -var=map[]: Variables to be passed to the playbook, in the key=value format (default map[]) -version=false: Shows the program version -consul="": The address of a consul server with playbooks and SQL files stored in KV pairs -dryRun=false: Runs through a playbook without executing any of the SQL

6. Getting help

For more details on this release, please check out the SQL Runner 0.4.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.