Lab Instructions for Data Engineering Immersion Day
Lab Instructions for Data Engineering Immersion Day
Welcome to the lab Instruction!
Instructor Led :
Self-paced : If you want to run pre-requisite steps by yourself:
AWS account - if you don't have one, Sign-up for free AWS account . All sevices in this lab are not covered under free tier, so it may incur some minor cost.
Follow the instruction in PreLab-instructor to set up a RDS source database yourself. It deploys:
Click the Deploy to AWS icons below to stand up the RDS database infrastructure.
| Region | Launch Template |
|---|---|
| N.Virginia (us-east-1) |
These labs are designed to be completed in sequence, and the full set of instructions are documented below. Read and follow along to complete the labs. Our lab instructor will give you a high-level overview of the labs and help answer any questions. Don't worry if you get stuck, we provide hints along the way.
Ensure your region is US East (N. Virginia)
Download the lab instruction file
This lab is designed to automate the Data Lake hydration with AWS Database Migration Service (AWS DMS), so we can fast forward to Lab2-Transforming in the data lake with Glue.
If you prefer to get hands-on with AWS DMS service, please skip this lab and proceed to Workshop Setup and Lab1-Hydrateing the data lake via DMS
Open the CloudFormation launch template link in a new tab. It will load a CloudFormation Dashboard and start the creation process for your lab environment, which deploys:
Click the Deploy to AWS icons below to stand up the core workshop infrastructure.
| Region | Launch Template |
|---|---|
| N.Virginia (us-east-1) |
Proceed through the wizard to launch the stack. Leave all options at their default values, but make sure to check the box to allow CloudFormation to create IAM roles on your behalf:
See the Events tab for progress on the stack launch that may take up to 5 minutes. You can also see details of any problems here if the launch fails. Proceed to the next step once the stack status advances to "CREATE_COMPLETE".
At this point, the data lake workshop environment has been setup, looks like this:
Download the lab1 instruction file
At this point, your DMS task should be finished with 'load Complete' status, and 16 tables are loaded in S3 from RDS by DMS
Download the lab2 instruction file
Nice work! You've successfully converted CSV raw data to Parquet, and added parquet tables to the Glue Data Catalog
Download the lab3 instruction file
Sweet! Now you have queried the Data Lake and visualized it in QuickSight. Next, We'll consume the Data Lake via machine learning.
Download the lab4 instruction file
Congratulations, you've successfully built the Data Lake from end to end. If you have time, try the optional steps in the 4 labs above. Otherwise, please remember to follow the steps below in the Workshop Cleanup to make sure all assets created during the workshop are removed so you do not see unexpected charges after today.
Make sure you have completed
Download the lab5 instruction file
In this lab, we show you how to query petabytes of data with Amazon Redshift and exabytes of data in your Amazon S3 data lake, without loading or moving objects. We will also demonstrate how you can leverage views which union data in direct attached storage as well as in your S3 Datalake to create a single source of truth. Finally, we will demonstrate strategies for aging off old data into S3 and maintaining only the most recent data in Amazon Redshift direct attached storage.
This lab requires a new Redshift cluster in US-WEST-2 (Oregon), use the following link to And gather the following information from the stack output above:
In the first part of this lab, we will perform the following activities:
https://s3.console.aws.amazon.com/s3/buckets/us-west-2.serverless-analytics/NYC-Pub/green/?region=us-west-2&tab=overview&prefixSearch=green_tripdata_2016https://s3.console.aws.amazon.com/s3/object/us-west-2.serverless-analytics/NYC-Pub/green/green_tripdata_2013-08.csv?region=us-west-2&tab=selectLogin to your query editor, create a schema workshop_das and table workshop_das.green_201601_csv for tables that will reside on the Redshift compute nodes, AKA the Redshift direct-attached storage (DAS) tables.
Hint
…Hint
COPY workshop_das.green_201601_csv
FROM 's3://us-west-2.serverless-analytics/NYC-Pub/green/green_tripdata_2016-01.csv'
IAM_ROLE '[Your-Redshift_Role_ARN]'
DATEFORMAT 'auto'
IGNOREHEADER 1
DELIMITER ','
IGNOREBLANKLINES
;Hint
select count(1) from workshop_das.green_201601_csv;
--1445285HINT: The [Your-Redshift_Role_ARN] in the above command should be replaced by the CloudFormation output value at the beginning of the lab.
In this month, there is a date which had the lowest number of taxi rides due to a blizzard. Can you find that date?
SQL-Based Hint
SELECT TO_CHAR(pickup_datetime, 'YYYY-MM-DD'),
COUNT(*)
FROM workshop_das.green_201601_csv
GROUP BY 1
ORDER BY 1;In the next part of this lab, we will perform the following activities:
Note the partitioning scheme is Year, Month, Type (where Type is a taxi company). Here's a quick Screenshot:
https://s3.console.aws.amazon.com/s3/buckets/serverless-analytics/canonical/NY-Pub/?region=us-west-2&tab=overviewhttps://s3.console.aws.amazon.com/s3/buckets/serverless-analytics/canonical/NY-Pub/year%253D2016/month%253D1/?region=us-east-1&tab=overviewhttps://s3.console.aws.amazon.com/s3/buckets/serverless-analytics/canonical/NY-Pub/year%253D2016/month%253D1/type%253Dgreen/?region=us-east-1&tab=overviewBecause external tables are stored in a shared Glue Catalog for use within the AWS ecosystem, they can be built and maintained using a few different tools, e.g. Athena, Redshift, and Glue.
Use the AWS Glue Crawler to create your external table adb305.ny_pub stored in parquet format under location s3://us-west-2.serverless-analytics/canonical/NY-Pub/.
Navigate to the Glue Crawler Page. https://console.aws.amazon.com/glue/home?#catalog:tab=crawlers
Click on Add Crawler, and enter the crawler name NYTaxiCrawler and click Next.
Select Data stores as the source type and click Next.
Choose S3 as the data store and the include path of s3://us-west-2.serverless-analytics/canonical/NY-Pub
Create an IAM Role and enter the name AWSGlueServiceRole-RedshiftImmersion.
Select Run on demand for the frequency.
Click on Add database and enter the Database of spectrumdb
Select all remaining defaults. Once the Crawler has been created, click on Run Crawler.
Once the Crawler has completed its run, you will see a new table in the Glue Catalog. https://console.aws.amazon.com/glue/home?#catalog:t
No open issues yet, or sync has not completed.