in this video you'll see how to extend
your data warehouse to your data lake
using a modern data architecture you can
move data from amazon redshift into an
amazon simple storage service or amazon
s3 data lake where it can be used to
build visualizations with amazon
quicksite and create machine learning or
ml models with amazon sage maker
to get started let's navigate to amazon
redshift
for our purposes we already have a two
node redshift cluster
let's open the redshift query editor to
review our infrastructure
we'll begin with this node nyc taxi is
the name of our database within nyc taxi
we see a spectrum schema and a taxi
schema
next let's go to aws glue where we have
a job in place to load data from the s3
bucket into the redshift database
the job reads sql files in the s3 bucket
loads them into the redshift data
warehouse and then transmits them back
to s3 as parquet files
let's look at the glue job in aws step
functions workflow studio
in the first node is the read filter job
that reads and loads data from the s3
bucket into the nyc taxi taxi schema as
sql files
let's look at the script
this code creates a temporary table
called nyc green taxi and the data is
then loaded into the taxi schema
the report job node transforms the
material in the nyc taxi database into
parquet format and unloads the files
back into the s3 bucket
here you can see the code for taking the
data from the nyc green taxi table and
unloading it into the s3 bucket as a
parquet file
let's apply and exit the workflow
now we can return to our state machine
and execute it
let's return to the aws glue console to
confirm the completion of both job runs
here you can see that both jobs
succeeded
now we'll go to the redshift query
editor to run a query on the table we
just created
here we can see the table data
now let's access the s3 bucket for our
data lake and confirm the parquet files
loaded properly
as you can see the nyc taxi schema data
has been saved in parque format
now that we have moved our data into the
data lake we can use visualizations and
ml models to interpret the data before
we do that let's review the
infrastructure we have set up to perform
these analytics
in the aws glue data catalog we have
created a database to store copies of
the data we'll use for analytics
we've also built and run a crawler to
mine the parquet files stored in the
data lake and populate a table in the
data catalog
let's look at the
table let's briefly review the data set
using amazon athena
the table has records including the date
distance and fair earned from a vendor's
taxi trips
now that we have established the data
lake we can use amazon quicksite
business intelligence tools to interpret
and analyze the data
let's ingest the data from our redshift
cluster
database since we want to perform
visualizations with data from the nyc
green taxi table we'll select the taxi
schema table
at this point we can preview the data
on the left is a complete list of fields
and at the bottom of the screen is our
data set
we can adjust data types if necessary
since we don't need to make adjustments
we can save and visualize the data set
for our first chart let's graph a count
of trips over time
to do this we'll drag the data we need
to the graph
the visualization now shows a count of
trips over time by trip type
let's add another graph to show the
history of trip distances
for this we'll need to add a calculated
field
we'll name the field and supply the
formula to compute
let's save the field
we'll use a clustered bar combo chart
for this visualization
let's apply the field we just created to
the x-axis
let's add the total amount and pick up
date time fields to complete the graph
here we can see the final visualization
we can also build ml models from
redshift data warehouse material
let's go to our second redshift cluster
node to review the infrastructure we
have set up for machine learning
this information schema involves sales
data
here are all the data sets included in
the schema
we also have an external schema called
spectrum ml with an external table
called sales raw
we have taken the raw sales data and
added some custom filters to create a
new table in the public schema called
sales clean
let's run this query
we'll use this table for building ml
models using amazon sagemaker
for demonstration purposes our jupyter
notebook instance is already running
the first step installs these packages
the next step imports aws datawrangler
which is used to connect to the redshift
cluster
we already have our user connected
next we'll aggregate the data in the
sales clean table and then split it into
a training data set and a testing data
set
let's also run a time series forecasting
model so we can visualize seasonality
and any other noise apparent in the data
with this code we'll create a time
series forecast model to do this we'll
implement a version of a standard method
called the auto-regressive integrated
moving average or arima
here we can see the model that's been
created
we'll also run a command that plots
forecasted versus actual sales
now we're going to run this command that
contains a random product code to
demonstrate pulling data from redshift
to use for machine learning
we'll forecast this data for the next 30
days and visualize the result
the blue line represents actual values
and the yellow line shows the forecast
we were able to use the same redshift
cluster for visualizing data as we did
to build machine learning models
you've just seen how you can extend your
data warehouse to your data lake
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