AWS Glue DataBrew is a visual data preparation tool
with an interactive point and click visual interface
that doesn't require writing any code.
Data preparation can take up to 80%
of your data analysis time due to time consuming,
expensive,
and manual or highly technical processes.
This demo will show you can import,
clean,
and normalize data
to get it ready for input to a business report
or a machine learning model.
We'll also show some exploratory data analysis
to get deeper insights in to the data.
This is first critical step in many analytics or machine learning workflows,
because inaccurate or corrupt data can produce incorrect results.
Let's get started by creating a new project.
DataBrew allows you to securely upload files from your local disc,
or connect to data in an Amazon S3 data lake,
Amazon Redshift, and a variety of other datastores.
For this demo, I'm going to select a dataset I previously set up
from the CityBike bikeshare program in New York City
found at citybikenyc.com.
We are now in a DataBrew project, allowing us to explore the dataset.
Note that in the console we are working with a small sample of the data,
but we will eventually run a job against the entire dataset.
The dataset consists of bike trip information such as trip duration,
start and end station, as well as customer details.
We want to do some analysis by day
so let's extract the day from the date column.
DataBrew recognized the data as a date,
and allows us to change the format to a string
which will make it easier to extract the day using the split function.
We then split the data using the space character as a delimiter.
Once the data is split,
we can reformat the time stamps to show just the day and month.
Then we rename the column so we can keep track of it more easily.
Data cleaning is an important part of the data preparation process,
and DataBrew makes it easy to clean data.
You can see that the start and end station names contain ampersands
that can't be processed in our data analysis.
We'll clean the data in this column by removing any special characters.
In this column you can see that there are two primary types of customers,
subscribers who are annual members,
and customers who hold a time based pass.
This dataset will be used to train a machine learning model
that predicts expected trip duration for a new customer in a given month.
To prevent any unintended bias in the ML model,
we need to convert the input from character to numeric form.
We will use the categorical mapping function to map
the user type to numeric values in a separate column.
We'll choose to map all the values,
and since there are just two values we assign them to one and two.
This creates a new column with our mapped data.
At any point, we can quickly undo,
redo,
edit,
or delete any step
and see instant updates to the data and statistics.
Next, let's do some quick analysis on our data to better understand it.
We'll compare average trip duration by user type and by start station.
So, we'll choose the group button,
select user type,
and then mean trip duration.
It's interesting to note that on demand customers, in general,
appear to have longer trip times.
DataBrew also has rich profiling functionality.
In a project, you can easily navigate to the profile view
to get statistics on the larger dataset
including missing cells,
duplicate rows,
a correlation matrix, and more.
We can zero in on a specific column, such as a start station name.
We get a distribution of values across the column, and other useful information.
For example, we can quickly see that the West 42nd and 8th Avenue station
Is the most popular station for the month of September.
For ID columns, you can quickly check cardinality, skew,
and other statistics to make sure that the nature of the data aligns with what you're expecting to see.
For our next analysis, we want to add bike maintenance records so we can check
when a particular bike was last serviced and if it impacts average trip times.
So, we'll want to join trip data with another dataset.
We'll choose the join option,
select the dataset,
specify a join key,
and join type.
After a few selections,
the join is run, and we have added maintenance records for the bikes.
We could then go on to flag any trip times longer than 30 minutes
when a bike was serviced a day before the rides to validate our hypothesis.
Note that all the steps performed on the sample of the data set get captured as a recipe
that can then be applied to the larger dataset.
We can now create a job to run this recipe.
We'll specify an Amazon S3 bucket to contain the outputs,
and then select CSV for business reports,
and the more compressed Parquet format for further processing in Amazon Redshift.
This recipe can now be run on new datasets for different months.
Other analysts and data scientists in your organization can also import this recipe
into their project and build on it for further analysis.
In this demo you've seen how easy it is to use DataBrew to prepare and analyze data
without writing any code, enabling you to generate business reports,
query data, and train machine learning models.
Once the data is prepared, your organization can immediately start using it
with AWS and third party analytics and machine learning services.
Please visit the AWS Glue website for additional information to get started.