TCB: tables

Oracle SQL Scheduled Jobs

A DB Scheduler is the best way to automate any backend database job.

For instance, if you want to process the persisted data using and stored procedure, in the background mode and do want to bother about the end to end start and stop of the process, a database job can be scheduled which would invoke the stored procedure, which further would invoke the tables' data, process the data and persist the data in the formatted/ processed conditioned state in the target tables.

Some common usage of such pipelines is to process the dashboard numbers per the populated data.

This blog-post would now walk you through the simple process involved in implementing a basic scheduled database pipeline, and would be using Oracle SQL as the technology choice.

1. Check if you have create table access, per result of the following sql:

CREATE TABLE abc_test
( id number(10),
name varchar2(50)
);
commit;

2. Check if the table is created:

select * from abc_test;

-- should return empty resultset if table exists

3. Create a stored procedure to do the activity required (like populate data in the table):

   create or replace procedure abc_test_proc as
       begin
       insert into abc_test(id, name) values (1,'first_test');
       commit;
       end;
   commit;

4. Create a scheduled job, that will invoke the above created stored proc. at a specified timestamp:

5. Validate the data in the table:

In day to day heavy transactions happening in our DB, we sometimes wish to retain the previous states of the tables.

This not only prevents the scenarios of recovering data after deletions by mistakes but also helps during the comparison requirements.

Here is a quick take on such a use case, where the developer has been asked for making sure the tables are backed up on the daily basis.

Based on the business requirements and infra availability, the clean up period can be decided. For this example, let's take a clean-up policy of 1 day.

Let's create a sample table - 'employees', using MySQL -

mysql> show create table employees;

+-----------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

| Table | Create Table |

+-----------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

| employees | CREATE TABLE `employees` (

`id` int(11) NOT NULL AUTO_INCREMENT,

`name` varchar(50) DEFAULT NULL,

PRIMARY KEY (`id`)

) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8 |

+-----------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

1 row in set (0.00 sec)

mysql> select * from employees;

+----+------+

| id | name |

+----+------+

| 1 | ABC |

| 2 | DEF |

+----+------+

2 rows in set (0.00 sec)

Let's create a stored procedure now, which will take care of the running on regular basis and meeting the expected functionality -

Repo:

https://github.com/namitsharma99/tableBackupStoredProcedure/blob/master/backup_main_tables.sql

Raw Code:

DROP PROCEDURE backup_main_tables;

DELIMITER $$

CREATE PROCEDURE backup_main_tables()

BEGIN

-- setup for today's backup

SET @today_table_name := concat('bkp_employees_',date_format(curdate(), '%m_%d_%Y'));

SET @today_stmnt := concat('create table ', @today_table_name, ' select * from employees;');

-- setup for yesterday's backup removal

SET @yesterday_table_name := concat('bkp_employees_',date_format(curdate()-1, '%m_%d_%Y'));

SET @yesterday_stmnt := concat('drop table ', @yesterday_table_name);

-- execute today's plan

PREPARE today_stmt FROM @today_stmnt;

EXECUTE today_stmt;

-- remove yesterday's backup

PREPARE yesterday_stmt FROM @yesterday_stmnt;

EXECUTE yesterday_stmt;

END $$

DELIMITER ;

mysql> show create procedure backup_main_tables;

+--------------------+----------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+

| backup_main_tables | | CREATE DEFINER=`admin`@`%` PROCEDURE `backup_main_tables`()

BEGIN

SET @today_table_name := concat('bkp_employees_',date_format(curdate(), '%m_%d_%Y'));

SET @today_stmnt := concat('create table ', @today_table_name, ' select * from employees;');

SET @yesterday_table_name := concat('bkp_employees_',date_format(curdate()-1, '%m_%d_%Y'));

SET @yesterday_stmnt := concat('drop table ', @yesterday_table_name);

PREPARE today_stmt FROM @today_stmnt;

EXECUTE today_stmt;

PREPARE yesterday_stmt FROM @yesterday_stmnt;

EXECUTE yesterday_stmt;

END | latin1 | latin1_swedish_ci | latin1_swedish_ci |

1 row in set (0.00 sec)

Dry Run Example:

mysql> select current_timestamp;

+---------------------+

| current_timestamp |

+---------------------+

| 2020-09-13 20:33:44 |

+---------------------+

1 row in set (0.00 sec)

Let's create a table supposing it was created yesterday -

mysql> create table bkp_employees_09_12_2020 select * from employees;

Query OK, 2 rows affected (0.04 sec)

Records: 2 Duplicates: 0 Warnings: 0

mysql> show tables;

+--------------------------+

| Tables_in_namit_bkp_test |

+--------------------------+

| bkp_employees_09_12_2020 |

| employees |

+--------------------------+

2 rows in set (0.00 sec)

mysql> select * from bkp_employees_09_12_2020;

+----+------+

| id | name |

+----+------+

| 1 | ABC |

| 2 | DEF |

+----+------+

2 rows in set (0.00 sec)

Invoking the stored procedure to test:

mysql> call backup_main_tables();

Query OK, 0 rows affected (0.03 sec)

mysql> show tables;

+--------------------------+

| Tables_in_namit_bkp_test |

+--------------------------+

| bkp_employees_09_13_2020 |

| employees |

+--------------------------+

2 rows in set (0.00 sec)

mysql> select * from bkp_employees_09_13_2020;

+----+------+

| id | name |

+----+------+

| 1 | ABC |

| 2 | DEF |

+----+------+

2 rows in set (0.00 sec)

Now this appears to be working fine.

But we cannot everyday login and manually execute this statement, correct?

So, let's schedule it on a daily basis, preferably at the COB (closure of business) timing.

We can use MySQL Events for this example, as below.

Repo:

https://github.com/namitsharma99/tableBackupStoredProcedure/blob/master/event.sql

Raw Code:

DELIMITER $$

DROP EVENT IF EXISTS run_proc_call $$

CREATE

EVENT `run_proc_call`

ON SCHEDULE EVERY 1 DAY STARTS '2020-09-13 23:55:55'

ON COMPLETION NOT PRESERVE ENABLE

BEGIN

call backup_main_tables();

END $$

DELIMITER ;

mysql> show events;

+----------------+---------------+---------+-----------+-----------+------------+----------------+----------------+---------------------+------+---------+------------+----------------------+----------------------+--------------------+

1 row in set (0.01 sec)

Also, as a good practice, it is always recommended to have exception handling in the functions/ procedures/ events. The above examples are very basic in nature, hence I haven’t used.

In MySQL, you can use exit handlers, while in SQL you can use Try-Catch blocks. But, this should be used and the errors should be logged in a dedicated logging table.

Try these out, and please leave a comment in case of any concern 👍

Happy Coding !!

TCB

Oracle SQL Scheduled Jobs - An Interesting Approach

How to take backup of MYSQL tables daily and remove the older backups

Featured post

Oracle SQL Scheduled Jobs - An Interesting Approach