SQL¶

Overview¶

Zuar Runner can automate SQL statements to any supported database.

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”.

Fill in the form.

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”

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.

Commenting in SQL¶

Zuar Runner only accepts multi-line comments.

Don’t use this comment syntax:

Use this 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.

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

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