Clickstream Analysis on Amazon EMR, Amazon Redshift and Elasticsearch
Learn how quickly you can start ingesting and aggregating clickstream logs using StreamSets Transformer running on Amazon EMR, and see how the data is analyzed in Elasticsearch, Kibana, and Amazon Redshift.
Clickstream analysis by definition is the process of collecting, analyzing, and reporting aggregate information about webpage visits. StreamSets Transformer is an execution engine that runs on different “flavors” of Apache Spark including Amazon EMR, Hadoop, Databricks, and SQL Server 2019 Big Data Cluster. For a full list, see installation requirements.
Here are the details of the dataset and pipeline components:
- Transformations: Include aggregations, such as:
- Number of views for each session with respect to action for a specific URL
- The total number of sessions for each client IP address
- Number of events captured for each brand of products
- Aggregations are stored in Amazon Redshift tables. (Note: if the tables don’t already exist, the destination can be configured for the tables to be auto-created.)
- All the logs are sent to Elasticsearch for searching and quick visualizations in Kibana. (Note: if the index doesn’t already exist in Elasticsearch, the destination can be configured for the index to be auto-created.)
Here are the aggregated stats being collected and stored in Amazon Redshift.
Elasticsearch and Kibana
Once the logs were available in Elasticsearch, I created an index pattern called clickstream_data with all the attributes of the logs.
Using the clickstream_data index pattern as source, I then created a dashboard with different visualizations in Kibana.
- Session Wise Analysis — Number of views for each session with respect to action for a specific URL
- Client Wise Analysis — The total number of sessions for each client IP address.
- Brand Analysis — Number of events captured for each brand of products
- HTTP Response Analysis — Number of events captured with a response status such as Successful, Request Lost, No Response, Error Response, etc.
Querying Amazon Redshift
Aggregations are stored in number_of_views_per_session, number_of_sessions_per_ip, and number_of_events_per_http_response Redshift tables for faster-querying. For example, query to see the top 5 IP addresses from where the HTTP sessions were initiated.
SELECT distinct(clientip),total_sessions from number_of_sessions_per_ip order by total_sessions desc limit 5
Sample Pipeline and Dataset on GitHub
After importing the sample pipeline, update pipeline parameters with Amazon EMR credentials and other configuration details, such as, Amazon S3 buckets, Amazon Redshift endpoint, and Elasticsearch URL before running the pipeline.
While there are different ways to dissect and analyze data, hopefully this blog and demo video gives you ideas on how to use some of these tools you might have at your disposal in order to make better, data-driven decisions, faster.
Originally published at https://streamsets.com on April 23, 2020.