Monitor the ETL of incremental data as scheduled

During the three-day holiday on May Day, some ETL logic problems occurred, resulting in the daily incremental data to be loaded into DW is not loaded as designed. Therefore, you need to check the generated incremental data after ETL to avoid the problem of passive processing when the incremental data is lost one day.

Requirement: if there is a problem with the incremental data of ETL on the current day, people or programs (DBAs and Bi) need to be informed and processed in a timely manner.

1. Create the following databases and tables on the ODS Server:

Create Database 'imm '/*! 40100 default Character Set utf8 */;

-- Item_info records the names of different projects and spot check tables within the project (premise: data is generated every day in this table). If there are new projects in the future, you only need to add records in this table.
Create Table 'item _ info '(
'Id' int (11) default null,
'Itemname' varchar (64) default null,
'Tabnamechk' char (32) default null
) Engine = InnoDB default charset = utf8;

-- The ICC table records the check time of each project and the status mark of whether ETL is successful.
Create Table 'icc '(
'Id' int (11) not null auto_increment,
'Entertime' datetime default null,
'Itemname' varchar (64) default null,
'Flag' smallint (6) default null,
Primary Key ('id ')
) Engine = InnoDB default charset = utf8;

2. Create procedure and event for the scheduled check:

Create procedure cc_pro ()
Begin
Declare v_max smallint;
Declare v_title bigint;
Declare v_itemn char (32 );
Declare v_tab char (64 );
Declare I smallint;
Set I = 1;
Select max (ID) into v_max from IMM. item_info;
While I <= v_max do
Select itemname, tabnamechk into v_itemn, v_tab from IMM. item_info where id = I;
Set @ dbname = v_itemn;
Set @ tabname = v_tab;
Set @ s_name = Concat (@ dbname, '.', @ tabname );
Set @ tempsql = Concat ('select count (db_id) into @ v_title from ', @ s_name ,';');
Prepare _ stmt from @ tempsql;
Execute _ stmt;
Deallocate prepare _ stmt;
If (@ v_title> 0) then
Insert into Imm. ICC (entertime, itemname, flag) values (now (), v_itemn, 1 );
Else
Insert into Imm. ICC (entertime, itemname, flag) values (now (), v_itemn, 0 );
End if;
Set I = I + 1;
End while;
End

Call procedure regularly and check whether the event scheduler is enabled before that:

+ ----------------- + ------- +
| Variable_name | value |
+ ----------------- + ------- +
| Event_scheduler | on |
+ ----------------- + ------- + Create event 'ccpro _ exec 'on schedule
Every 1 day starts '2017-05-05 07:00:00'
On completion not preserve
Enable
Comment 'check incremental data quality after ETL execution is completed'
Do begin
Call Imm. cc_pro2;
End

 

The above events are used to view the increment of each item at seven o'clock every day.

3. Next, you can write a shell script to send emails to the check results of the day (I used to like to send emails to mobile phone 139, but there are too many emails available for mobile phone binding. So I can send ONLY emails if I am so lazy .)

#! /Bin/bash
# Scriptname: icc_result.sh
# Author: liuming
# Datetime: 2011-05-05

Function send_message (){
Mysql-U $ dbuser-p $ passwd-d $ dbname-e 'select * from ICC where date (entertime) = Date (now (); '> $ r_txt
Mail-s "'date + % Y-% m-% d _ % H: % m' ETL (increment data) Status" $ to_email <$ r_txt
}

Function main (){
R_txt = "'pwd'/icc_result.txt"
Dbuser = 'xxxx'
Passwd = 'xxxx'
Dbname = 'imm'
Tabname = 'icc'
To_email = 'xxxx @ xxx.com'

Send_message
}

Main

 

4. For apps, you can provide the relevant table names for them to determine before calculation. N words are omitted here...