SQL Runner 0.7.0 released

Share

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:

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.

Share

Related articles