In today's fast-paced business world, data-driven decision-making is crucial. However, accessing and analyzing data can be a tedious and time-consuming task, especially for database administrators (DBAs) and business people who rely heavily on data to drive their decisions. To address this problem, a database alert system has been developed that can automate data monitoring and alert users via email. The database alert system is designed to help users save time and effort by automating data monitoring. Users can store SQL queries that they want to monitor, and the system will run these queries on a selected data and frequency. If the system finds any results for the query, it will alert the user via email.
To build a database alert system, you need to follow a few simple steps. First, you need to set up the database and tables to store the queries, users, and other information. Any database management system (DBMS) can be used, such as MySQL, PostgreSQL, or SQL Server. Next, you need to create a user interface that allows users to input the queries and select the data and frequency. This user interface can be created using any programming language, such as Python, PHP, or JavaScript. Once the user inputs the queries and selects the data and frequency, the system executes the queries and checks for any results. If it finds any results, it sends an email alert to the user.
To implement the query execution and alert system, Python and SQL can be used. Finally, you need to test the system to ensure that it is working correctly. You can test the system by inputting some test queries and selecting a short frequency to check if the system alerts you via email. The database alert system has many benefits for DBAs and business people. For DBAs, the system can help them monitor their application metrics and track their business processes without having to manually check multiple dashboards and run SQL queries. For business people, the system can help them keep track of their business operations and make data-driven decisions.
In conclusion, the database alert system is a simple yet effective solution for automating data monitoring and alerting users via email. It can help DBAs and business people save time and effort by automating their data monitoring and making it easier for them to access the data they need. The system can be built using any DBMS and programming language, and it has many benefits for businesses looking to streamline their data monitoring processes.
Table structure
create or replace TABLE USFD.ETL.ALERTSYSTEM (
ROWNUMBER NUMBER(38,0),
SQLQUERY VARCHAR(1000),
FREQUENCY VARCHAR(50),
EMAILTO VARCHAR(500),
SUBJECT VARCHAR(500),
BODY VARCHAR(1000),
ISACTIVE BOOLEAN,
LASTRUNDATETIME TIMESTAMP_NTZ(9),
LASTRUNSTATUS BOOLEAN,
LASTRUNERROR VARCHAR(1000)
);
Notification integration object definition
create or replace notification integration my_email
type=email
enabled=true
allowed_recipients=('abc@gmail.com', 'abc@GMAIL.COM');
System Send email definition
call system$send_email(
'my_email',
'abc@gmail.com,abc@gmail.com',
'Test',
'This is the test email from Snowflake'
);
Procedure to pull records that are scheduled at the time of running the proc
CREATE OR REPLACE PROCEDURE USFD.ETL.DAILYALERT()
RETURNS TABLE ("ROWNUMBER" NUMBER(38,0), "SQLQUERY" VARCHAR(2000), "EMAILTO" VARCHAR(75), "SUBJECT" VARCHAR(250), "BODY" VARCHAR(5000), "FREQUENCY" VARCHAR(250))
LANGUAGE SQL
EXECUTE AS OWNER
AS '
Declare
DayNumber varchar(25);
res Resultset;
Begin
DayNumber := DATE_PART(dw, CURRENT_TIMESTAMP()::DATE);
res := (SELECT ROWNUMBER,SQLQUERY,EMAILTO,SUBJECT,BODY,FREQUENCY FROM usfd.etl.AlertSystem
WHERE
( FREQUENCY like ''Weekly%'' AND ( LASTRUNDATETIME IS NULL OR TO_DATE(LASTRUNDATETIME) <> CURRENT_DATE())
And SUBSTRING(FREQUENCY,8,1) = TO_CHAR( DATE_PART(dw, CURRENT_TIMESTAMP()::DATE))
And SUBSTRING(FREQUENCY,10) <= TO_CHAR(CURRENT_TIMESTAMP(), ''HH24:MI''))
Or
(( FREQUENCY like ''Daily%'' ) AND ( LASTRUNDATETIME IS NULL OR TO_DATE(LASTRUNDATETIME) <> CURRENT_DATE()) And REPLACE(FREQUENCY,''Daily_'','''') <= TO_CHAR(CURRENT_TIMESTAMP(), ''HH24:MI''))
Or
(FREQUENCY LIKE ''Every_%'' AND ( LASTRUNDATETIME IS NULL OR REPLACE(FREQUENCY,''Every_'','''') <= TIMEDIFF(Hour, LASTRUNDATETIME, CURRENT_TIMESTAMP())))
);
return table(res);
end
;
Procedure to send email from the alert pulled records
CREATE OR REPLACE PROCEDURE USFD.ETL.CALLALERT123()
RETURNS VARCHAR(16777216)
LANGUAGE JAVASCRIPT
EXECUTE AS OWNER
AS '
var rownum,sqlquery, emails, emailsubject, emailbody,sql1,resultdata;
var return_value = "";
var errstr="";
resultdata="";
var currenttime;
var sql_updatestatement,sqlupdatequery;
try
{
var sql_query = "call DailyAlert()" ;
var sql_statement = snowflake.createStatement({
sqlText: sql_query
}
);
/* Creates result set */
var result_scan = sql_statement.execute();
while (result_scan.next())
{
rownum=result_scan.getColumnValue(1);
sqlquery=result_scan.getColumnValue(2);
emails=result_scan.getColumnValue(3);
emailsubject=result_scan.getColumnValue(4);
//return sqlquery;
sql_statement = snowflake.createStatement({sqlText:sqlquery});
//return sql_statement;
emailbody=sql_statement.execute();
while(emailbody.next())
{
resultdata+=''\\n''+ emailbody.getColumnValue(1);
}
if(resultdata!="")
{
sqlquery = "call system$send_email(''my_email'',''"+emails+"'',''"+emailsubject+"'',''"+ resultdata+"'')";
//return sqlquery;
sql_statement = snowflake.createStatement({sqlText:sqlquery});
sql_statement.execute();
}
//update lastrundatetime for that row
var STORE_DATE1 = snowflake.execute({sqlText: "select CURRENT_TIMESTAMP(2)"});
while (STORE_DATE1.next())
{
currenttime = STORE_DATE1.getColumnValue(1);
}
sqlupdatequery = "update usfd.etl.ALERTSYSTEM set LASTRUNDATETIME=''" + currenttime.toISOString() + "'',LASTRUNSTATUS =true where rownumber="+rownum;
//return sqlupdatequery;
sql_updatestatement = snowflake.createStatement({sqlText:sqlupdatequery});
sql_updatestatement.execute();
}
}
catch (err)
{
var STORE_DATE1 = snowflake.execute({sqlText: "select CURRENT_TIMESTAMP(2)"});
while (STORE_DATE1.next())
{
currenttime = STORE_DATE1.getColumnValue(1);
}
errstr = "Failed: Code: " + err.code + "\\n State: " + err.state;
errstr += "\\n Message: " + err.message;
errstr += "\\nStack Trace:\\n" + err.stackTraceTxt;
sqlquery = "call system$send_email(''my_email'',''abc@gmail.com'',''Error in Alert'',''" + errstr +"'')";
sql_statement = snowflake.createStatement({sqlText:sqlquery});
sql_statement.execute();
sqlupdatequery = "update usfd.etl.ALERTSYSTEM set LASTRUNDATETIME=''" + currenttime.toISOString() + "'', LASTRUNSTATUS =false,lastrunerror=''"+ errstr +"'' where rownumber="+rownum;
//return sqlupdatequery;
sql_updatestatement = snowflake.createStatement({sqlText:sqlupdatequery});
sql_updatestatement.execute();
}
//return ''Success'';
return sqlupdatequery;
';
Task scheduled every 60 minutes
CREATE OR REPLACE TASK DailyAlertTask
WAREHOUSE = COMPUTE_WH
SCHEDULE = '60 minute'
AS
call CallAlert123()
show tasks;
alter task dailyalerttask resume;