SQL Runner

Overview

Run playbooks of SQL scripts in series and parallel on Snowflake DB, Amazon Redshift and PostgreSQL.

Used with Snowplow for scheduled SQL-based transformations of event stream data.

Setup Guide	User Guide

Quick start

Assuming go, docker and docker-compose are installed:

 host> git clone https://github.com/snowplow/sql-runner
 host> cd sql-runner
 host> make setup-up    # Launches Consul + Postgres for testing
 host> make             # Builds sql-runner binaries
 host> make test        # Runs unit tests

 # DISTRO specifies which binary you want to run integration tests with
 host> DISTRO=darwin make integration

Note: You will need to ensure that ~/go/bin is on your PATH for gox to work - the underlying tool that we use for building the binaries.

When you are done with testing simply execute make setup-down to terminate the docker-compose stack.

To reset the testing resources execute make setup-reset which will rebuild the docker containers. This can be useful if the state of these systems gets out of sync with what the tests expect.

To remove all build files:

guest> make clean

To format the golang code in the source directory:

guest> make format

Note: Always run make format before submitting any code.

Note: The make test command also generates a code coverage file which can be found at build/coverage/coverage.html.

How to use?

First either compile the binary from source using the above make command or download the published Binary directly from the GitHub release:

• Darwin (macOS)
• Linux
• Windows

CLI Output

sql-runner version: 0.10.1
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
  -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
  -showQueryOutput
    	Will print all output from queries
  -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

Copyright and license

SQL Runner is copyright 2015-2022 Snowplow Analytics Ltd.

Licensed under the Apache License, Version 2.0 (the "License"); you may not use this software except in compliance with the License.

Unless required by applicable law or agreed to in writing, software distributed under the License is distributed on an "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. See the License for the specific language governing permissions and limitations under the License.