Installing Apache Superset on Amazon EMR: Add data exploration and visualization to your analytics cluster

Posted by Gary A. Stafford in AWS, Bash Scripting, Big Data, Build Automation, Cloud, Python on December 24, 2020

Introduction

AWS provides nearly twenty-five different open-source data analytics applications that can be automatically installed and configured on Amazon Elastic MapReduce (Amazon EMR). Of all those options, EMR doesn’t offer a general-purpose data exploration and visualization tool. However, with EMR, you can automate the installation of additional software as part of the cluster creation process or post cluster creation. This brief post will explore how to install, configure, and access Apache Superset, the modern data exploration and visualization platform on Amazon EMR’s Master Node, as a post-cluster creation step. You can use these same techniques to install other software packages on EMR as well, manually or as part of an automated Data Pipeline.

Amazon EMR

According to AWS, Amazon EMR is a cloud-based big data platform for processing vast amounts of data using open source tools such as Apache Spark, Hive, HBase, Flink, Hudi, and Zeppelin, Jupyter, and Presto. Using Amazon EMR, data analysts, engineers, and scientists are free to explore, process, and visualize data. EMR takes care of provisioning, configuring, and tuning the underlying compute clusters, allowing you to focus on running analytics.

AWS currently offers 5.x and 6.x versions of Amazon EMR. The latest Amazon EMR releases are Amazon EMR Release 6.2.0 and Amazon EMR Release 5.32.0. Each version of Amazon EMR offers incremental major and minor releases of nearly 25 different, popular open-source big-data software packages to choose from, which Amazon EMR will install and configure when the cluster is created.

Apache Superset

According to its website, Apache Superset is a modern data exploration and visualization platform. Superset is fast, lightweight, intuitive, and loaded with options that make it easy for users of all skill sets to explore and visualize their data, from simple line charts to highly detailed geospatial charts.

Superset natively supports over twenty-five data sources, including Amazon Athena and Redshift, Apache Drill, Druid, Hive, Impala, Kylin, Pinot, and Spark SQL, Elasticsearch, Google BigQuery, Hana, MySQL, Oracle, Postgres, Presto, Snowflake, Microsoft SQL Server, and Teradata.

As shown in their Gallery, Superset includes dozens of visualization types, including Pivot Table, Line Chart, Markup, Pie Chart, Filter Box, Bubble Chart, Box Plot, Histogram, Heatmap, Sunburst, Calendar Heatmap, and several geospatial types.

Setup

Using this git clone command, download a copy of this post’s open-source GitHub repository to your local environment.

git clone --branch main --single-branch --depth 1 --no-tags \
    https://github.com/garystafford/emr-superset-demo.git

To demonstrate how to install Apache Superset on EMR, I have prepared an AWS CloudFormation template. Deploying the template, cloudformation/superset-emr-demo.yml, to AWS will result in the AWS CloudFormation stack, superset-emr-demo-dev. The stack creates a minimally-sized, two-node EMR cluster, two Amazon S3 buckets, and several AWS Systems Manager (SSM) Parameter Store parameters.

There is also a JSON-format CloudFormation parameters file, cloudformation/superset-emr-demo-params-dev.json. The parameters file contains values for eight of the ten required parameters in the CloudFormation template, all of which you can adjust. For the remaining two required parameters, you will need to supply the name of an existing EC2 key pair to access the EMR Master node. The key pair will need to be deployed to the same AWS Account into which you are deploying EMR. You will also need to supply a Subnet ID for the EMR cluster to be installed into. The subnet must have access to the Internet to install Superset’s required system and Python packages and to access Superset’s web-based user interface. If you need help creating a VPC and subnet to deploy EMR into, refer to my previous blog post, Running PySpark Applications on Amazon EMR: Methods for Interacting with PySpark on Amazon Elastic MapReduce.

