Blogs

How to generate AWS custom text alerts

As more of our customers are now using Kognitio on AWS I thought it would be good to look at how some of the enormous range of services available on AWS can be accessed directly from Kognitio SQL.

Kognitio has a capability called External Scripts and this is generally used for advanced analytics but the scripts are very flexible and can be used to access many things including AWS services either from the command line or via the python libraries. 

A common usage pattern for Kognitio on AWS is to create RAM images at the start of the day using data that has been extracted from operational systems overnight. Best practice is then to run a script to check that there have been no problems with the overnight processing – checks such as ensuring there is data from the previous day etc.

If there are problems, it is better to proactively send out an alert message rather than waiting for someone to check the report. This blog shows you how to do this by sending SMS and email messages using the AWS Simple Notification System. 

We assume some familiarity and adequate permissions in both Kognitio and AWS. There will be AWS charges incurred for sending text messages etc but they are reasonable for a small number of messages.

Checking the images

First, lets simulate a problem:

-- create a schema for this exercise
create schema validation;
set schema validation;

-- create a table to collect messages in
create table alert_messages
(
  alert_ts timestamp,
  message varchar(1000)
);

-- create a table simulating a table that has missing data for the previous day
create table t1
(
  some_columns int,
  loaded_date date
);

insert into t1 values
--(1, current_date - interval '1' day),
(2, current_date - interval '2' day),
(3, current_date - interval '3' day),
(4, current_date - interval '4' day);


-- check table t1 to make sure it has data for the previous day and 
-- insert a message into the alert_message table if not
insert into alert_messages
    select * from values (current_timestamp, 'Table t1 has no data for yesterday')
    where not exists(select * from t1 where loaded_date = current_date - interval '1' day);

select * from alert_messages;
ALERT_TS MESSAGE
2019-09-13 11:57:39.000000 Table t1 has no data for yesterday

This simple example shows how to insert a message if data for the previous day is missing but of course more complex tests and more informative messages can be produced depending on your data.

Sending notifications

We will now set up the AWS Simple Notification Service (SNS) so that we can alert the correct people to deal with the problem. We will set up two topics, one for summarised SMS notifications and one for more informative email messages.

  • Choose “Simple Notification Service” from the AWS console services menu, click topics and then click “Create Topic”
  • Now enter the topic name “alert-blog-sms”, a display name of your choice and fill in any other options you require (tagging for example) and then click “Create Topic”. 
  • You will be taken to the new topic page where you can add subscriptions – your own mobile number is probably a good start.

Now repeat and use the topic name “alert-blog-email” and add your email address as a subscription (you should receive an email asking you to confirm this subscription).

To be able to send messages from an external script you will need to add the KognitioDatanodeRole ARN to the access policy of each SNS topic or create a policy like the one below (make sure you replace the ARN for each topic with your own) and attach it to the KognitioDatanodeRole. 

The KognitioDataNode role is created by the Kognitio CloudFormation template as part of creating the Kognitio Launcher and the Role ARN for it is available by following the link next to it in the Resources section of the Launchers CloudFormation stack.

If you are running a local Kognitio server on the Launcher you will need to attach the policy to the KognitioLauncherRole instead.

{
    "Version": "2012-10-17",
    "Statement": [
        {
            "Sid": "VisualEditor0",
            "Effect": "Allow",
            "Action": [
                "sns:Publish"
            ],
            "Resource": "arn:aws:sns:eu-west-1:000000000000:alert-blog-sms"
        },
        {
            "Sid": "VisualEditor1",
            "Effect": "Allow",
            "Action": [
                "sns:Publish"
            ],
            "Resource": "arn:aws:sns:eu-west-1:000000000000:alert-blog-email"
        }
    ]
}

We can now use an external script to send a summary to your mobile (remember to replace the SNS topic ARN with the one for your topic):

-- publish a message on the text message SNS topic
external script using environment local_shell
receives(alert_ts timestamp, message varchar(1000))
sends(bash_output varchar(5000) character set utf8)
output 'fmt_field_separator "^"'
limit 1 threads
requires 250 MB RAM
script S'EOF(
nmessages=$(wc -l)
if [[ "${nmessages}" == "0" ]] ; then
  message="There are no alert messages for this mornings imaging"
else
  message="You have ${nmessages} message(s) in the Kognitio alert_messages table"
fi
aws sns publish --topic-arn arn:aws:sns:eu-west-1:000000000000:alert-blog-sms --message "${message}" --region eu-west-1
)EOF'
from table alert_messages;

Or we can send a list of the messages in an email:

-- publish a longer message on the email SNS topic
external script using environment local_shell
receives(alert_ts timestamp, message varchar(1000))
sends(bash_output varchar(5000) character set utf8)
output 'fmt_field_separator "^"'
limit 1 threads
requires 250 MB RAM
script S'EOF(
nmessages=0
messages=" "
while read LINE; do
  ((++nmessages))
  messages="${messages}
${LINE}"
done 
if [[ "${nmessages}" == "0" ]] ; then
  message="There are no alert messages for this mornings Kognitio imaging"
else
  message="You have ${nmessages} message(s) in the Kognitio alert_messages table:${messages}"
fi
aws sns publish --topic-arn arn:aws:sns:eu-west-1:000000000000:alert-blog-email --region eu-west-1 --message "${message}"
)EOF'
from (select * from alert_messages order by alert_ts);

There are many more options in SNS including push notifications but the basic approach above will be the same and the AWS documentation is the best place to learn about them.

This basic introduction to using AWS services with Kognitio shows how to:

  • Attach an access policy for the service you want to use to the KognitioDatanodeRole or to the KognitioLauncherRole role.
  • Call the AWS service using the AWS CLI.

You could also use a Python external script and the boto3 library for more complex tasks. 

The source code used above is available from the Kognitio GitHub page.

Please leave any comments, questions or suggestions for further blogs below.

Leave a Reply

Your email address will not be published nor used for any other purpose. Required fields are marked *