Secure Data Lake with AWS Lake Formation Tutorial

Rajat Venkatesh — 11/19/20192 Min Read — In AWS S3, AWS Glue, AWS Lake Formation, NYC Trip Records, Data Security

AWS Lake Formation helps to build a secure data lake on data in AWS S3. This blog will help you get started by describing the steps to setup a basic data lake with S3, Glue, Lake Formation and Athena in AWS. The tutorial will use New York City Taxi and Limousine Commission (TLC) Trip Record Data as the data set.

At the end of the tutorial, you will have a data lake setup with Lake Formation. The next blog describes how to setup data engineer and data analyst personas.

Lake Formation Diagram

Prerequisites

  • Setup AWS Lake Formation using instructions in Getting Started Tutorial.
  • After the tutorial, the following users and roles are setup:
    • Administrator is the Administrator IAM User
    • NycWorkflowRole is the IAM Role for Workflows
    • lakeadmin is the Data Lake Administrator
  • Upgrade to Lake Formation Permissions model as described the Upgrade Tutorial.
  • Setup AWS Athena

Import Taxi Data into Lake Formation

Allow NycWorkflowRole to read NYC Data

  1. Goto IAM > Policies > Create Policy
  2. Follow the wizard and create a policy with name S3NycBucketRead with the following definition:
{
    "Version": "2012-10-17",
    "Statement": [
        {
            "Sid": "VisualEditor0",
            "Effect": "Allow",
            "Action": [
                "s3:GetObject",
                "s3:ListBucket"
            ],
            "Resource": [
                "arn:aws:s3:::nyc-tlc",
                "arn:aws:s3:::nyc-tlc/*"
            ]
        }
    ]
}
  1. Attach S3NycBucketRead policy to NycWorkFlowRole

Create a database and grant permissions

  1. Head to AWS Lake Formation > Databases > Create Database

  2. Fill the form with details below and click on Create Database

    • Name: taxidata
  3. Click on Databases in left navigation pane

  4. Select taxidata.

  5. Click on Actions > Grant

  6. Fill up the form with following details and then click on Grant

    • IAM Users and Roles: NycWorkflowRole
    • Database Permissions: Create Table, Alter.

    grant permissions

Query and Analyze Permissions in AWS LakeFormation

Create a Crawler to register the data in Glue data catalog

A Glue Crawler will read the files in nyc-tlc bucket and create tables in a database automatically.

  1. Head to AWS Glue > Crawlers > Add Crawler
  2. Fill in the following details in the wizard and click Finish at the end.
FieldValue
Crawler NameTaxiCrawler
Tagsproject: lake_formation_example
Crawler Source TypeData Stores
Choose a data StoreS3
Crawl data inSpecified Path in another account
Include Paths3://nyc-tlc/trip data/
Exclude Patterns
  • fhv_tripdata_2015*
  • fhv_tripdata_2016*
  • fhv_tripdata_2017*
  • fhv_tripdata_2018*
  • yellow*
  • green*
Add Another Data StoreYES
Choose a data StoreS3
Crawl data inSpecified Path in another account
Include Paths3://nyc-tlc/misc/
Exclude Patterns
  • *foil*
  • shared*
  • uber*
  • *.html
  • *.zip
  • FOIL_*
Add another Data StoreNo
IAM RoleChoose an Existing Role
IAM RoleNycWorkflowRole
FrequencyRun On Demand
Choose an existing databaseTaxiData
Prefix added to tablescsv_
  1. In the the crawlers list, select the TaxiCrawler and click the Run Crawler button. This should take less than a minute and it will crawl the CSV files you've not excluded in the taxi S3 bucket.
  2. Database -> Tables and review the tables.

tables

Verify Data in AWS Athena

Fire up athenacli as lakeadmin and run a couple of queries to verify the data looks good.

us-east-2:default> select * from taxidata.csv_trip_data limit 10;
+----------------------+---------------------+------------------+--------------+--------------+---------+-------------------+
| dispatching_base_num | pickup_datetime     | dropoff_datetime | pulocationid | dolocationid | sr_flag | hvfhs_license_num |
+----------------------+---------------------+------------------+--------------+--------------+---------+-------------------+
| B00013               | 2015-01-01 00:30:00 |                  | <null>       | <null>       | <null>  | <null>            |
| B00013               | 2015-01-01 01:22:00 |                  | <null>       | <null>       | <null>  | <null>            |
| B00013               | 2015-01-01 01:23:00 |                  | <null>       | <null>       | <null>  | <null>            |
| B00013               | 2015-01-01 01:44:00 |                  | <null>       | <null>       | <null>  | <null>            |
| B00013               | 2015-01-01 02:00:00 |                  | <null>       | <null>       | <null>  | <null>            |
| B00013               | 2015-01-01 02:00:00 |                  | <null>       | <null>       | <null>  | <null>            |
| B00013               | 2015-01-01 02:00:00 |                  | <null>       | <null>       | <null>  | <null>            |
| B00013               | 2015-01-01 02:50:00 |                  | <null>       | <null>       | <null>  | <null>            |
| B00013               | 2015-01-01 04:45:00 |                  | <null>       | <null>       | <null>  | <null>            |
| B00013               | 2015-01-01 06:30:00 |                  | <null>       | <null>       | <null>  | <null>            |
+----------------------+---------------------+------------------+--------------+--------------+---------+-------------------+
10 rows in set
Time: 4.926s
us-east-2:default> select * from taxidata.csv_misc limit 10;
+------------+-----------------+---------------------------+---------------+
| locationid | borough         | zone                      | service_zone  |
+------------+-----------------+---------------------------+---------------+
| 1          | "EWR"           | "Newark Airport"          | "EWR"         |
| 2          | "Queens"        | "Jamaica Bay"             | "Boro Zone"   |
| 3          | "Bronx"         | "Allerton/Pelham Gardens" | "Boro Zone"   |
| 4          | "Manhattan"     | "Alphabet City"           | "Yellow Zone" |
| 5          | "Staten Island" | "Arden Heights"           | "Boro Zone"   |
| 6          | "Staten Island" | "Arrochar/Fort Wadsworth" | "Boro Zone"   |
| 7          | "Queens"        | "Astoria"                 | "Boro Zone"   |
| 8          | "Queens"        | "Astoria Park"            | "Boro Zone"   |
| 9          | "Queens"        | "Auburndale"              | "Boro Zone"   |
| 10         | "Queens"        | "Baisley Park"            | "Boro Zone"   |
+------------+-----------------+---------------------------+---------------+
10 rows in set
Time: 3.116s

Summary

This tutorial showed how to setup a basic data lake containing NYC Taxi Trip data using AWS Lake Formation. Now the data lake is ready to provide secure access to data engineers, analysts and data scientists. We would love to hear if this tutorial was helpful to you. Get in touch using the chat widget.