Instructor: Okay, so let's use the Athena service
to query our data in Amazon S3 using the SQL query language.
So we are getting into Athena, and, as we can see,
this is the new editor.
And, so, before you run your first query,
you need to set up a query result location in Amazon S3.
So let's click on View settings and then Manage.
And then, here, when to specify a location of query results.
So what I have to do is to go into Amazon S3,
create a bucket, and I'll call this one
stephane-demo-athena-eu-central-1,
and that should be unique.
And then we'll create this bucket.
Now the bucket is created, I can click on View details.
Here is my buckets, and I can just copy
the bucket name right here.
And I'm going to just enter s3:// and then my bucket name.
Just click on Save, and now my query result location
is going to be in this S3 buckets.
So, now that we have this done,
we can refresh this page, and we can see
that there are already some Data Sources,
the AwsDataCatalog, and some Databases we can use, okay.
But what I'm going to do is that I'm going to create
my own database for it to work
because you may not necessarily have this.
So, for this, it's pretty easy.
We're going to create our S3 access logs in Amazon S3.
So I've made my bucket called
demo-s3-access-logs-stephane-2020,
which contains my access logs in this S3 logs folder.
So what I have to do now is to actually create a database,
and create this table that will present the objects in here.
So, how to do so?
Well, let's go into our code.
And the first line we have under S3 events,
athena-s3-access-logs-sql, is to create this database.
So we'll paste this in,
create database, and then click on Run.
And this creates a database successfully,
so now we have a specific database, here,
called s3_access_logs_db.
Next, we need to create an external table
that represents the bucket logs.
So let me paste, copy and paste this.
And the question is, how did I get this?
Well, if you type athena s3 access logs, on the internet,
you will have a link, and this link will actually give you
the query units you do to create these proper access logs.
So the one thing I have to change is under the location,
when to change a target bucket name and the prefix.
Well, it turns out that my properties will give me this
so I'll copy this S3 URI,
which has the bucket name and the prefix.
So I will paste this in right here, and we're good to go.
Now let's run this, and now the query is successful.
So, now, under this database, we see there's a table called
mybuckets_logs, with a bunch of columns.
So if I click on this, and click on Preview Table,
now this is going to run a select star
from my table, limit 10.
And, at the bottom, we see that it returns 10 rows, okay,
and these 10 rows represents my access logs,
so this is super handy.
Now I get all my access logs data in this table,
super, super handy.
So this is just scratching the surface because now,
using Athena, we can run some more
interesting types of queries.
So, as we can see right now, we have done nothing.
We didn't set up any database, per se,
we didn't provision database, this is all serverless okay.
All these queries are run by the Athena service
directly on the data that sits within our S3 buckets,
which is, I think, extremely powerful.
So, in this query, we're going to say
we want to get the requesturi_operation,
the httpstatus, count star, to count how many,
FROM this table, and we're going to do a GROUP BY.
So this is a more advanced SQL query,
but it's gonna give us a log of reports
of the status codes as well as how many times they occurred.
So, we can see, for example, that a GET 404
happened 49 times, and a HEAD 200 happened 81 time,
and so on, or GET 403 happened 14 times.
So, this could help us, for example,
do a report into if visioning what types of queries,
or what types of requests do not happen,
or do not succeed, on our buckets, so super handy.
And one last, for example,
we saw that there were a few, four or three errors,
so it can say, hey, this is when it's unauthorized,
this is very fishy, either someone is not authorized
to do something, and, cool, we want to know who and why.
Or if someone is trying to get unauthorized access
to our buckets and we wanted to just deep dive into it.
So, again, we run this query and then we have 44 rows,
which are not present here because the data is probably
very, very big, but we have 44 rows, okay,
and these rows will represent what type of query
generated and ended up in a 403, which is super handy.
So, again, the whole power, here, of Athena
is that we're able to literally query data on Amazon S3,
do some complex queries directly,
without setting up any servers,
without transforming our data,
just by setting up the right data formats,
and specifying what the data is,
Athena is able to help us with all these things, okay.
Finally, in Athena, you can see the recent queries
as well as saved queries, if you wanted to,
and edit the settings if you wanted to encrypt
the query results in the target buckets.
So that's it for this lecture, I hope you liked it,
and I will see you in the next lecture.