- [Presenter] In this video,
you'll see how you can transform data sets
from AWS Data Exchange
with AWS Glue DataBrew.
You'll learn how you can export third-party data sets
from your AWS Data Exchange subscriptions
to the Amazon Simple Storage Service,
or Amazon S3,
and profile the data with AWS Glue DataBrew,
and then visually explore
and transform the data to prepare it for analysis.
To get started,
let's review a few prerequisites.
First is the Amazon S3 bucket,
where we'll export the data from AWS Data Exchange.
We also need two identity and access management,
or IAM, roles.
The first is the AWS Glue DataBrew Service role
that will execute the data profiling
and transformation jobs.
It requires read-write access
to the AWS Glue Data Catalog,
and the Amazon S3 bucket.
The second role has full access
to AWS Glue DataBrew and AWS Data Exchange,
as well as read-write privileges
for the Amazon S3 bucket.
We'll assume this role as part of this demonstration.
Let's go to the AWS Glue Console.
We'll need a database to store tables
containing the schema of the third-party data.
Let's create one.
We're ready to export data from AWS Data Exchange.
Let's explore the available data products.
For this example,
we'll use a sample data product.
Let's search for it.
We're already subscribed to this product.
Let's see the data we subscribed to.
We'll use the ESG/GC High Level sample.
Let's choose the revision to use.
We'll select the asset
and prepare it with AWS Glue DataBrew.
Let's find our Amazon S3 bucket folder location.
We'll send the data to the exports folder.
We have the option to encrypt the data.
We'll skip that step.
Let's prepare the data.
The dataset has been created in AWS Glue DataBrew.
Let's view it.
The dataset preview tab
provides connection information for the dataset
and an overview of the dataset's overall structure.
The data is displayed in a grid-like visual interface.
Let's view the dataset schema.
We can see the column names, data types,
and values for the first five rows of each column.
We can also view additional configurations,
such as file type.
To better understand the data, let's
run a data profile job.
We'll simplify the default job name.
The job can be run on a sample
or the full data set.
Let's choose the ladder.
Next, we'll provide the S3 bucket location
of the job output.
This time we'll use the profiles folder.
We can also configure how the job
will analyze the data set.
For example, we can enable or disable duplicate rows.
We can also enable the job
to identify personal identifiable information, or PII.
Let's do that.
We can select certain categories of PII
to be labeled or use all.
We can also enable a correlations widget
that visualizes a correlation coefficient
for the numeric data.
Let's have correlations calculated for all columns.
By default, all columns are profiled for statistics.
We'll keep this configuration.
The rest of the configurations are optional,
such as applying data quality rules.
Also optional are the advanced job settings,
such as whether the job
can time out or be retired if it fails.
Let's edit the maximum number of DataBrew nodes
allocated to a job run.
We can also associate a schedule and assign tags.
We will choose the Glue DataBrew service role
created as part of the prerequisites.
Let's create and run the job.
We can go to the jobs tab
to see when the profile job finishes running.
Now that it has completed,
let's view the data profile.
We can see a summary and capture PII data.
Correlations and the distribution of numerical values
are also visible.
Column data was condensed into a graphical summary.
Let's view the column statistics.
This page breaks down information
by individual column.
Columns containing numeric values
show additional statistics.
We did not apply data quality rules,
but if we had, those would appear here.
In the data lineage tab,
we see a representation
of the data source, jobs applied,
and where it's stored.
Now we want to transform the data set.
The first step is to create a project.
Data cleaning steps in DataBrew
are stored as a recipe.
We'll create a new recipe
and use the default name.
Our data set is already populated.
We can decide whether to apply sampling.
We'll sample the first 500 rows.
We'll apply the Glue DataBrew service role.
Let's create the project.
The project session opens within an interactive workspace.
The left pane shows the current view of our data.
The right pane shows the project's transformation recipe,
which is currently empty.
Let's review the data sample.
Now we can apply data transformations.
To start, let's change the format of the date column.
Let's preview the changes.
We can compare the original
and new formats side by side.
Let's apply the changes.
Our recipe now includes a step
that reflects the format change.
We can also format string values.
Let's try this with the name column.
Let's preview the change.
In this case, we'll cancel the format change
and delete the column instead.
Before we apply the change,
let's review the other columns.
We'll also delete the dom_region column.
When ready, we can apply the changes.
Many other possible transformations are available,
such as applying functions.
We can also create advanced transformations,
like nesting complex semi-structured data, pivoting tables,
and grouping columns to create an aggregated value.
Let's group some columns.
First, we'll select the column
by which we want to create the new aggregate value.
We'll select industry.
Then we'll select the numeric measures and functions
we want to aggregate.
Let's preview the changes.
The group type is currently set
to replace all existing columns
with the new table we see here.
Instead, let's group the new columns
within the existing columns.
Now we see the full table again
with the new columns included.
Let's finish the grouping.
Notice that the industry column
is now the first column.
We can move it to adjust the presentation of the data.
Let's apply the change.
When we're satisfied with our transformations,
we can publish the recipe.
Now let's create a job to apply the transformations
we identified as part of the recipe
on the entire data set.
We'll give the job a name.
Next, we'll define the output.
Let's select the file format and compression type.
We'll save the output
in the AWS Glue Data Catalog database we created.
Let's define a new table.
We'll store this table in the data lake folder
of our Amazon S3 bucket.
We can have more outputs,
but we'll only use one.
As with the other jobs we've run,
encryption and job settings are available,
but we'll leave those as they are.
We can also associate a schedule and assign tags.
Finally, we'll select the AWS Glue DataBrew Service role,
and then create and run the job.
Returning to the workspace for the project,
we can view the job details while it's in progress.
The job has succeeded.
Let's go to AWS Glue to view the table.
The table schema represents all the columns
and their data type.
Now we're able to analyze our data.
Let's run some queries in Amazon Athena.
We'll select the Glue database.
Let's run a query to preview the table.
Here we see 10 results from the data we transformed,
and here are the two aggregated columns we created.
You've just seen how you can transform data sets
from AWS Data Exchange with AWS Glue DataBrew,
all via an interactive editor,
without having to write a single line of SQL
or any kind of code.
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.