(upbeat music)
- [Instructor] Before we get started,
we'll need two things.
Firstly, a subscription to a Redshift data product
in AWS Data Exchange
and secondly, a Redshift cluster.
If we select the navigation bar
here on the left,
we can see an option to browser the catalog.
And here in Refine results,
you move halfway down the page,
we can select the filter
for data available through Amazon Redshift.
Today, we'll use the first dataset,
the Worldwide Event Attendance,
so that you can repeat the exercise yourself
and get comfortable with the overall process.
As you can see by the green banner at the top of the page,
I've already subscribed to this product.
If you wish to subscribe,
you can click the orange button that would usually be here,
Continue to subscribe to subscribe to the product.
Once you have subscribed,
you'll then the product
available in your Entitled data section of the console.
As you can see,
I have the Worldwide Event Attendance product
just here at the bottom of my list.
Now that we have a data exchange product available
that we can share with new or existing Redshift clusters,
let's move across to the Redshift service
and see that in action.
I have a few demo clusters available,
but we'll create a new cluster for today's task
so that we can discuss
a few of the configuration requirements.
If we select Create Cluster,
we can provide that cluster with a name.
We'll choose the node type
and for this example,
we'll use an RA3.xlplus.
And you should note that data sharing
with AWS Data Exchange
only works with the RA3 node types.
You can't use the DC2s.
We'll just select the minimum amount of nodes, two node,
and then we'll move down to the configuration.
We'll let Redshift autogenerate our password
and we will change some of these additional configurations.
Firstly, we'll open the database configuration
and we will turn on encryption
using the AWS Key Management Service
and the default Redshift key.
Once we've done that,
we can click Create Cluster.
That will take a short time to confirm
and you should see a blue banner at the top of the page
stating that the cluster is being created.
I'll return in a second
and show you what we've created as you can now.
See, my RA2.explus two-node Redshift cluster
has now been created
and is available for use.
Now that it's available for use,
we should think about how we can use
our datashare from AWS Data Exchange.
If we select datashares that you can see
over here on the left in the navigation bar,
you can now see the datashares that you have
from within your account,
from secondary or other accounts,
and any subscriptions that you might have.
And as you can see,
we have our Worldwide Event test data subscription
now visible in Redshift.
To bring that data into the Redshift cluster
and begin working with it,
we will use the Redshift query editor.
And today, we'll use version two of the query editor.
To connect to your cluster,
click on the cluster name on the left.
As you can see here, redshift-cluster-1.
You should see a blue banner establishing connection.
You can then run queries against your cluster.
For example, here I'm asking the cluster
to show the datashares which are available.
We run that query.
You can now see the Worldwide Event test datashare
which is available from AWS Data Exchange
as an inbound datashare,
and it provides the producer account
and the producer namespace information.
And we'll use that in a second
to describe the datashare with another simple command.
Here we're going to ask the cluster
to describe the datashare
and we'll provide the datashare name,
and then we'll provide the AWS account ID
and the namespace that you found in the previous query
from the producer account
and the producer namespace.
If we highlight that query,
we can click Run
and you will see the content of the datashare
at the bottom of the screen
such as the different tables
and the schemas that are included within that product.
To create a database from our product,
we can add another statement,
this time to create a database
and here we'll provide a database name.
We're going to create it from our datashare
and we provide our datashare name.
And then finally we provide our AWS account ID
and our namespace again to identify the resources
it's going to be created from.
We'll highlight that statement and click Run.
And if successful, you should see
a query respond at the bottom of the page.
As an administrator, I could
begin conducting queries immediately,
but you may need to provide access
to these particular databases.
So using simple Grant Usage statements on the database,
you can grant access to individual users
or groups as you can see on the screen.
With our first datashare query,
let's check out the size of one of our tables
with a simple Select Count statement,
and here we're looking to count
the number of records in the sales table, a simple query.
We'll run that query
and you can see at the foot of the page
that we have over 170,000 records
in our shared dataset from AWS Data Exchange.
From here our analysts
will likely wish to create aggregate views
and other tables so that our consumers
can have query performant views
across different aspects of this data.
In this example, we will create a table,
a new table called Event Aggregate
and we'll create that from our existing datashare database.
Let's run that query.
Once that table has been created,
we'll be able to run simple select stars across that table
locally with reduced performance overhead
without needing to run the original query
to generate that data.
And that'll mean our consumers
will have faster access to this data.
To combine this data with our own data,
we could use the COPY command to load data
from Amazon S3 into the cluster
and join that data with this new data.
Alternatively, we could use Redshift Spectrum
to ingest our existing Glue catalog
and conduct joins across data
that's held within an existing data lake.
In the background, I've associated
an IAM Redshift Spectrum role with my cluster.
So if I want to create a new data catalog,
an external schema from my Glue data catalog,
I can use the Create External Schema command
and the IM role that I have
associated with my Redshift cluster
to create an external schema.
And that external schema will allow me to query
and join data within that external catalog
with the data that I've brought in
from my AWS Data Exchange datashare.
In this final query of the demo,
I'm going to join my Data Exchange data sets
with an existing dataset from my Flue catalog.
In the query, I will aggregate data
from the event, venue, and sales tables
and I'll aggregate it at the state level,
highlighting how much income we've made by state.
But I'll pull in the data from my external Spectrum schema
and that includes census data.
So here I'll be able to understand at the state level
the population of the individual state
and the population density,
and hopefully that will help me understand
a little bit more about these individual events
and how maybe profitable they are.
So let's run that query.
So you've seen how quick and easy it is
to start joining, sharing,
and aggregating data on the AWS platform.
The next step I would suggest
is to go and explore the AWS Data Exchange catalog.
Find some datasets that you can join for yourself
and begin thinking about how you can use this data.