Secure Data Lake with AWS Lake Formation Tutorial
Tags: aws, database, governance, security
Published: 2020-01-27 11:03:41

[AWS Lake Formation](https://aws.amazon.com/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](https://registry.opendata.aws/nyc-tlc-trip-records-pds/)
as the data set.

<figure>
  <img src="https://tokern.io/static/085cef4d34b2ada62c0bf1015cf6d48a/ec5a1/aws_lake_formation.webp" alt="Lake Formation Diagram"/>
  <figcaption>Image by <a href="https://www.awsgeek.com/posts/AWS-Lake-Formation/index.html">Jerry Hargrove</a></figcaption>
</figure>


At the end of the tutorial, you will have a data lake setup with Lake Formation. 

## Prerequisites

- Setup AWS Lake Formation using instructions in [Getting Started Tutorial](https://docs.aws.amazon.com/lake-formation/latest/dg/getting-started-setup.html).
- 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](https://docs.aws.amazon.com/lake-formation/latest/dg/upgrade-glue-lake-formation.html)**.**
- 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/*"
                        ]
                }
      ]
    }
```


3. 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.

    ![](https://tokern.io/static/24a2e138604c8e844281339b1beb84cf/ec5a1/grant-permissions.webp)


### 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.


| Field | Value |
|:------|:-----|
|Crawler Name|TaxiCrawler|
|Tags|*project: lake_formation_example*|
|Crawler Source Type|Data Stores|
|Choose a data Store|S3|
|Crawl data in|Specified Path in another account|
|Include Path|s3://nyc-tlc/trip data/|
|Exclude Patterns|<ul><li>fhv_tripdata_2015\*</li><li>fhv_tripdata_2016\*</li><li>fhv_tripdata_2017\*</li><li>fhv_tripdata_2018\*</li><li>yellow\*</li><li>green\*</li></ul>|
|Add Another Data Store|YES|
|Choose a data Store|S3|
|Crawl data in|Specified Path in another account|
|Include Path|s3://nyc-tlc/misc/|
|Exclude Patterns|<ul><li>\*foil\*</li><li>shared\*</li><li>uber\*</li><li>\*.html</li><li>\*.zip</li><li>FOIL_\*</li></ul>|
|Add another Data Store|No|
|IAM Role|Choose an Existing Role|
|IAM Role|NycWorkflowRole|
|Frequency|Run On Demand|
|Choose an existing database|TaxiData|
|Prefix added to tables|csv_|

3. 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.
4. *Database -> Tables* and review the tables.

![](https://tokern.io/static/ef6fc204abc8d87cae7e241d19e468bb/ec5a1/tables.webp)

## Verify Data in AWS Athena

Fire up [athenacli](https://github.com/dbcli/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.