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
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=localhostMany 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=localhostNote 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
-consulOnlyForLockflag lets you run local playbooks, while using Consul for locking. - A random number templating function has been added – use
randomIntin 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
SUCCESSandERRORmessages to the terminal. - Our Snowflake target configuration now lets you specify
us-westas 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.zipOnce downloaded, unzip it, for example with Linux:
$ unzip sql_runner_0.7.0_linux_amd64.zipRun 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 version6. 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.