The CloudFormation stack is created using a Python script, create_cfn_stack.py. The python script uses the AWS boto3 Python SDK.

	#!/usr/bin/env python3

	# Purpose: Create EMR bootstrap script bucket and deploy the cfn stack
	# Author: Gary A. Stafford (December 2020)
	# Reference: https://gist.github.com/svrist/73e2d6175104f7ab4d201280acba049c
	# Usage Example: python3 ./create_cfn_stack.py \
	# –ec2-key-name emr-demo-123456789012-us-east-1 \
	# –ec2-subnet-id subnet-06aa61f790a932b32 \
	# –environment dev


	import argparse
	import json
	import logging
	import os

	import boto3
	from botocore.exceptions import ClientError

	sts_client = boto3.client('sts')
	cfn_client = boto3.client('cloudformation')
	region = boto3.DEFAULT_SESSION.region_name
	s3_client = boto3.client('s3', region_name=region)

	logging.basicConfig(format='[%(asctime)s] %(levelname)s – %(message)s', level=logging.INFO)


	def main():
	args = parse_args()

	# create bootstrap bucket
	account_id = sts_client.get_caller_identity()['Account']
	bootstrap_bucket = f'superset-emr-demo-bootstrap-{account_id}–{region}'
	create_bucket(bootstrap_bucket)

	# upload bootstrap script
	dir_path = os.path.dirname(os.path.realpath(__file__))
	upload_file(f'{dir_path}/bootstrap_emr/bootstrap_actions.sh', bootstrap_bucket, 'bootstrap_actions.sh')

	# set variables
	stack_name = f'emr-superset-demo-{args.environment}'
	cfn_template_path = f'{dir_path}/cloudformation/superset-emr-demo.yml'
	cfn_params_path = f'{dir_path}/cloudformation/superset-emr-demo-params-{args.environment}.json'
	ec2_key_name = args.ec2_key_name

	# append new parameters
	cfn_params = _parse_parameters(cfn_params_path)
	cfn_params.append({'ParameterKey': 'Ec2KeyName', 'ParameterValue': ec2_key_name})
	cfn_params.append({'ParameterKey': 'Ec2SubnetId', 'ParameterValue': args.ec2_subnet_id})
	cfn_params.append({'ParameterKey': 'BootstrapBucket', 'ParameterValue': bootstrap_bucket})
	logging.info(json.dumps(cfn_params, indent=4))

	# create the cfn stack
	create_stack(stack_name, cfn_template_path, cfn_params)


	def create_bucket(bootstrap_bucket):
	"""Create an S3 bucket in a specified region

	:param bootstrap_bucket: Bucket to create
	:return: True if bucket created, else False
	"""

	try:
	s3_client.create_bucket(Bucket=bootstrap_bucket)
	logging.info(f'New bucket name: {bootstrap_bucket}')
	except ClientError as e:
	logging.error(e)
	return False
	return True


	def upload_file(file_name, bootstrap_bucket, object_name):
	"""Upload a file to an S3 bucket

	:param file_name: File to upload
	:param bootstrap_bucket: Bucket to upload to
	:param object_name: S3 object name
	:return: True if file was uploaded, else False
	"""

	# Upload the file
	try:
	response = s3_client.upload_file(file_name, bootstrap_bucket, object_name)
	logging.info(f'File {file_name} uploaded to bucket {bootstrap_bucket} as object {object_name}')
	except ClientError as e:
	logging.error(e)
	return False
	return True


	def create_stack(stack_name, cfn_template, cfn_params):
	"""Create EMR Cluster CloudFormation stack"""

	template_data = _parse_template(cfn_template)

	create_stack_params = {
	'StackName': stack_name,
	'TemplateBody': template_data,
	'Parameters': cfn_params,
	'TimeoutInMinutes': 60,
	'Capabilities': [
	'CAPABILITY_NAMED_IAM',
	],
	'Tags': [
	{
	'Key': 'Project',
	'Value': 'Superset EMR Demo'
	},
	]
	}

	try:
	response = cfn_client.create_stack(**create_stack_params)
	logging.info(f'Response: {response}')
	except ClientError as e:
	logging.error(e)
	return False
	return True


	def _parse_template(template):
	with open(template) as template_file_obj:
	template_data = template_file_obj.read()
	cfn_client.validate_template(TemplateBody=template_data)
	return template_data


	def _parse_parameters(parameters):
	with open(parameters) as parameter_file_obj:
	parameter_data = json.load(parameter_file_obj)
	return parameter_data


	def parse_args():
	"""Parse argument values from command-line"""

	parser = argparse.ArgumentParser(description='Arguments required for script.')
	parser.add_argument('-e', '–environment', required=True, choices=['dev', 'test', 'prod'], help='Environment')
	parser.add_argument('-k', '–ec2-key-name', required=True, help='Ec2KeyName: Name of EC2 Keypair')
	parser.add_argument('-s', '–ec2-subnet-id', required=True, help='Ec2SubnetId: Name of EC2 Keypair')

	args = parser.parse_args()
	return args


	if __name__ == '__main__':
	main()

