‫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.