Introduction

In this assignment, you will use Amazon Redshift, one of AWS' query engines. Redshift is built on a strongly relational model, emphasizing the speed of joins across many tables, but extends that model to support hierarchical data such as JSON objects. It can operate either as a data warehouse or lake.

Data warehouses:

1. Store historical data that will rarely be updated, although it may be extended by new data.
2. Are optimized for queries over updates.
3. Are optimized for joins across many tables, categorizing data by ever-finer levels of detail.
4. Often store their data by columns rather than rows.

Data lakes:

1. Store its data on object-stores.
2. Hold a wide variety of data, including structured/semistructured formats (JSON, Parquet) and unstructured formats (e.g., text files).
3. Are read in place by query tools, separating data from computation.

In Part 1, you will first set up a Redshift data warehouse, load it with data, and do some typical analyses. In Part 2, you will use Redshift to analyse external files stored in an S3 data lake and explore the performance tradeoffs.

Part 0: Get Everything Configured

Preparing Redshift for your first use requires several steps. A Redshift cluster is typically shared across a group, department, or entire organization. Data engineers and security administrators define the cluster and once it is running and has the correct permissions, business analysts will then simply log in and analyze their data. These analysts will often not call Redshift directly but instead use business intelligence tools such as Tableau or Power BI that in turn call the cluster's API to query the large tables it stores.

Setup 1: Define an IAM role for access permissions

When creating a Redshift cluster, we need to establish its authority to: (1) manage the virtual machines that run its queries, (2) access all the S3 objects that will be either loaded into Redshift (Part 1 of this assignment) or directly accessed as a data lake (Part 2).

1. Sign in to the AWS Console, and navigate to the "IAM" service (not "IAM Identity Center").
2. Click "Roles" in the left-hand menu, and the "Create role" button in the upper right-hand corner.
3. In the role creation wizard, select "AWS service" and the "Redshift" category. Select the "Redshift Customizable" use-case.
4. You will see a list of many policies: search and check both AmazonS3FullAccess and AWSGlueConsoleFullAccess. Click "Next".
5. Name the role "RedshiftS3Full" and click "Create role".

Setup 2: AWS Network Configuration

We will do our queries in us-west-2 to access sample data located there and need to set up networking to allow that.

1. In the AWS Console, navigate to the "VPC" service.
2. Switch to the us-west-2 region by selecting it in the upper-right.
3. Create a VPC:
   1. Select "Your VPCs" and "Create VPC".
   2. Select "VPC and more" and name the VPC "732vpc" or similar.
   3. Click "Create VPC".

Setup 3: Configure Redshift Serverless