view raw create_cfn_stack.py hosted with ❤ by GitHub

To execute the Python script and create the CloudFormation stack, which will create the EMR cluster, run the following command. Remember to update the parameters to the name of your EC2 key pair and the Subnet ID for the EMR cluster.

python3 ./create_cfn_stack.py \
    --ec2-key-name <your_key_pair_name> \
    --ec2-subnet-id <your_subnet_id> \
    --environment dev

Here is what the complete CloudFormation workflow looks like.

Security Group Ingress Rules

To install Superset on the EMR cluster’s Master node via SSH, you need to open port 22 on the Security Group associated with the EMR cluster’s Master Node, allowing access from your IP address. You can use the AWS Management Console or AWS CLI to open port 22. We will use jq and AWS ec2 API from the AWS CLI to get the Security Group ID associated with the EMR cluster’s Master Node and create the two ingress rules.

export EMR_MASTER_SG_ID=$(aws ec2 describe-security-groups | \
    jq -r ".SecurityGroups[] | \
    select(.GroupName==\"ElasticMapReduce-master\").GroupId" | \
    head -n 1)

aws ec2 authorize-security-group-ingress \
    --group-id ${EMR_MASTER_SG_ID} \
    --protocol tcp \
    --port 22 \
    --cidr $(curl ipinfo.io/ip)/32

Superset Script

Once the CloudFormation stack is created and the ports are open, we can install Apache Superset on the EMR Master Node. The bootstrap script,bootstrap_emr/bootstrap_superset.sh, will be used to install Apache Superset onto the EMR cluster’s Master Node as the hadoop user. The script is roughly based on Superset’s Installing from Scratch instructions.

