In this video, you'll see how you can integrate a serverless data lake into your modern data architecture.
Using various AWS Analytics services,
you can rapidly ingest and transform vast amounts of structured and unstructured data from data-streaming sources,
query and visualize the data to get meaningful insights, and apply machine learning to your data lake.
The process begins with data ingestion.
For our purposes, we’ll represent ingestion using data already exported from our Amazon Relational Database Service (Amazon RDS)
to an Amazon Simple Storage Service (Amazon S3) staging bucket.
Starting in AWS CloudShell, we’ll copy the data from the staging bucket into a new S3 bucket created for our data lake.
Let’s go to our S3 bucket to see if the data was copied into it.
The “tickets” object contains our data on ticket sales collected from professional baseball and football games.
The Amazon Database Migration Service (DMS) folder is our data source.
These folders contain the copied data.
The next step in setting up our architecture is to apply data transformations.
For that, we’re going to use AWS Glue.
AWS Glue is a fully managed data catalog and ETL (extract, transform, and load) service.
To create our AWS Glue Data Catalog, we’ll add a crawler.
The crawler will comb through our data store to determine the schema for our data,
and then create metadata tables in our data catalog.
For the path destination, we’ll specify the S3 bucket.
We'll create a new AWS Identity and Access Management (AWS IAM) role for this crawler.
We'll leave the run frequency as run on demand.
Let's add a database for the crawler’s output.
Let’s change the configuration option to add new columns to the table if the crawler detects schema changes in the data store.
Now let's finish configuration and run the crawler.
We can see that 15 tables were added.
Let’s navigate to the database we created to see them.
Next, we’ll do a data validation exercise on one of the tables.
In this case, the crawler could not determine names for the database columns when crawling the S3 data bucket
and simply provided numerical placeholders.
Let's edit the schema to input appropriate column names for the data they represent.
Let's save our changes.
Once all our database tables are validated, we can convert the database tables into Parquet format.
Let's go to AWS Glue Studio to create some ETL jobs that will establish new portal locations in our S3 bucket for the Parquet tables.
Each job will use the ticketdata database as the source.
The first job will transform the sport_team table.
In the Apply Mapping node, we'll adjust the id data type from string to double.
Next, we’ll change the output format to Parquet.
For the S3 target location, we’ll choose our bucket and the tickets object.
We’ll also specify the folder that will store the Parquet files and the table that corresponds to this job.
Let's also rename the job to match the table.
In the Job details tab, we'll select the IAM role for this job.
Let's disable the job bookmark since we don't need it.
Now let's save and run the job.
We can review the job in the monitoring tab.
Here we can see that one job, which is the one we just created, is running.
While our first job is running, let's create jobs for our other tables.
We can clone our first job and adjust the settings rather than configure a new job from scratch.
We’ll adjust the job name and select the next table to work with.
A useful feature of AWS Glue is that it creates a name folder for each of the tables automatically.
For the sport_location table, we’ll keep the same data type settings.
Next, we’ll revise the folder location of the URL.
Now we’ll confirm the IAM role and other settings, and then save and run the job.
Let's repeat the cloning process for a third job.
Now let's go to the Monitoring page to view the status of these jobs.
All five jobs were successful.
Let's go to our S3 bucket to see the Parquet portal location.
Here we can see the newly created Parquet object with folders for all five tables.
Looking in one of the folders, we also see the type is Parquet.
Next, we'll return to AWS Glue and create a crawler for Parquet files.
We’ll retain the default source type settings.
For the path destination, we’ll specify the S3 bucket holding our tickets object.
Let's also ensure the dms_parquet folder is included in the path.
We'll choose the appropriate IAM role.
We’ll choose our ticketdata database.
We can also add a prefix to make it easier to interpret table data.
Let's finish and run our crawler.
When the crawler finishes running, we’ll be able to see all the Parquet tables added to this data lake.
For now, let’s move on to the next step of our Data Lake integration: data querying and visualization.
Let's go to Amazon Athena, where we'll query our data.
Athena is a serverless query service that makes it easy to analyze data in Amazon S3 using standard SQL.
As you can see, Athena automatically identified the AWS Data Catalog as the data source and ticket data as the database.
Let's run a query against the Parquet tables to generate results on the sporting event information.
The results identify the sporting events, event dates and times, team names, and locations.
We can create a “view” from this query.
A view is a logical table that updates each time we run the query.
As you can see, Athena generated a second query for the view we created.
Let's run a third query for sporting ticket information.
We can save this query so we can run it again anytime.
We’ll call it sporting_event_ticket_info.
Now let's run the query again.
The results show information such as seat location, ticket prices, and ticketholders.
Let’s create a view from this query as well.
We can query the view we just created to see the average ticket price for each sport.
Let's save this query.
Of course, we can create a view from this query if we want to reuse it.
With our architecture set up and some queries saved,
we can now visualize our data using interactive business intelligence dashboards in Amazon QuickSight.
We'll begin by creating a new dataset using Athena, since that is where we made our SQL queries.
We’ll select our ticketdata folder as the data source, and then validate the connection.
Now we'll select a table to visualize.
We’ll select the sporting_event_ticket_info query we saved.
Once the query is ready, we can select fields to visualize and how we want to see the information.
Here we see the ticket price sum as a currency.
Let's add another dashboard to visualize ticket prices versus event dates.
For this, we’ll use a vertical bar graph.
It's easy to add data to the graph.
Simply drag fields to the field wells at the top of the page.
Amazon QuickSight also produces insights to help you monitor your data and detect any anomalies.
An additional step we can take to integrate our data lake is to apply Machine Learning (ML) models to it.
Let's go to the Amazon SageMaker console.
In SageMaker, we'll create a Jupyter notebook instance that integrates with our data lake in Athena.
Let's name our notebook.
For this notebook, we need to create a new IAM role.
It's optional to specify which S3 buckets to grant access to.
We'll allow all S3 buckets.
Let's open the IAM role and grant access to Athena, where we've stored our data queries.
We'll attach a new policy granting full access to Athena.
Let's complete our notebook setup.
Now we'll open the notebook in Jupyter and run it in a Conda Python 3 environment preconfigured for deep learning frameworks.
We'll run the command to install the Athena Java Database Connectivity (JDBC) driver.
Next, we'll use the JDBC connection to Athena and populate the Pandas DataFrame.
First let's confirm the region configuration.
Now we'll run the command to input the S3 data bucket that houses our Athena queries
and include the region where it’s located.
The output is our DataFrame, which we can use to plot our data.
We’ll specify our ticketdata database to upload our Parquet files.
Finally, let's run a command to plot the data.
Here you can see the populated plot.
SageMaker notebooks enable easy deployment of hundreds of pretrained algorithms for applying ML to data sets.
You've just seen how you can integrate a serverless data lake into your modern data architecture.
You can learn more about this topic in the description and links for this video.
Thanks for watching. Now it’s your turn to try.