This documentation describes a release under development. Documentation for the latest release, 3.6.2, can be found here.

SQL

Overview

Mitto can automate SQL statements to any supported database.

Use the Mitto 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 Mitto’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.

Mitto 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 Mitto 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

Mitto only accepts multi-line comments.

Don’t use this comment syntax:

Use this comment syntax:

Parameterized SQL

Parameters can be passed to SQL in Mitto 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.