As part of installing Superset, the script will also deploy several common database drivers, including Amazon Athena, Amazon Redshift, Apache Spark SQL, Presto, PostgreSQL, and MySQL. The script will also create a Superset Admin role, and two Superset User roles — Alpha and Gamma.

	#!/bin/bash

	# Purpose: Installs Apache Superset on EMR
	# Author: Gary A. Stafford (December 2020)
	# Usage: sh ./bootstrap_superset.sh 8280
	# Reference: https://superset.apache.org/docs/installation/installing-superset-from-scratch

	# port for superset (default: 8280)
	export SUPERSET_PORT="${1:-8280}"

	# install required packages
	sudo yum -y install gcc gcc-c++ libffi-devel python-devel python-pip python-wheel \
	openssl-devel cyrus-sasl-devel openldap-devel python3-devel.x86_64

	# optionally, update Master Node packages
	sudo yum -y update

	# install required Python package
	python3 -m pip install –user –upgrade setuptools virtualenv

	python3 -m venv venv
	. venv/bin/activate

	python3 -m pip install –upgrade apache-superset \
	PyAthenaJDBC PyAthena sqlalchemy-redshift pyhive mysqlclient psycopg2-binary

	command -v superset

	superset db upgrade

	export FLASK_APP=superset
	echo "export FLASK_APP=superset" >>~/.bashrc

	touch superset_config.py
	echo "ENABLE_TIME_ROTATE = True" >>superset_config.py
	echo "export SUPERSET_CONFIG_PATH=superset_config.py" >>~/.bashrc

	export ADMIN_USERNAME="SupersetAdmin"
	export ADMIN_PASSWORD="Admin1234"

	# create superset admin
	superset fab create-admin \
	–username "${ADMIN_USERNAME}" \
	–firstname Superset \
	–lastname Admin \
	–email superset_admin@example.com \
	–password "${ADMIN_PASSWORD}"

	superset init

	# create two sample superset users
	superset fab create-user \
	–role Alpha \
	–username SupersetUserAlpha \
	–firstname Superset \
	–lastname UserAlpha \
	–email superset_user_alpha@example.com \
	–password UserAlpha1234

	superset fab create-user \
	–role Gamma \
	–username SupersetUserGamma \
	–firstname Superset \
	–lastname UserGamma \
	–email superset_user_gamma@example.com \
	–password UserGamma1234

	# get instance id
	INSTANCE_ID="$(curl –silent http://169.254.169.254/latest/dynamic/instance-identity/document \| jq -r .instanceId)"
	export INSTANCE_ID
	echo "INSTANCE_ID: ${INSTANCE_ID}"

	# use instance id to get public dns of master node
	PUBLIC_MASTER_DNS="$(aws ec2 describe-instances –instance-id ${INSTANCE_ID} \|
	jq -r '.Reservations[0].Instances[0].PublicDnsName')"
	export PUBLIC_MASTER_DNS
	echo "PUBLIC_MASTER_DNS: ${PUBLIC_MASTER_DNS}"

	# start superset in background
	nohup superset run \
	–host "${PUBLIC_MASTER_DNS}" \
	–port "${SUPERSET_PORT}" \
	–with-threads –reload –debugger \
	>superset_output.log 2>&1 </dev/null &

	# output connection info
	printf %s """
	**********************************************************************
	Superset URL: http://${PUBLIC_MASTER_DNS}:${SUPERSET_PORT}
	Admin Username: ${ADMIN_USERNAME}
	Admin Password: ${ADMIN_PASSWORD}
	**********************************************************************
	"""

view raw bootstrap_superset.sh hosted with ❤ by GitHub

To install Superset using the bootstrap script, we will use another Python script, install_superset.py. The script uses paramiko, a Python implementation of SSHv2. The script also uses scp, a module that uses a paramiko transport to send and receive files via the scp1 protocol.

	#!/bin/bash

	# Purpose: Installs Apache Superset on EMR
	# Author: Gary A. Stafford (December 2020)
	# Usage: sh ./bootstrap_superset.sh 8280
	# Reference: https://superset.apache.org/docs/installation/installing-superset-from-scratch

	# port for superset (default: 8280)
	SUPERSET_PORT="${1:-8280}"

	# install required packages
	sudo yum -y install gcc gcc-c++ libffi-devel python-devel python-pip python-wheel \
	openssl-devel cyrus-sasl-devel openldap-devel python3-devel.x86_64

	# optionally, update master node packages
	sudo yum -y update

	# set up a python virtual environment
	python3 -m pip install –user –upgrade setuptools virtualenv
	python3 -m venv venv
	. venv/bin/activate

	python3 -m pip install –upgrade apache-superset \
	PyAthenaJDBC PyAthena sqlalchemy-redshift pyhive mysqlclient psycopg2-binary

	command -v superset

	superset db upgrade

	FLASK_APP=superset
	export "FLASK_APP=${FLASK_APP}"
	echo "export FLASK_APP=superset" >>~/.bashrc

	touch superset_config.py
	echo "ENABLE_TIME_ROTATE = True" >>superset_config.py
	echo "export SUPERSET_CONFIG_PATH=superset_config.py" >>~/.bashrc

	ADMIN_USERNAME="SupersetAdmin"
	ADMIN_PASSWORD="Admin1234"

	# create superset admin
	superset fab create-admin \
	–username "${ADMIN_USERNAME}" \
	–firstname Superset \
	–lastname Admin \
	–email superset_admin@example.com \
	–password "${ADMIN_PASSWORD}"

	superset init

	# create two sample superset users
	superset fab create-user \
	–role Alpha \
	–username SupersetUserAlpha \
	–firstname Superset \
	–lastname UserAlpha \
	–email superset_user_alpha@example.com \
	–password UserAlpha1234

	superset fab create-user \
	–role Gamma \
	–username SupersetUserGamma \
	–firstname Superset \
	–lastname UserGamma \
	–email superset_user_gamma@example.com \
	–password UserGamma1234

	# get ec2 instance id
	INSTANCE_ID="$(curl –silent http://169.254.169.254/latest/dynamic/instance-identity/document \| jq -r .instanceId)"

	# use ec2 instance id to get public dns of master node
	PUBLIC_MASTER_DNS="$(aws ec2 describe-instances –instance-id ${INSTANCE_ID} \|
	jq -r '.Reservations[0].Instances[0].PublicDnsName')"

	# start superset in background
	nohup superset run \
	–host "${PUBLIC_MASTER_DNS}" \
	–port "${SUPERSET_PORT}" \
	–with-threads –reload –debugger \
	>superset_output.log 2>&1 </dev/null &

	# output superset connection info
	printf %s """
	**********************************************************************
	Superset URL: http://${PUBLIC_MASTER_DNS}:${SUPERSET_PORT}
	Admin Username: ${ADMIN_USERNAME}
	Admin Password: ${ADMIN_PASSWORD}
	**********************************************************************
	"""

