- Now let's take a look at a demo
of performing Scan and Query operations.
All right so we're back here in DynamoDB
and here is the Product Catalog Table
that we created earlier.
And so if we take a look at that
so we created this table and we also
loaded some data into that table
so we can explore that by going to the Items tab
and of course we don't have a ton of data but it's enough
to allow us to take a look at these operations.
And so
with a,
the two primary operations that we have are Scan and Query.
So Scan operations, as the name suggests,
will perform a full table Scan.
And so you know there may be a time to do that
and so if we were to for example
if we were to do a Scan
and look for
let's say the title of a book, right?
Let's look for where title is greater than 18.
Let's see if we can do that.
So the attribute will be title
and the reason that we are doing a Scan here
is because we don't have an index on title.
The only index that we have is ID.
And so without an index the only, we can't do a Query.
And so we have to use a Scan.
And then we're Scanning using a filter.
So we're saying that we're looking for
or we're Scanning the table looking for
items where the title attribute equals
with a, you know we can set it to, you know
we're looking for these as string values
and then we can also say begins with 18.
And so if we were to do that and start the search,
there you go now we're getting all the titles
that begin with 18.
It appeared to come back fast
and you know technically it did,
that's because we don't have a whole lot of records.
But if you were into
you know the millions of records
and larger scale applications, it's very easy
to get into the millions and millions of records
maybe into the hundreds of millions or billions.
And in those cases doing a Scan operation like that
where you're literally doing a full table Scan
analyzing every single record in the table
and you know asking the question
does this begin with 18?
Does this begin with 18?
You know to do that a billion times will take a long time.
And so DynamoDB is capable
of single digit millisecond latency
but only when we use the Query type of operation.
So if we were to do a Query
then you'll notice here the difference.
Watch what happens.
With the Scan we're able to basically say
well I want a filter on just about any attribute.
And you can also combine that.
Let's just try this one more time before we move on.
We can also say that
brand is a string that equals or begins with
or let's just say contains mountain, right?
And then if we start that search
then now we retrieve that one record.
So again that's a full table Scan
and so notice the difference here
that when we go from Scan to Query.
Now it's requiring us to enter the partition key,
or the primary key, and it's saying
you know you can only do a Query on an index
and in this particular case the ID is the only index
the primary key being the only index.
And so you'll also notice that we don't get any choice
other than an equals.
So the scan operations provide us with the ability
to do a number of different kinds of comparison operators,
equals, not equals, less than, greater than, between,
all those kinds of things.
But you don't get that kind of flexibility
with the partition key or the primary key.
So here, if we were to remove these, and the we could say
well why it wants a Query based on the ID
so show me 203, start the search
and just like that is comes back,
that kind of operation where you are basing it on an index
is going to be much much faster.
So that's the general recommended best practices
that we try our best to find a primary key
that allows us to do that kind of Query.
And the other thing to keep in mind with DynamoDB
just based on the distributed nature of it,
we talk a lot more about this in other videos
like the Solutions Architect video,
you know you really need to give some thought to this ID,
the kind of value that you're using there.
If one of these products were to become much more popular,
let's say that this particular product,
ID 203, became so popular that
millions and millions of people were looking at it,
then the fact that this one ID
is getting so much more traffic than the other IDs
would mean that we would end up with a hot key
where one key is getting a lot of traffic
and as a result we would end up with a hot partition.
And we could see some performance degradation
with that kind of thing.
And so ideally we have IDs that allow the demand,
whatever that is, the reads and the rights
to be evenly spread across
the cluster that is powering this DynamoDB table.
Okay let's go back to our scan
and here we're not, we don't have any filters.
Let's go ahead and do that
and then without any filters it just gives us everything.
So now we can see essentially all the records,
they're in the table.
It will paginate and so we can create indexes
after the fact, global indexes after the fact.
There is a local secondary index
and a global secondary indexes.
Local secondary indexes can only be created
at the time you create the table.
Global secondary indexes can be created at any time.
So let's go back to the items real quick
and I'll show you if we wanted to Query this table,
and if we find that we were regularly
querying the table based on the title,
then we would want to create an index for that.
We don't want to rely on the scan operation
to return fast results because once this is big enough,
it just won't.
So in this particular case we could create
a global secondary index for the title attribute.
So let's see if we can do that real quick.
Let's create an index and in this particular case
let's just verify that title.
Yep so it's just title, right.
So Indexes, Create, Title.
And it is a string.
And in terms of projected attributes
we can go ahead and say all.
That way we don't,
if we only projected the keys,
then we would have to pull the key from the
from the index and then use the key
to pull the data from the table.
But if we project all of the attributes into the index,
then we can just get everything from the index,
rather than having to go and join it to the
you know to the table in our application.
And we do have the ability to change the capacity units.
For global index the
the throughput capacity that we provisioned
is separate from the table.
So here I might say well you know
let's just go with two and two for now
for the sake of this demo.
And then we'll go ahead and say Create the Index.
And it will take a few minutes for that to create
so we'll go ahead and give that just a few minutes
and we'll fast forward to that creation.
All right and there we go.
Now our global secondary index
for the title is now active.
That did take a few minutes.
It took a little bit longer
than it took to create the table.
And again you can see here it has one partition key
so you can think of,
for those of us who might be you know
coming from a background in relational databases,
you can think of a global secondary index
as a, sort of a,
It's very similar to a materialized view.
So as changes are made to the table,
those changes are then reflected
in the global secondary index.
And so if we were to come back here to our items,
and we refreshed this,
we don't really get the console,
let's just try to refresh this.
I don't think the console gives us the ability
to query based on,
yeah there we go so it does.
Now that we have our index active
and by refreshing the page here,
now we have the ability to
query based on the title index.
Right so now if we do a Query,
now it's saying title as a string equals
and then of course we have to know the title of the book.
So if we wanted to get information based on,
you know the exact title of a book,
such as Book 101 Title, and we run that search,
then very quickly, in single digit milliseconds,
we could get that result back.
So that's an example of you know using Queries
again Queries can leverage some type of an index
so the, again, the primary key is an index
and then we created a secondary index
on the title attribute.
And so by using Query we can
get very fast, single digit millisecond response
otherwise, maybe there's a time and a place
when you need to, but just remember
that scan operations are full table scans
and you know when you have a lot of items
it will add latency to those results.