SQL

Overview

Zuar Runner can automate SQL statements to any supported database.

SQL Connector

Use the Zuar Runner SQL plugin to create SQL jobs containing these SQL statements.

Typical use cases for SQL jobs include creating schemas, tables, views, and stored procedures.

Create a SQL Job

Click +Add Job. and choose “SQL job”.

Add Job Image

Fill in the form.

SQL UI Wizard
  • Title - title of the job - Best practice naming convention [SQL] Table or Brief Desc.

  • Sql - the SQL query to be sent to the database

To send queries to a different database than Zuar Runner’s internal PostgreSQL database, uncheck “Use default database URI”

Adjust DB

Click “Done”.

SQL Job Configuration Options

See the full list of supported SQL job configuration options.

SQL Syntax and Validation

SQL queries in SQL jobs need to be written in the syntax of the database they are being sent to.

Zuar Runner does not validate SQL queries when creating a SQL job. Best practice is to write your SQL queries in another tool (e.g. DataGrip) to test the SQL, and then when you are ready to save and automate the SQL, copy the SQL into a Zuar Runner job.

Example SQL Job

This SQL drops and recreates the normal_sql table in the test schema. This is one method for creating new tables in the database.

SQL Editor

Commenting in SQL

Zuar Runner only accepts multi-line comments.

Don’t use this comment syntax:

Incorrect comment syntax

Use this comment syntax:

Correct Comment Syntax

Parameterized SQL

Parameters can be passed to SQL in Zuar Runner jobs. This is useful when users need to make small changes to SQL jobs frequently (e.g. changing a date range in a WHERE clause).

Rather than the user having to comb through tons of SQL, they can simply change a parameter in the SQL jobs JSON config.

Below is an example of using a parameter (:number) in a SQL job.

Using Parameter Example

Instead of hardcoding the value in the SQL itself, the value is defined in the job’s config.

Defining SQL Parameter

Key/value pairs can be added to the parameters of the JSON config.