view raw install_superset.py hosted with ❤ by GitHub

The script requires a single input parameter, ec2-key-path, which is the full path to your EC2 key pair (e.g., ~/.ssh/my-key-pair.pem). Optionally, you can change the default Superset port of 8280, using the superset-port parameter.

python3 ./install_superset.py \
    --ec2-key-path </path/to/my-key-pair.pem> \
    --superset-port 8280

The script uses SSH and SCP to deploy and execute the bootstrap script,bootstrap_superset.sh. The output from the script includes the URL of Apache Superset running on the EMR cluster. The output also contains the username and password of the Superset Admin.

********************************************************************
Superset URL: http://ec2-111-22-333-44.compute-1.amazonaws.com:8280
Admin Username: SupersetAdmin
Admin Password: Admin1234
********************************************************************

SSH Tunnel

According to AWS, EMR applications publish user interfaces as websites hosted on the master node. For security reasons, these websites are only available on the master node’s local web server. To reach any of the web interfaces, you must establish an SSH tunnel with the master node using either dynamic or local port forwarding. If you are using dynamic port forwarding, you must also configure a proxy server to view the web interfaces.

Running the command in your terminal will start the SSH tunnel on port 8157. Once the tunnel is enabled, you can access Apache Superset in a web browser, using the script output’s URL shown in the script output above. Use the Admin credentials or either of the two User credentials to sign in to Superset.

Once signed in, you will have the ability to connect to your data sources and explore and visualize data. Below, we see an example of a SQL query executed against an Amazon RDS for PostgreSQL database, running in a separate VPC from EMR.

Sample query of an Amazon RDS PostgreSQL database using Superset’s SQL Editor

Conclusion

In this post, we learned how to install Apache Superset onto the Master Node of an Amazon EMR Cluster. If you want to install an application on all the nodes of an EMR cluster, you can add the commands to the bootstrap script, which runs when CloudFormation creates the cluster.

This blog represents my own viewpoints and not of my employer, Amazon Web Services. All product names, logos, and brands are the property of their respective owners.

Amazon EMR, Apache Superset, Data Visualization, Elastic MapReduce

Getting Started with Data Analysis on AWS using AWS Glue, Amazon Athena, and QuickSight: Part 2

Posted by Gary A. Stafford in AWS, Big Data, Cloud, Software Development, SQL on January 14, 2020

Introduction

In part one, we learned how to ingest, transform, and enrich raw, semi-structured data, in multiple formats, using Amazon S3, AWS Glue, Amazon Athena, and AWS Lambda. We built an S3-based data lake and learned how AWS leverages open-source technologies, including Presto, Apache Hive, and Apache Parquet. In part two of this post, we will use the transformed and enriched data sources, stored in the data lake, to create compelling visualizations using Amazon QuickSight.

High-level AWS architecture diagram of the demonstration.

