(upbeat music)
- We'll start by accessing the AWS console and navigating
to the data exchange service,
using the search bar at the top of the screen.
(upbeat music)
Once you've access the service,
you'll see the data catalog we'll be using a QIAGEN product
for today's demonstration.
So our first task is to find that product in the catalog.
And there are two means of doing that.
Firstly, we could filter back to the specific healthcare
and life sciences data, or secondly,
we could search for the QIAGEN product
that we're going to use.
And that's what we'll do is that we'll provide
this search terminology and click search
and we can now see the five products available
in the AWS data exchange data catalog.
If we want to use this data,
we'll need two things initially.
Firstly, a place to store the data,
normally an Amazon S3 bucket and secondly,
an active subscription to a product itself.
We'll be using this QIAGEN Oncoland example data set
for our demonstration today.
So I'll select that product.
Once you've accessed the product,
you can see a green banner at the top of my screen,
which highlights that I've already subscribed
to the product.
If you want to subscribe, you'll be able to click
the continue to subscribe button
where you'll be prompted for some further information
and get the chance to confirm the subscription
with the data provider.
Once the data provider has reviewed your request,
hopefully that will be approved.
And you'll find that data product
in the entitled data section
of the AWS data exchange consult.
Now that we have entitlements for our data,
we can select our dataset to see this specific product.
Below that we can select our specific product.
And that will show us the underlying data revisions
and they contain our data.
Select in the revision.
We can see the assets or the files,
which the revision contains and selecting individual assets
within the revision, we now can choose
to prepare the data with AWS Glue DataBrew
which is an extraction transformation and load service.
Or we could conduct a number of export actions
such as exporting the revision to Amazon S3,
exporting just selected assets to Amazon S3
or downloading the selected assets locally.
We are going to download our data to Amazon S3,
to use it with AWS native services.
So first let's create an Amazon S3 bucket.
To do that, we'll move to the Amazon S3 management console
and click the create bucket button.
Next, we'll provide a bucket name.
We'll make sure that the bucket is in the correct region.
As AWS data exchange is region specific.
Your products will be available in specific regions.
When creating the bucket, we have other options
around locking public access to the bucket
and we'll leave this as default blocked public access.
We have the ability to add versioning to our objects
within our bucket and tags so
that we can effectively manage
and automate actions associated with them.
And equally we can apply encryption on our bucket.
We'll leave these orders default go
and select create bucket.
If that's been successful,
we should see a green button out at the top of the page,
highlighting that we now have a new bucket moving
back to AWS data exchange.
We can now choose to export our revision in totality,
into Amazon S3.
At this point, we have a couple of options around
our export of the revision.
We could conduct a simple export,
which we'll just take these assets
and move them to the bucket.
Or we could conduct an advanced export,
including things like S3 key naming patterns.
S3 key naming patterns will help us
to structure the data in our bucket.
If all of the files were in an identical format,
we'd want them all in a single folder
so that they could be read as a single table.
But in this case,
each file contains differently structured data.
So here we'll want to separate the files
into their own folders with each folder representing
a file or a specific table of data.
So the first thing that we'll do is select our bucket.
Once we've selected our bucket,
we can then choose dynamic references.
And here I'm going to choose a folder name first
and we'll use an asset name as the folder name.
And we'll append that variable before adding a slash.
And we'll add a second dynamic reference to the asset name
and append that.
We'll leave the remainder of the options
as default and click export.
If that's been successful,
you should see an export job at the bottom of the page.
We'll get this some time to complete and I'll return
and show you what this has done.
Now that that job is complete.
Approximately 1.4 gigabyte of data will have been downloaded
from data exchange to Amazon S3.
If we move to our Amazon S3 bucket, we can now see,
we have a series of folders within the bucket
and each of these folders contains a file.
And that file is essentially a table of data.
How do we get access to that data?
Well, we're going to use AWS Glue,
which is AWS' extraction, transformation
and load service to do that.
We'll use crawlers first to infer a Schema
we'll create a database and the coolers will create tables
for that will allow us to query that data.
So the first thing we're going to do is create that crawler.
So we'll click crawlers and add crawler.
First, we need to provide our crawler with a simple name.
So we'll use a simple name like my oncoland crawler
Next we'll be prompted to specify the source type
of the crawler.
We can crawl data stores or we could crawl
existing catalog tables.
If we're repeating crawls of S3 data stores,
we have a couple of options here.
We can recrawl all folders.
Each time we run this crawler,
or we could only crawl new folders
that are added to the bucket.
So this helps us plan for different data patterns.
Clicking next, we then get to choose the data store.
We're going to stay with Amazon S3,
but equally this could be a JDBC connection, Mongo DB,
or an Amazon document database.
Next, we will select a path in my account
and we'll click next.
At this point,
we're prompted whether or not we wish
to add another data store here.
Here we'll say no and click next.
here we asked for an IAM role.
This Glue crawler is going
to access the underlying Amazon S3 bucket.
So it needs a role to do that,
permissions to access that bucket.
The crawler can create this role on your behalf.
All you need to do is provide it with a relevant name.
So here we'll provide the same name
that we provided the crawler.
So our IAM role will be called AWS Glue service role dash
my dash oncoland dash crawler and we'll click next.
We then get to choose whether
or not we run this crawler on demand.
So one-off ad hoc task or whether we want
to choose custom or regularly scheduled pools of this data.
As it's a one-off download,
we'll choose to run this on demand.
Next, we need to choose an output folder
or an output database so that once
we've inferred the schema of our data,
we can store that schema somewhere and allow us
to query this data in place.
We're going to create a new database for this purpose.
And we'll call that my oncoland DB and click create.
Once our database has been created,
click next and click finish.
We can now see my oncoland crawler
just over halfway down the page.
And if we select that we can choose to run the crawler.
And I would turn in a second
to show you the output of this crawler.
Now that my Glue crawler has completed.
And it took around a minute to do that.
It should have created eight tables for me and you can see,
yes it has at the top.
Each one of those tables representing an asset,
which sat within the data exchange revision.
We could look at the individual databases and table,
metadata and configuration using these sections of blue.
But instead, what I want to show you
is the data catalog itself.
So moving to Amazon Athena,
which is Amazon's SQL query tool,
we can now see our data catalog, my database
and a series of tables,
which represent the individual folders in Amazon S3.
Moving to the right of this screen,
we can see a query window
and just running a simple select count.
So all we're doing here is counting the number
of records in this specific table,
the probe level expression, text GZ table.
And this is the large table within the dataset,
actually around 1.2 to 1.4 gigabyte,
if I remember rightly.
Unfortunately, this data isn't yet optimized
for Cloud Query.
So as we can see right now,
just conducting a simple count of the records
because the dataset is so large,
it's taking quite a lot of time.
So we'll let this complete see how long it took,
but then I'm going to show you some techniques
to improve things like query speed,
with data that you've downloaded
from Amazon AWS data exchange.
As you can see on the screen that count to nearly 40 seconds
to run scans 1.18 gigabytes of data
and returned over 116 million rows
and a single query taking this long to execute
will impact your ability to analyze
and get value from this data quickly.
So next I'll show you how you
can transfer inefficient data formats,
like text format into an optimized format,
specifically parquet using a simple query in Athena.
The benefits here are one,
query speed will be much improved,
but also storage efficiency.
So your data will take up less storage space.
As an example,
a one terabyte CSB file can be stored in a parquet format
in just 130 gigabytes.
At this size, parquet would be over 30 times faster
to query, would scan significantly less data at that query.
And we've provide significant cost savings
in a consumption-based model.
We'll open a new tab to conduct that query
and I've already prepared it.
So I would just paste that now.
The structure of the query,
we're going to create a table
and we'll provide the table name here,
probe level expression, underscore parquet.
And we're going to specifically denote the format
of this data.
So we'll say with format equals parquet
and parquet compression, specifically snappy compression.
And in terms of the data
that's going to fill that particular table,
we're going to create that as a select start
from the original probe level expression, text data file.
Once click run query,
that will begin to create a parquet version
of this particular table,
which we can then query independently.
We'll let this query run and I'll return
to show you the results of the query.
It took just over three minutes,
19 seconds to take that original text file
and convert it into parquet.
So now let's take a look at the impact
of that transformation and what we can do here
is we'll return to our former query.
But this time, instead of using
our text file, we will use our parquet file table.
So we'll just run that select count again
and see what difference that makes now.
So as you can see, the runtime there was just 0.7 seconds
with zero kilobyte data scans.
And we have exactly the same output this time.
So are over 116 million records,
except this time it took a significantly reduced amount
of time to get that result.
In this demonstration,
I've shown you how you can download data
from AWS data exchange,
how you can use AWS Glue to infer a schema
and create a data catalog
and how you can use Amazon Athena to query
that data catalog and optimize your data formats.
But where did you go from here?
Well, now that we have our data downloaded
into Amazon S3 and we have a data catalog
and we have a schema for our data,
we're able to integrate these data catalog
with the many and various native AWS services,
as an example,
and moving back to Glue, we could connect notebooks,
whether that's SageMaker notebooks or Zeppelin notebook
service to our data set so that we could begin
to conduct some data science.
Alternatively, if we wanted to process this data,
we might wish to create Spark jobs
and that could help us potentially automate
the regular conversion of this data,
or maybe the movement of this data
to a relational database or a data warehouse.
And that's the kind of final element I'd touch upon here.
We can create new databases and new connections
to different data stores to enable us
to integrate this data,
not just with the AWS services,
but also with your own business processes.
Hopefully this has been helpful.
If you'd like any more information,
just reach out to your local AWS representative.
(upbeat music)