Query S3 Data with SQL: Amazon Athena and AWS Glue Tutorial

Key Takeaways

Let's continue our journey on querying data directly with Athena SQL on S3. So I have lot more to share with all of you and let's learn that in the interactive manner. So first of all I am going to use certain terminologies as a part of my upcoming discussion. So that's the reason I have taken all these terminologies here. So we are going to gain an understanding and then we will be moving towards our demonstration. So let's start with the first one. What is data catalog? So if you are going to see this statement anywhere or if you are going to see this terminology anywhere you can say that data catalog is a central meta data repository means it stores information about your data not the data itself. Data will be present in the data store or actual data will be present in the data store. Data catalog will be storing the data definition. Okay. Next service which is a major service we are going to discuss that is Amazon Athena. What Athena is a very popular a very famous service with respect to the data analytics track. So it is a serverless interactive query service that lets you use standard SQL to analyze data directly in Amazon S3. means you no need to move or load the data anywhere. You can directly query the data from the S3 bucket. So from here you can say that your data is actually present in the S3. You are using Athena in order to query that data. But how we query that data soon we are going to understand and learn that. Okay. It uses restro under the hood and integrates with AWS glue data catalog for schema management. Now what is this particular term? So maybe if you have already worked on this term, you will be aware that it is a distributed SQL query engine and it is specifically designed for big data analytics everybody. So I have already taken this terminology also for all of you. And the last one which I will be discussing as a part of this particular discussion right now I'm not done yet. I have lot more to share with all of you as I had mentioned this in the starting only. Glue. Glue is a fully managed serverless data integration service that helps you discover your data, prepare and transform it and load it into data stores. Means if you have to give one one keyword for all these services. Data catalog is a centralized meta data repository. Amazon Athena is the serverless interactive query service. AWS glue is a fully managed data integration service. It is majorly used to design pipelines. And how we are going to use this service as a part of our demo. I'm soon going to help you out with that. So basically I have planned a demo for all of you in different ways. We are going to execute a direct queries. We are going to understand few features of AWS glue and we are also going to explore the Python code. Okay. So first thing let me help you out with this particular diagram. What exactly is happening from where we are starting? We know S3 act as a single source of truth or S3 act as a data lake where you can store your data in a raw format, in native format, in any format. Okay. So here S3 is the location or a data store where your actual data is present. When you are using Athena, okay, or when you are using Amazon Athena console, what exactly you are doing? you are running the SQL queries from the Amazon Athena and this glue data catalog is storing the schemas or the table definition. Okay, so this is exactly what is happening. Athena is referring the schema definition from the glue data catalog which is pointing to the data present in the S3 bucket. So actual storage location is S3 and then other services we are using for the data definition and quering it. Okay, let's start with a very simple demonstration first and then later I am going to take you through certain differences which I have taken for all of you means what can be different Athena scenarios when glue is not used with Athena what are the differences and finally the use cases where you can use different combinations or different scenarios of Athena okay let's start with the first demo everybody What is the first demo we are going to do? We are going to create a S3 bucket and we will be storing a data into S3 bucket. Let me help you out with that. So without any further delay, let's go to the AWS management console. Okay, let me close the additional tabs and I will be starting from here. Okay. So if you are starting for the first time and if you have already watched my previous video lectures, my starting point is console home. So search for S3 in the search bar and you will be navigated to the S3 dashboard. Now from the S3 dashboard what we are going to do our demonstration will be started with creating a S3 bucket. So click on create bucket everybody and I am going to create a general purpose bucket. We have already got a very good understanding on bucket types on different features of Amazon S3 as a part of our previous video lectures. Okay. Just give a globally unique name space. So for example I'm giving a name Athena demo DS bucket something like this. Okay, we can check if this bucket is existing or not. If this bucket could be existing, we will be appending some unique keywords at the end. Okay, test all things with respect to block public access setting. I'm keeping default because anyhow I am going to follow these steps in the same geographical location. So scroll down and say create bucket. Your bucket would be created. Let's wait for the bucket to be created everybody. So this is my Athena demo DS bucket. Now I am going to create a prefix here for the better management okay or for making the search and filteration easy. So click on create folder and let's say I'm going to give a name called sales data because I'm going to use a sample sales CSV file. Click on create folder. Now I had already explained to all of you this create folder is going to create a prefix is going to create a partitions which can be used in order to manage to filter your data easily and which is going to improve the performance at some point of time. Okay. Now I would be using a simple CSV file. I can just show you. Let's say I will be using the sales data dot CSV. The sales data docsv is containing all these information and I have other file also like this is sales dot csv. Okay. So any sample data we can use it search wise. Okay. Let me start with one of the CSV file. So I am going to upload the CSV file into the S3 bucket. Now we have already discussed that we can also use command line interface, management console or programming interface to interact with AWS services. And don't worry, I will be giving you the exposure to all the interfaces as a part of the demonstration. Okay. So let's quickly upload the data inside this particular folder. Okay. So you can do a drag and drop or you can browse for the file. Let me do a drag and drop everybody. Let's say I'm dragging this and dropping it here. It's going to upload a CSV file. So CSV file is uploaded here. Means first part is done. I have a S3 bucket. I have a sample data available in the S3 bucket. Now obviously for a demo purpose I have taken a small data set. You can have a large data set you can take. And just to give you exposure actually I'm going to take one more data set going forward. This is the one. If you want data set with more attributes I can also provide you a link like you can see it is having 266 records. Okay.