Background

If you recall the demonstration from part one of the post, we had adopted the persona of a large, US-based electric energy provider. The energy provider had developed and sold its next-generation Smart Electrical Monitoring Hub (Smart Hub) to residential customers. Customers can analyze their electrical usage with a fine level of granularity, per device and over time. The goal of the Smart Hub is to enable the customers, using data, to reduce their electrical costs. The provider benefits from a reduction in load on the existing electrical grid and a better distribution of daily electrical load as customers shift usage to off-peak times to save money.

Data Visualization and BI

The data analysis process in the demonstration was divided into four logical stages: 1) Raw Data Ingestion, 2) Data Transformation, 3) Data Enrichment, and 4) Data Visualization and Business Intelligence (BI).

Full data analysis workflow diagram (click to enlarge…)

In the final, Data Visualization and Business Intelligence (BI) stage, the enriched data is presented and analyzed. There are many enterprise-grade services available for data visualization and business intelligence, which integrate with Amazon Athena. Amazon services include Amazon QuickSight, Amazon EMR, and Amazon SageMaker. Third-party solutions from AWS Partners, many available on the AWS Marketplace, include Tableau, Looker, Sisense, and Domo.

In this demonstration, we will focus on Amazon QuickSight. Amazon QuickSight is a fully managed business intelligence (BI) service. QuickSight lets you create and publish interactive dashboards that include ML Insights. Dashboards can be accessed from any device, and embedded into your applications, portals, and websites. QuickSight serverlessly scales automatically from tens of users to tens of thousands without any infrastructure management.

Using QuickSight

QuickSight APIs

Amazon recently added a full set of aws quicksight APIs for interacting with QuickSight. For example, to preview the three QuickSight data sets created for this part of the demo, with the AWS CLI, we would use the list-data-sets comand.

aws quicksight list-data-sets –aws-account-id 123456789012

view raw

list-data-sets.sh

hosted with ❤ by GitHub

	{
	"Status": 200,
	"DataSetSummaries": [
	{
	"Arn": "arn:aws:quicksight:us-east-1:123456789012:dataset/9eb88a69-20de-d8be-aefd-2c7ac4e23748",
	"DataSetId": "9eb88a69-20de-d8be-aefd-2c7ac4e23748",
	"Name": "etl_output_parquet",
	"CreatedTime": 1578028774.897,
	"LastUpdatedTime": 1578955245.02,
	"ImportMode": "SPICE"
	},
	{
	"Arn": "arn:aws:quicksight:us-east-1:123456789012:dataset/78e81193-189c-6dd0-864fb-a33244c9654",
	"DataSetId": "78e81193-189c-6dd0-864fb-a33244c9654",
	"Name": "electricity_rates_parquet",
	"CreatedTime": 1578029224.996,
	"LastUpdatedTime": 1578945179.472,
	"ImportMode": "SPICE"
	},
	{
	"Arn": "arn:aws:quicksight:us-east-1:123456789012:dataset/a474214d-c838-b384-bcca-ea1fcd2dd094",
	"DataSetId": "a474214d-c838-b384-bcca-ea1fcd2dd094",
	"Name": "smart_hub_locations_parquet",
	"CreatedTime": 1578029124.565,
	"LastUpdatedTime": 1578888788.135,
	"ImportMode": "SPICE"
	}
	],
	"RequestId": "2524e80c-7c67-7fbd-c3f1-b700c521badc"
	}

view raw

list-data-sets.json

hosted with ❤ by GitHub

To examine details of a single data set, with the AWS CLI, we would use the describe-data-set command.

	aws quicksight describe-data-set \
	–aws-account-id 123456789012 \
	–data-set-id 9eb88a69-20de-d8be-aefd-2c7ac4e23748

view raw

describe-data-set.sh

hosted with ❤ by GitHub

QuickSight Console

However, for this final part of the demonstration, we will be working from the Amazon QuickSight Console, as opposed to the AWS CLI, AWS CDK, or CloudFormation templates.

Signing Up for QuickSight

To use Amazon QuickSight, you must sign up for QuickSight.