1. In the AWS Console, navigate to the "Amazon Redshift" service.
2. Switch to the us-west-2 region by selecting it in the upper-right.
3. Select "Redshift Serverless" from the left-side menu and "Get started with Amazon Redshift Serverless".
4. Create a namespace "serverless" or similar.
5. Under "Associated IAM roles", click "Associate IAM roles" and select RedshiftS3Full.
6. Create a workgroup "serverless" or similar.
7. Set the "Base capacity" to 4 (or other small value since we aren't actually doing big queries here).
8. Select the VPC you created above, and corresponding security groups and subnets.
9. "Save configuration" and wait for it to be completed.

Setup 4: Create the Redshift Tables

Download the provided create-create-tables.py script. At the top of that file, fill in your AWS_ACCOUNT_ID (visible in the top-right of the AWS console beside your name). Run the program: it will create files create-tables-1.sql and create-tables-2.sql.

1. Open the "Query editor v2".
2. Connect to the workgroup: select it in the connection list on the left and use the defaults.
3. Paste the contents from create-tables-1.sql and click "Run".

Explore

Let's have a look around before continuing. The statements that you just executed built five tables:

• amenities: Open Street Map data showing the locations and attributes of amenities within the greater Vancouver area. For our purposes, an amenity is a place where a customer might spend money (actual data, publicly available).
• customers: Customers located across the Canadian provinces and territories (synthetic data).
• greater_vancouver_prefixes: For each municipality in the Greater Vancouver area, the three-letter postal code prefixes for addresses in that municipality. Note that some prefixes apply to multiple municipalities (actual data, publicly available).
• paymentmethods: Credit and debit card data (synthetic data).
• purchases: Purchases by customers at Vancouver amenities using a payment method (synthetic data).

Each table has a primary key. With one exception, the data is normalized and tables refer to each other via foreign key references. The exception is the tags field of the amenities table, which contains optional tags selected from a list of many keywords.

Explore the CREATE TABLE statements and run some SQL SELECT queries to learn the table contents and their interrelationships. Here are some links to useful references:

• Redshift SELECT statement
• Redshift SQL functions
• Complete Redshift SQL Reference Guide

Some queries you might use to familiarize yourself with the tables. Do only as many as you find useful—you will not submit your answers:

• How many customers, purchases, payment methods, and amenities are there?
• What are the types of amenities (amenity)?
• What is the range of the purchase dates?
• What are the types (mtype) of payment methods?
• How many customers are there from each province and territory?
• What are the average and median purchase amounts?
• What's in greater_vancouver_prefixes?

Part 1: Redshift as a Data Warehouse

With our data stored in Redshift, we can use it as a data warehouse and answer some questions.

Question 1: Debit or credit?

Our first question:

Do people from Ontario tend to put larger purchases on one payment method?

Write a SQL query that returns the median purchase amounts that customers from Ontario (province code "ON") made with credit and debit cards.

In your answers.txt, give:

• An answer the question: what were the median values, and which method had larger purchases?
• The query used to arrive at this answer.

Question 2: Sushi and Semi-Structured Fields

Redshift supports semistructured data via its SUPER datatype. The name is shortened form of "superset" and, as the name implies, a column with this type can contain arbitrary data.

The only column with a SUPER type in our system is amenities.tags. This column contains an arbitrary collection of kay/value pairs, varying from row to row. For example, some amenities have a field, addr_city, naming the city in which they are located, but most do not.

You refer to a field as though it were a subcolumn within tags, such as tags.addr_city. If a field is absent from a row, the value is considered NULL:

SELECT * FROM amenities WHERE tags.addr_city IS NOT NULL;

With that, we can address our actual question for this data:

Who spends more at restaurants that serve sushi: locals (residents of Greater Vancouver) or tourists?

We will take "sushi restaurant" to mean "things that are a 'restaurant' amenitity and have a cuisine tag 'sushi' (case-insensitive)".

• The amenity column will tell you what is/isn't a 'restaurant'.
• Sushi restaurants will have a cuisine tag 'sushi' (case insentisive, hint).

You will need to distinguish purchases from Vancouver locals vs others. This will create a relevant view:

CREATE VIEW vancouver_custs AS
SELECT DISTINCT
  custid, p.city IS NULL AS From_Van
FROM customers 
  LEFT JOIN greater_vancouver_prefixes AS p
  ON p.pcprefix=SUBSTRING(customers.postalcode, 0, 4);

In your answers.txt, give:

• An answer the question: who spends how much on sushi?
• The query used to arrive at this answer.

Part 2: Redshift as a Data Lake

In the previous section, we used Redshift to operate on data within the 'data warehouse'-side of the data lake: analysing data that have been cleaned, normalized, and loaded into tidy tables.

Redshift provides an extension, Spectrum, that allows it to query such data directly without first loading it into Redshift's controlled, relational model. By separating computation from storage, we avoid duplication of data and permit many different systems to access it. For example, an S3 object could be queried by Spectrum and also read by an EMR Spark job.

In AWS, an S3 bucket is a data lake. For this assignment, we will read files directly from S3. You still need to define a Redshift schema for an object in the data lake via a CREATE EXTERNAL TABLE statement, specifying a column structure and the location of the object in S3.

Creating an external schema and tables

The create-create-tables.py program also created a create-tables-2.sql file. It contains statement that will create data lake tables that reference files in S3.

Run those statements.

Note that the purchases table has been created as both a Redshift native table (data warehouse style, as purchases or public.purchases), and as an external table (data lake style, as s3ext.purchases). This will allow us to compare the two approaches.

Question 3: Average purchase per day for the first five days

Our final question:

What was the average purchase per day for the first five days of August?

Write a query that returns a two-column, five-line table with the date and average (mean) purchases for August 1–5. The Redshift DATE_PART function may be helpful.

Data-Warehouse Style

Run that query with the purchases table as its source: that is in the data warehouse as with previous questions. Note the Query ID in the bottom-right of the Query Editor window.

In another editor window, fill in your query_id in this query to see some analytics for what just happened.

SELECT step_name, table_name, source, input_rows, output_bytes, output_rows
  FROM sys_query_detail
  WHERE query_id=0
  ORDER BY start_time

Note the input rows, and output bytes and rows for the step that actually reads the table (non-empty table_name).

Normally if you run the same query query more than once, Redshift will used cached results so that query_id won't have any execution to report. The "SET enable_result_cache_for_session" statement provided in create-tables-2.sql should prevent this, but if not, make some trivial change to the query to force re-execution.

Data-Lake Style

Repeat the same query with s3ext.purchases as the source table. You should get the same results, but with a different data source. Again, note the Query ID.

You can examine the table read for an external-source query like this:

SELECT source_type, table_name, scanned_files, returned_bytes, returned_rows
  FROM sys_external_query_detail
  WHERE query_id=0

Note the number of scanned files, returned bytes, and returned rows. The number of rows should match the previous query, since we are performing the same WHERE filter.

Compare and Constrast

In the original CSV file (s3://sfu-cmpt-732-redshift), the ext-purchases data has 30,000 rows and the average is approximately 57 bytes/row. Calculate the average number of bytes per row returned for each of your "Question 3" queries.

From these values, what can you infer about how Redshift scans native (data warehouse style) tables, and how the external (data lake style) tables are read?

In your answers.txt:

• Both versions of the queries run here.
• The counts of bytes and rows for each query, and the average bytes per row.
• Describe what you have learned about how either storage method (native tables vs S3 files) are stored/read. You may want to use terms like "row-oriented", "column-oriented", or "Hive partitioning" here.

Cleanup

When you are done with this assignment, delete the namespace you created.

1. In the Redshift console, select "Workspace configuration".
2. Select your workspace and "delete".
3. Also delete the namespace here and do not create a final snapshot (since you will be paying to store it indefinitely).

Submission

Submit your answers.txt file to Assignment 9.