What is this article about?

In this article, we will start exploring AWS Athena,

You will learn how to create a query.

This article is mainly for BI developers who want to extend their capabilities to handle Big Data.

What is Athena?

Athena is a full, serverless service that gives you the power to make SQL-like queries on top of structured and semi-structured files in S3.

It is important to note that Data is not stored in Athena. Athena only query the data nut doesn’t store it.

If you would like to query files in s3, you should first create a table.

The difference between a Relational DB and Athena

Getting Started

First you need an AWS account for using its services.

** If you already have an Account skip to the next section.

1. Open your browser
2. Enter https://aws.amazon.com/
3. Click on “Create an AWS Account”
4. Enter your email and password
5. Click “Continue”
6. Enter your Contact Information
7. Check the box “Check here to indicate that you…”
8. Click “Create Account and Continue”
9. Enter your “Payment Information”
10. Click “Verify and Add”
11. Finish the “Confirm your identity” process
12. Select the Free Basic Plan
13. That is, you have an AWS Account
14. Sign into the Consol.

Second, you need to upload you file to S3

1. Go to S3 and click “Create Bucket”
2. Enter you Bucket Name and choose the region
3. Click the Next
4. Click “Create Bucket”

Now you should see your Bucket

1. Click your Bucket
2. Click “Create Folder”
3. Name you Folder “Financial_Example” and click “Save”
4. Upload the Financial_Example File to your new Folder
5. You can find the file here.

Creating a Table

1. Click on “Connect data source”
   
2. Check the “Query data in Amazon S3”, “AWS Glue data catalog”
3. Click Next
   
   And then you need to choose:
   
   1. Using AWS Glue on S3
      * Glue is an AWS service that creates automatic schemas based on files.* It’s a great way to automate things if you have been granted the right permissions.
   2. Create a table manually (generate a SQL-like script).
4. Choose the manual option and click “Next”, which will bring you to this screen:
   
5. Choose your database (or create a new one)
6. Choose a table name and the folder your table will be connected to (the Folder we created earlier) and click “Next”.
   * note the external bullet, which is selected, and can’t be changed.The reason for this is that we can’t insert data into Athena. Athena doesn’t save any data, only meta data. There is no insert clause in Athena. (There is, however, an insert into s3.)
7. Choose CSV/JSON and click “Next”:
   
8. Name your Columns and their Types (You can also add multiple columns as bulk.) and click “Next”
   
9. The last step is configuring partitions
   
   * It’s important to note that if you have a column that functions as the partition key, you should not include it in the columns, but instead only in the partition section.
10. Click “Create table”, and you your SQL Query is completed. 😊

Now, you should be able to see your table on the left panel and you can query it.

Another way to accomplish this is from the screen seen here below:

Important notes

* It’s important to know that Athena isn’t actually creating tables.

What it’s doing is creating schemas from tables, which enables us to design queries using the tables, despite the fact that the data is located in unrelated files.

* pay attention not to put files in your source folder, if they do not correspond to the schema you just defined.

Treat this folder as a place you insert only files that contain rows or objects with the same schema.

Next Post, we will dive into Athena with examples of query over CSV and JSON. Stay tuned!