There are two Editions of Amazon QuickSight, Standard and Enterprise. For this demonstration, the Standard Edition will suffice.

QuickSight Data Sets

Amazon QuickSight uses Data Sets as the basis for all data visualizations. According to AWS, QuickSight data sets can be created from a wide variety of data sources, including Amazon RDS, Amazon Aurora, Amazon Redshift, Amazon Athena, and Amazon S3. You can also upload Excel spreadsheets or flat files (CSV, TSV, CLF, ELF, and JSON), connect to on-premises databases like SQL Server, MySQL, and PostgreSQL and import data from SaaS applications like Salesforce. Below, we see a list of the latest data sources available in the QuickSight New Data Set Console.

Demonstration Data Sets

For the demonstration, I have created three QuickSight data sets, all based on Amazon Athena. You have two options when using Amazon Athena as a data source. The first option is to select a table from an AWS Glue Data Catalog database, such as the database we created in part one of the post, ‘smart_hub_data_catalog.’ The second option is to create a custom SQL query, based on one or more tables in an AWS Glue Data Catalog database.

Of the three data sets created for part two of this demonstration, two data sets use tables directly from the Data Catalog, including ‘etl_output_parquet’ and ‘electricity_rates_parquet.’ The third data set uses a custom SQL query, based on the single Data Catalog table, ‘smart_hub_locations_parquet.’ All three tables used to create the data sets represent the enriched, highly efficient Parquet-format data sources in the S3-based Data Lake.

Data Set Features

There are a large number of features available when creating and configuring data sets. We cannot possibly cover all of them in this post. Let’s look at three features: geospatial field types, calculated fields, and custom SQL.

Geospatial Data Types

QuickSight can intelligently detect common types of geographic fields in a data source and assign QuickSight geographic data type, including Country, County, City, Postcode, and State. QuickSight can also detect geospatial data, including Latitude and Longitude. We will take advantage of this QuickSight feature for our three data set’s data sources, including the State, Postcode, Latitude, and Longitude field types.

Calculated Fields

A commonly-used QuickSight data set feature is the ‘Calculated field.’ For the ‘etl_output_parquet’ data set, I have created a new field (column), cost_dollar.

The cost field is the electrical cost of the device, over a five minute time interval, in cents (¢). The calculated cost_dollar field is the quotient of the cost field divided by 100. This value represents the electrical cost of the device, over a five minute time interval, in dollars ($). This is a straightforward example. However, a calculated field can be very complex, built from multiple arithmetic, comparison, and conditional functions, string calculations, and data set fields.

Data set calculated fields can also be created and edited from the QuickSight Analysis Console (discussed later).

Custom SQL

The third QuickSight data set is based on an Amazon Athena custom SQL query.

	SELECT lon, lat, postcode, hash, tz, state
	FROM smart_hub_data_catalog.smart_hub_locations_parquet;

view raw

custom_sql_quicksight.sql

hosted with ❤ by GitHub

Although you can write queries in the QuickSight Data Prep Console, I prefer to write custom Athena queries using the Athena Query Editor. Using the Editor, you can write, run, debug, and optimize queries to ensure they function correctly, first.

The Athena query can then be pasted into the Custom SQL window. Clicking ‘Finish’ in the window is the equivalent of ‘Run query’ in the Athena Query Editor Console. The query runs and returns data.

Similar to the Athena Query Editor, queries executed in the QuickSight Data Prep Console will show up in the Athena History tab, with a /* QuickSight */ comment prefix.

SPICE

You will notice the three QuickSight data sets are labeled, ‘SPICE.’ According to AWS, the acronym, SPICE, stands for ‘Super-fast, Parallel, In-memory, Calculation Engine.’ QuickSight’s in-memory calculation engine, SPICE, achieves blazing fast performance at scale. SPICE automatically replicates data for high availability allowing thousands of users to simultaneously perform fast, interactive analysis while shielding your underlying data infrastructure, saving you time and resources. With the Standard Edition of QuickSight, as the first Author, you get 1 GB of SPICE in-memory data for free.

