SQL Runner 0.7.0 released

11 September 2018  •  Mike Hadam

We are pleased to announce version 0.7.0 of SQL Runner. This release adds many new features including the printing out of query output, templated playbooks, the ability to view evaluated SQL file templates, and more:

  1. Viewing query output
  2. Templated playbooks
  3. Check SQL queries
  4. Other changes
  5. Upgrading
  6. Getting help

1. Viewing query output

Version 0.7.0 introduces the ability to view output from queries. Using the -showQueryOutput flag will print all query results to the console formatted in a table, following each step.

$ ./sql-runner -showQueryOutput -playbook integration/resources/good-postgres.yml -var test_date=`date "+%Y_%m_%d"` -fromStep "Create schema and table"
...
2018/08/15 00:17:35 EXECUTING Output (in step Output @ My Postgres database 1): /opt/gopath/src/github.com/snowplow/sql-runner/integration/resources/postgres-sql/good/output.sql
2018/08/15 00:17:35 QUERY OUTPUT:
| AGE | FIRSTNAME |   CITY   | COUNTRY |
|-----|-----------|----------|---------|
|  18 | john      | new york | us      |
|  20 | ben       | london   | uk      |
|  32 |           |          |         |
...

2. Templated playbooks

The -var will now pass variables into playbooks, as it does with SQL files. This should be useful for treatment of secrets and credentials that you don’t want to embed directly in playbooks.

Along with this, the -var flag also now permits multiple key-value pairs; the pairs must be comma separated (e.g. key=value,key2=value2), like so:

$ ./sql-runner -playbook integration/resources/good-postgres-with-template.yml -var password=,host=localhost

Many thanks to community member dannymc129 for contributing these features!

3. Check SQL queries

The new -fillTemplates flag will evaluate a SQL file, and print the query in the console, exactly how it would be run against the database. This can assist in debugging templated files, where it’s useful to see the transformations around how variables are inserted into templates.

For example:

$ ./sql-runner -fillTemplates -playbook integration/resources/good-postgres-with-template.yml -var username=postgres,password=,host=localhost

Note that with the -fillTemplates flag, no SQL will actually be executed.

4. Other changes

This release brings a whole host of other updates:

  • The new -consulOnlyForLock flag lets you run local playbooks, while using Consul for locking.
  • A random number templating function has been added - use randomInt in your SQL templates. Thanks to community member Tobi for contributing this feature!
  • SQL Runner now returns a dedicated exit code (8) if no queries are found to be run.
  • During dry run, SQL Runner will now attempt to connect to targets, printing corresponding SUCCESS and ERROR messages to the terminal.
  • Our Snowflake target configuration now lets you specify us-west as the region variable for the default Snowflake region (previously this would error).
  • Use of godep has been replaced with dep.

5. Upgrading

SQL Runner 0.7.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.7.0_linux_amd64.zip

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

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

Once downloaded, unzip it, for example with Linux:

$ unzip sql_runner_0.7.0_linux_amd64.zip

Run it like so:

$ ./sql-runner
sql-runner version: 0.7.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
  -consulOnlyForLock
      Will read playbooks locally, but use Consul for locking.
  -deleteLock string
      Will attempt to delete a lockfile if it exists
  -showQueryOutput
      Will print all output from queries
  -dryRun
      Runs through a playbook without executing any of the SQL
  -fillTemplates
      Will print all queries after templates are filled
  -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
  -version
      Shows the program version

6. Getting help

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