Monitor¶
A Zuar Runner monitor job sends a SQL statement to a specific
database. When the job is run, if any result other than 0
, FALSE
,
or NULL
is returned by the SQL statement, the Zuar Runner monitor job
fails.
You can think of a monitor job as a SQL test.
This job type can be used as part of a sequence and stop the sequence if certain SQL conditions are met (if the sequence is set to not continue on error). It can also be used to alert users when specific SQL conditions are met and trigger other actions via Scheduling
See the Zuar Runner SQL job for more information on automating SQL.
Create a Monitor Job¶
Use the Generic plugin to create a custom IO job.
Set the type to monitor
.
Use the job config below as a template:
{
"dbo": "postgresql://db/analytics",
"sql": [
"select 1;"
]
}
dbo- This is the database you want Zuar Runner to send the SQL statement to. Learn more about database URLs.
sql - This is the SQL statement that is sent to the database in the dbo.
The example monitor job above would fail when run because the result would not be 0
, FALSE
, or NULL
.
Alternatively, this monitor job would succeed:
{
"dbo": "postgresql://db/analytics",
"sql": [
"select 0;"
]
}
Example Monitor Job Use Cases¶
Check for Duplicates in a Table¶
Take a simple table named public.id_name
:
id |
name |
---|---|
1 |
Justin |
2 |
Andy |
We can create a monitor job to test that this table (public.id_name
)
doesn’t contain any duplicate values in the name column.
The SQL statement we will use:
SELECT name, COUNT(*) FROM public.id_name GROUP BY name HAVING COUNT(*) > 1;
The monitor job config looks like this:
{
"dbo": "postgresql://db/analytics",
"sql": [
"SELECT name, COUNT(*) FROM public.id_name GROUP BY name HAVING COUNT(*) > 1;"
]
}
When this job runs, the SQL statement returns no results and therefore the job succeeds.
However, if we insert a duplicate name into the table: idname1Justin2Andy3Justin When the monitor job runs, it fails.
Check for missing values in a table¶
Take the same example table above named public.id_name
:
id |
name |
---|---|
1 |
Justin |
2 |
Andy |
We can create a monitor job to test that this table (public.id_name
) doesn’t have any missing values in the name column.
The SQL statement we will use:
SELECT COUNT(*) FROM public.id_name WHERE name IS NULL;
The monitor job config looks like this:
{
"dbo": "postgresql://db/analytics",
"sql": [
"SELECT COUNT(*) FROM public.id_name WHERE name IS NULL;"
]
}
When this job runs, the SQL statement returns 0
and therefore the job succeeds.
However, if we insert a NULL
name into the table:
id |
name |
---|---|
1 |
Justin |
2 |
Andy |
3 |
NULL |
When the monitor job runs, it fails.