QuickSight Analysis

The QuickSight Analysis Console is where Analyses are created. A specific QuickSight Analysis will contain a collection of data sets and data visualizations (visuals). Each visual is associated with a single data set.

Types of QuickSight Analysis visuals include: horizontal and vertical, single and stacked bar charts, line graphs, combination charts, area line charts, scatter plots, heat maps, pie and donut charts, tree maps, pivot tables, gauges, key performance indicators (KPI), geospatial diagrams, and word clouds. Individual visual titles, legends, axis, and other visual aspects can be easily modified. Visuals can contain drill-downs.

A data set’s fields can be modified from within the Analysis Console. Field types and formats, such as date, numeric, currency fields, can be customized for display. The Analysis can include a Title and subtitle. There are some customizable themes available to change the overall look of the Analysis.

Analysis Filters

Data displayed in the visuals can be further shaped using a combination of Filters, Conditional formatting, and Parameters. Below, we see an example of a typical filter based on a range of dates and times. The data set contains two full days’ worth of data. Here, we are filtering the data to a 14-hour peak electrical usage period, between 8 AM and 10 PM on the same day, 12/21/2019.

Drill-Down, Drill-Up, Focus, and Exclude

According to AWS, all visual types except pivot tables offer the ability to create a hierarchy of fields for a visual element. The hierarchy lets you drill down or up to see data at different levels of the hierarchy. Focus allows you to concentrate on a single element within a hierarchy of fields. Exclude allows you to remove an element from a hierarchy of fields. Below, we see an example of all four of these features, available to apply to the ‘Central Air Conditioner’. Since the AC unit is the largest consumer of electricity on average per day, applying these filters to understand its impact on the overall electrical usage may be useful to an analysis. We can also drill down to minutes from hours or up to days from hours.

Example QuickSight Analysis

A QuickSight Analysis is shared by the Analysis Author as a QuickSight Dashboard. Below, we see an example of a QuickSight Dashboard, built and shared for this demonstration. The ‘Residential Electrical Usage Analysis’ is built from the three data sets created earlier. From those data sets, we have constructed several visuals, including a geospatial diagram, donut chart, heat map, KPI, combination chart, stacked vertical bar chart, and line graph. Each visual’s title, layout, and field display has all customized. The data displayed in the visuals have been filtered differently, including by date and time, by customer id (loc_id), and by state. Conditional formatting is used to enhance the visual appearance of visuals, such as the ‘Total Electrical Cost’ KPI.

Conclusion

In part one, we learned how to ingest, transform, and enrich raw, semi-structured data, in multiple formats, using Amazon S3, AWS Glue, Amazon Athena, and AWS Lambda. We built an S3-based data lake and learned how AWS leverages open-source technologies, including Presto, Apache Hive, and Apache Parquet. In part two of this post, we used the transformed and enriched datasets, stored in the data lake, to create compelling visualizations using Amazon QuickSight.

All opinions expressed in this post are my own and not necessarily the views of my current or past employers or their clients.

Amazon Athena, Amazon QuickSight, AWS Glue, Business Intelligence, Data Catalog, Data Lake, Data Visualization

3 Comments

Programmatic Ponderings

Posts Tagged Data Visualization

Installing Apache Superset on Amazon EMR: Add data exploration and visualization to your analytics cluster

Introduction

Amazon EMR

Apache Superset

Setup

Security Group Ingress Rules

Superset Script

SSH Tunnel

Conclusion

Getting Started with Data Analysis on AWS using AWS Glue, Amazon Athena, and QuickSight: Part 2

Introduction

Background

Data Visualization and BI

Using QuickSight

QuickSight APIs

QuickSight Console

Signing Up for QuickSight

QuickSight Data Sets

Demonstration Data Sets

Data Set Features

Geospatial Data Types

Calculated Fields

Custom SQL

SPICE

QuickSight Analysis

Analysis Filters

Drill-Down, Drill-Up, Focus, and Exclude

Example QuickSight Analysis

Conclusion

Gary Stafford

Recent Posts

Top Posts & Pages

Tag Cloud

Tweets