You can translate the question and the replies:

best way to create a scheduler job to throw exception if a table has records in it

Hi Sorry if this is covered in some example docs but i've not found anything that really helps with what i'm looking for. Can you provide an example of how to create a scheduled job that will throw an error if the extraction sql query returns records I have a number of jobs that are depenedent on each other, however the first job needs to check that the last job ran successfully i.e the last job cleans up after itself and truncates a number of staging tables. i cant set the dependency of the first job on the last job as this creates a circular dependency what i'm wanting to do is simply select * from a table that i know should be empty if the last job failed to run. I also dont want to truncate the tables first incase the jobs didnt complete and i want to investigate how far through the process it got before the automation job kicks off again My underlying tables are postgres so i cant use a stored proc as its not supported in my version of postgres, i was thinking i could write a custom function to call that but i would like to know if anyone has some sample code of doing this and if i can do it direct from the extraction sql Any help is appreciated
user
22-10-2019 04:28:05 -0400

3 Answers

Hi, I see that what you are looking for should be easily achievable using the Triggers section of the Scheduler jobs that you have created. There is an option to add dependencies where you can choose from other jobs in that you have created in the Scheduler, which need to be complete before this job gets executed. For more information, you can refer to [Dependencies Among Jobs](https://community.denodo.com/docs/html/browse/7.0/scheduler/administration/creating_and_scheduling_jobs/configuring_new_jobs/dependencies_among_jobs) Hope this helps.
Denodo Team
23-10-2019 04:17:37 -0400
Hi Not really I think you've misunderstood. I'm actually using the triggers dependency of the jobs. What i need is to create a job that fails if the Select statement returns a record count so that my dependent jobs dont execute. I have chained dependencies from the last to the first job, but i only want the first job to trigger if the last job successfully executed last time. If hasnt executed yet or is errored then i want the first job to error and not execute. If I make the first job dependent on the last job then you get a circular dependency chain that mean no job executes. What i'm looking for is a way to inform the first job that the last job hasnt completed successfully so dont start - i need an exampl of how to throw an exception to cause the job to error.
user
23-10-2019 04:30:43 -0400
OK so i've resolved this by creating a custom function (as below) in postgres that i call in the extraction query "select ststg.is_table_truncated('myschemaname','mytablename');" in first job with no dependencies. The function fails if it finds a value in the table that i expect to be empty if my last job had truncated it. CREATE OR REPLACE FUNCTION "ststg"."is_table_truncated"(schema_name text, table_name text) RETURNS integer AS $func$ DECLARE cnt int; tabname text; BEGIN tabname = schema_name ||'.'||table_name; EXECUTE 'SELECT count(*) FROM ' || tabname INTO cnt; IF cnt > 0 THEN RAISE EXCEPTION 'Staging tables are not truncated for table %, record count = %', tabname, cnt USING HINT = 'Check the ETL jobs completed successfully'; END IF; RETURN cnt; END $func$ LANGUAGE plpgsql; I hope this might help others.
user
23-10-2019 08:07:25 -0400
You must sign in to add an answer. If you do not have an account, you can register here