In this video, you'll see how you can build a modern data architecture using native AWS services.
With this approach, you can seamlessly integrate your data lake and data warehouse,
enabling easy data movement and unified governance over your data.
In the first use case, we’ll show low-code and no-code approaches for ingesting,
transforming, moving data into a data warehouse ready for analytics.
We’ll begin with a MySQL instance containing a sample database in Amazon RDS.
In AWS Glue, we created a Java Database Connectivity (JDBC) connection pointing to the sample database.
The database table was then crawled to generate metadata in an AWS Glue Data Catalog table.
We then created a job in Amazon Glue Studio to ingest data from the AWS Glue Data Catalog table
into an Amazon Simple Storage Service (Amazon S3) data lake.
In the Data source node, we can see the schema of the table and preview the records.
In the Transform node, we can view the targets, adjust the data types as necessary,
and preview the results.
Here, we can see that the data target is the S3 bucket, and the format is Parquet.
This job will also create a new table in the AWS Glue Data Catalog.
AWS Glue supports many data sources, targets, and transformations.
Additional data sources are available in AWS Marketplace.
For instance, we could bring in data from Salesforce or Splunk.
Back in Amazon Glue Studio, let's view the job runs.
Here’s confirmation that the mysql_to_datalake job had a successful run.
Let's visit the S3 bucket where the data was ingested.
The bucket already contains a number of Parquet files.
Going to the AWS Glue Data Catalog, we can see that the data_lake_source_table was generated as expected.
Now that the data has been ingested into S3, let’s see how it can be transformed using AWS Glue DataBrew.
Glue DataBrew is a visual tool that enables users to quickly prepare data for analytics without writing any code.
Here, we have a dataset called “data-lake-source-table,” which is connected to the AWS Glue Data Catalog table with the same name.
We have also created a demo project,
which provides an interactive workspace for applying data transformations.
Our demo-project lets us see the data we ingested into the S3 bucket.
On the right, is the “recipe” of transformation steps we’ll apply.
AWS Glue DataBrew includes more than 250 prebuilt transformations.
Each recipe can include up to 100 transformations.
Let's review the first step.
This "merge columns" step transforms the data from three columns—first_name,
middle_name, and last_name—into a single column represented as full_name.
We can preview the transformation before applying it to the table.
Let's review the second step of the recipe.
This step creates a column called derived_sex to reflect a person’s sex as either male or female.
Here we can see a preview of the transformation.
The third step uses logical conditions to define three cases related to age.
The first case filters the age values between 21 and 30, the second between 31 and 40, and the third between 41 and 50.
Anyone younger or older is filtered out of the table.
When we preview the changes, we see the new age_group column.
The fourth and fifth steps in this recipe delete the columns made redundant by the previous three steps.
With AWS Glue DataBrew, we can publish a recipe and apply it to multiple datasets.
On the Jobs tab, we see our published recipe jobs.
We can select one to review its run history and details.
We can also edit the output settings to apply the job to a different dataset.
In the current configuration, the data output is an AWS Glue Data Catalog table.
This eliminates the steps needed to crawl the S3 bucket and apply the recipe to the table in Glue DataBrew.
This is how the output table appears in the AWS Glue Data Catalog.
We can query this data directly using Amazon Athena.
The final piece of this architecture is the Amazon Redshift data warehouse,
where we've created a cluster to store our transformed data.
On the Editor tab, we can see the schema attributes represented on the redshift_demo_table.
In AWS Glue, we can see the JDBC connection pointing to the Redshift demo table,
which has been crawled as before.
Here we see the metadata that appears in the AWS Glue Data Catalog after crawling the redshift_demo_table.
In Amazon Glue Studio, we can see the ETL job that loads the transformed data into the data warehouse.
From the Data source node, we can review the output schema and preview the results.
Let's do the same for the Data target node.
Let's review the most recent job run.
The data_lake_to_data_warehouse run completed successfully.
Let's return to Amazon Redshift.
The data is ready to be queried using the Amazon Redshift query editor.
Now that we have reviewed the steps for building a modern data architecture,
let's see how we can orchestrate and automate this entire workflow.
Here we can see our flow visualized in AWS Step Functions Workflow Studio.
The first node orchestrates the ingestion of data from MySQL into the data lake.
The second node orchestrates the AWS Glue DataBrew job that transforms the data.
The third node orchestrates the AWS Glue job that loads the transformed data from the data lake into the Redshift data warehouse.
Once we've executed the steps, we can monitor their progress using the Graph inspector.
Now let’s look at a different use case that involves sharing the data lake with other accounts.
We can use AWS Lake Formation to register the location of the data lake.
This requires selecting the path to the S3 bucket and assigning an AWS Identity and Access Management (AWS IAM) role.
In this case, we’ve already registered the data lake location.
Note that AWS Lake Formation shares the AWS Glue Data Catalog.
Let's view the tables.
When sharing data with consumer accounts, we can share the entire database or just parts of it,
such as a few tables or even a few columns within a table.
To share the data_lake_source_table,
we can simply select it and then grant data permissions to specific accounts.
Consumer accounts are external accounts.
We can choose which table permissions to grant.
Here’s where we choose whether to allow all data access or column-based access.
Because we’ve already granted access to this table, we'll cancel and move on.
Under Permissions, we can see that the source table has been shared with
the AWS account corresponding to the principal ID.
We can view the list of shared objects in the AWS Resource Access Manager console.
Now that the table has been shared with a consumer account,
permissions can be granted to users within that account.
In the IAM console, we have two users, data_analyst and data_scientist,
who require access.
We'll grant them access to this local database in AWS Glue.
When the consumer account is not part of the same organization as the data lake account,
it must accept the resource share in Resource Access Manager.
In this case, the consumer account has already accepted the resource share. Let's view it.
The Glue data lake source table, Data Catalog, and local database are all shared resources.
The next step is to create a sharable resource link from AWS Lake Formation.
This can be done for the entire database or a single table.
Now we can fill in any fields not already populated and create the link.
A link has already been created, so let's cancel and view it.
We can see the underlying table object and its attributes.
Now that we have the link, we can grant access to it.
We'll grant permission to the data_analyst IAM user.
We’ll choose the database.
Let's assign the table and grantable permissions.
For this user, we'll grant column-based access.
Let's select the columns account_id, first_name, middle_name, and last_name.
Then we can finish and grant the permissions.
We can repeat the same process to grant permissions for the data_scientist user.
For this user, we'll share the account_id, sex, and age columns.
Now we’ll sign out and log in using the users’ AWS accounts to validate their access.
Let's open Amazon Athena.
We’ll run a query to demonstrate we have access to the data_lake_source_table.
The query revealed data from the account_id, first_name, middle_name, and last_name columns.
Now let’s validate the data_scientist account's permissions.
Again, we’ll run a query in Athena.
As you can see, the account_id, sex,
and age columns were successfully granted to the data_scientist.
You've just seen how you can build a modern data architecture with
AWS services using low-code and no-code approaches for ingesting,
transforming, and moving data into a data warehouse ready for analytics.
You can learn more about this topic in the discription and links for this video.
Thanks for watching now it's your turn to try.