- [Narrator] In this video, you'll learn how to join data
from AWS Data Exchange with your data in Amazon Redshift.
Using the methods shown, you can export third party data
from AWS Data Exchange
directly to Amazon Simple Storage Service
or Amazon S3, create an external schema for this data,
and run queries on this data from your Redshift cluster.
Before proceeding with the demo,
let's cover some prerequisites.
We'll be using Redshift Spectrum to join data
we export to Amazon S3 with data in a Redshift cluster.
Therefore, we need a Redshift cluster.
Let's navigate to an Amazon Redshift cluster
we have already set up for this example.
We'll be using redshift-cluster-2.
We have also already created an AWS Identity
and Access Management, or IAM role,
and attached it to this cluster.
Let's find the role in the properties tab.
Here's the role. Let's drill down.
The role permissions provide full access to Amazon S3
where we need to export the third party data
from our Amazon Data Exchange Subscription
and to Amazon Redshift and the AWS Glue Data Catalog.
Next, let's navigate to AWS Data Exchange
to see the third party subscription and data we'll be using.
For this demo, we'll be using a data set with Amazon S3
as the delivery method.
Here is the entitled data from our subscription.
Let's expand it.
We have multiple revisions.
For this video, we'll be using the July seven revision.
We need to export this data into our Amazon S3 bucket.
We will manually export it.
You can also use the automatic data export feature
to automatically receive data
from future revisions directly into your Amazon S3 bucket.
Let's specify our Amazon S3 bucket
and the folder that we want the data to be exported to.
Let's keep the default options selected
and export the data.
Now that the job has completed
and the revision has been exported to S3,
we'll go to Amazon S3 to see the data.
Let's find the bucket and folder that we specified earlier.
We have exported the data successfully.
Now we must catalog the data in AWS Glue Data Catalog.
Once we've done that,
we will be able to create an external schema
and query the data using Redshift Spectrum.
For our purposes, we'll create a table
in the Glue Data Catalog using a glue crawler.
Glue crawlers can scan data in all kinds of repositories,
classify it, extract schema information from it,
and store the metadata automatically
in the AWS Glue Data Catalog.
Let's create a new crawler.
Let's specify a name for the crawler.
Let's retain the default source type settings.
Here, we'll provide the Amazon S3 path
where the data we exported is stored.
For this demo, we will use just one data source.
Next, we'll choose an IAM role.
This IAM role will grant the AWS Glue Service
access to the files in Amazon S3.
We'll retain the default frequency setting.
We can optionally create a new database
as part of this process.
For our purposes,
we'll use a database that has already been created.
Let's review our options and finish setting up the crawler.
Next, we'll run the crawler.
The crawler has finished running.
Notice that one table was added to the database.
Let's take a quick look at it.
Let's drill down.
The table points to the Amazon S3 location
where the file we exported
from AWS Data Exchange is located.
Next, we'll connect to our Amazon Redshift cluster.
We'll use version two of the query editor.
Let's connect to our database.
We'll edit the connection
using the default username and database.
Our Redshift cluster has an empty database called dev.
It's empty at the moment, but we can add data to it.
Let's paste in a command that will create an external schema
to connect to our data in the Amazon S3 bucket.
Let's run this command.
If we refresh the Redshift clusters,
we should be able to see our data.
Here's the data that we created
when we ran the glue crawler.
Now we can run another command
on this table and see its results.
Here are the results of the query we just ran.
As you can see, by using an external schema,
you are able to directly query your data
in Amazon S3 via Redshift Spectrum.
Now, let's see how Redshift Spectrum can be used
to join your data in Amazon S3 with your data in Redshift.
Let's create a table in our Redshift cluster
to demonstrate this.
Here's the table we just created.
Next, we'll insert some data into this table.
Here's the data.
We can also run a query to get account
of the records we just created.
Next, let's run a query that will join the company data
with the data table that exists in Amazon S3.
As you can see, we were able to join the data
in Amazon S3 with data in Redshift successfully.
You can also load data from Amazon S3
into your Redshift cluster using a copy command
and natively join the two sets of data.
You've just seen how you can join third party data
from AWS Data Exchange with your data in Amazon Redshift.
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.