In this video, you'll see how you can use modern data architecture for purpose-built data stores on AWS.
With this approach, you can seamlessly move data between data stores,
deploy machine learning (ML) models, and derive analytical data insights using federated queries.
Let's begin with an example of moving data from Amazon Relational Database Service (Amazon RDS)
to Amazon Simple Storage Service (Amazon S3).
Here, you can see a PostgreSQL database in Amazon RDS.
To perform the migration, we defined our source and target endpoints in AWS Database Migration Service (AWS DMS).
We also have a change data capture (CDC) endpoint for collecting changes to the source data store during the migration.
A replication instance was set up to perform the data migration.
We also have a database migration task defined to perform a full dump.
The full dump task connects to the source endpoint, reads it, and formats it into a schema for the target endpoint.
We also applied mapping rules for the schemas and table names included in the source database.
The database migration task dumps data in our S3 bucket, which acts as our data lake.
Here we can see the data that was dumped by the database migration task.
It’s composed of player, stadium, and ticket data for professional sporting events.
Our second example will show data movement between AWS Glue and S3.
This two-step process involves running a data crawler and then translating the data format.
We defined our Glue crawler to crawl all folders of the destination data store,
which is the "tickets" folder in our S3 data bucket.
We established an AWS Identity and Access Management (AWS IAM) role with the necessary permissions,
created a schedule for the crawler, and configured a database for the crawler’s output.
Here, the crawler returned all the tables in the database.
We can validate the schema before starting an extract, transform, and load (ETL) job.
Let's view the "person" table.
The data files are stored in CSV format.
When the Glue crawler cannot define a header type from the source, as in the first column here,
the name defaults to a numerical placeholder.
We can edit the schema so that the data is properly transformed.
Once all the data is validated, we’ll go to AWS Glue Studio and run an ETL job that transforms the CSV files into Parquet format.
For this example, we defined the job to capture data from the S3 bucket,
perform an "apply mapping" action that transforms the data format, and then deliver the transformed data back to S3.
In the job details, we supplied an appropriate IAM role and defined the type as a Spark job.
We also specified the worker types, number of workers, job bookmarking capability,
and job timeout along with the number of retries.
Now we’ll run the job and monitor its progress.
Back in Glue Studio, we can review the job script for converting the job from CSV to Parquet and edit it for future job runs, if necessary.
Now let's go into Amazon Athena and run some queries on the transformed data.
Athena makes it easy to access data in S3.
All we have to do is point to the database in the AWS Glue Data Catalog, define the schema, and run the query.
Let's start by running a simple query.
Here are the results.
To simplify further, we can create views from existing queries like this one.
Views allow us to query subsets of data or combine subsets in a single query.
For example, we have created this view from our table.
When we analyze against the view, the amount of data scanned is 50.7 MB,
compared to 1.59 GB had the data been in CSV format and no view existed.
We can also build Amazon QuickSight visualizations with this data.
To do this we'll import the queries we just performed in Amazon Athena.
In this case, we've already imported the queries and have them as an existing data source.
We can edit the database, if needed.
We can also create a dataset for the visualizations.
Here is a sample visualization, that shows the aggregate of seat levels and ticket prices by location.
QuickSight also has an AutoGraph option that uses machine learning capabilities to choose
the most appropriate visual type for the number and type of the fields selected.
Next, let's run an Athena Federated Query that targets data in the Amazon RDS PostgreSQL database we moved to S3 earlier.
From Athena, we'll connect to the new data source.
You can use an existing Lambda function to manage the connection or create a new one.
We already created a function tailored to our needs. Let’s quickly look it over.
Back in the Athena query editor,
let's run a query to join data from S3 with the PostgreSQL database via the Athena Federated Query connection.
The results represent the joined data.
Now we can use Amazon SageMaker to build, train, and deploy ML models from this joined data.
To prepare the data for the ML algorithms,
we’ve used a Jupyter notebook to integrate with S3 using Athena and populate the frames for data manipulation.
We assigned the notebook with the necessary IAM roles to allow it to access the S3 bucket, Athena, and Glue.
Let's review the commands the notebook executed.
The first command applied a pip install for PyAthena to install an Athena JDBC drive.
With the driver installed, the notebook used the JDBC connection to connect to Athena and populate the Pandas DataFrame.
Another command loaded the Athena data from S3 into the Pandas DataFrame to join the query.
As you can see, we applied a query to deliver 10 data fields on stadiums.
With another query, we applied ML algorithms to interpret the data in the Pandas DataFrame.
After capturing ticket data for different sporting events,
we applied a mat plot library for an analytical visualization.
In our final example, we’ll join data between Amazon Redshift and Aurora PostgreSQL using federated queries in Redshift.
We have an Aurora PostgreSQL instance stored in Amazon RDS.
We used the query editor to load data from the "customer" table.
We also have a Redshift cluster loaded with customer data.
We then created an external table by running commands to apply appropriate permissions while joining with the Aurora PostgreSQL database.
We are now able to create the schema associated with the Aurora PostgreSQL RDS database.
Here we can see the columns that are joined in the schema.
At this point, we could run any number of queries from the federated customer table.
For our purposes, we'll end here.
You've just seen how you can use modern data architecture for purpose-built data stores on AWS.
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.